Choices in the New World of Microsoft BI
The next version (Denali) of Microsoft SQL Server Analysis Services has a new framework called the Business Intelligence Semantic Model. But look inside the new model, and you’ll see that there are two models sitting side by side – the multidimensional model and the tabular model.
The multidimensional model is familiar to those who have been working with Analysis Services in the past – it’s almost exactly the same as in AS2008 – a few improvements, of course, but very recognizable.
The tabular model is familiar to those who have been working with PowerPivot in Excel 2010 and/or SharePoint. It is based on the same Vertipaq engine.
So how do you choose between them?
You can use both, of course, but there’s not a straightforward way to convert from one to the other. (I’m working with some other folks to build a tool that will help convert back and forth. If you’re interested in that conversion/migration, please let me know.)
If you’re familiar with previous versions of Analysis Services, you’ll probably be more comfortable with the multidimensional world. If you’re working with PowerPivot, you’ve got the tabular.
But what are the advantages of each?
The tabular model is supposed to be easier – no aggregations, partitions appear to be less significant, DAX is supposed to be easier than MDX, working directly with tables in the model is supposed to be easier than creating dimensions and measure groups for database developers who are used to working with tables.
The tabular model can also be faster for querying. It’s faster out of the box, without any configuring. It may be faster in answering certain queries (Distinct Count?) no matter how the multidimensional model is configured. We still have a lot of learning to do about that.
The multidimensional model can handle more data efficiently. The tabular model is limited because it holds all of its data in-memory. That’s an advantage for speed, but not for scalability.
But that’s not the end of the story – because the tabular model has a Direct Query mode, querying the source relational database tables directly as it’s returning data to the end users. For that to work efficiently, there’s relational database optimization to be done – notably indexing – and now, in Denali, columnar indexing.
But we can query the source relational database tables with the multidimensional model, too – we have ROLAP and HOLAP. How are they going to compare with the tabular Direct Query in terms of speed, ease of configuration, and scalability?
And when we start talking about this, there are a lot of other details. DAX (the tabular calculation language) supports a lot of logic, but not as much as MDX in the multidimensional side. And then, in Direct Query mode, some of the best DAX functions aren’t allowed – at least for now!
So there are a lot of things to consider. You can check this all out with the publicly available CTP3 of SQL Server Denali (and, of course, come to my seminars in Scandinavia!) – but I expect it’s going to take all of us some time to learn how to make these choices.
And we also have to try to figure out – how do we expect Microsoft to enhance each of these options in the future?