Sometimes we need to combine measures from two fact tables in the same report and apply a filter to one of them. If the filter is on a dimension shared by both fact tables; that’s great. However, if the dimension is only related to one of the facts, then we have a problem: Oracle BI would show null values for a measure that’s not related to the dimension.
Imagine that we have two fact tables: Revenue and Budget. Revenue is recorded for Product but also contains information about General Ledger (GL) Account. Budget is defined for Product but not for GL Account so there’s no relationship between them. Our business model is shown in the image below. Of course in a normal repository there would be more dimensions and facts but they have been left out to clarify the example.
Sample dimensions and facts
We can show Revenue and Budgeted Revenue in the same report without any issues (as shown in the screenshot below). OBIEE simply queries both fact tables. The product dimension is not a problem as it’s shared between Revenue and Budget facts.
Report without filters
But if we need to apply a filter on the GL Account we won’t be able to see any values for Budgeted Revenue. That’s because OBI recognizes that Budget fact is not related to GL Account dimension and therefore shows null values (empty cells) as in the screenshot below. You can see that Revenue has been filtered.
Budgeted Revenue with null values
Proposed Solution
To overcome this we should define the measures in the Budget fact on the grand total level in the GL Account dimension. We can do this in the OBI repository in the properties of a measure. In the properties of a column just go to the last tab Levels. Be aware that we’re changing the properties of the measure Budgeted Revenue – not the properties of the logical table source.
Have a look on the image below. We’re setting Budgeted Revenue measure on the grand total level in the dimension GL Account. If you think about it, defining a measure on the total level of a dimension is like saying the measure is not defined at all in that dimension. It does however make a difference in OBI because now this measure will show up even if GL Account dimension is included in the report.
Defining Budgeted Revenue measure on GL Account dimension
After the change we are able to see the Budgeted Revenue because we’ve defined it on the grand total level of GL Account dimension. The filter in the product dimension was never an issue since both facts are related to that dimension. See how Revenue now has a filter? Budgeted Revenue appears in the request but isn’t affected by the filter. Defining the measures on the grand total level solves the issue of having a filter on a dimension that’s not related to the measure. If you try this, let me know how it goes!
Final results of combining measure with filter on GL Account dimension
Before closing, I should mention that having an attribute from a dimension that’s not related doesn’t make that much sense. For example if we added GL Account Number in the report we’d see the same Budgeted Revenue repeated for each GL account number. It would also sum up incorrectly as you can see in the screenshot.
Report with GL Account Number added
This can be solved with aggregation based on dimensions – but clearly that deserves a separate blog entry!
Posts
