Multidimensional or Tabular
November 9, 2013 5 Comments
This post is not a list of multidimensional features unsupported by tabular. This has been documented already by various reliable sources including the following posts, as well as PASS presentations.
- Tabular model: Not ready for prime time?
- Decisions: PowerPivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012
First thing I would like to say is I agree that there is lots of work for tabular to catch up to the feature-rich multidimensional. The tabular-model designer inherited from Power Pivot is sluggish for models with lots of tables. The Excel-like DAX formula bar is, to put it politely, annoying. Without saying anything too controversial, MS corporate BI has been playing second fiddle lately.
However, tabular does make sense for many customers today. For most customers, having fast performance is more important than the unsupported features – which invariably either have “workarounds” or are fringe use cases.
On the workarounds, if the same functionality can be delivered to the business, they don’t care if we technical people see it as a “workaround” because it’s not delivered the same way we are used to. And the business people are the ones that matter. This applies to many-to-many relationships, parent-child hierarchies, role-playing dimensions (can create multiple instances of same table), and various other items.
For what I’m calling the fringe use cases, the supportability of some of these does not make sense for many customers. Hand a solution to support that uses MDX stored procs, extensive scoped-cell assignments and they will struggle. How many implementations use these features because the developer thought they were cool rather than having any real business need? I think quite a few.
Other use cases may be showstoppers like unary operators and writeback, but not for the majority of implementations.
Scoped-cell assignments are in the potential showstopper list too, but in most cases if calculation logic is pushed to the ETL layer (where it belongs if not one of the strengths of the cube/tabular model like aggregated level calcs, or those which would cause a data explosion problem at the relational level, etc) to avoid the formula engine where possible, then DAX is a pretty capable and powerful language for calculations built into the tabular model.
On the tabular memory limitation, many customers I’ve talked to are worried they won’t fit into memory when they are actually nowhere near the upper limit of what they can relatively easily get on a server (especially when limited to the required data). Also, more memory will only become more viable in the future. For a multi-terabyte data warehouse implementation like a Yahoo.com, then yes but again this is a fringe use case.
As noted elsewhere,
- When a project chooses multidimensional or tabular, it is not possible to change your mind without starting development again.
- I think it is widely accepted that Microsoft is more likely to build new features and put future development investment into tabular than multidimensional.
It is a valid statement that a project may need some of the features unsupported by tabular at a later date, which could be a problem. Conversely a project may encounter performance issues with multidimensional that were not anticipated at the start of the project. One could also argue that, as new features are built into tabular, could be stuck with multidimensional and unable to leverage better tabular capabilities in the future.
Update Nov 13 2013 – post from Marco Russo: Updates about Multidimensional vs Tabular