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
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.
Posts
