SQL Server 2012: Multidimensional vs tabular

To expand on what I talked about in SQL Server 2012 (“Denali”): Details on the next version of SSAS, there is a new data model called Tabular.  So when you are creating a new project, which model should you use, Multidimensional vs Tabular?  Well, there is no clear-cut answer, but there are some factors that can make you choose one over the other:

  • If you want to use DAX, you have to use Tabular
  • If you want to use Power View, you have to use Tabular
  • If your dataset is extremely large, go with Multidimensional
  • If you need writeback support, you have to use Multidimensional
  • If you need access to many different external data sources, choose Tabular
  • If you need complex calculations, scoping, and named sets, choose Multidimensional
  • If you need extreme speed and consistently fast query time, choose Tabular
  • If you need Many-to-Many relationships, choose Multidimensional (can be done in Tabular but difficult)
  • If your solution requires complex modeling, choose Multidimensional
  • If you need any of the following features, you must use Multidimensional: Actions, Custom Assemblies, Custom Rollups, Custom Drillthrough Actions (but BIDS Helper adds support for actions in a PivotTable in Excel but not in PerformancePoint), Linked objects, or Translations

I would recommend going with Tabular if possible, as it is better to use for these reasons:

  • It uses your existing relational model, so there is usually no need to create a star schema (which usually means using ETL to create new dimension and fact tables in a Data Mart or Data Warehouse).  Complex DAX may require a star schema
  • It uses DAX, which is much easier to use than MDX, and least for the basics (but mastering DAX and optimizing DAX is hard)
  • It uses xVelocity/Vertipaq, which is much faster than Multidimensional
  • It is faster to develop
  • It’s less expensive to use in terms of time, resources and skill requirement
  • You can extend the data model without reprocessing the whole database by using calculated columns.  Instead it requires a much faster “Process Recalc”
  • In situations where a multidimensional model requires the use of snapshots (i.e. quantity totals by day), tabular is better because it can avoid snapshots by making up-to-date calculations at query time (thanks to its speed because the data is in memory)
  • In situations where a multidimensional model requires a distinct count (i.e. how many new distinct customers this month), tabular is better because it stores data in a way that discount count is very fast (writing a measure vs changing the data model and reprocessing the data)

Keep in mind the option on creating both types of models against the same data warehouse.  Say you built a multidimensional model before tabular was released, but now want to use Power View.  You can always just build a tabular cube to allow for the use of Power View.  Also note that Tabular will only work if there is sufficient memory for the entire cube in memory, otherwise you won’t be able to open the database.

You can also use the tabular model to prototype in since it’s so much easier and quicker to use, and then decide later whether to convert it to a full-blown multidimensional model.  And because of its ease of use, the tabular model could also be used by power business users to prototype a cube in, and then later IT can take that model and enhance it in tabular or convert it to multidimensional (self-service BI at it’s best).

Table-1
Table-21

More info:

Why to use #Tabular in Analysis Services 2012 #ssas

Video Vertipaq vs OLAP: Change Your Data Modeling Approach

Decisions: PowerPivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012

Observations on interoperability between BISM Tabular and OLAP clients

Comparing Tabular and Multidimensional Solutions (SSAS)

So what is the Business Intelligence Semantic Model or BISM really?

Multi-dimensional or Tabular? Which model to use?

Creating Your First Tabular Model (part 1)

Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services

SQL Server 2012 Tabular versus Multidimensional

Video BISM: Multidimensional vs. Tabular

Choosing an Analysis Engine

The choice between Tabular or Multidimensional models in SQL Server Analysis Services 2012

Did Big Data Kill OLAP Cubes?

Introducing the BI Semantic Model in Microsoft® SQL Server® 2012

What’s the future of SQL Server Analysis Services

OLAP Rigidity wins against Tabular?

Decision Matrix

SSAS ends with 2012 version?

Whiteboard Wednesday #3: MOLAP vs. Tabular in Analysis Services

Taking the Tabular Journey

Getting started with Tabular Model in SQL Server 2012 – Part 1

SSAS Tabular vs Multidimensional

SSAS Tabular Models: The Good, the Bad, the Ugly & the Beautiful (comments)

Choosing between Analysis Services Multidimensional and Tabular Models Part 1

Analysis Services Relationship Limitations and Workarounds

The SSAS Tabular Model

About James Serra

James is SQL Server MVP and a independent consultant with the title of Business Intelligence/Data Warehouse/Master Data Management Architect and Developer, specializing in the Microsoft SQL Server BI stack.
This entry was posted in Data warehouse, SQL Server 2012, SQLServerPedia Syndication, SSAS, Tabular. Bookmark the permalink.

17 Responses to SQL Server 2012: Multidimensional vs tabular

  1. Cathy Dumas says:

    Hi, many-to-many is supported in tabular. Although there are no many-to-many dimensions, many-to-many functionality can be implemented in DAX. Please see the Many to Many revolution white paper, located at http://www.sqlbi.com/articles/many2many/, for a description of how to implement in tabular.

    Also drillthrough is supported in tabular, although custom drillthrough actions are not supported. However, custom drillthrough actions can be implemented using BIDS Helper.

    Computing calculated columns in tabular does require a processing operation at the database level, called a Process Recalc. Obviously just doing a Recalc is faster than getting the data, but for a sufficiently large data set it is not free.

    Not sure if I agree with your statement that a star schema is not required for tabular, it really depends on the scenario. ETL might be required to materialize bridge tables in the data warehouse for many-to-many calculations. It all depends on the complexity of the DAX you are writing.

    Finally, regarding skill level for tabular – basic tabular is easy, mastering DAX and optimizing DAX is hard. Pros definitely need to make a real investment in a tabular skillset before they will be able to deliver performant models.

    People should also think carefully about the differences between DirectQuery and ROLAP if real time implementations are required, there are pros and cons to the various scenarios.

    Otherwise I think you have hit the major points here, and glad you like tabular.

  2. Jorge Fernández Navarro says:

    Good post James. I´m thinking in upgrade our solution, based on MSBI2008, my option was creaating both types of models, tabular for some users and uses, and MD for others, your post confirm it to me.

  3. Devin Knight says:

    Great post James! Like you said I see a lot of companies running both for a while.

  4. Pingback: Data Warehouse vs Data Mart | James Serra's Blog

  5. Pingback: Reporting and DW recommendations » davoscollective.com

  6. Pingback: Think Data Mining – OLAP versus Vertipaq

  7. Srini says:

    Thanks James for very useful information.

    Could you provide some insight on how security can be implemented in Tabular Model or any example would be very helpful

  8. Pingback: Microsoft SQL Server 2012: Tabular Model Resources « blacksheepbi

  9. Pingback: Types of BI « Smarter SQL

  10. Pingback: SQL Server BISM Tabular Model « Sladescross's Blog

  11. Pingback: Presentation Slides for Building an Effective Data Warehouse Architecture | James Serra's Blog

  12. Pingback: Tabular model: Not ready for prime time? - SQL Server - SQL Server - Toad World

  13. Pingback: Tabular model: Not ready for prime time? | James Serra's Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>