Next on the list… cross drilling. In dimensional modeling terms, we refer to this as “drilling across”… as in “drilling across fact tables.” We do this when we need metrics from two or more fact tables in the same analysis. This is easy when the analysis is using all conformed dimensional attributes. In most cases, the requirement is for combining these metrics with conformed and non-conformed dimension attributes.
In OBIEE, we typically solve this by making level-based measures (LBMs) for the fact measures to the nonconformed dimensions at the total/all level. This allows users to analyze measures across different fact tables using combinations of conformed and non-conformed dimensional attributes. What happens behind the scene is the following:
- A query is issued for each fact table, aggregating them to the common level of the other fact tables.
- The resulting datasets of each fact table query from #1 above are merged/joined based on the conformed dimensional attributes using a full outer join.
This is the correct behavior and solution for solving cross drilling, which OBIEE has been doing, well… forever. However, 11.1.1.7.1 has changed this behavior: the full outer join has disappeared. Instead, the BI Server issues a left outer join. This alters all the analyses that have been written that required cross drilling and may produce different results. So what happened?
I first noticed this with a client that had upgraded from 11.1.1.5 to 11.1.1.7.x, and I was initially puzzled. Upon investigating, I found support document Doc ID 1601703.1. As per that document, “There was a change in behavior in 11.1.1.7.1. The issue matches this change. Basically there was an inconsistency in how filters were working with grand totals and subtotals. As a result, full outer joins were replaced with left outer joins.”
I guess it can work with left outer joins; but, more than likely this solution will not give you what you want as a result. Before I describe the solution to re-introduce full outer joins, you may want to note which table is the “left” in the left outer join. From my experiments, the table with the most conformed dimensions seems to be the one on the left. So if fact table A has the most conformity, then the SQL would be A LEFT OUTER JOIN B ON (…).
The question then is how do I get back to the FULL OUTER join. The answer lies in the session variable FOJ_GRAND_TOTAL_LBM: with “FOJ” standing for FULL OUTER JOIN. As the Oracle doc states there are 2 ways to resolve: REPORT ONLY or INIT BLOCK approach. The REPORT ONLY solution will only solve this for an individual report, and you would have to make changes to each report in which you wanted the full outer join to be used. The INIT BLOCK solution will make this change global.
REPORT ONLY
In the advanced tab of the report, enter the following within the PREFIX box:
SET VARIABLE FOJ_GRAND_TOTAL_LBM=1;
INIT BLOCK
- Create a new session variable in the RPD: FOJ_GRAND_TOTAL_LBM
- Set the default value of this session variable: 1
- Under Initialization Block, click (This is to create the dummy init block)
- Set the init block’s name: SET_FOJ_GRAND_TOTAL_LBM
- Within the init block, click the Disabled check box.
- Click OK, within init block.
- Click OK, within the session variable.
(Note: the default initializer is all that is needed to set the value; but, all session variables need a init block assigned. Thus the need to create the disabled init block)
