Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Drilling through from an MDX Query

Unfortunately, the MDX sample application is not an option as a place to practice this because the application is set up to request cell sets. Although the source code for the sample application is available and straightforward enough, taking that route is unquestionably beyond the scope of this lesson. Therefore, we will use another avenue that is certainly well within the grasp of the majority of the people who are reading these words, although it is a bit off the beaten path with regard to our focus so far. We will enact a drillthrough within an MDX query using MSSQL Server 2000's Data Transformation Services.

Preparing DTS to Query the Cube

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 in Figure 15.

    Figure 15Figure 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 on its immediate left.

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

    Enterprise Manager now appears as shown in Figure 16.

    Figure 16Figure 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 in the figure).

  6. Click All Tasks, as shown in Figure 17.

    Figure 17Figure 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 box appears, as depicted in Figure 18.

    Figure 18Figure 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 box appears.

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

    The Choose a Data Source dialog box 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 box appears, defaulted to the Connection tab.

  11. Click the Provider tab.

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

    Figure 19Figure 19 Data Link Properties dialog box—Provider tab.

  12. Click Next.

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

  13. Type the Analysis Server name in the Data Source box (where MOTHER appears in the picture shown in Figure 20).

  14. Type HR (for the HR cube) in the Location box.

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

  16. At Enter the Initial Catalog to Use (item number 3 on the dialog box), select FoodMart 2000 from the drop-down selector.

    The Data Link Properties dialog box—Connection tab appears, as shown in Figure 20.

    Figure 20Figure 20 The Data Link Properties Dialog box—Connection tab.

  17. Click Test Connection to ascertain connectivity.

    We test positive for connectivity, per the test connection confirmation dialog box, pictured in Figure 21.

    Figure 21Figure 21 Test connection succeeded confirmation dialog box.

  18. Click OK.

  19. Click OK again to return to the dialog box.

    The DTS Import/Export Wizard—Choose a Data Source dialog box reappears, as shown in Figure 22.

    Figure 22Figure 22 DTS Import/Export Wizard—Choose a Data Source dialog box.

  20. Click Next.

The DTS Import/Export Wizard—Choose a Destination dialog box appears. The DTS Wizard is asking here to which database we want to direct the output from the DTS task we are building. Because 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!

  1. Type in the Username and Password information to access the MSSQL Server.


    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).

  2. In the Database box, select <new> with the drop-down selector.

    The Create Database dialog box appears.

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

    The completed Create Database dialog box appears, as shown in Figure 23.

    Figure 23Figure 23 Create Database dialog box.

  4. Click OK.

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

Figure 24Figure 24 DTS Import/Export Wizard—Choose a Destination dialog box.

  1. Click Next.

We next arrive at the DTS Import/Export Wizard—Select Table Copy or Query dialog box. 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 for performing ETL functions similar to and far more complex than this one, but also 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.

  • + Share This
  • 🔖 Save To Your Account