Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives - Page 5
March 3, 2003
As an example, say we want to examine the HR salary costs between the quarters for fluctuations. We note 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. While 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.
25. 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 below:
We note, scrolling down the result set in the Drillthrough Data Window, that the pay_date values all appear to fall within the Q2 1998 timeframe, as expected. Be careful, here, though: 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. While the results would still certainly be accurate, we might want to make information consumers aware of the apparent inconsistency.
26. 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. Unfortunately, the MDX Sample Application will not be an option as a place to practice this, because the application is set up to request cell sets. While the source code for the sample application is available and straightforward enough, taking that route is unquestionably beyond the scope of this lesson. We will use another avenue, therefore, that, while a bit off the beaten path with regard to our focus so far, is certainly well within the grasp of the majority of the people who are reading these words. We are going to enact a drillthrough within an MDX query using MSSQL Server 2000's Data Transformation Services.