BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Analysis Services - Tabular or Multi-Dimensional Model?
- What the two SSAS Server modes have in common
- Creating a tabular model
- Creating and using a multi-dimensional model cube
- Comparing tabular model DAX with multi-dimensional MDX (this blog)
- Other differences between multi-dimensional and tabular
- 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:
- DAX will run more quickly than MDX; and
- 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.
- Analysis Services - Tabular or Multi-Dimensional Model?
- What the two SSAS Server modes have in common
- Creating a tabular model
- Creating and using a multi-dimensional model cube
- Comparing tabular model DAX with multi-dimensional MDX (this blog)
- Other differences between multi-dimensional and tabular
- Conclusions and a recommendation