Analysis Services 2012: Tabular or Multidimensional?
If you regularly follow this blog, then chances are likely that you are already well aware of the new Tabular mode in SQL Server Analysis Services 2012. If not, don’t sweat it – we won’t be diving into anything super technical in this post 😎
Analysis Services Tabular is a new mode that I would describe as being somewhat complementary to the traditional multidimensional mode with which most of us (who have been working with Analysis Services prior to 2012) are familiar.
A few of the most distinguishing factors between the two types of models are as follows:
- Data in Tabular models is typically persisted in memory…as opposed to disk
- DAX is the native language for creating measures and querying data out of a Tabular model…as opposed to MDX
- PowerView reports can only be built on top of Tabular models *** (<— those 3 asterisks mean there’s more explanation at the bottom of this post)
At first glance, and a marketing perspective, Tabular models are the bees knees. For most people, thinking in terms of tables and relationships (Tabular) is a bit easier than thinking in terms of dimensions and measures (Multidimensional). It is also suggested that in terms of query languages, DAX (Tabular) is easier to pick up than MDX (Multidimensional). And obviously querying data from memory (Tabular) is faster than querying it from disk (Multidimensional).
Easier to develop with superior query performance…sounds great right? Unfortunately, its not quite that simple. Truth be told, there are tons and tons of caveats to this logic and making the decision to go with Tabular over Multidimensional (or vice versa) is a hard one. After a decent amount of reading and practice with Tabular models, I’m now clinging much tighter to the consultants’ motto…it depends.
So if you’re interested in learning about a few of the primary factors to consider when choosing between Tabular and Multidimensional, then join me this Wednesday for a quick 30 minute presentation on this topic. Below is a summary of the presentation and a link to register…don’t worry, it’s free 😉
The 20-Minute Tabular Model
Date and Time: Wednesday, March 20, 2013 @ 11:00 ET
Presenter: RDA Senior Software Engineer Bill Anton
The Tabular model is the new kid on the block in SQL Server Analysis Services 2012. We will quickly cover the differences between Tabular and Multidimensional models and then demonstrate the process of creating a simple Tabular model using Visual Studio.
And, for those of you interested in taking a deep dive into Tabular models and DAX, I highly recommend the following books:
Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model
The authors of this book… Marco Russo (b | t), Alberto Ferrari (b | t), and Chris Webb (b|t) … also wrote Expert Cube Development, which is probably the best book on Analysis Services (multidimensional) I’ve ever come across. I’ve since made a point to read anything and everything by these guys.
DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX
This book, by Rob Collie (b | t), will get you up to speed with DAX in short time. Rob has an amazing knack for breaking down complex concepts into chunks more manageable by someone new to DAX…giving you just enough detail to accomplish some pretty amazing things while not overloading your brain.
Applied Microsoft SQL Server 2012 Analysis Services: Tabular Modeling
In addition to being the founder of the local Atlanta PASS BI users group, Teo Lachev (b | t) is a SQL Server MVP with deep technical knowledge in the BI stack. He’s also been a huge help in bouncing ideas around when a colleague and I recently ran into a nasty performance problem at a client site (thanks again Teo!).
*** Microsoft is currently working on an update that will enable PowerView reports to run against Analysis Services Multidimensional models. This functionality is in the “Customer Technology Preview” phase and will likely be released later this year.
This is going to be a big deal! I have run across a number of businesses that love love LOVE PowerView…but they can’t use it. These businesses already have a perfectly good cube and despite how sexy PowerView is…almost too sexy to be a Microsoft product…it doesn’t justify the cost of a multidimensional-to-tabular model conversion.