By: Ray Barley | Comments (4) | Related: > Analysis Services Development
Problem
I am building a SQL Server 2012 Analysis Services Tabular model to support ad-hoc queries for a particular department within my organization. One of the requirements is that the database must always provide the most up-to-date data at all times. I know that the SSAS Multidimensional model supports a ROLAP storage mode which would meet my requirement but I want to use the SSAS Tabular model. How can I accomplish my task with the SSAS Tabular model?
Solution
The SSAS Tabular model is a new feature included with SQL Server 2012. As the name implies, SSAS Tabular is a table-based model. It has an in-memory database engine that was first introduced with the PowerPivot add-in for Excel. By default an SSAS Tabular model project is deployed as an in-memory database that provides excellent response time for user queries. However, the in-memory model requires a processing step to keep it up-to-date with the latest data in the underlying data source; i.e. it is a copy of your data. In a nutshell processing means reading the underlying data source periodically and updating the Tabular database. The Tabular model also supports a "DirectQuery" mode where user queries can be performed against the underlying data source used to populate the model. In this case the data is always up-to-date because you are querying the underlying data source directly. While there are some limitations that you need to be aware of with DirectQuery, it provides essentially the same capability as the ROLAP storage mode in an SSAS Multidimensional model.
There are five things that you need to do in your SSAS Tabular project to take advantage of DirectQuery:
- Review the limitations when using DirectQuery mode
- Set the DirectQuery Mode property of the model to On
- Set the Query Mode project deployment property (requires DirectQuery set to On)
- Set the Impersonation Settings project deployment property
- Specify the DirectQuery partition for each table in the model
In this tip I will provide the details you need for setting up an SSAS Tabular model that uses the DirectQuery mode. You can find a number of tips on the SSAS Tabular model right here on MSSQLTips.com; just search for SSAS Tabular. In this tip I'm going to assume that you have already created an SSAS Tabular project using SQL Server Data Tools (SSDT). If you need to review the steps to create an SSAS Tabular project using SSDT, take a look at the tip Getting Started with Tabular Model.
Limitations
There are a number of limitations that you should be aware of when you choose DirectQuery; the main ones are:
-
Only a single SQL Server 2005 and later database is supported as a data source
-
Calculated columns are not supported; however, you can provide these columns in the SQL Server data source
-
Some DAX functions are not available; e.g. time intelligence
-
Only DAX queries are allowed (i.e. you can use PowerView); you can't perform queries from Excel, Report Builder, Reporting Services, etc.; i.e. any tool that creates MDX queries; I assume this limitation will go away at some point
-
DirectQuery mode can only access data in a single partition; SSAS Tabular supports multiple partitions per table; you just have to designate a single partition that DirectQuery will use
For the complete details on the limitations see DirectQuery Mode (SSAS Tabular).
DirectQuery Mode Model Property
To set the DirectQuery Mode property of the model click on Model.bim as shown below:
If you do this before you run the Import Data Wizard, your partitions will automatically be setup for DirectQuery.
Query Mode Project Deployment Property
To set the Query Mode project deployment property, right click on the project in the Solution Explorer and select Properties; select the appropriate value for Query Mode as shown below:
The following are the key points on the Query Mode setting:
- In-memory is the default; the entire database is loaded into memory and all queries are handled from memory
- DirectQuery performs queries by accessing the underlying SQL Server database
- DirectQuery with In-Memory and In-Memory with DirectQuery are hybrid modes where you can query the deployed database using In-Memory or DirectQuery; to query using In-Memory you need to process your partitions
When you select one of the hybrid modes you can specify whether to use In-Memory or DirectQuery in your connection string; e.g. DirectQueryMode=DirectQuery or DirectQueryMode=In-Memory. If you omit the DirectQueryMode property from your connection string then the following defaults apply based on your Query Mode setting:
- DirectQuery with In-Memory will use DirectQuery
- In-Memory with DirectQuery will use In-Memory
Impersonation Settings
The Impersonation Settings (on the project properties) specifies the credentials to be used when querying the tabular database. You have two choices:
- Default - use the credentials specified in the import wizard
- ImpersonateCurrentUser - use the credentials of the current user
DirectQuery Partition
Whenever you have multiple partitions (whether it's a table in a SQL Server database, a measure group in an SSAS Multidimensional database, or a table in an SSAS Tabular model) you normally specify a query for each partition making sure that rows only appear in a single partition. When you choose DirectQuery mode (or one of the hybrid modes), you must designate a single partition for each table that includes all of the rows that you want to be available for querying in DirectQuery mode.
To see the partition settings for a table, open the Model.bim file in your project (double click Model.bim in the Solution Explorer or right click it and select Open), select a table, and open the Partition Manager dialog from the table's properties:
The following are the main points on the Partition Manager dialog:
-
The Partition Name notes the partition that has been selected for DirectQuery
-
If you have multiple partitions, you can select the partition to be used for DirectQuery and click the Set as DirectQuery button
-
Normally you would set the Processing Option for the DirectQuery partition to Never Process this Partition; however, if you only have a single partition for a table, you need to allow the partition to be processed so that you will have data in your SSDT project
For additional details on partitioning and DirectQuery mode, take a look at Partitions and DirectQuery Mode (SSAS Tabular) in the MSDN library. The key takeaway from this section is that for DirectQuery mode (including the hybrid modes) you need to specify the single partition to be used for querying.
Deploy and Test the Tabular Model
Before deploying your SSDT tabular project, right click the project in the SSDT Solution Explorer and select Properties to verify your settings:
Make sure that you have the Query Mode set to DirectQuery or one of the hybrid modes and your Server is set to an instance of SSAS running in Tabular mode.
To deploy right click the project in the SSDT Solution Explorer and select Deploy. After a successful deployment you can query the SSAS Tabular database by using PowerView or a DAX XMLA query. Since I don't have PowerView available right now, I used the following DAX XMLA query:
Perform the following steps to execute the XMLA query above:
-
Open SQL Server Management Studio
-
Connect to your SSAS Tabular instance
-
Right click on your database, select New Query, XMLA, and enter the XMLA as shown above
The final test is to launch SQL Server Profiler, start a new trace that uses the underlying data source for the SSAS Tabular database, and verify that a query shows up when you run the XMLA query. Since the SSAS Tabular database is deployed using DirectQuery, every DAX query will cause the equivalent SQL query to be run on the underlying data source. SQL Server Profiler confirms that a SQL query is indeed executed.
While Excel, Report Builder and Reporting Services are popular tools for querying and reporting, you cannot use them at this point because an SSAS Tabular database in DirectQuery mode can only handle DAX queries and those tools produce MDX queries. At some point in the future I expect this limitation will go away. You can use PowerView which is a new, browser-based tool that comes with SQL Server 2012 and is available via SharePoint.
Next Steps
- The DirectQuery and hybrid options provide a great way to make the most recent data always available while eliminating the need to "process" the partitions in your SSAS Tabular model.
- As usual there is a wealth of information available in the MSDN library; this is a good starting point: Tabular Modeling (SSAS Tabular) in the MSDN library.
- Download the sample project here. It was built using SSDT 2010 and uses the AdventureWorksDW2012 database (available here) as the data source for the SSAS Tabular model.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
