While the tabular model in SSAS makes it much easier to build cubes than the multidimensional model, I am not seeing it used as much as I expected. One reason is if you are a BI developer and have been using the multidimensional model for a while, you tend to stick with the tool that you are most familiar. You will need a compelling reason to learn a new tool and make the switch, and with the tabular model being a “version 1″ with some missing features, there is little incentive to make the change.
And those missing features can be show stoppers:
- Partitions are supported, but are processed serially
- The cube needs to fit in memory, so there is a size limitation, meaning does not support large datasets
- No cell-level security
- Many-to-many relationships not built into the model, requires a work around using DAX
- Does not support custom Drillthrough Actions (but BIDS Helper adds support for actions in a PivotTable in Excel but not in PerformancePoint)
- Does not support reporting actions or standard actions (URL, HTML, DataSet, RowSet, and other elements)
- Does not support Scope assignments, work around is to use XMLA
- Parent-child hierarchies require DAX expressions
- Not extensible with .NET custom assemblies
- Does not support Writeback
- Does not support language translations
- Does not support named sets
- Does not support role-playing dimensions
- Does not support ability to disable visual totals (security)
- Does not support ragged hierarchies, work around to use HideMemberIf with BIDS Helper
- Does not have ability to override Default Member
- Does not support more than one cube in a database
- Does not support custom rollups (especially useful when aggregating data following a chart of accounts)
- Does not support linked objects
- Does not support unary operators
- Does not support custom formatting of measures
- Does not support Data Mining
- Does not support calculated members
- Does not support cell calculations
- Does not allow for display folders, work around is BIDSHelper
- Does not allow for the naming of the All level
- Supports real-time access using Directquery (multidimensional uses ROLAP), but Directquery has two limitations: won’t support PerformancePoint and Excel (MDX queries are not supported for a model in DirectQuery mode), and does not cache results
- Not available in SQL Server Standard Edition
It seems most developers are waiting until the tabular model adds these features before using it for new projects. While you can look at the list of show stoppers and determine that your new project won’t need them now, it is risky to assume you won’t need one of those features later. Then what?
The tabular model makes sense for proof-of-concepts or small projects (especially if a user has created a model in Power Pivot as you can import that workbook into the tabular model). It can also be used for simple models requiring the absolute best performance for the user experience. Right now I can’t recommend it for anything else. I was really excited about the Tabular model when it came out, but all these limitations make it hard for me to recommend it to clients to use for new projects. But it is definitely the future for Microsoft and I am expecting to see more features for the tabular model and very little enhancements to the multidimensional model.
More info:
SQL Server 2012: Multidimensional vs tabular
Building Real-World Microsoft BI Dashboards Today
Decisions: PowerPivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012
Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services
Comparing Tabular and Multidimensional Solutions (SSAS)
SSAS Tabular Models: The Good, the Bad, the Ugly & the Beautiful (comments)
Multidimensional vs Tabular – Making the correct decision
Microsoft SSAS: Should I use Tabular or Multidimensional?
BISM Tabular – Proceed with Caution
Updates about Multidimensional vs Tabular #ssas #msbi
