SQL Server 2012 (Denali) Direct Query in Tabular Projects

Part of the architecture of BISM (the new Business Intelligence Semantic Model to be released in SQL Server 2012) is a Direct Query mode.


The Direct Query mode is as it sounds, where the model (a tabular project) pulls data directly from a SQL Server relational database at the time that the query is made, calculating measures and KPIs including aggregation on the fly (ROLAP style).
It does NOT store data in memory.

I see this as a way of using tabular projects to simply build a business (Semantic) layer on top of a relational database to distribute to the business.

It allows customers to leverage existing data marts/warehouses that they have spent many hours and $$$ investing in, and present a business facing model to users.  It replaces views and allows Microsoft users to build a comprehensive semantic model.

It still allows users to leverage the simple Excel-like interface and also allows them to make the most of the new Power View tool which can only read Tabular Projects.  All this but without them having to make big changes to existing hardware which is one of the big issues with moving to ‘in memory’ BI.

In summary:

 The Positives:
· Can implement on existing marts/warehouses

· Can leverage existing hardware

· Enables the business to manage the semantic layer, to my mind a key benefit of Tabular Projects, and be less reliant on developer reliant views

· Can leverage Power View as an end user tool

 Some Drawbacks:

· Some of the functionality cannot be used including custom columns in tabular projects and some of the DAX functions.

· Obviously the tabular model will not leverage the capabilities of in memory performance and column based storage so will be slower.

· Can only build a model off one SQL Server source


In short the direct query capability in Tabular Projects could be a good stop gap for customers looking to move to SQL 2012 but not yet ready to invest in hardware and re-develop existing data mart schemas.  It allows customers to leverage the new Power View and excel like interface of tabular projects and then over time look to replace existing OLAP cubes and data marts with the tabular models.

As for the hybrid options available in Tabular Projects…still exploring how that works and will post later.