- 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
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.
Go to the Start button on the PC and then navigate to Microsoft SQL Server, Enterprise Manager, as shown in Figure 15.
Figure 15 Navigate to MSSQL Server 2000 Enterprise Manager.Click Enterprise Manager.
The Enterprise Manager—Console Root appears.
Expand Microsoft SQL Servers by clicking the + sign on its immediate left.
Expand SQL Server Group by clicking the + sign on its immediate left.
Enterprise Manager now appears as shown in Figure 16.
Figure 16 MSSQL Server 2000 Enterprise Manager view.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).
Click All Tasks, as shown in Figure 17.
Figure 17 Select Import Data—and Call DTSClick Import Data from the context menu.
The Data Transformation Services (DTS) Import/Export Wizard—Introduction dialog box appears, as depicted in Figure 18.
Figure 18 The Data Transformation Services (DTS) Import/Export Wizard
introduction screen appears.Click Next.
The Data Transformation Services (DTS) Import/Export Wizard—Choose a Data Source dialog box appears.
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.
Click the Properties button.
The Data Link Properties dialog box appears, defaulted to the Connection tab.
Click the Provider tab.
Ensure that Microsoft OLE DB Provider for Olap Services 8.0 is selected, as shown in Figure 19.
Figure 19 Data Link Properties dialog box—Provider tab.Click Next.
We are returned to the Data Link Properties—Connection tab.
Type the Analysis Server name in the Data Source box (where MOTHER appears in the picture shown in Figure 20).
Type HR (for the HR cube) in the Location box.
Enter the appropriate Username and Password information in the respective input boxes.
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 20 The Data Link Properties Dialog box—Connection tab.Click Test Connection to ascertain connectivity.
We test positive for connectivity, per the test connection confirmation dialog box, pictured in Figure 21.
Figure 21 Test connection succeeded confirmation dialog box.Click OK.
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 22 DTS Import/Export Wizard—Choose a Data Source dialog
box.Click Next.
)
Figure 15)
Figure 16)
Figure 17)
Figure 18)
Figure 19)
Figure 20)
Figure 21)
Figure 22The 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!
Type in 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).
In the Database box, select <new> with the drop-down selector.
The Create Database dialog box appears.
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 23 Create Database dialog box.Click OK.
)
Figure 23The 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 24 DTS Import/Export Wizard—Choose a Destination dialog
box.
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.