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):

Total value

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:

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:

An MDX 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.

 

  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 has 0 threads Add post