SSAS 2012 Tabular Model

Tabular models are in-memory databases in Analysis Services. Using state-of-the-art compression algorithms and multi-threaded query processor, the xVelocity in-memory analytics engine (VertiPaq) delivers fast access to tabular model objects and data by reporting client applications such as Microsoft Excel and Microsoft Power View.

Tabular models support data access through two modes: Cached mode and DirectQuery mode. In cached mode, you can integrate data from multiple sources including relational databases, data feeds, and flat text files. In DirectQuery mode, you can bypass the in-memory model, allowing client applications to query data directly at the (SQL Server relational) source.

Tabular models are authored in SQL Server Data Tools (SSDT) using new tabular model project templates. You can import data from multiple sources, and then enrich the model by adding relationships, calculated columns, measures, KPIs, and hierarchies. Models can then be deployed to an instance of Analysis Services where client reporting applications can connect to them. Deployed models can be managed in SQL Server Management Studio just like multidimensional models. They can also be partitioned for optimized processing and secured to the row-level by using role based security.

Your Ad Here

A single term like BISM suggests that Analysis Services 2012 and PowerPivot are a single, cohesive product, whereas the Tabular and Multidimensional models are actually very different beasts. If you’re going to be working with Analysis Services 2012 on a project the first decision you’ll have to make is which type of model to use, and if you change your mind later you’ll have to start development again from scratch and learn a lot of new skills. I hope one day that the two models will merge again but it won’t happen soon.

Microsoft has correctly identified that many people want to do BI but were put off by the complexity of building Multidimensional models in previous versions of Analysis Services. The simplicity of the Tabular model goes a long way to solving this problem; Tabular also replaces Report Builder models which were really a just a simple semantic layer for people who didn’t like SSAS or had valid reasons to stay with relational reporting. In order not to scare off this market a new name is necessary to avoid the negative connotations that come with “Analysis Services” and “cubes”.

Calling something a “semantic model” suggests that it’s a nice, light, thin, easy-to implement layer on top of your relational data warehouse, with no data duplication (which is often seen as a Bad Thing) involved. In actual fact anyone who has used the Multidimensional model will know you almost always use MOLAP storage which involves all the data being copied in Analysis Services; and I suspect when people start using the Tabular model they will be using it in Vertipaq mode (where again all the data gets copied into Analysis Services) rather than in DirectQuery mode (where all queries are translated to SQL which is then run against SQL Server).