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…
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.
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…
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.
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.
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