Business Intelligence – Oracle

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

8 Responses to “Oracle BI EE 10.1.3.3/2 – Drill keys and its usage”

  1. Vivek said

    Very well explained!
    Thank you.

    Vivek.

  2. Kurt Wolff said

    Attributes at any given level will drill to the level key at that level. So, in the example above, REGION_ID (not a level key) would drill to REGION_NAME (the level key at the Region level).

  3. Venkatakrishnan J said

    Thats a very good point Kurt. Thanks for mentioning that.

    -Venkat

  4. Giorgio said

    Is it possible to define the order in which the columns appear when we use a composite level key? For example could we define somehow that when I drill down to country I want to see the country_name column first and then the country_id.

  5. Vikram Takkar said

    HI Venkat,

    I have one question if say i have at one level i have 4 attributes(columns) e.g.

    — User Level
    First Name
    Region
    Last Name
    Team

    Key at this Level is composite key containing all the attributs.So whenever there is drill to this User Level report will show all the four columns right.

    But my question is i want to know how can i given the sort order in ther report to show.

    e..g if i want to show the columns in this order at drill down (First Name) (Last Name) (Team) (Region).How can i achieve this..?

  6. Arundati said

    Kurt said:
    Attributes at any given level will drill to the level key at that level. So, in the example above, REGION_ID (not a level key) would drill to REGION_NAME (the level key at the Region level).

    My customer does not like this behavior. How can we make sure that this does not happen or REGION_ID is not drillable?

  7. Alex said

    If you have a composite key in a level (ex: COD_CITY, DESC_CITY), after drill down to this level, Dashboard shows the fields in an unpredictable order.
    Do you know hoh to force the order?

  8. […] Oracle BI EE 10.1.3.3/2 – Drill keys and its usage page_revision: 2, last_edited: 1241681429|%e %b %Y, %H:%M %Z (%O ago) edittags history files print site tools+ options edit sections append backlinks view source parent block rename delete help | terms of service | privacy | report a bug | flag as objectionable Hosted by Wikidot.com — get your free wiki now! Unless stated otherwise Content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License Click here to edit contents of this page. Click here to toggle editing of individual sections of the page (if possible). Watch headings for an “edit” link when available. Append content without editing the whole page source. Check out how this page has evolved in the past. If you want to discuss contents of this page – this is the easiest way to do it. View and manage file attachments for this page. A few useful tools to manage this Site. See pages that link to and include this page. Change the name (also URL address, possibly the category) of the page. View wiki source for this page without editing. View/set parent page (used for creating breadcrumbs and structured layout). Notify administrators if there is objectionable content in this page. Something does not work as expected? Find out what you can do. General Wikidot.com documentation and help section. Wikidot.com Terms of Service – what you can, what you should not etc. Wikidot.com Privacy Policy. _uff = false; _uacct = “UA-68540-5″; _udn=”wikidot.com”; urchinTracker(); _qoptions={ qacct:”p-edL3gsnUjJzw-” }; […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: