One of the questions that i was asked recently is a means for doing discoverer style reporting in BI EE. As you might probably know, one of the major drawbacks of BI EE (atleast untill 11g comes out) is the fact that during drills we lose the information about other contextual values. For example, if you look at the screenshot below, we have a normal Product hierarchy setup within the repository.
So, when we drill from Product Category to Product Sub-category, BI EE automatically will apply a filter on the contextual drilled Product category. For example, drilling on Electronics will apply a filter on Electronics to the report.
What if we want a capability wherein drilling on Product Category level will take us to the next level without filtering the report itself. And what if end users want both the capabilities in a single report. This kind of capability can be provided to an extent by a very simple repository design technique.
To start with, our goal is to have 2 columns for each level. One column will provide normal BI EE drills and the other will provide a drill without filter. The question is how do we bypass the filtering of a report as that is something we cannot control from the UI (without using Navigation). In order to negate the effects of filtering, we need to introduce a new column for every level in the dimension which will have a constant value as shown below
Now, include these new columns in the dimension hierarchy as shown below.
Ensure that the new columns introduced are part of the drill key with drill enabled.
The idea is now, every level will have 2 options. If we drill on the actual product column name, we would get normal drills. And if we drill on the custom columns, we would get the drills without contextual filter.
When we click on Drill Level 1, we would get the new level without any filters (remember the position of the columns above is important). Actually there would be a filter, but that is a filter equivalent to 1=1.
But if you click on the Product Category, you would still be having the normal drills.
The major advantage with this is the fact that you can mix and match the drills as you traverse the hierarchy. This would be very useful for analysts who want to compare the filtered contextual data with an un-filtered contextual data.
Posts
