Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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

Comments

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

Loading comments...