Working with DirectQuery in SQL Server Analysis Services and Pyramid Analytics' BI Office
by Matt Burr , Senior Solutions Engineer at Pyramid Analytics
Someone asked recently, “Does Pyramid Analytics support DirectQuery in SQL Server Analysis Services?” The short answer is, “Yes!” The long answer is this blog post.
What and Why
For tabular models in SQL Server Analysis Services (SSAS) 2012 and higher, DirectQuery offers a “real-time” experience. When you query a tabular model in DirectQuery mode, SSAS queries the model’s data source to respond, so the answer reflects the current state of that source. (For more information on the advantages of DirectQuery mode in SSAS, see Microsoft’s article here).
Microsoft restricts what you can do when you use MDX in DirectQuery mode. Since BI Office uses MDX to query tabular models, the restrictions affect BI Office. The restrictions affect not just BI Office, though; they affect any product using MDX in DirectQuery mode.
Microsoft documents the restrictions here.
The first restriction is this: if you want to query a tabular model in DirectQuery mode with MDX, you’ve got to use SSAS 2016. Earlier versions of SSAS did not support MDX in DirectQuery mode. If you’re running an earlier version, DirectQuery and BI Office are a no-go.
Once you’re using SSAS 2016, DirectQuery mode puts five restrictions on MDX (which I quote from the document linked above):
- No relative object names. All object names must be fully qualified.
- No session-scope MDX statements (named sets, calculated members, calculated cells, visual totals, default members, and so forth), but you can use query-scope constructs, such as the 'WITH' clause.
- No tuples with members from different levels in MDX subselect clauses.
- No user-defined hierarchies.
- No native SQL queries (normally, Analysis Services supports a T-SQL subset, but not for DirectQuery models).
Despite these restrictions, BI Office works well in DirectQuery mode. Its wizards and other capabilities use fully-qualified object names (restriction #1), it uses query-scoped constructs in its MDX statements (restriction #2), and it doesn’t use native SQL queries (restriction #5). And since restriction #4 eliminates user-defined hierarchies, you’re unlikely to put tuples with members from different levels in a sub-select clause (restriction #3). The restriction that affects BI Office most is restriction #4: no user-defined hierarchies.
Figure 1: Example of a user-defined hierarchy in the Data Modeler
A user-defined hierarchy is a multi-level hierarchy you create (for example, in BI Office Data Modeler) to help users navigate data; for example, a hierarchy with Year, Month, and Date as levels is a user-defined hierarchy.
This restriction means your dimensions in DirectQuery mode comprise attributes only. This creates a “two-dimensional” experience where you navigate through data by dicing and cross-joining data instead of drilling up and down hierarchies.
Since BI Office allows you to hand-code MDX calculations, if you choose, you could write a calculation or set that violates a restriction. If you do, BI Office shows you an error when you run your query: Query execution encountered an MDX construct that is not allowed on DirectQuery models. Compare what you’re doing to the list of restrictions above to find out what to change. You are unlikely to run into this error, though, when using BI Office built-in capabilities with DirectQuery models.
Figure 2: Exception when you use MDX that is not allowed in DirectQuery mode.
BI Office and DirectQuery mode work well together. Restrictions on user-defined hierarchies will affect how you analyze data in BI Office when working in DirectQuery mode, though. In a future blog post, I’ll look at some of the ways to use BI Office to analyze data in DirectQuery mode in the absence of user-defined hierarchies.