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

About James Serra

James is SQL Server MVP and a independent consultant with the title of Business Intelligence/Data Warehouse/Master Data Management Architect and Developer, specializing in the Microsoft SQL Server BI stack.
This entry was posted in SQLServerPedia Syndication, Tabular. Bookmark the permalink.

13 Responses to Tabular model: Not ready for prime time?

  1. Dave DuVarney says:

    James, thanks for posting this. Tabular is nice, but not a full replacement for Multidimensional. The list of missing features really points to some limitations. I would echo your point that many features aren’t needed now, but likely will be used in the future. Number one on my list would be many-to-many relationships. As I see cubes evolve in organizations, many-to-many relationships are almost always something that makes it’s way into a model.

  2. Jon Capezzuto says:

    I think it’s incorrect and somewhat misleading to claim that tabular isn’t ready for prime time. It’s all about picking the right tool for the right job. This is a very comprehensive list, but I’ve used tabular on many projects now where none of these limitations have been a requirement. Why spend weeks setting up multidimensional when I can roll out the same solution using tabular in hours/days if I know I won’t need these advanced features?

    • James Serra says:

      Hi Jon…I’m not claiming tabular should never be used, just to understand all the limitations before you use it. I’m glad to hear you have used it on many projects with no issues. I like tabular and hope it becomes widely used – I just have seen a number of projects have to reverse course after starting with tabular and then running into a roadblock that required rewriting the cube in multidimensional.

  3. Calvin Ferns says:

    Hi James, while I agree it is a V1 product and these features should be incorporated out of the box in future releases I would disagree with the general tone. Personally I love the query speed and development speed and will put up with having to ‘hack’ around to manage many to many’s and scope statements. I think once you get your head into it the limitations aren’t as restrictive as they look when put in a big long list!

    Anyway, I’m all for pressure on MS to get more features into the product so carry on, but give it a fighting chance. (caveat: I probably wouldn’t yet use it in a financial environment, you will just have too many hacks!)

    • James Serra says:

      Thanks for your feedback Calvin. I really like tabular also, it just has been my experience with my clients that just about all of them are forced to use multidimensional because one or more of the limitations for them was a show stopper. I’m expecting most of these limitations will be removed by Microsoft and I can tell clients with confidence that tabular is the way to go for their projects.

  4. Thanks for the post James.

    First thing I would like to say is that I agree that there is lots of work for tabular to catch up to the feature-rich multidimensional. Without saying anything too controversial, the corporate MS corporate BI offering has been playing second fiddle lately.

    However, I have to say that tabular does make sense for many customers today. For most customers, having fast performance is more important than most of the features listed – most of which 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 translations, unary operators and writeback, but not for the majority of implementations.

    Scoped-cell assignments is probably 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 memory limitation, many customers 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.

    Regarding only recommended for simple models, I guess this depends on the definition of “simple”. The more complex models (as defined by more tables/dimensions/facts) are often the ones that suffer from performance issues in multidimensional and can benefit from tabular in memory.

    As I think you allude to…

    When you start down multidimensional or tabular, you can’t change your mind without starting development again.
    Microsoft are more likely to build new features and put future development dollars into tabular than multidimensional.

    So despite the correct statement that that a project may need some of the currently unsupported features at a later date; conversely one could argue that, as new features are built into tabular, could be stuck with multidimensional and not able to leverage better tabular capabilities in the future.

    Thank you for starting a healthy discussion.

  5. Pingback: Multidimensional or Tabular | Christian Wade's Blog

  6. Marco Russo says:

    James, I think that the sentence “If you are using Power View, the DAX statements it generates against the tabular model are much less efficient than the MDX statements Power View generates against the multidimensional model” should be reviewed. First of all, Power View does not generate MDX, but send DAX queries to Multidimensional model. Second, the DAX code that runs on a Multidimensional model sometime is faster than MDX, because in DAX you can make some assumptions in the query that cannot be made in an MDX one. Power View does not use this type of optimization, anyway, so in my experience a well-written DAX query performs at the same speed than an MDX one (even if differences might be generated by the cache).
    Talking about performance, the very reason why Tabular is adopted in some scenarios is because it’s faster than Multidimensional on many-to-many relationships and for distinct count calculations. It’s not always easy to obtain the best performance, but several ISVs using Tabular as their analytical engine received high benefits from this approach.

    There is another point that should be clarified. You say “Does not support ability to apply visual totals (security)” but actually the opposite is true. When you use Role based security in Tabular, you only get visual totals (without the performance issues you might have in Multidimensional for such a choice) and you cannot obtain non-visual totals. So it is true that you don’t have the same features than Multidimensional in this case (a partial workaround is using calculated fields for generating visual totals visible to anyone), but the available one is the opposite you indicated.

    My direct experience is that many of the features that are missing are not required in all the projects and I’ve seen models growing well without needing them. The real pain, today, is the development environment. This is not so productive as it could (and should) be, discussion about that would be long, but this is an area where many BI developer using Tabular have more problems (and tools like BIDS Helper or BISM Normalizer are not only workarounds but real life savers – even if they are not enought and much more should be done in this area).

    • Lovemore says:

      Thanks for clarifying these issues Marco. I view Tabular model as a complement to the Multidimensional model. i will be using it for proof of concepts and other quick developments. Once happy, i will consider updating the OLAP cube.

  7. Alejandro Leguizamo says:

    Well, in general, I have to agree on the fact that Corporate BI has been left behind by Microsoft (With few very important exceptions like SIvakumar Harinath’s work to allow Power View for SSAS MD, and notice there are a few strongly and desperately needed as Dimension Partitioning and many others –see Chris Webb’s delightful blog at http://cwebbbi.wordpress.com/2008/04/30/things-id-like-to-see-in-analysis-services-2011/ )

    About Tabular? Well, I would put a HUGE “Use on your own risk” sign on top of it. IF and only if you are certain that some features will never ever be used down the road of your BI Project , then ok, go with it. But if you want a safe bet, then go Multidimensional. Specially if you are any kind of BI Manager , that has to talk to the business community and justify critical Information Management decisions, then Multidimensional is the way to go, cause it sure minimizes risks (Not only present but future).

    Until Tabular doesn’t grow to, at least , provide exactly the same capabilities that Multidimensional offers, then I see very few (besides speed) compelling reasons for using it in a Corporate environment. (For instance, multideveloper experience is already bad as it is on Multidimensional, then imagine Tabular)

    These are my 0.02 cents

  8. Pingback: Real-time query access with PDW | James Serra's Blog

  9. Pingback: Real-time query access with PDW - SQL Server - SQL Server - Toad World

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>