Azure BI

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: http://msdn.microsoft.com/en-us/library/hh212940(v=SQL.110).aspx. 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)

	Multidimensional	Tabular	PowerPivot
Actions	Yes	No	No
Aggregations	Yes	Yes	Yes
Calculated Measures	Yes	Yes	Yes
Custom Assemblies	Yes	No	No
Custom Rollups	Yes	No	No
Distinct Count	Yes	Yes (via DAX)	Yes (via DAX)
Drillthrough	Yes	No	Yes (detail opens in separate worksheet)
Hierarchies	Yes	Yes	Yes
KPIs	Yes	Yes	Yes
Linked objects	Yes	No	Yes (linked tables)
Many-to-many	Yes	No	No
Parent-child Hierarchies	Yes	Yes (via DAX)	Yes (via DAX)
Partitions	Yes	Yes	No
Perspectives	Yes	Yes	Yes
Semi-additive Measures	Yes	Yes	Yes
Translations	Yes	No	No
User-defined Hierarchies	Yes	Yes	Yes
Writeback	Yes	No	No

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.

-JP