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

Monday Mar 3rd 2003 by William Pearson
Share:

Explore the drillthrough capabilities that debut in Microsoft SQL Server 2000 Analysis Services. Join Author Bill Pearson in a hands-on tutorial that examines the exciting new drillthrough capabilities, from both user and design perspectives, and discover how to make it possible for information consumers to see "what lies beneath" the OLAP cube's summary totals.

About the Series ...

This is the ninth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Introduction

In the first article of the series, we used the Cube Wizard to build an initial cube with the assistance of the Dimension Wizard. We progressed through subsequent articles, creating similar dimensions to those we built with the Wizard, focusing largely in our second article on using the Dimension Editor to illustrate options for building a more customized cube. We continued this examination of dimensions in Article Three, where we recreated the calendar time dimension, this time focusing on the process through which the Dimension Wizard converts existing time / date fields to a time dimension, along with its hierarchy of levels and members. Article Three also exposed ways to customize the predefined, time-related properties that the wizard establishes in building the time dimension, suggesting options for customization of these properties to enhance the cube, from the dual perspectives of user-friendliness and the reporting needs of the organization. We created an example of an alternate time dimension for fiscal time reporting, and then we discussed some of the considerations surrounding the simultaneous housing of both hierarchies in the same OLAP cube structure.

In Article Four, we examined another special type of dimension, the Parent-Child dimension, and explored the attributes that make it different from a regular dimension. We discussed the considerations that surround Parent-Child dimensions, such as the recursive nature of their data sources, and various actions that must be handled differently in their creation and maintenance. We created a parent-child dimension using the Dimension Wizard, within which we worked with levels and properties. Finally, we enabled values at the parent level of our newly created parent-child dimension. In Article Five, Working with the Cube Editor, we reviewed, summarized and integrated many of the concepts and components that we had previously constructed individually in earlier lessons. We undertook a complete cube build "from scratch," pulling together all that we had learned, to demonstrate the assembly of a cube more sophisticated than the cube we generated in our first lesson with the Cube Wizard.

In Article Six, Exploring Virtual Cubes, we introduced the concept of virtual cubes, and practiced their creation and use. We discussed the options that virtual cubes provide, from the often-intermingling perspectives of consolidation of multiple data sources, presentation enhancement and control, and other functionality. Through the use of hands-on illustrations, we demonstrated some of the options that virtual cubes offer to extend the functionality and capabilities of individual OLAP cubes

In our last two-part article, Custom Cubes: Financial Reporting, we established as our primary objective the construction of a simple cube to meet some illustrative business requirements, revolving around basic Income Statement Financial Reporting. We expanded upon many of the concepts we have introduced at some level in earlier lessons, involving the integration of cubes, as well as a host of information about cube components and general cube design and creation. We discussed some of the challenges that accompany cube design for financial reporting. We also explored the use of Custom Members as an alternative approach to "merging" cubes, using cubes that we created under the scenario of a realistic business constraint--the absence of a single fact table that contained "all that we needed in one place" to meet the objectives of the cube's design.

We navigated the process of Parent-Child dimension creation to practice the steps, and introduced various new concepts that we have not encountered in the series up to this point, including the use of Custom Members and the handling of rollup and aggregation considerations. Among other concepts we discussed and put into action, we made use of a UNION ALL query to prepare a "virtual" fact table for more effective cube creation, introduced methods of sign and data type control within our presentation, and addressed formatting and other presentation considerations as we created a Financial Reporting cube that focused upon the Income Statement.

In this article we will not only work within Analysis Services through the now-familiar interface and associated dialogs, but we will graft in, as an alternative approach, the creation of basic MDX expressions and queries for use with multidimensional data sources. Our focus will be establishing the drillthrough capability within our cube models, from two distinct perspectives, while examining the valuable uses of the drillthrough functionality from an information consumer perspective.

Exploring Drillthrough

In this lesson, Drilling Through to Details: From Two Perspectives, we will explore executing drillthrough statements on multidimensional cubes. First, we will discuss scenarios where drillthrough from summary cube data to the underlying details might be valuable to information consumers. Next, we will examine strengths and weaknesses of the capability in MSSQL 2000 Analysis Services. We will discuss the steps that need to be taken to implement drillthrough, then set up a sample drillthrough in the Cube Editor, so as to focus on concepts in an introductory fashion.

We will then practice the creation and use of an MDX query that uses the DRILLTHROUGH statement to retrieve the source data for a cube cell. We will explore the syntax for the DRILLTHROUGH statement, and discuss options and parameters that surround this functionality, as a part of our hands-on exercises.

In this lesson, we will:

  • Enable a cube for Drillthrough in the Cube Editor;
  • Perform a Drillthrough within Analysis Services;
  • Discuss filtering and other aspects of Drillthrough in general;
  • Discuss Drillthrough using an MDX Query;
  • Perform a Drillthrough using MDX.

Overview of Drillthrough in Analysis Services

One of the attractions of using an OLAP cube is the speed with which it allows us to browse and report upon summary data for the organization. The summary information that we are retrieving typically aggregates detailed transactions at a lower level. The aggregated totals stored in the cube can represent thousands (sometimes far more) of rows of information (transactions) within the data source from which it originates.

The source containing the detail data is often built to be optimized for Online Transaction Processing (OLTP), and, while it can most often deliver summary information to us directly, the process of aggregation is far more optimal in the database (our cube) that is designed for Online Analytical Processing (OLAP). The OLTP source would effectively have to aggregate every line of detail whenever we needed a total of any sort, and would take longer to provide us with that total, than would the cube, which stores data at the summary level at all times.

Thus, the strength of the OLAP data source is its ability to deliver aggregated data rapidly. The disadvantage of this scenario becomes evident when we need to view underlying detail. One of the many business advantages that we seek, through the implementation of business intelligence, is the capability to analyze data, to track activities and balances through to the causative factors, and, therefore, to determine ways to increase or decrease the activity, as we deem desirable.

For example, say a single store stands out from its peers in an analysis of stores income, because it has a higher profit margin consistently. We want to determine what the store is doing differently, so that we can attempt to apply the same principles to our other stores, to achieve the same desirable effects, or to at least better their current results. We might also see an increase in overall HR costs, and decide to investigate the underlying causes. After isolating and examining the detail that supports the balances we have determined to be unusually high, we might find that increases in turnover are driving higher overall HR costs, as turnover means increased training, recruitment expenses, and so forth. In cases like these and many others, effective analysis relies upon drillthrough, or the ability to look at the transactions underneath the aggregate numbers, with which we typically begin the analysis process.

We must be mindful of the fact that, in the case of most data warehouses, the data we see in the drillthrough presentation is the detail in the fact table of the data warehouse, and not necessarily the data as it appears in the original OLTP data source. As a result, some pre-aggregation, etc., might have occurred during the ETL process that brought the data to the fact table from the OLTP source. This is certainly a drawback, in the eyes of many, who would prefer to "go further," and be able to drill all the way to the original OLTP data source straightway. While this is certainly possible with programming, through Data Transformation Services, or through the use of a combination of approaches, the "native" drillthrough supports only drilling to the table from which the data enters Analysis Services. In this case, the fact table is that source.

We will examine the drillthrough process within the Cube Editor, inside Analysis Services, to gain an understanding of how we can enable drillthrough for our cubes. We will then examine the drillthrough process, and discuss a few of its characteristics and limitations, while we are inside Analysis Services, to gain a comfort level for the concepts before diving into the MDX approaches to drilling through.

Next, we will look at drillthrough from an MDX query, and discuss how we might apply filters to the views we receive within drillthrough, as well as other attributes of the process that bear consideration.

Enabling Drillthrough for an OLAP Cube

Let's go into the Cube Editor and enable drillthrough for the HR cube, a sample cube provided by the Typical MSSQL Server 2000 Analysis Services. We will start Analysis Services, and navigate to the HR cube, with the following steps:

1.      Start Analysis Manager (Start --> Programs --> Microsoft SQL Server --> Analysis Services --> Analysis Manager).

2.      Expand the Analysis Servers folder by clicking the "+" sign to its left.

3.      Expand your server (typically named the same as the host PC, but determined by the installation / setup), and then expand the FoodMart 2000 database.


Illustration 1: Navigate to the FoodMart2000 Database in Analysis Manager

4.      Expand the FoodMart2000 database by clicking on the "+" sign to its left.

5.      Expand the Cubes folder (seen in the Illustration 2 below), by clicking the "+" sign to its left.

The cubes appear, similar to those shown in Illustration 2 below.


Illustration 2: Sample Cubes provided with the Analysis Services Installation.

6.      Right-click the HR cube, and then click Edit from the context menu.

The Cube Editor appears.

Let's make sure that we have a common display showing at this stage.

7.      Click the Data tab (lower right half of the Analysis Manager screen).

8.      Drag the Department and Time dimensions to the row and column axes, respectively, to match the display shown below.


Illustration 3: The Data Viewing Pane, after Dimensions are Placed

9.      Select the Tools top menu item.

10.  Select Drillthrough Options on the cascading menu, as shown in Illustration 4 below:


Illustration 4: Select Drillthrough Options

The Cube Drillthrough Options dialog appears.

11.  Check the Enable Drillthrough box by clicking it.

12.  Select the following columns for display by clicking the checkboxes to the immediate left of each.

  • pay_date
  • salary_paid
  • overtime_paid
  • overtime_hours
  • full_name

The dialog appears as partially shown below:


Illustration 5: The Cube Drillthrough Options Dialog (partial view)

In our setpoints above, we have enabled drillthrough, and defined what fields from the actual underlying data source will be displayed within a drillthrough view.

We can also set filters on the drillthrough, to restrict the data returned, as follows:

13.  Click the Filter tab of the Cube Drillthrough Options dialog.

Here we can type in a filter to further restrict the data returned in the drillthrough presentation. We will leave this blank, as shown below, for this exercise.


Illustration 6: The Filter Tab of the Cube Drillthrough Options Dialog

14.  Click OK.

The Drillthrough Settings dialog, shown in Illustration 7 below, warns us that the cube must be saved for the changes we have just made to take effect, as shown below.

Click for larger image

Illustration 7: The Drillthrough Settings Warning Dialog

15.  Click OK.

 

We now need to process the cube to implant our drillthrough setup.

16.  Select the Tools top menu item.

17.  Select Process Cube on the cascading menu, as shown in Illustration 8 below:

 


Illustration 8: Select Process Cube (Circled in Red)

Note that we might have clicked the Process Cube button (shown in Illustration 9) to accomplish the same thing.


Illustration 9: The Process Cube Button

We receive another warning about saving the cube (unless we made it a special point to save it after our changes and immediately before the last step.)

18.  Click OK.

A dialog appears warning us about aggregations and giving us the opportunity to take advantage of the Storage Wizard to help us with the aggregation design process. The dialog appears below.


Illustration 10: "Just Say No" to Storage Design

19.  Click No to continue.

The Process a Cube dialog appears. Here we can select from three processing options as follows:

  • Incremental update
  • Refresh data
  • Full Process

We are also given the option to incrementally update the dimensions of this cube, as well as to specify additional cube processing options. We will select Full Processing, and leave all else as it is, as shown in Illustration 11.


Illustration 11: Select Full Process

20.  Select the Full Process radio button.

21.  Click OK.

The cube begins processing, displaying a Process Log window. Once it has finished, a green message across the bottom of the Log window indicates this, as depicted in the next illustration.


Illustration 12: "Processing Completed Successfully" appears in the Log Window

22.  Click Close on the Process Log window.

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 on a given cell and observing the drillthrough process. First, let's expand year 1998 by double clicking the 1998 column label.

23.  Expand year 1998 by double clicking the 1998 column label.

24.  Scroll over so that only 1998 Q1 through Q4 appears, as shown below.


Illustration 13: Expand 1998, and Display Q1 through Q4

 

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.

Drilling through from an MDX Query

First, we will open the MSSQL Server 2000 Enterprise Manager, from which we can easily access Data Transformation Services for the purposes of a practice session for writing our query against an OLAP cube.

1.             Go to the Start button on the PC, and then navigate to Microsoft SQL Server ---> Enterprise Manager, as shown below:

Click for larger image

Illustration 15: Navigate to MSSQL Server 2000 Enterprise Manager.

2.             Click Enterprise Manager.

The Enterprise Manager - Console Root appears.

3.             Expand Microsoft SQL Servers by clicking the "+" sign to its immediate left.

4.             Expand SQL Server Group by clicking the "+" sign to its immediate left.

Enterprise Manager now appears as shown below:


Illustration 16: MSSQL Server 2000 Enterprise Manager View

5.             Right-click the MSSQL Server in the tree (most likely named after the computer on which it resides--mine is MOTHER, as shown above).

6.             Click All Tasks, as shown in Illustration 17 below.


Illustration 17: Select Import Data - and Call DTS

7.             Click Import Data from the context menu.

The Data Transformation Services (DTS) Import / Export Wizard - Introduction dialog appears, as depicted in Illustration 18.


Illustration 18: The Data Transformation Services (DTS) Import / Export Wizard Introduction Screen Appears

8.             Click Next.

The Data Transformation Services (DTS) Import / Export Wizard - Choose a Data Source dialog appears.

9.             Select Microsoft OLE DB Provider for Olap Services 8.0 via the dropdown selector in the Data Source box.

The Choose a Data Source dialog changes in appearance, to be context sensitive to the data source provider we have selected.

10.         Click the Properties button.

The Data Link Properties dialog appears, defaulted to the Connection tab.

11.         Click the Provider tab.

12.         Ensure that Microsoft OLE DB Provider for Olap Services 8.0 is selected, as shown in Illustration 19.


Illustration 19: Data Link Properties Dialog - Provider Tab

13.         Click Next.

We are returned to the Data Link Properties - Connection tab.

14.         Type the Analysis Server name in the Data Source box, (where MOTHER appears in the picture in Illustration 20 below).

15.         Type HR (for the HR cube), in the Location box.

16.         Enter the appropriate Username and Password information in the respective input boxes.

17.         At Enter the Initial Catalog to Use (item number 3 on the dialog), select FoodMart 2000 from the dropdown selector.

The Data Link Properties dialog - Connection tab appears as shown below.


Click for larger image

Illustration 20: The Data Link Properties Dialog - Connection tab

18.         Click Test Connection to ascertain connectivity.

We test positive for connectivity, per the test connection confirmation dialog, pictured below.


Illustration 21: Test Connection Succeeded confirmation dialog

19.         Click OK.

20.         Click OK again to return to the dialog.

The DTS Import / Export Wizard - Choose a Data Source dialog reappears, as depicted below:


Illustration 22: The DTS Import / Export Wizard - Choose a Data Source

21.         Click Next.

The DTS Import / Export Wizard - Choose a Destination dialog appears. The DTS Wizard is asking to which database we want to direct the output from the DTS task we are building. As we do not have a table already set up for our output, we will create one. Our purpose here is only to see the output of a DRILLTHROUGH statement within an MDX query, but imagine the opportunities with using this tool for the extraction and loading of data of this sort!

22.         Type the Username and Password information to access the MSSQL Server.

Note: See the online help for the two main modes of security in MSSQL Server 2000 if this is not familiar--and perhaps get assistance in what to put in the aforementioned fields.

23.         In the Database box, select <new> with the dropdown selector.

The Create Database dialog appears.

24.         Type in MDX_DRILL in the Name box, and leave the two size setpoints at default.

The completed Create Database dialog appears, as shown below:


Illustration 23: The Create Database Dialog

25.         Click OK.

The Create Database dialog closes and we are returned to the DTS Import / Export Wizard - Choose a Destination dialog. Here we see the new database name added in the Database selector box (near the bottom of the dialog), as shown in Illustration 24.


Illustration 24: The DTS Import/Export Wizard - Choose a Destination Dialog

26.         Click Next.

We next arrive at the DTS Import / Export Wizard - Select Table Copy or Query dialog. We are creating a destination database as part of the "export" (our query results) from our OLAP cube. We could have put the table (which is what we really want here) within an existing database, but that might not be a good idea without proper planning and security considerations.

One of the strengths of the DTS tool is that it is very comprehensive, not only in performing ETL functions similar to (and far more complex than) this, but in its capability to build structures for us that have yet to be created. Furthermore, the entire process of visually building the task, which we are doing now, needs not be treated as an ad hoc evolution. DTS allows us to save the "program" we are creating as VB, a database object that can be reused, and so forth.

We are not simply copying the source data here, but are using a query to extract results, the main purpose of which is to illustrate the output of an MDX query with a DRILLTHROUGH statement. Let's proceed with selecting the "query" option here and getting to the results we seek.

27.         Click the radio button to the left of the Use a Query to Specify the Data to Transfer option, to select it, as shown below.


Illustration 25: The DTS Import / Export Wizard - Select Table Copy or Query Dialog - Query Option Selected

28.         Click Next.

The Type SQL Statement dialog of the DTS Import / Export Wizard appears. We will type in a basic DRILLTHROUGH - enabled MDX query that will retrieve the data supporting a specific value in the HR cube.

29.         Type the following MDX syntax into the Query Statement box of the dialog:

DRILLTHROUGH

SELECT{ [Measures].[Org Salary]} ON COLUMNS,

{[Department].[All Department].[Store Temporary Stockers]} ON ROWS

FROM HR

WHERE [Q2]

The Type SQL Statement dialog of the DTS Import / Export Wizard appears as shown in Illustration 26, complete with the new MDX Query.


Illustration 26: The DTS Import / Export Wizard - Type SQL Statement Dialog with MDX Query

We next arrive at the DTS Import / Export Wizard - Select Table Copy or Query dialog. We are creating a destination database as part of the "export" (our query results) from our OLAP cube. We could have put the table (which is what we really want here) within an existing database, but that might not be a good idea without proper planning and security considerations.

One of the strengths of the DTS tool is that it is very comprehensive, not only in performing ETL functions similar to (and far more complex than) this, but in its capability to build structures for us that have yet to be created. Furthermore, the entire process of visually building the task, which we are doing now, needs not be treated as an ad hoc evolution. DTS allows us to save the "program" we are creating as VB, a database object that can be reused, and so forth.

We are not simply copying the source data here, but are using a query to extract results, the main purpose of which is to illustrate the output of an MDX query with a DRILLTHROUGH statement. Let's proceed with selecting the "query" option here and getting to the results we seek.

27.         Click the radio button to the left of the Use a Query to Specify the Data to Transfer option, to select it, as shown below.


Illustration 25: The DTS Import / Export Wizard - Select Table Copy or Query Dialog - Query Option Selected

28.         Click Next.

The Type SQL Statement dialog of the DTS Import / Export Wizard appears. We will type in a basic DRILLTHROUGH - enabled MDX query that will retrieve the data supporting a specific value in the HR cube.

29.         Type the following MDX syntax into the Query Statement box of the dialog:

DRILLTHROUGH

SELECT{ [Measures].[Org Salary]} ON COLUMNS,

{[Department].[All Department].[Store Temporary Stockers]} ON ROWS

FROM HR

WHERE [Q2]

The Type SQL Statement dialog of the DTS Import / Export Wizard appears as shown in Illustration 26, complete with the new MDX Query.


Illustration 26: The DTS Import / Export Wizard - Type SQL Statement Dialog with MDX Query

The DRILLTHROUGH statement contains a SELECT clause to identify the cube cell for which source data is retrieved. The SELECT clause is identical to an ordinary MDX SELECT statement except that in the SELECT clause only one member can be specified on each axis (in effect, crating a "single number result"). If more than one member is specified on an axis, an error occurs.

Optionally, the MAXROWS syntax specifies the maximum number of the rows in each returned rowset, which may or may not be functional, depending on the application within which we use it. The MAXROWS statement would come after the DRILLTHROUGH statement, if used. See the Books Online or MSSQL 2000 Reference Library for more information.

30.         Click Next.

The DTS Import / Export Wizard - Select Source Tables and Views dialog appears, as shown in Illustration 27 below.


Illustration 27: The Select Source Tables and Views Dialog of the DTS Import / Export Wizard

This portion of the DTS Import / Export Wizard allows us to modify, with a great deal of flexibility, the data that we extract from the source / transfer to the new database. Our present needs are simple--to show the results of the drillthrough query--but there is one appealing feature that can be quite handy in these sorts of instances. This is the Preview capability, whose button appears in Illustration 27 above.

Let's get a foretaste of what we will be extracting by using the Preview feature at this point. (The button is activated when the row in the Table(s) and View(s) box is selected.)

31.         Click the Preview button.

The results appear, and are not unlike the results we saw using the drillthrough function within Analysis Manager, except perhaps the limitation on the number of lines. The results appear as shown below.


Illustration 28: The Preview Feature at Work, Giving Us a Sample of Drillthrough Results

The Preview feature gives us a capability of verifying the data before running the Data Transformation Services (DTS) package. If the results do not meet expectations, we can click Back to return to previous dialog boxes, where we can make adjustments that will produce the appropriate data. Our query is basic, and our objective, again, is simply to see DRILLTHROUGH in action, although the potential for DTS is quite impressive.

32.         Click OK.

We are returned to the DTS Import / Export Wizard - Select Source Tables and Views dialog. Note that the Wizard has assigned a default table, [MDX_DRILL].[dbo].[Results], to which it plans to pump the extracted data. While we could change this, we will leave things as they are for the purposes of this lesson.

33.         Click Next.

The Save, Schedule and Replicate Package dialog of the DTS Import / Export Wizard appears, as shown in Illustration 29 below.


Illustration 29: The DTS Import / Export Wizard - Save, Schedule and Replicate Package Dialog

We will run the package immediately, as we are only interested in seeing the results using our DRILLTHROUGH query.

34.         Ensure that Run Immediately is checked.

35.         Click Next.

The DTS Import / Export Wizard - Completing the DTS Import / Export Wizard dialog appears, as shown in Illustration 30 below.

Click for larger image

Illustration 30: The DTS Import / Export Wizard - Completing the DTS Import / Export Wizard Dialog

The Wizard provides us an opportunity at this point to review all setpoints.

36.         Click Finish.

The Executing Package status dialog appears (shown in Illustration 31 below), informing us of the various steps that DTS is undertaking, in progression.


Illustration 31: The DTS Import / Export Wizard - Executing Package Status Dialog

The package runs quickly, and the Successfully Copied 1 Table(s) from the OLAP Server to MSSQL Server message box informs us that the package has completed, as shown next (Illustration 32).


Illustration 32: Message Box Indicating Successful Completion of the DTS Package

37.         Click OK to close the message box.

38.         Click Done on the Executing Package status dialog.

The dialog closes, and we are returned to the enterprise manager console. DTS has processed our query, and placed the results in a table that it created as a part of the extraction process. All that remains is an examination of the table to view the results.

39.         Expand the SQL Server (typically named after the PC) within which we have been working. (Click the "+" sign immediately to its left.)

40.         Expand the Databases folder underneath the Server.

The tree listing the databases contains our new database, MDX_DRILL, as shown in Illustration 33.


Illustration 30: MDX_DRILL Appears in the Databases Folder

41.         Expand database MDX_DRILL by clicking the "+" sign immediately to its left.

42.         Select Tables underneath MDX_DRILL.

The tables appear to the right of the console. Notice that all are system tables (generated for any MSSQL Server database), except a table called Results. It is here that the output of our DRILLTHROUGH query resides.

43.         Right-click the Results table.

44.         Click Open Table from the context menu.

45.         Select Return All Rows from the second menu that appears, as shown below.


Illustration 34: Opening the Result Dataset - Return All Rows

The query result set appears, similar to that partially shown below:


Illustration 35: Result Dataset Displayed

And so, we see a result set, not unlike that which we obtained when we performed drillthrough from inside Analysis Manager.

There are many options available for using the DRILLTHROUGH statement within our MDX query. The Books Online (installed along with MSSQL Server 2000, or available from the CD's, the Microsoft site, and other locations) describe ways to precisely control the size of the result set, and so forth. In addition, while we simply used DTS to explore an example of the use of DRILLTHROUGH within an MDX query, an understanding of DTS, gained from the Books Online and other resources, may pay great dividends when a ready, convenient means of executing queries is useful.

46.         Close the results set view.

47.         Select Console from the top menu.

48.         Click Exit to close the Enterprise Manager console, as shown in Illustration 36.


Illustration 36: Result Dataset Displayed

Next in Our Series ...

In this lesson, Drilling Through to Details, we explored performing drillthrough with multidimensional cubes. First, we discussed scenarios where drillthrough from summary cube data to the underlying details can be valuable to information consumers. Next, we examined some of the strengths and weaknesses of the capability in MSSQL 2000 Analysis Services.

We examined the steps that need to be taken to implement drillthrough, as a part of setting up a sample drillthrough in the Cube Editor, to focus on concepts in an introductory fashion. We then practiced the creation and use of an example MDX query that used the DRILLTHROUGH statement to retrieve rowsets from the source data underneath cube cells. We explored the syntax for the DRILLTHROUGH statement, and discussed options that surrounded this functionality, as a part of our hands-on exercises.

In our next lesson, Reporting Options for Analysis Services Cubes: Microsoft Office, we will begin a set of articles that examine some of the effective options available for report building with Analysis Services OLAP cubes. To begin our examination of this popular topic, we will present an introduction to using Excel PivotTable reports to retrieve and display information from our cubes. First, we will discuss the steps needed to define our data source, and to establish a connection to the cube. Next, we will expose the layout of the PivotTable report, and explore the use of the PivotTable toolbar in browsing and reporting upon cube data. Finally, we discuss the intersection of dimensions in PivotTable report axes as a means of making our reports truly multidimensional.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved