The Basics of MDX

Those of you who are familiar with SQL will have relatively little trouble picking up the basic concepts of MDX. As you look at the general syntax for an MDX statement, you will see the familiar SELECT and FROM clauses:

SELECT

{member selection}

ON

COLUMNS,

{member selection}

ON

ROWS

FROM

[cube name]

A member selection can be any combination of dimensions or members. These selections are given an axis designation. In MDX, a member selection can actually be placed in any one of up to 64 axes. To keep things simple, look at the most common axes: columns and rows. When a member selection is placed On Columns, that member selection will be column-oriented. Place a member selection On Rows, and that selection will be row-oriented. Finally, the cube name identifies the name of the cube with which you are working.

Take a look at the following MDX query. Here you are requesting Internet sales and tax amounts as columns, and the product category as rows. As you can see in the FROM clause, this data is coming from the Analysis Services Tutorial cube.

SELECT {[Measures].[Internet Sales-Sales Amount],

[Measures].[Internet Sales-Tax Amount]} ON COLUMNS, {[Product].[Product Categories].[Category].Members} ON ROWS FROM [Analysis Services Tutorial]

You will note that you are identifying the member selections by explicitly walking through the cube structure that gets you to that member. For instance, the query selects the Category members using [Product].[Product Categories].[Category].Members. This expression explicitly walks through the Product dimension, the Product Categories hierarchy, and the Category level, and finally ends with the members of the Category level. Refer back to Figure 23-3 to get a graphical view of how the cube structure works.

Notice that the resulting dataset, shown in Figure 23-7, is in a cross tab structure. This is the power of MDX and OLAP cubes. With MDX, you can create any number of datasets that are structured in any number of ways. Although this type of result could be achieved via a SQL statement, it would not be as straightforward and as easy as an MDX query.

Figure 23-7

Figure 23-7

You can also limit the results of your MDX query by slicing your selections using a WHERE clause. This works similarly to a WHERE clause in a SQL statement. The following MDX query limits the results to sales for the United States only. The resulting dataset is shown in Figure 23-8.

SELECT {[Date].[Calendar Quarter].Members} ON COLUMNS,

{[Product].[Product Categories].[Category].Members} ON ROWS FROM [Analysis Services Tutorial] WHERE ([Customer].[Country-Region].[United States], [Measures].[Internet Sales-Sales Amount])

SELECT {[Date].[Calendar Quarter].Members} ON COLUMNS,

{[Product].[Product Categories].[Category].Members} ON ROWS FROM [Analysis Services Tutorial] WHERE ([Customer].[Country-Region].[United States], [Measures].[Internet Sales-Sales Amount])

Figure 23-8

Figure 23-8

You may be wondering why the Internet Sales Amount measure is included in the WHERE clause of the previous MDX query. In every OLAP cube, one measure is designated as the default measure. If an MDX query is passed without explicitly asking for a particular measure, the default measure is returned. The default measure is typically designated by the adminstrator of your Analysis Services database.

To get the exact measure you are looking for, you must explicitly call for it in your MDX query. The most common way a measure is passed in an MDX query is through the WHERE clause. For instance:

SELECT {[Customer].[Country-Region].Members} ON COLUMNS

{[Product].[Product Categories].[Category].Members} ON ROWS FROM [Analysis Services Tutorial] WHERE ([Measures].[Internet Sales-Sales Amount])

Deciphering Excel's MDX Queries

In the next section of this chapter, you discover how you can pass your own MDX queries to an OLAP server using ADO and a little VBA in order to retrieve a flat dataset. One of the benefits of being able to see the MDX query behind an OLAP-based pivot table is that you can see how the MDX for a particular view should be set up. This gives you a kind of built-in MDX tutor that you can leverage when you need it.

Create an MDX Log

You can set up an MDX log that documents every MDX query that is passed to the OLAP server. The procedure shown here does just that. As you can see, this procedure is entered into the PivotTableUpdate event of the worksheet. This ensures that each time there is a change in the pivot table, the MDX query is captured. You then simply trap the MDX query in a string variable and append it to a text file:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim StrMDX As String

'Trap the MDX statement

StrMDX = ActiveSheet.PivotTables("PivotTable1").MDX

'Append the MDX to a specified text file Open "C:\MDX_Log.txt" For Append As #1 Print #1, StrMDX & Chr(13) & Chr(10) Close #1

End Sub

After looking at a few of the MDX queries that Excel outputs, you will soon realize that the way Excel uses MDX is different from the way you would. There are two reasons for this. First, Excel adds some syntax that it feels is necessary to make the MDX query run properly. This is analogous to the way that Excel records macros; it errs on the side of creating more syntax than is actually necessary in order to play it safe. Second, Excel imposes automatic rules when building its MDX queries.

In the following example, the text in bold is the syntax that is actually needed to run this MDX query. The rest is syntax Excel uses as safeguards and rules to ensure that the query runs the way Excel expects it to run.

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].Members [All Products]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Analysis Services Tutorial] WHERE

({[Measures].[Internet Sales-Sales Amount]}) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

The following sections outline some of the most common syntactical expressions you will find in Excelgenerated MDX queries that can be safely eliminated.

NON EMPTY

By default, Excel will inhibit the display of any members that are empty. It does this by using the NON EMPTY keyword. You don't need this keyword unless you are looking for only nonempty members.

You can force Excel to include empty members by adjusting the Display properties of the pivot table. To do so, right-click the pivot table and select Table Options O Display. Then place a check in Show Items with No Data on Rows and Show Items with No Data on Columns. Any subsequent MDX queries will not include the NON EMPTY keyword. You can also change these settings by using the DisplayEmptyRow and DisplayEmptyColumn properties of the PivotTable object:

ActiveSheet.PivotTables("PivotTable1").DisplayEmptyRow = True ActiveSheet.PivotTables("PivotTable1").DisplayEmptyColumn = True

Hierarchize

The Hierarchize function sorts all members in hierarchical order. Most OLAP cubes are sorted properly by default, so you do not need to include this in your MDX statements. Excel plays it safe by employing this function.

DrilldownLevel

By default, Excel will always pull the ALL level of any hierarchy and then drill down to the needed level by employing the DrilldownLevel function. This function drills downs one level below the specified level.

Various Server-Side Settings

Excel will automatically call for all of the server-side settings. These settings allow the OLAP administrators to push down formatting, language, and other properties to the consumers of their OLAP data. When using MDX queries via VBA, you will rarely need these settings. You can, for the most part, ignore any of the formatting-related keywords such as CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS, and DIMENSION PROPERTIES PARENT_UNIQUE_NAME.

With some practice, you will be able to spot the necessary MDX that you can leverage in building your own queries. From the sample MDX created earlier in this section, you can whittle the MDX down to the following query that can be used in VBA to retrieve OLAP data:

SELECT {[Product].[Product Categories].Members} ON COLUMNS FROM [Analysis Services Tutorial]

WHERE ({[Measures].[Internet Sales-Sales Amount]})

You would logically think that if an OLAP-based pivot table uses MDX queries to retrieve data, then you would be able to feed a pivot table with custom MDX queries. Unfortunately, that is not the case. There is currently no way to make a pivot table accept custom MDX queries as arguments for its data.

0 0

Post a comment