1. 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.

   Reply ↓

2. 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?

   Reply ↓

   • 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.

     Reply ↓

3. 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!)

   Reply ↓

   • 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.

     Reply ↓

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.

   Reply ↓

   • Great points Christian, thanks for your feedback!

     Reply ↓

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

6. 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).

   Reply ↓

   • 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.

     Reply ↓

7. 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

   Reply ↓

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

10. First of all, I must say it’s good to see someone who views this product as I do. I must say that I’ve seen so many demos online GLORIFYING tabular like it was the second coming of Christ. This product is definitely NOT ready for prime time. I totally agree with you. Most if not all enterprise cubes require most of the functionality you listed above that’s missing from Tabular. Frankly if not for the distinct count and very very large many-to-many bridge tables, I would not even consider Tabular given it’s long list of shortcomings.

    One thing you left out that I couldn’t believe that they left out in tabular was the Member Property functionality. Quite a number of my current and former clients generate detailed reports with 25+ columns. Try adding that many columns to a report running from Tabular. You lose that whole vertipaq storage compression advantage because the column store structure has to be broken down, decompressed and returned more as rows many time over (for 25+ columns in the report) basically resembling the original DW database record they processed cubes from in the first place. Yes 25+ column reports don’t make sense but try telling that to a financial analyst who has used that report for the last 10+ years. I know Multidimensional cubes wouldn’t perform faster but I have used member properties and dimension attribute relationships to built such reports. You end up adding only 3 attributes to such a report and the rest being member properties of these three attributes. Being member properties, they do not participate in any cross joins and performance is way much faster.

    The funny thing is that the demos you see online on Tabular have billions of rows (number of rows is basically not relevant in column storage as long as the cube fits memory) and 3 – 4 columns. How about running a report from tabular cubes with 25+ columns and see how badly that performance degrades?

    Reply ↓

11. Pingback:Notes on Deciding whether to implement a Tabular or Multidimensional Model - John Desch (Microsoft Consulting Services - BI Blog) - Site Home - MSDN Blogs

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>