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!

Advertisement