By: Siddharth Mehta | Comments | Related: > Analysis Services Development
Problem
To query an Analysis Services cube, MDX is used as the query language. In most business settings, one would find a set of queries that are common across a number of user query requirements. To cater to this, even with a modest size IT team, there is a good chance that the same queries are developed redundantly either within a SSAS MDX script or repetitively in an ad-hoc manner in client applications. In this tip we would look at how to reuse queries without redeveloping them over and over.
Solution
Technically speaking, we have an issue of code refactoring and the intention is to centralize redundantly formed queries for ease of use and to improve performance in certain cases. In MDX, a construct is available that serves almost exactly the problem at hand, and it's called Named Sets. I will take it for granted that the reader has a working knowledge of MDX and SSAS.
Named Sets have two types, Static and Dynamic. Dynamic named sets were introduced in SQL Server 2008 and in this tip we will be discussing static named sets only.
Before we start with an example to understand named sets, we should be aware of different query scopes in which we can create and use named sets. Query scopes can be classified in three categories - Query, Session and Global. We will not discuss these scopes in detail as the scope names are relevant enough to reflect the meaning.
Let's say that the IT team has been assigned the task of addressing a number of queries that are used by a number of business analysts. Generally, ad-hoc requirements are assigned in an ad-hoc manner to developers, and these queries are formed in client applications like SSRS reports for example.
As a first step towards addressing the requirements, we should creating a process to track these queries. In the most simple form, one can create an Excel matrix of dimensions vs measuregroups, similar to the dimension usage tab and track each query in the matrix. The benefit is that by tracking queries, one would be able to figure out repeated queries that qualify for named sets or reuse existing queries without spending time and resources to redevelop existing queries. There is no standard template for this purpose, and one can design and develop a tracking sheet that best suits one's IT implementation vs business requirements process.
When a query qualifies to be created as a named set by the tracking mechanism described above, one needs to think of the scope in which the named sets should be used. In my personal opinion:
- If the query is too complex and huge, one can create a named set to use the same as an alias at multiple places in a query.
- If the same query is required across a session, for e.g. multiple reports accessed using the same connection, creating named sets in session scope is advisable.
- Generally speaking, there is a very thin line between session scope and global scope. Unless you have a single odd user who uses the same query across the session and no other users require the same kind of query, one should create named sets in global scope.
To create a named set in global scope, you need to define it in the "Calculations" tab of the cube. The benefit of static named sets in the global scope is that they are already evaluated at the time the cube is processed. Keep in view that static named sets help in a limited set of scenarios, where data do not change often, for the rest we can use dynamic named sets. In my personal opinion, if you use a very complex filter criteria in the "WHERE" clause of your MDX query, it's worth considering a static named set. From an implementation perspective, one of the examples where static named sets are useful, is to populate a series of filters/drop-downs across various scorecards in your dashboard.
An example of a named set can be seen in the below screenshot. A good source to learn about static named sets are examples in the SSAS Samples project that ships with SQL Server 2008, and the below screenshot is from that source.
After the named set is created, post processing of the cube, you can see the named set as shown in the below screenshot.
Next Steps
- Create static named sets of a few commonly used sets that you use in your filter criteria, and use the same in your query.
- Measure the performance of using a static named set against using the same query in an ad-hoc manner, by executing the query to retrieve the same set using a named set and through ad-hoc queries.
- Read these additional SSAS tips
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
