SQL Server Magazine

A well-designed SQL Server 2000 Analysis Services data warehouse can yield significant information when you analyze the data warehouse cubes, and many excellent client tools are available to help you navigate OLAP cube data. Business intelligence (BI) tools such as ProClarity Analytics Platform, Microsoft Data Analyzer, and Microsoft Excel PivotTables offer visual drag-and-drop operations that let you organize dimensional data in various ways. But these tools hide the MDX queries that are behind the graphics. MDX is a dimensional query language similar to SQL but specialized for extracting data from multidimensional data sources. As you visually manipulate data in the analysis tool, the tool generates MDX to query the data source and provide results to fulfill your request.

Despite the convenience of these powerful tools, you sometimes need to use MDX to achieve a particular result. MDX can help you answer specific business questions such as, Which five regions returned the top performance last year, and who were the top customers in those regions? But for the new or occasional developer, MDX syntax can be challenging. Although MDX examples are plentiful online and in newsgroups and some good MDX books are available, MDX expressions can be difficult to learn and apply to a specific business environment, especially when you don't have examples that contain data similar to the data you're dealing with.

One way to create representative MDX queries that work with your own cube's data is to log the MDX that your analysis tool generates. Then, you can examine the MDX to learn how its syntax works. Most client-side tools use PivotTable Service to connect to Analysis Services, and you enable logging through PivotTable Service, so you can use logging with a variety of analysis tools. Enabling logging is easy. As an example, let's look at how to turn on logging to access the MDX that Excel generates when it creates a PivotTable report.

Analysis tools communicate with multidimensional data sources by using the OLE DB client-side component PivotTable Service. For example, Excel PivotTables use PivotTable Service on the client-side computer to access Analysis Services cubes as a data source. The primary way that the client-side PivotTable Service connects to an Analysis Server is by using a connection string to specify which server to connect to and the connection attributes.

In Excel 2002 (the release I used in writing this article), you specify PivotTable data sources by using either the Import External Data menu item or the PivotTable and PivotChart Wizard. You can access both of these options through Excel's Data menu. With either method, you need to define the external data source. Using the Import External Data menu item generates an Office Data Connection (.odc) file; using the wizard generates an OLAP query file (.oqy).

Regardless of the method you use to specify the data source, Excel creates a connection string that connects the client to Analysis Services. You enable logging in this connection string by adding a Log File attribute that tells Excel to accumulate MDX queries while you work with the PivotTable. Adding the Log File attribute is a temporary modification. To turn logging off, you have to remove the Log File attribute.

How to Set Up Logging
If you've never connected to an Analysis Services data source before, you need to do so before you can get a connection string to modify. For this example, let's use the Import External Data method to create a connection. From Excel's Data menu, select Import External Data, Import Data. In the Select Data Source dialog box, select +Connect to New Data Source.odc or click New Source to create a new connection and start the Data Connection Wizard that Figure 1 shows. Select Microsoft SQL Server OLAP Services. In the next dialog box, select the server you want to connect to and click Next to display the Choose Data dialog box. Select the Analysis Services database and cube to analyze—in this case, the FoodMart 2000 cube—as Figure 2 shows, and save the resulting .odc file.

Before you can add the Log File attribute to the connection string, you first have to find the connection string. Excel provides a seamless interface to Analysis Services cubes, and the data-source file location isn't obvious. The default location for .odc files is C:\Documents and Settings\<your user name>\My Documents\My Data Sources, and the default location for .oqy files is C:\Documents and Settings\<your user name>\ApplicationData\Microsoft\Queries. For this example, you need to find the file named <server name> FoodMart 2000 Sales.odc.

After locating the data-source file, you need to edit the connection string and add the required Log File attribute. Note that although these data-source files are plaintext files, they're also machine readable, and various Excel components use them. Be careful when editing the connection string, and save a backup in case you make a mistake. If the data-source file becomes unusable, you'll need to recreate the data source from Excel.

You can either edit the data-source file in its original location or edit it while you're opening the data source to use in Excel. From Excel's Import External Data menu, select Import Data. The resulting list of data sources is the combination of all data sources, including the default locations I mentioned.

In the dialog box that presents the list of data sources, right-click the data source you want and select Open With or a similar functionality to open the file in a plaintext editor such as Notepad. Now you can examine and edit the file.

Let's look at how to edit each type of file; .odc files contain XML, whereas .oqy files contain name-value pairs. If you're working with an .odc file, you can find the connection string in the XML code at the <odc:ConnectionString> element. The connection string has multiple parts separated by semicolons, and each part is a property of the connection. To add the Log File property, insert a semicolon after the last property in the Connection= keyword string (Auto Synch Period=10000) and add Log File="C:\myLOG.txt", as Figure 3 shows. (The quotation marks delimit the file location.) Excel will now log MDX activity to this local file.