OBIEE 10G/11G - Drilling (Down and Through)

1 - About

Drilling is the action to discover the data along an hierarchy (ie through the different levels). In OBIEE, drilling is one way to navigate or interact within the data.

OBIEE allows two types of drilling:

  • drill down: from one level to lower level of the same hierarchy
  • drill through: form one level of a hierarchy to an other level from an other hierarchy. (For instance, from a Date Dimension level date to the Time Dimension level hour)

3 - Type of drill

3.1 - Down

3.1.1 - On Attribute column

When you define the hierarchy of a dimension in the BI Server, you will have a standard drill down functionality in the form of a web-link.

You can drill down from:

  • the row heading or column heading.
  • or from a member in an attribute column. Drilling on a member adds the lower level

Drilling on a

  • heading adds the lower level to the view.
  • member adds the lower level and change the selection of data. It affects both filters and selection steps (for 11g). A filter is added for the current member.

Example : If in a report, you have the Cust City column from the city level, you will have then a link attached which permit you to add the columns :

  • CustId,
  • Cust First Name
  • and Cust Last Name.

To have this behaviour, you have to configure your dimension as the picture below.

The level key to display is set by selecting the Use for drill down check box on the Level Key dialog box.

The yellow key is a drill down key to choose the columns that you want to add when you drill.

The green key is the unique key of the level, is set as primary key to be used in order to calculate the Number of Elements for the level.

3.1.2 - On Hierarchical column

On Hierarchical columns, just expand the members. Expanding and collapsing does not change the selection of data for the column.

3.2 - Through

Drill Through is referred in the Student Guide as “Setting the Preferred Drill Path”. Preferred Drill Path is most commonly used to drill from one dimension to another.

4 - Note

4.1 - Within an expression

When you use more than an attribute column of a hierarchy in an expression then OBI will drill in the lowest level.

For instance, with the below expression, even if you have defined a quarter level between the year and month level, you will drill down in the month level.

CASE '@{TimeLevel}{Year}'
WHEN 'Year' then "Date Dimension"."Year" 
ELSE  "Date Dimension"."Month" 

because the month level is the lowest level of the expression.

5 - Documentation / Reference

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap