Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The SSAS Tabular Model

By Phil Factor,

The SSAS Tabular model in SQL Server 2012 would seem to be a great addition to SQL Server for the users of Business Intelligence, in that it provides a rapid and adaptable means for business users to select data for analysis using familiar tools.  For simpler data structures and smaller data volumes, it is a good analysis tool but I get the impression that take-up has been less than you’d expect, given the more general enthusiasm in the industry for in-memory tabular databases. I can’t help wondering whether the restricted means of developing and deploying Tabular model is to blame for this.

The tabular model is not a replacement for SSAS’s multidimensional ‘OLAP cube’ model though it’s more appropriate for some BI use cases. It is basically a server version of PowerPivot that provides ‘Individual BI’, and can import Excel PowerPivot models. The two BI products are entirely separate, though they both run in SSAS.

The xVelocity analytics engine, which underlies ColumnStore, PowerPivot and the SSAS tabular model, is trouble-free. It’s had three years of production use since its introduction with PowerPivot. It seems to work faster than conventional SSAS multidimensional MOLAP models, even though it’s less versatile. It combines a familiar relational way of organizing data with an intrinsic understanding of the cube. This allows you the flexibility of querying data in ad-hoc ways that aren’t defined as regular relationships. The new DAX language isn’t hard to pick up for anyone who understands SQL.

The problem would seem to be the client tools, in that SSDT is the tool for developing and deploying Tabular databases via tabular model project templates, usually by importing Excel PowerPivot models. Only one person at a time can develop the schema because it is held in one XML file. The tool is quirky and doesn’t support every feature of SSAS. Working with more than twenty tables is painfully slow. Although you can use SSMS manage a tabular database in very limited ways, Microsoft wants you to use SSDT and redeploy when you want to make a change.

Once you’ve deployed SSMS comes into its own for querying in MDX or DAX since you can open up an MDX query window. In fact, Excel, Reporting Services (Power View) and SSDT can all be used, so there is no problem once the model is deployed. Before then, it’s not so much fun.

Total article views: 509 | Views in the last 30 days: 3
Related Articles

SQL Server 2012: Tabular Models vs PowerPivot Models

In SQL Server 2012, there is a new data model, called tabular, that is part of the new feature calle...


Tabular Model

One of the most exciting features in SQL Server 2012 is the introduction of the Tabular Model. The T...


Implementing Tabular Data Models: Microsoft Virtual Academy

  Implementing Tabular Data Models Date: November 11, 2014 Time: 9am?5pm PST Where: Live, on...


SSAS Tabular Model – Query Modes

One of the most compelling features of the new SSAS Tabular Model is the ability to set the model’s ...


What happens when a SSAS Tabular model exceeds memory?

If you are using the Tabular model in SSAS, it will use the xVelocity technology to load your entire...