Home > Business Intelligence > Multidimensional Business Logic – MDX and DAX

Multidimensional Business Logic – MDX and DAX

A little MDX can do a lot in an Analysis Server cube.

One of the goals in the seminar I’m teaching in November (Advanced Analysis Services for SQL Server 2008 R2 and Denali) is to provide a simple framework to help people build MDX and DAX calculations.

There are many challenges in learning MDX, but there are some basic strategies for making it easier. I use a couple of templates in the SQL Server Management Studio as a basis for creating calculations.

Here’s how it works. If I’m creating a calculation involving time (and that’s what many of the most important calculations do) I use the following template:

with member measures.test as

1

select {measures.[Internet Sales Amount] ,measures.test} on 0

,[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Now, if you haven’t used MDX before that maybe looks complicated. But it’s really not very complicated because you can use that same template all the time. Just plug in the names of your cube, your date dimension, and the measure you’re using to build your calculation. Execute the query and you should see a value of 1 for the test measure in every row.

After you have that working you can build your calculation. Replace the 1 with whatever calculation you’re trying to build. If you’re doing a Year To Date Sum, it would look like this:

with member measures.test as

sum

(

ytd([Date].[Calendar].CurrentMember)

,measures.[Internet Sales Amount]

)

select {measures.[Internet Sales Amount] ,measures.test} on 0

,[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Of course, this isn’t real easy. You still have to learn MDX to be able to do this. And after you learn MDX, there are always more complex things to do with MDX! But if you always use the template, you have a consistent place to start. You can see the results of your calculation immediately. You can fix any errors.

After you have the calculation working fine, you open the Business Intelligence Development Studio, select the cube, go to the calculations tab, select the new calculation button, and paste it in.

I’m going to show this in the Advanced Analysis Services seminar – and also how to do the same thing with DAX in the Denali Tabular Model. DAX is designed to be simpler than MDX. I think it is, but it’s still going to be necessary for everyone to invest some time in order to use it effectively.

Why am I teaching basic MDX and DAX strategy in an Advanced Analysis Services seminar? In my experience, there are many people who are very good at using Analysis Services who have never gotten very far with MDX. I would like to help them get more out of it. And most of us are just getting started with DAX. But it’s only a small part of the seminar – there’s a lot more about performance tuning, configuring, dimensions, partitions, aggregations, data modeling, and all the other good Analysis Services topics.

Categories: Business Intelligence
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: