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:


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

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



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