- About the Series
- Introduction to Drillthrough
- Overview of Drillthrough In Analysis Services
- Enabling Drillthrough for an OLAP Cube
- Reprocessing the Cube to Include Drillthrough
- Drilling through from Analysis Services
- Drilling through from an MDX Query
- The Drillthrough-enabled MDX Syntax in Action
- Running the DTS Package and Viewing Query Results
- Next in Our Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Drilling through from Analysis Services
We have now enabled drillthrough from the Cube Editor, and reprocessed the cube to embed the drillthrough design into its structure. We must do this whether we intend to drill through from Analysis Services, as we will do next, or from an MDX Query. In either case, drillthrough must be enabled within the cube before we can perform a drillthrough.
Performing a Drillthrough to the Details
The data is again retrieved in the data Preview pane. We can test the results of our work by double-clicking a given cell and observing the drillthrough process. But first, let's expand year 1998 by double-clicking the 1998 column label.
Expand year 1998 by double-clicking the 1998 column label.
Scroll over so that only 1998 Q1 through Q4 appears, as shown in Figure 13.
Figure 13 Expand 1998 and display Q1 through Q4.
)
Figure 13As an example, suppose we want to examine the HR salary costs between the quarters for fluctuations. We note that the salary expense is uncannily consistent, with the only fluctuations of any size at all occurring at the Store Temporary Checkers and Store Temporary Stockers rows at the bottom. Although we can probably guess why this might be, and although the small amount of fluctuation is not something we would want to spend a week analyzing, we could still try to ascertain the reasons behind the differences by drilling through and examining the underlying transactions. Let's select a quarter's value and do just that.
Double-click the Q2 value for the last line of the display: Store Temporary Stockers.
After a short time, the drillthrough appears, as partially illustrated in Figure 14.
Figure 14 Partial results: drillthrough of Q2 1998 org salary.
)
Figure 14Scrolling down the result set in the Drillthrough Data window, we note that the pay_date values all appear to fall within the Q2 1998 timeframe, as expected. Be careful, here, however: If we are displaying fiscal quarters in the cube, and select a grain-level date on a transaction—which would be calendar—we might obtain an apparent "outlier" in the group. Although the results would still certainly be accurate, we might want to make information consumers aware of the apparent inconsistency.
Close the Drillthrough Data window.
We are ready to pursue drillthrough within an MDX query now that we have covered the basics in a more graphical way.