Outer Join LTS Pruning… It’s Here!

September 9th, 2013 by

A few months ago Charles Elliott and I were tasked with assessing OBIEE performance for a client here in the US. Queries were taking hours to run (literally) and obviously users weren’t happy. In the context of BI, there are many places where performance can be improved (database tuning, data warehouse modeling, query writing, etc.) but we decided to start with the RPD.

 One of our resources at this client pointed to us that every query they ran used a database view (no, not a materialized view) which in turn sourced a handful of physical tables. These tables were all joined in the view using outer joins. So, every single query the BI Server ran, had to go through all the joins contained in this view, even if it didn’t need data from all the tables in question. We called this the “Inception Effect” (remember the movie?) because you always had to go 5 levels deep, and each one took longer than the last.

In this post, I want to demonstrate this problem and how we can “trick” the BI Server into behaving the way we want.

IMPORTANT: Please keep in mind that for this first example, we are using version 11.1.1.6.0. This detail will be very relevant later.

The Problem

For this test, we’re obviously not using our client’s data. We will be working with data from the 2010 FIFA World Cup, and our tables are GOALS, PLAYERS and MATCHES. The GOALS table will be the source for both the logical dimension and fact, while PLAYERS and MATCHES will also provide additional information for our logical dimension. This is our physical model:

LTS Pruning 1

In our (very simple) business model, we have a fact table called Fact: Goals which uses the GOALS physical table as its only source:

LTS Pruning 2

And we also have a dimension table called Dim: GPM, which has one LTS with all three physical tables as sources (the columns in this table were named for easy identification of the source table). Note the join types for this logical table source:

LTS Pruning 3

So, let’s see what this looks like when we create a very simple query in Answers.

LTS Pruning 4

Note that we have a column from the GOALS table, one from the PLAYERS table and one from our fact. As expected, our query should include one outer join (remember that the LTS for our dimension included a couple of outer joins), but upon closer examination, we see that it actually includes both outer joins, even though it didn’t need to include the MATCHES table.

LTS Pruning 5

Furthermore, if we remove the ‘Players – Name’ column as well (leaving only columns that come from the GOALS table), the two outer joins still show up:

LTS Pruning 6

You may be wondering: Why do I care, as long as the result is still correct? Well, in our client assessment this was extremely important, because all the outer joins included in the queries all the time were causing them to run much slower than they should.

The Solution

The solution in these situations is to “trick” the BI Server into using the outer-joined logical table sources only when needed. It requires a little more development time, but in the end it is well worth it. Let’s take a look at what our model looked like with this approach and what the results were.

LTS Pruning 7

As you can guess by the LTS names, the GOALS LTS includes only the GOALS table as its physical source, the GOALS_PLAYERS LTS includes the GOALS and PLAYERS tables, while the GOALS_PLAYERS_MATCHES LTS includes all three of them. The order of the logical table sources matters, as you want you first choice on top, and your last choice at the bottom. Alternatively, you can use Priority Groups to determine the order in which the BI Server is going to try and use each LTS based on the query criteria. Let’s see if it actually works.

In our multiple LTS test, I added an extra column to our model called ‘LTS ID’ that will help us identify in Answers the LTS being used for each query.

So this is our first result set:

 LTS Pruning 8

This query includes only columns from the GOALS table, plus our LTS ID column, which shows the LTS being used. The important thing, however, is that when we look at the SQL issued by the BI Server, we see that no outer joins were included, indicating once more that the BI Server was smart enough to choose the most efficient LTS for this query.

LTS Pruning 9

If we include a column from the PLAYERS table to our request, this is what we see:

LTS Pruning 10

And the SQL validates our theory, including only one outer join:

LTS Pruning 11

Lastly, if we add a column from the MATCHES table to the request:

 LTS Pruning 12

Our SQL should include both outer joins, as it is using the LTS seen in the request, with all 3 tables:

LTS Pruning 13

The New World Order

Recently, we heard a rumor from Oracle Product Management that with the 11.1.1.7 release of OBIEE, the BI Server would be a little smarter around pruning outer-joined logical table sources. We decided to put it to the test, and the results were encouraging.

We went back to the one LTS idea:

LTS Pruning 14

But in this case, if the theory is correct, the BI Server should be able to prune the unnecessary tables despite the outer joins.

When we run a request in Answers with columns from all three tables:

LTS Pruning 15

The BI Server will issue both outer joins in the query as expected:

LTS Pruning 16

However, if we remove the ‘Matches – Venue’ column:

