Oracle BI EE 10.1.3.4.1 – Drills without Automatic Filters – Discoverer Style Drills – Repository Design
July 20th, 2009 by Venkatakrishnan J
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.
July 20th, 2009 at 9:53 pm
Isn’t the same functionality available by clicking column headings?
July 21st, 2009 at 5:11 am
Ram – Actually Yes. I should have mentioned this. The main reason for me to introduce this is to have a custom drill path and one would have the flexibility to do a individual row based drills with or without filter. For example, one can group the Product Categories into 2 groups. Drilling on one group will apply filters only specific to those categories. Something like Answers Bins but with more functionality. I will try to cover this as well.
July 22nd, 2009 at 8:29 am
Hi Venkat,
Could you please briefly explain how to drilldown without automatic filters with “Column heading” changes .I have been struggling hard to get this done.
Thx
Vinod
July 23rd, 2009 at 3:58 pm
Hi Venkat,
Is there any way it could work in pivot table and display levels in order way – according to their hierarchy? Your workaround does work only if dummy columns are placed as first in table. It doesn’t work if there is following set A / Drill to B / B / Drill to C, because it filters on A,B and so on.
You would get the same effect, if you created dummy TOTAL level and displayed it first in table. Clicking on dummy-total displays consequent levels, also according to drillpath. Thus there is drawback – again levels are not ordered.
Marcin
July 24th, 2009 at 6:40 am
@Marcin – Your concern is very valid. The main advantage of the above technique is in the pivot tables as one cannot use the Column Header drills there. The idea in pivot tables(even in normal table view) is, you keep clicking on the same Level1 custom Drill column and not the next level Drill column. Then it would not apply the filters. The column order is necessary as i said above, only for the Level1 custom column that we created. It always has to be first.
August 20th, 2009 at 5:56 am
is it possible to get a drill to expand to 2 columns using the RPD? I know we can do it with a Navigate in a column on an Answers report but how about automatically?