WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 519 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

Should you use tabular or multi-dimensional SSAS mode? Here's the answer!
Part five of a seven-part series of blogs

If you're building a data warehouse, you'll want to know whether you should be creating cubes using the legacy multi-dimensional Analysis Services server mode, or creating data models in the new tabular mode. This blog gives Wise Owl's take on the subject.

1. Analysis Services - Tabular or Multi-Dimensional Model?
2. What the two SSAS Server modes have in common
3. Creating a tabular model
4. Creating and using a multi-dimensional model cube
5. Comparing tabular model DAX with multi-dimensional MDX (this blog)
6. Other differences between multi-dimensional and tabular
7. Conclusions and a recommendation

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 07 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Comparing tabular model DAX with multi-dimensional MDX

Not only is the architecture different for the two approaches to using Analysis Services; so too is the underlying language.

## An example of DAX

Suppose that we want to show the total value of transactions in a pivot table (that is, we want to sum Price * Quantity):

This pivot table shows the total quantity sold, and also the total value of all transactions.

Here's a DAX formula which would give the total value measure:

A typical DAX expression, giving total price * quanttiy.

## An example of MDX

Suppose we want to calculate the transaction value as in the above example.  Here's an MDX calculation which would create the necessary measure:

A measure in MDX to create the expression for summing.

As this shows, neither DAX or MDX look remotely like SQL, although DAX has a lot in common with the Excel formula language.

## Which is better?

I don't claim to have built huge models containing terabytes of data, but reading through other blogs, it seems reasonable to state two truths:

1. DAX will run more quickly than MDX; and
2. MDX can be used in more reporting scenarios.

However, I think the DAX versus MDX question is a red herring.  You wouldn't choose Oracle over SQL Server because you preferred Oracle SQL to T-SQL, and you shouldn't choose an Analysis Services server model based on the language that you'll have to learn.