Last November I wrote about the future of SSAS and BISM (see here for the original post). Now with the release of CTP3 of SQL Server codenamed ‘Denali’ (download here), the new BISM and the two different modes are open to the public so I can take a look if my thoughts are still valid.

First take a look at what Microsoft gave to us with the release of the CTP3. Analysis Service can now run in two modes: the ‘old’ UDM mode, now called Multidimensional mode and the new VertiPaq mode, called Tabular mode. A good overview is given in the blogpost by TK Anand. Basically the two modes can perfect live side by sides in a corporate environment.

But which of the two model has the future and therefor the priority of the product team? I still believe that the PowerPivot part of the team and the Tabular mode has the higher priority. They developed the very powerful VertiPaq engine and they are not going to drop that asset. At the other hand, the Multidimensional model has the very powerful functional engine. I got a very high overview of the engine a part of the SSASMaestros course, and it is a state of the art piece of technology. But as PowerPivot started of as a branch of the SSAS code, it has got the same FE, but it is not that big used at this moment. But that will probably change in future releases, especially if you take a look at the improvements in PowerPivot v2.

At this moment there are still big differences between the Multidimensional and Tabular modes of Analysis Service. If you take a look at the different features it is still easy to select the correct mode for the business need. The comparison table can be found at the following MSDN link: The table contains also the PowerPivot mode of Analysis Service which is actually the third mode of Analysis Service. (Technically there is also a fourth mode: the client PowerPivot mode)

Calculated MeasuresYesYesYes
Custom AssembliesYesNoNo
Custom RollupsYesNoNo
Distinct CountYesYes (via DAX)Yes (via DAX)
DrillthroughYesNoYes (detail opens in separate worksheet)
Linked objectsYesNoYes (linked tables)
Parent-child HierarchiesYesYes (via DAX)Yes (via DAX)
Semi-additive MeasuresYesYesYes
User-defined HierarchiesYesYesYes
I think that in the end with upcoming versions the difference between the Multidimensional and Tabular version will be gone. I expect that the MOLAP storage engine will become part of the Tabular mode and the Multidimensional mode will become the PDW version of Analysis Service for the (very) big datawarehousing solutions. I don’t think that the Yahoo 12 TB Cube will perform as good as it does now when using the Tabular mode.