A report that uses multiple facts may be prompted on dimensions that are not necessarily conforming to all the facts. At first one may think such a functionality is not valid. This posting demonstrates how such reporting requirements are common and are achievable in OBIEE though not in a very straightforward manner.
It is a basic OBIEE reporting concept that a report using metrics from more than one fact, requires that all the dimensional columns be conformed across the facts used in the report. In other words, it makes no sense to look at a side by side comparison of revenue and cost by product if the cost information is not available by product to start with. However, it is a valid question to ask how is revenue generated from certain products compared to the overall cost. Requirements like this usually have us facing the problem of developing a report that sources data from two facts: a revenue fact supporting a product dimension, and a cost fact that does not support the product dimension. At first one may be tempted to respond to the requester that a report like this is not possible since we are dealing with a “multiple facts and a non-conforming dimension” situation. But a closer look reveals that such requirements are completely valid from a functional perspective and therefore should be doable. The problem that remains though is that prompting a report on a non-conforming dimension will have OBIEE at a loss on how to aggregate a metric along a dimension it is not linked to.
Let’s take the example below of the 2 stars for Timecard and Capacity with their corresponding dimensions. Time Entry Status is a non-conforming dimension I want to create a prompt on.
What I am aiming at here is trend Reported Hours (From the Timecard Time and Labor fact) alongside Capacity Hours (from the Capacity Fact). I really wanted to report on more advanced Utilization and Availability metrics, but for the sake of this posting I will keep things simple. Following is an analysis using the Date dimension together with metrics from both facts. So far, everything is pretty straightforward since the Date Dimension is linked to both Reported Hours and Capacity Hours. Notice that while there is Capacity setup for 2014 Q 3, there are no Reported Hours for this quarter since no one entered a timecard yet. (I mention this here because I will come back to it a little later.)
Now, however, I get challenged when I attempt to add a report prompt on the Time Entry Status dimension. Notice that the Capacity metric is not populated anymore since Capacity Fact is not associated with the Time Entry Status Dimension.
First I tried to resolve this by adding an RPD aggregation rule on the Capacity metric against the Time Entry Status dimension at its top level.
OBIEE is now able to associate Capacity with Time Entry Status at high level even though it is not a truly conforming dimension. Below is what I get when do that:
Notice that while we now see Capacity populated, it is less when what was reported in the first report. In addition, Capacity is still missing for the months in “2014 Q 3”. This is happening because now that I tied the Capacity metric to the Time Entry Status Dimension, OBIEE is only fetching the Capacity if there are Reported Hours for the corresponding months. If you really care to list those missing months that have Capacity but no Reported Hours, keep reading.
My second approach of solving this involves using a dynamic repository variable to pass the Time Entry Status prompt selection to the RPD logical metric definition. (We don’t need to set the aggregation content of the Capacity fact against Time Entry Status with this approach.)
This is my dynamic session variable and corresponding initialization block defined in the RPD. This variable will be set and passed over from the prompt. By default, I initialized it to all the statuses in the dimensions table: W_TIME_ENTRY_STATUS_D.
This is my updated Reported Hours metric defined to filter on the above session variable.
This is what my prompt looks like now.
And here is what I get when prompting on Time Entry Status this time: all the months listing Reported Hours and Capacity. And of course Reported Hours is filtered by the session variable to only reflect the statuses I selected.
As a habit, I like to see what SQL query OBIEE is generating for my report. The session log shows the SQL querying separately from both the Timecard and Capacity Facts and grouping by month before combining the results for presentation.