LTS Pruning 17

The BI Server will remove the MATCHES table from the query, therefore eliminating one of the outer joins:

LTS Pruning 18

And finally, if we leave only columns from the GOALS table in our query, no outer joins are included:

LTS Pruning 19

And all this, using one single LTS in 11.1.1.7.0.

Now, I’m not claiming that the newer versions of OBIEE will be able to prune every unnecessary outer join in every single situation. This was a simple example, with only a few tables, but it does show that Oracle has been working on this. And that’s very good news if you are a RPD developer.

Until next time!

Comments

  1. Giuseppe Lolli Says:

    You are absolutely right but this was the standard way OBIEE was supposed to work until 11.1.1.7.0 were released.
    If you join stuff in the LTS, the server will ALWAYS JOIN what you specify in the joins section!!!!

    I had an experience to a client where I did an upgrade from 10g to 11g.
    They had a time dimension configured as snowflake having dim time as follows:
    year–quarter–month–week
    and different fact tables joined at different time granularity levels.
    Ie. sales was joined with month table.

    They had (wrongly) implemented a unique LTS joining all the tables, hence the server was always joining the entire flake!
    In the example, sales were multiplied by 4 or 5 (depending on the number of week existing in each month)because always joining ALSO the week table!
    And they had never noticed that because never used those reports :)

    I fixed this doing exactly what you describe and now it works charm, but my upgrade was from 10g to to 11.1.1.6.8.
    Only in a second moment we moved to 11.1.1.7.1.
    If I knew that from 7.0 onwards the server included such improvement I wouldn’t have bored my self splitting and remapping the LTSs!!!!!!

    regards
    Giuseppe Lolli

  2. Sachin Says:

    I would be very interested to know on how you were able to display the LTS name ? What is the expresion/logic behind column LTS ID ?

  3. Stewart Bryson Says:

    Sachin:

    Just create a logical column in the logical table that’s called “Source LTS” or “LTS ID”, or whatever. Then, in each of the LTS’s, hardcode a different value for that logical column.

  4. amit gross Says:

    in your solution the 3 tables were put as sources in the GPM logical table , therfore, logical joins were not needed in the bussiness model layer.
    the only join info it has is the FK from the physical layer.
    how did the OBIEE know to issue a left outer join?

  5. Christian Says:

    Ah, one of the finer points of logical modelling and “why is which join enforced”. There always was (and is) quite some misunderstanding out there with regards to this question.

    This is definitely to be filed under the category “under-communcated features/changes”!

  6. Andy Rocha Says:

    Giuseppe: In my experience, in previous 11g versions of OBIEE, the BI Server was able to prune unnecessary LTS joins, if they were inner joins. The BI Server inability to prune tables was mostly found with outer joins. But you are correct, this is a great improvement for 11.1.1.7.

    Sachin: Stewart is correct. This is a very simple way of validating LTS usage.

    Christian: I totally agree, and I find that LTS modeling is one of the most difficult topics for folks to understand in regards to the RPD. It is of huge importance, though.

  7. Christian Says:

    Difficult to understand maybe, but without this it’s quite pointless to start modelling / optimizing.

    Stewarts hint towards hardcoded columns for value-based LTS switching would be another one.

  8. Andy Rocha Says:

    Christian: I totally agree. LTS modeling is crucial to performance tuning in OBIEE.

    Amit: If I understood correctly, you are asking how to change the join type in the LTS to outer, right? In the General tab of the LTS, locate the Joins area and click the Type column. A drop-down will appear with the available types.

  9. Sarathi Says:

    Nice article. Keep the good work continue

  10. Chadical Says:

    I’m bragging. I was “the guy” on “that con call” that raised this issue on a high profile internal project. Glad to see others are stoked on the improvements in the LTS pruning algorithms :-)

  11. Maria Says:

    Hi! Great post, thanx a lot!

    I think Oracle implemented the improvement even earlier that 11.1.1.7.0 because our prod is on 11.1.1.6.7 and we don’t have this problem actually.

  12. Thomas Says:

    I just wonder, how many RPD’s are out there “making use” of the 10g feature that every join in a given LTS is always being executed (i.e. in general the trick with a coverage fact table, but I also have one or two other customers situations in mind where I did right outer joins and and NVL around the left table).
    Is there a checkbox to revert to the old behaviour (best per each single LTS)?

  13. Prakash Says:

    Great stuff. I am facing similar situation. This will help be resolve ti.

Website Design & Build: tymedia.co.uk