Oracle BI EE 10.1.3.4.1 – Reporting on Non-Transactional Dimension values – Equivalence of Outer Joins

As with any reporting tool BI EE works on the premise of converting the front-end column selection to the corresponding physical queries. But one of the advantages that makes BI EE stand apart from other reporting toolsets is its ability to do certain joins/sorting etc in-memory without pushing them down on to the database. This feature has multi-fold advantages. Especially in BI Applications this feature is put into good use by having a single repository that can report across multiple ERP modules using conforming dimensions. One other advantage of this is the fact that it can help in generating reports that produce all dimension members even if those members do not have a transaction.

For example, whenever a dimension and a fact measure are pulled into a report, BI EE will convert them into a physical join between the dimension and the fact measure. So, in effect only those dimension members that have a corresponding fact transaction would come out in the report. In most cases, end users might want to see all the dimension members. This is illustrated better by the screenshots below

As you see, even though we have 5 different Channels, since our fact table had only transactions for 3 channels, our report produces data only for 3 Channel Members. But what if the end users want to see all the 5 members in the report (non-transactional channel members would be shown as Null in the measure). The most common resolution for this is to use outer joins in the physical layer. But this cannot be done in all the cases as when one applies any filter on the fact measures, the outer join effect is negated. Also, there are possible performance impacts in using outer joins. So, lets look at a modeling technique that will negate the use of outer joins.

The basic idea is to simulate a Cartesian join across dimensions and then make the BI Server to do the conforming dimensions join using a separate dummy fact table. So, to start with create a Dummy fact table in the Physical layer which will be based on a Select (Select 1 FROM DUAL or something like that)

Now make every dimension in the warehouse to have a Cartesian join with this dummy fact table.

Now create a new Logical Fact table source containing this fact table and the Dummy Fact column. Ensure that the fact column has an aggregation set (same aggregation as the other measures in the original fact table)

Now, include this fact along with other facts whenever you want an outer join but with filters on the outer joined fact table. Just hide the dummy from display.

Now, sometimes end users might want to browse multiple dimensions simultaneously. Since there is no join across dimensions, this would result in not producing all dimension member combinations. This is illustrated below

As you see, we only get those channels for which we have transactions on the fact table. In many cases we do not want dimension member values to be dependent on the fact table. To negate this, we would have to use the same dummy fact table that we used above and put that as the implicit fact table as shown below.

Now, if you view the same report again

This is because, the Implicit Fact column makes the BI Server to choose the Dummy Fact as the fact table for joining the dimensions and hence would result in a Cartesian product across the dimensions.