Difference Between between the two modes of SSAS: Multi dimensional mode vs Tabular mode

Question

I was wondering if anyone here knows the exact differences for these 2 modes, more specifically:

• What can we do in one model that we can't do with the other? (Multi-dimensional vs Tabular and vice versa)

• How is the data stored in one model versus another?

• If I am wring an SSRS / PowerBI / Excel report against this, what limitations does one model have over the other?

• Does the tabular model have cubes? If not, what is the alternative storage medium and how does it differ from cubes (maybe provide for me some background on what cubes are to begin with)

• What are the differences in security considerations? As I understand, with the Multi-dimensional model, row-level, column, level and even cell-level security can be applied - what is available with this for the tabular model?

• Also, as I understand SQL Server 2016 is moving to using the Tabular Model by default and that there may be some differences/improvements over what is current in use (SQL Server 2014) - can you please provide a list of what those are?

Thank you so much in advance.

Answer 1

A good place to start might be these articles which should be accurate as to the differences in SSAS 2014.

• Advice on the decision points for choosing to build a Tabular or Multidimensional model
• Paul Turley’s high-level description of Tabular strengths and weaknesses
• Dimension relationships
• Summary level presentation

Many-to-many relationships and writeback and scope statements and non-visual dimension security are some of the biggest missing features in SSAS 2014 Tabular in my opinion.

Tabular security is row based and just supports visual totals, not non-visual totals or cell security. But in many cases you don't want to use cell security for performance reasons.

Tabular uses in-memory columnar storage. Multidimensional uses disk-based row-based storage. So scanning a billion row fact table requires reading all columns from disk in Multidimensional and takes a minute or two to return a query on a fact table that large. If you optimize the Multidimensional model by building an aggregation then the query may take seconds. Tabular just scans the columns used in the query and simple queries or calculations even on a billion row table may return in under a second.

With SSAS 2016 Tabular the bidirectional relationship was added which was a very big deal for modeling flexibility and allowing many-to-many relationships. And parallel partition processing made loading large models feasible.

SQL 2017 installer for SSAS has Tabular as the default.

If you have the option for using SSAS 2016 Tabular or above it is highly recommended for performance and modeling flexibility. Here is what's new in SSAS 2016 and SSAS 2017.