Business Intelligence – Oracle

Archive for October 24th, 2007

Oracle BI EE – Custom Drill Path Navigation

Posted by Venkatakrishnan J on October 24, 2007

One of the common questions that we typically get in the forums is how do we enable multiple custom drill paths. For example, check the forum entry here wherein the user wanted to have multiple drill paths. The user himself came up with an answer which i actually had forgotten about. Its a very interesting way of creating multiple drill paths. I thought i would blog about both the methods(including the one that i had suggested) as this might prove useful for others who want to have custom drill paths.

Lets first take an example. I will take the same example as was asked in the forum entry above. The rephrased question for the sake of simplicity is “How do we enable users to traverse the entire drill hierarchy? For example, if we have Year->Quarter->Month->Day, how can we make the users jump from Year to Month or even Year to Day directly”. Well, this is a pretty straightforward request but this capability is not available out of the box using OBI EE So, lets take the first approach of using column selector views. Lets start with creating a sample report with Brand, Region and Sales. We will include the Period dimension once this report has been created.


Lets pull in the Year column into the report and go into the Column Selector view. In this view add both Month and Week columns as shown below. We are basically creating a column selector view with Year, Month and Week columns so that we have the flexibility of selecting any one of them as they are part of the same hierarchy.


Include this column selector view into the compound layout. Now we have achieved the flexibility have both the custom drills and choosing any drill levels in a hierarchy. Its basically a column selector view implementation but used for select hierarchical attributes.


Lets look at the second method wherein for each drill path we will be creating a new report. Then we will be adding these reports as navigation targets for the Year column. So, when clicking on year we would basicaly have all the reports displayed as drill paths that we can choose to achieve the necessary drills. This method is not that flexible in the sense that one would have to keep on adding new reports for each and every drill path. For our example, in addition to the base report having Year, Region, Brand and Sales as columns, we would be creating 2 more reports. First report would have Year, Month, Region, Brand and Sales as columns to achieve Year->Month drill. Second report would have Year, Month, Week, Region, Brand and Sales as columns to achieve the Year->Month->Week drill. For both these reports create a criteria on Year with is prompted clause.


Once this is done. Go to the Year Column properties of the base report and add these 2 reports as the navigation targets.


So basically when we click on Year now we would get 2 drill paths having the report names that we just created.


Of course, there are also other methods depending on specific requirements. But the above should give a basic idea if you have a similar requirement.

Posted in All Posts, OBI EE Plus | 8 Comments »