- 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
The Drillthrough-enabled MDX Syntax in Action
We are not simply copying the source data here, but we 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.
Click the radio button to the left of the Use a Query to Specify the Data to Transfer option to select it, as shown in Figure 25.
Figure 25 DTS Import/Export Wizard—Select Table Copy or Query dialog box—query option selected.Click Next.
)
Figure 25The Type SQL Statement dialog box 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.
Type the following MDX syntax into the Query Statement box of the dialog box:
DRILLTHROUGH SELECT{ [Measures].[Org Salary]} ON COLUMNS, {[Department].[All Department].[Store Temporary Stockers]} ON ROWS FROM HR WHERE [Q2]
The Type SQL Statement dialog box of the DTS Import/Export Wizard appears (as shown in Figure 26), complete with the new MDX query.
)
Figure 26 DTS Import/Export Wizard—Type SQL Statement dialog box 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.
Click Next.
The DTS Import/Export Wizard—Select Source Tables and Views dialog box appears, as shown in Figure 27.
Figure 27 The Select Source Tables and Views dialog box of the DTS
Import/Export Wizard.
)
Figure 27This 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 que—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 Figure 27.
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.)
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 in Figure 28.
)
Figure 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, in which 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.
Click OK.
We are returned to the DTS Import/Export Wizard—Select Source Tables and Views dialog box. Note that the Wizard has assigned a default table, [MDX_DRILL].[dbo].[Results], to which it plans to pump the extracted data. Although we could change this, we will leave things as they are for the purposes of this lesson.
)
Figure 29