Understanding the MDX behind OLAPbased Pivot Tables

You may not know it, but when you are using a pivot table with an OLAP cube, you are sending the OLAP server MDX (Multidimensional Expression) queries. MDX is an expression language that is used to return data from multidimensional data sources (such as OLAP cubes).

To see the MDX query behind your OLAP-based pivot table, simply run the following procedure, which uses the MDX property of the PivotTable object:

Sub GetMDX()

MsgBox ActiveSheet.PivotTables("PivotTable1").MDX End Sub

You will get a message box that looks similar to the one shown in Figure 23-6.

Figure 23-6

What you see in the resulting message box is the actual MDX query that was used to fill the pivot table with which you are working. Because the pivot table is refreshed or changed, subsequent MDX queries are passed to the OLAP database. The results of the query are sent back to Excel and displayed through the pivot table. This is how you are able to work with OLAP data without a local copy of a pivot cache.

Don't be fooled by the seeming complexity of this MDX statement. Excel tends to play it safe by throwing in superfluous syntax. Later you will learn how to decipher Excel's MDX. In the meantime, take a moment to cover the fundamentals of MDX. A basic understanding of MDX is not only beneficial when working with OLAP data sources, but necessary to use the techniques outlined later in this chapter.

MDX is a robust topic that is rich in scope and complexity. In that light, this chapter only touches on the fundamentals of MDX. If, after reading this chapter, you have a desire to learn more about MDX, consider picking up MDX Solutions, an excellent guide to MDX that is both easy to understand and comprehensive.

0 0

Post a comment