Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 3, 2003

Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives - Page 5

By William Pearson


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:


Illustration 14: Partial Results, Drillthrough of Q2 1998 Org Salary

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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM