- 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
Running the DTS Package and Viewing Query Results
We will run the package immediately because we are interested only in seeing the results using our DRILLTHROUGH query.
Ensure that Run Immediately is checked.
Click Next.
The DTS Import/Export Wizard—Completing the DTS Import/Export Wizard dialog box appears, as shown in Figure 30.
Figure 30 The DTS Import/Export Wizard—Completing the DTS
Import/Export Wizard dialog box.
)
Figure 30The wizard provides us an opportunity at this point to review all setpoints.
Click Finish.
The Executing Package status dialog box appears (shown in Figure 31), informing us of the various steps that DTS is undertaking, in progression.
Figure 31 The DTS Import/Export Wizard—Executing Package status
dialog box.
)
Figure 31The 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 in Figure 32.
)
Figure 32 Message box indicating successful completion of the DTS
package.
Click OK to close the message box.
Click Done on the Executing Package status dialog box.
The dialog box 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.
Expand the SQL Server (typically named after the PC) within which we have been working. (Click the + sign immediately on its left.)
Expand the Databases folder underneath the Server.
The tree listing the databases contains our new database, MDX_DRILL, as shown in Figure 33.
Figure 33 MDX_DRILL appears in the Databases folder.Expand database MDX_DRILL by clicking the + sign immediately on its left.
Select Tables underneath MDX_DRILL.
)
Figure 33The tables appear on 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.
Right-click the Results table.
Click Open Table from the context menu.
Select Return All Rows from the second menu that appears, as shown in Figure 34.
Figure 34 Opening the result table—(returning all rows).
)
Figure 34The query result set appears, similar to that partially shown in Figure 35.
)
Figure 35 Result table 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 CDs, the Microsoft site, and other locations) describe ways to precisely control the size of the result set, and so forth. In addition, although 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.
)
Figure 36