Making Dims & Facts Work Together

This is a very common RPD modeling question on IT Toolbox – it comes up every week it seems.  The problem is stated something like this:

I have 2 fact tables and 3 dimension tables.  One of the dimension tables doesn’t work with Fact #2 while the other 2 dimension tables work with both facts.  When I make a report with all 3 dimensions and both facts, Fact #2 is incorrect or missing.

Let’s restate the problem a bit using good BI language.  2 of the dimensions are conformed across both of your fact tables, but one is non-conformed.  Of course conformed is a fancy word meaning can be used across multiple facts, also known as shared.  So the question then becomes simply: How do I make a non-conformed dimension work?

I addressed this topic in a recent presentation, so I’m simply going to use some of those slides and annotate a bit more.  In fact, I will discuss two different solutions that you can have in your toolkit.  As is usual with me, its not the example so much but the concepts behind the example.

So when OBI gives you the wrong answer, in this case it’s because you never told it how to answer it. (its your fault!)  OBI needs to know how to handle Order Amt by SR Status as right now it doesn’t.  We will have to give it a little bit more smarts to do this.

With this solution we are essentially tricking OBI into thinking there is a real join between them and the dimension is perfectly conformed.  Of course with every trick there can be consequences:

If you look into the 1st query (the one we’re interested in), you’ll see that a filter was indeed applied to the dimension table.  Unfortunately for this example the dimension table also happens to be a fact table (aka a Combo table).  In a normal star model, this really wouldn’t present much of a problem, as the W_SR_STATUS_D table might have only 1 record in it with the value of ‘Open’.  However in many cases this will not be true; with a fact table it is certainly not true.  We will have many records that are Open, and each will return from its portion of the SQL.  Note also that the W_SR_F doesn’t join to any other of the tables aside from W_Customer_D.  What is going to happen most likely in this query is it will be over-counted due to these multiple records coming back and being joined with the rest of the query.  Although indeed that is a bad scenario, keep in mind your specific setup.  If you can guarantee that only record will come back all of the time, then this technique works quite well.

A different solution is shown below, one with less trickery but more effort:

Here we are solving the problem by using Level Based Metrics.  Specifically, when we set a field to the Totals level, we are telling OBI to ignore that dimension when considering this fact.  In a weird sense, Total = All = None at All!  One reminder: setting totals level on the Content tab does absolutely nothing – it is assumed to be at totals level if left blank.  Unfortunately we have to do the config on the metrics themselves and not globally on the LTS.

The big downside to this technique is that you have to do it for every metric that you might possibly conform.  This can be a lot of work, easy to make mistakes, and difficult as all heck to QA.  This is the reason I don’t like this approach – the first one is easier but potentially dangerous or not even possible.  Lets look a bit further:

When you run it now, things look a bit better.  Note anything you do with the SR Status dimension will be totally ignored by the Orders half of the query, so you may need additional filtering to clean the report up to your liking.

To summarize:

I use both of these techniques frequently.  Sometimes in the same Business Model.  I’ve even had times where I converted from one to another.

Best of luck!


Posted on June 24, 2011, in OBI EE Development, Repository and tagged , , , , , , . Bookmark the permalink. 10 Comments.

  1. This was very helpful. It got me over the hump. It seems odd that OBIEE doesn’t make it very easy to do this. Seems that cross star joins would be a common requirement.
    Maybe 11g solves this issue? I’ve read that you can build an Answers query that goes against more than one subject area.

    • About 11G – you still need to make the Business Model work regardless of how you organize your subject areas. And yes I agree it is ponderous to have to configure this manually – it should know how to make it work without being explicit.

  2. Here I give issue in summary..

    Fact tables F1 and F1 and dimensions D1 and D2, and D1 is conformed dimension, D2 is joined with F2…

    Report is built on columns from F1, F2 and D1 and D2…and In report there is a filter on column from D2 which is no confirmed..
    All settings have been done on F2 columns set at grand total level of D1 so obiee will ignore join F2-D1
    Report is running fine and results are coming but few records are missing..
    physical sqls if we see there is a sql for f1,d1,d2 say 100 records.. and other for f2-d2 say 150 records.
    In the report it’s displaying only 100 records for f1,d1,d2,f2 and missing 50 records from f2-d2..if I see sql its doing left outer join of two sqls dimension from F1-f2..How can i achieve FULL outer join and to get the report of 150 records,
    This is issue is coming only when there is a filter on d2 column which is not confirmed dimensions..if remove filter on dimension it fires two separate sqls for each fact please advise,,thanks

    • You might have it backwards – the settings should be on F1 for D2 @ Totals level. You need to tell OBI to basically ignore anything on D2 when dealing with F1 facts. Your comment says “ignore F2-D1”, but you also say D1 is conformed – you don’t want to ignore the conformed dimensions.
      Jeff M.

  3. Sorry..I was saying other way around..

    To be more specific, Dims : Time and Customers & Facts: Sales and Inventory

    Sales is joined with Time and Customers
    Inventory is joined only with Customers
    measure columns in inventory are set at grand total level of time to ignore join with time dim.
    Report is as follows:
    Customer, Sales, Inventory filtered by Year and report is showing up only those customers who have sales in particular year filtered and Inventory too will show up for only those customers.
    But there are could be some customers which don’t have sales in that particulate year but have inventory as inventory is not joined to time..but those are ignored..
    Reason: obiee is firing single query with two sub queries for each fact and doing left outer join on dimension customer from sales on inventory…and this only happening when there is a filter on year(Time dime) which is non conformed
    If i remove year is fine..and we can see two sqls separately for each fact: sales and inventory and it will do full outer join in memory..

    any suggestions?

  4. Thanks Jeff, I have gone through your slides and tried the other method by joining in physical layer(1=1) and checked the result .
    God thing is I’m able to see all records from both facts, bad news is results for F2- Inventory are giving high values and checked the sqls it’s joined with Time with out join condition hence it’s doing cross/Cartesian join. And this F2-Inventory doesn’t satisfy the condition One record for filter.

    If I use grand total on columns its giving correct results but excluding few records which are not common to F1 fact.
    Is there any way to get full outer join records from two facts when there is a filter on non confirmed dimension.

    P.S: I tried to other scenario with physical join (1=1) and able to get correct results from both facts with full outer in memory by two different sqls to each. But here fact table satisfies the condition ‘ it has only record for filter.

    I’m exploring the union of reports to achieve this…I’m hoping the best.:-)

  5. Hi Jeff, Kumar,
    im looking at a very similar problem as yours Kumar.
    what i dont quite understand is why you need to set the total level of the non conformed dimension on each metric in the fact. i would think setting the total level for the dimension on the fact LTS would have the same effect on all the metrics in the fact. what is the purpose of setting the levels on the LTS if you still need to set the level on each metric?

    my example is with one fact being inventory_forecast at the month level and the other being current_inventory which really doesnt join to the date dimension.
    one thing i have done to try to sort out this issue is populate the load date of the inventory table, and map the inventory table to the date dimension at the day level. but the problem of having the forecast metrics showing as 0 occurs… until i set the level of the date dimension to total specifically on the forecast metric.

    but like i asked, why do i need to do this on each metric when i have set this on the fact LTS for the date dimension?

  6. additionally, both of these tables are combo tables. so i have also created a dimension for each. it is the setting of the levels of the current_inventory_dimension on the forecast_fact metric that worked

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: