SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Tabular model: Not ready for prime time?

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?

Understanding the difference between SQL Server Analysis Services Multidimensional and Tabular models

BISM Tabular – Proceed with Caution

Updates about Multidimensional vs Tabular #ssas #msbi

Multidimensional or Tabular

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...