Home > Multi-dimensional SSAS, SQL 2012, Tabular SSAS, Theory > Multi-dimensional or Tabular? Which model to use?

Multi-dimensional or Tabular? Which model to use?

SQL 2012 gives us two options for building Analysis Services cubes.  Standard multi-dimensional cubes, and new tabular mode cubes derived from Excel’s PowerPivot plug in.  Both solutions offer the ability to build high-performing enterprise quality databases.

Most of the time, it’s not going to be a clear cut decision on which technology to use.  Both have various benefits and gotchas.  Both – thanks to BISM – will allow users to interact in the same tools in identical manners.

There are a few situations however, when there is a clear choice.  The following options are not a comprehensive list.  They are, however, what I feel are some of the most compelling reasons to chose one paradigm over the other.

Choose Multi-dimensional if you need…

Writeback

This is an easy one, because writeback – in either measure group, or dimension form – is not available in Tabular Cubes models at this point. So, if your project calls for a writeback solution, you’ll need to build a Multi-Dimensional cube.

Role Playing Dimensions

Yes, I know that Tabular cubes now support mutiple relationships between obejcts, and there is a DAX function called USERELATIONSHIP() that allows you to traverse non-default relationships. This certainly works, however, it’s going to be a PAIN for users to use. In a role playing dimension scenario, the user would now have to know that the Order Amount measure will ONLY work with the Order Date (not Invoice Date, or Ship Date). In a multi-dimensional cube, this scenario would work, although it might not provide the answer a user is expecting.

If you need to create one or two multi-relationship tables in a Tabular cube, that’s fine. But, if your design calls for more than two Role-Playing dimensions, my vote would be to save yourself the trouble and model it in a Multi-Dimensional project.

Many-to-Many

Like above, yes, I know these are possible using Tabular cubes. They are complicated to set up in DAX though. They also require some pretty hefty SELECT CASE statements to work, and are rather unflexible when modeled in a Tabular Cube.

So, like above, if you only need one or two, Tabular is probably OK. Any more than that, or if there are a lot of measures associated with the M2M, stick with Multi-Dimensional.

Choose Tabular if you need…

Performance

Since Tabular modeled cubes use the Vertipaq (1) engine, and they run completely, they are fast. Very fast. No more designing aggregations, and keeping the balance between speed and disk usage. Heck, no more reading from disk. Just pure blatant speed, thanks to RAM.

If consistent sub-second performance is your Number One (2) concern, go with Tabular.

External Data

If you have a need to bring some external data – like Census statistics – into your model, it’s MUCH easier done with Tabular. You don’t have to bring that data into your warehouse or marts, just simply create a connection to where ever this data is, and bring it in.

This *can* be done in Multi-Dimensional, but it’s going to involve some data modeling, ETL, and probably some other processing to get it to the point of being ready to model in a cube. Save yourself the trouble and use Tabular.

PowerView

If you would like to use the newest reporting technology, PowerView, then you have no choice – at this time – than to use Tabular. The PowerView creates DAX queries to send back to the data source, and currently BISM will not translate DAX to MDX (it does translate the other way around). I’m sure, post RTM of SQL 2012, we’ll see an update that will enable functionality to build PowerView reports against Multi-Dimensional cubes, but for now, that option doesn’t exist.

Maybe you need both…

Don’t forget about the option of building both types of models against the same warehouse.  Say you need Writeback, but also want to build PowerView reports.  So, what’s stopping you from building a Tabular cube for the PowerView and a simple Multi-Dimensional cube just to support Writeback?

As updates are released for both models, and the BISM architecture, this list will most likely change.  There will probably always be some compelling reasons to choose one model or the other, at least as long as both models exist.

(1) – Rob Kerr – MSBIAcademy.com — “Why is Vertipaq So Fast?”
(2) – Wikipedia

  1. luke
    April 4, 2012 at 9:05 pm

    Very helpful, thanks…
    As we dig into sql2012 this is great knowledge to have.
    Curious to know if you know of a key book/text on DAX since its so much newer than MDX?

    • April 5, 2012 at 10:53 am

      Thanks Luke, much appreciated.

      This book, http://amzn.to/HpkbOT, by Marco Russo & Alberto Ferrari, is one of the best DAX introduction guides that I’ve read. It is for the previous version, but still very applicable. Watch for an update to the 2012 version, as I’m expecting a flood of new SQL 2012 books out soon!

  1. February 26, 2012 at 1:40 am
  2. April 4, 2012 at 10:01 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 882 other followers

%d bloggers like this: