Drilling increases or decreases the display of report detail for dimensions with large amounts of level data. It can consist of drilling down (in many forms) or drilling up. Drilling down retrieves more detailed data within a dimension. You can drill down into more detailed data until the lowest level of a dimension as defined in the database outline is reached.
A before and after drill down example is shown below:
Before drilling:
After drilling:
In this example, for Oct, you can drill down to Audio and Visual. Drilling up is the opposite of drilling down, where you move up the hierarchy within a dimension to less detailed data. Query Options enables you to customize the behavior of the drill level. Shortcut menus also control the drill levels.
Note: | You cannot drill down on a Date Time Series member. |
To drill down to more detail:
Double-click the member to drill down using the default Drill Level as defined in Query Options.
Tip:
You can also select a member, and select Drill. then Down to drill down using the default drill level defined in Query Options on the shortcut menu.
Additional drill down options include:
Down—Drills down to more dimension detail using the default drill level defined in Query Options.
Up—Drills up to less dimension detail.
Next—Drills down to the children. This is the default Drill Level. For example, a drill on Year retrieves Qtr1, Qtr2, Qtr3, and Qtr4.
Bottom—Drills down to the lowest level of members in a dimension. For example, a drill on Year retrieves Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec.
All Descendants—Retrieves data for all descendants. For example, a drill on Year retrieves all quarterly and monthly members.
Siblings—Retrieves data for siblings. For example, a drill on Jan retrieves Jan, Feb, and Mar.
Same Level—Retrieves data for all members at the same level. For example, a drill on Sales might retrieve values for COGS, Marketing, Payroll, Misc, Opening Inventory, Additions, Ending Inventory, Margin %, and Profit %.
Same Generation—Retrieves data for all members of the same generation as the selected member or members. For example, a drill on Sales retrieves COGS, Marketing, Payroll, and Misc.
The sample below shows the drill through feature applied to a CubeQuery with multiple dimensions in the rows.
The initial CubeQuery section includes:
Rows
Jan, Feb, March
100 (children)
200 (children)
Columns
East (children)
100 (children)
200 (children)
Filters
Sales
The relational section includes:
The initial results of the relational query are:
The following topic mappings are defined for the drill through:
Table 48. Mapping of Relational topics to OLAP Dimensions for Drill Through
| Relational | OLAP |
|---|---|
| Details.State | Market |
| Details.Product | Product |
| Details.Month | Year |
When the context of New York is 100-10, and Jan. is passed in the CubeQuery, these results are displayed in the relational query:
You can drill down or drill up on a member that has a shared member defined in the Essbase outline. Essbase determines which members are eligible — the base member or the shared member, and returns drilled or stored members based on the drill path.
This is an Essbase outline:
Product
100
150 (stored member)
100-10
100-20
Brand1
150 (shared member)
The stored member 150 has children; the shared member 150 does not. Drilling up and down on 150 gives different results:
Drilling down on 150 returns nothing if it is interpreted as the shared member; or, returns 100-10 and 100-20 if it is interpreted as the regular member.
Drilling up on 150 returns Brand1 if it is interpreted as the shared member; or returns 100 if it is interpreted as the regular member.
The proximity of the shared member to the regular member gives different results when drilling down. When drilling up on a member that has a shared member, Analytic Server looks at the members to determine which one is being queried. For example, drilling up on 100-20 sometimes returns Diet and returns 100. depending on whether Diet or 100 is closest to the member 100-20. If 100-20 is alone, then Essbase determines that this is the regular member. If Diet is closer, then 100-20 may be interpreted as the shared member.