Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

Using MDX

In this lesson you’ll learn the basics of the MultiDimensional eXpression language (MDX).

MDX as a topic by itself has filled countless thousand-page books; here I’ll condense the topic

to give you a working knowledge of the language. You’ll be using MDX for reports in clients

like SSRS, to build KPIs, to perform calculations, and to do many other things.

To start this lesson and make sure your cubes have the same configuration, please

download Lesson16Complete.zip from this lesson’s download on the Wrox web-

site (

www.wrox.com). After downloading, make sure you can deploy it to your

development SSAS instance.

ANATOMY OF BASIC MDX

Knowing T-SQL is almost a liability when you’re learning MDX because the two are funda-

mentally similar, but completely different in their details. As you specify measures or attributes

that you want to see, you can place them on the columns area or rows area. You can also use

them as filters. For example, the most rudimentary MDX statement would be the following:

SELECT

[Measure].[MeasureName] on columns,

[Dimension].[Attribute] on rows

FROM [Cube name]

A real example using our sample project would look like this:

SELECT

[Measures].[Sales Amount] ON COLUMNS

FROM [AWDW2008R2Cube]

17

889633c17.indd 147 8/16/10 9:13:02 PM

148



LESSON 17 Using MDX

To run th ...