Oracle Analytics: How to report over non-conforming fact tables

A few months ago, I attended the inaugural 'Rittman Mead Training' course in London. It was a great chance to learn about the latest version of the Oracle BI tool set and to meet and discuss concepts - with some experts in their field.
During one of the discussions, the problem of reporting over multiple facts with non-conforming dimensions was brought up and I thought it would be worthwhile to discuss how we came up with a solution here.

Scenario:
The business wants to write an Answers request that has dimensions and measures from two different logical entities that share some but not all the same dimensions.
In addition, they want to have the ability to filter on some of the non-common dimensions.

We have a large logical star called 'Loan Account' which models accounts held by the business, that has a huge amount of dimensions:


In addition, we have a smaller star, that models the targets that the company has for loan accounts, used for bonuses for employees and budgeting:


As you can see from the two logical stars, they have some common dimensionality. However, the Loan account fact has dimensions that don't make sense in the targets fact.

If you wanted to write an Answers request that utilized measures from both facts, the BI server will not throw up an error.
The BI server will try to federate the queries but won't understand how to link the two - its solution is to 'Cast as Null' the non conforming measure. The request doesn't fail, it will issue two sql statements but as it can't determine the level for the non-confirming measures - it plays safe and nulls them.

Solution:
We need to trick the BI server into assuming that the non-conforming dimension fact table is actually conforming.

The fact table that has the lowest dimensionality will have the measures usually fail - so the first consideration is to decide to modify all the measures or just those that are frequently used in requests. In addition, you need to know the dimensions that are going to be used in the requests.

In this example, as target measures are used in pre-built requests, the development team know which measures need to be modified; we know which ones will be used and the dimensions that will be used.

Assuming that all the 'normal' logical build has been done, for those measure that need to conform - go to the level tab within the logical column:


For each and every dimension, that the measure need to conform to, set the Logical Level to the highest level in the Dimensional Hierarchy (e.g Total or All).


Now, when you run the request. The BI server can work out how to federated the two sql statements.

This will work in Siebel Analytics (7.8.2) and should work in newer iterations of OBIEE. If anyone, how done something similar on OBIEE let me know.



....LiS wanders off!