Business Intelligence – Oracle

Archive for November 10th, 2007

Oracle BI EE 10.1.3.3/2 – Drill keys and its usage

Posted by Venkatakrishnan J on November 10, 2007

Now that i am back from my vacation, i thought i would blog about the usage of Drill keys in BI EE. It is very simple but not a lot of users know why and where it is actually used. In fact, when i was browsing through my emails today i had 2 questions that were more or less related to the drill keys. Also, there was another forum entry wherein Adrian had responded to a user’s question which was again related to the Drill Keys. So, i thought i would elucidate more on this with some screenshots. Lets try to understand this with an example. We will take a simple example of a GEOGRAPHY dimension. Following would be our levels

   Level1   –   Geography Total
      Level2   –   Region
         Level3   –   Sub-Region
            Level4   –   Country

As you see above, we would like users to drill from Geography to Region to Sub-Region and then to Country. Each level above can have multiple attributes(columns). For example. Region can have REGION_NAME and REGION_ID. So, before we move on, lets try to understand two concepts.

   DRILL FROM   –   What are the attributes/columns in each level that you would like to have the hyperlinks(for enabling drilling). For example, in Region level we have 2 attributes REGION_NAME and REGION_ID. So, whenever any user creates a report with either of these 2 columns we must have the drill down to country enabled to both these columns.

   DRILL TO   –   That is the target column that you would get after drilling. For example, when you drill down from Region to Country, what are the columns that you would like to see in the Country Level see after drilling down. It can be one or more columns.

So, all columns that are part of DRILL FROM will be included in the level as attributes. All those columns that are part of DRILL to will also be included in the level but will be part of Drill Keys. So, generally you would not have drill keys in the first level. Lets take the above said example and try to map the columns.

      

So, following are the drills possible in the above hierarchy.

1.   TOTAL_NAME   to   REGION_NAME

2.   REGION_ID, REGION_NAME, REGION_SOURCE_ID   to   SUBREGION_NAME

3.   SUBREGION_ID, SUBREGION_NAME, SUBREGION_SOURCE_ID   to   COUNTRY_NAME, COUNTRY_ID.

Also, note that Country level has composite level key. So, what this means it will bring 2 additional columns COUNTRY_NAME, COUNTRY_ID while drilling to Country level. Look at the below digram. When i click on Asia Subregion it will bring both the COUNTRY_NAME and COUNTRY_ID columns within ASIA. You can use Drill Keys to simulate multilevel drilling.

      

      

Advertisements

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