Despite the benefits of the multidimensional database for storing analytic data, some data elements required for analysis are better suited to the relational structure of a relational database. The scope of data residing in an Essbase database is typically at a summary level, where data is summarized and calculated for planning and analysis. Detailed, transactional data usually is not examined during the planning and analysis of a business.
For example, say you are using Essbase to analyze retail sales for the first quarter in the Eastern region. Detailed data, such as a list of customers who purchased a particular product in a particular size, is not used during the normal course of analyzing business performance. However, as you analyze sales results, you may want to view more detailed information. Drill-through is a tool that enables you to drill from the summarized and calculated data stored in the Essbase server of your organization into detailed data stored in a relational database.
The database administrator predefines a data mapping for you from Essbase to the relational source. For example, the Essbase members East, West, South, and Central might map to a field called Region in a relational database. As you navigate through data in the spreadsheet, Essbase knows how the current data maps to the relational source. For example, suppose you select cell G4 in the following sheet:
Figure 171. Example of Drill-Through Sheet
The dimensional attributes of the cell are as follows: Actual, Profit, New York, Feb, and Product. The combination of one or more of these attributes becomes the basis of a drill-through query that returns data from the relational source.
From Essbase Spreadsheet Add-in, you can access a predefined drill-through report that is based on the dimension or member intersections of Essbase data cells in the sheet. Using an Essbase Integration Services tool called Integration Services Console, an administrator at your organization sets up drill-through reports for you to access; that is, each drill-through report is already defined in terms of what to retrieve from the relational source.
In Essbase Spreadsheet Add-in, you can access drill-through reports from the Linked Objects Browser dialog box. When you select a drill-through cell in the sheet and select Essbase > Linked Objects, the Linked Objects Browser dialog box displays an entry for drill-through that you can select and launch.
Figure 172. Linked Objects Browser Dialog Box with Drill-Through Entry
You can define a style for cells tagged as drill-through to identify which cells in the spreadsheet are associated with drill-through reports. For more information, see "Accessing Drill-Through from the Spreadsheet".
An administrator at your organization can predefine drill-through reports for you to view or customize. The person who develops a report determines whether the report can be customized by drill-through users. If a report can be customized, use the Drill-Through Wizard to customize it. The Drill-Through Wizard is a graphical user interface that steps you through the following customization tasks:
Decide which columns from the predefined report you need to see.
Change the default display order of columns across the sheet.
Select an ascending or descending sort order for a particular column; for example, sort a list of store managers in alphabetical order.
Define a filter on a column so that only data meeting certain criteria is retrieved.