SQL Server 2012

In SQL Server 2012 Microsoft introduced the BI Semantic Model - "BISM". BISM encompasses the different deployment options for Business Intelligence within SQL Server 2012. The highest level choice is between Multi-dimensional Analysis Services and Tabular. Multi-dimensional is the latest incarnation of the hierarchical Analysis Services model which has been in widespread use since SQL Server 2000, and was heavily revised in SQL 2005 and 2008. Tabular is a new deployment option specific to SQL 2012 and based around tables rather than hierarchies.

MDX is the query language of hierarchical cubes, and continues to be used to query SQL 2012 Multi-dimensional. It can also be used to query Tabular models which have been deployed to the xVelocity (previously VertiPaq) in-memory engine within Analysis Services. So Tabular models which have been deployed this way can be queried by any MDX generating client tool (by and large anything which could connect to Analysis Services previously), or also through the DAX language.

Tabular models can also be deployed using Direct Query mode, but these can only be queried through DAX, and not MDX. In terms of the native client tools for SQL 2012, Pivot Tables use MDX, and PowerView uses DAX. To connect to all the BISM deployment modes you would need both Pivot Tables and PowerView. XLCubed felt users would benefit from a consistent experience whichever flavour of BISM was being accessed, and so version 7 can query using MDX or DAX (with the new table object). The diagram below outlines this.

For further information go to:Tabular