Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to reduce MDX code redundancy in SQL Server Analysis Services SSAS


By:   |   Last Updated: 2011-02-08   |   Comments   |   Related Tips: > 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


Last Updated: 2011-02-08


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools