By: Siddharth Mehta | Comments | Related: > Analysis Services Development
Problem
Creating named sets for frequently used fields in a query, is a very common practice to facilitate easier querying of a cube by users. Developers generally overlook a very important specification when creating named sets, which is to make the named set dynamic. Static named sets have limitations and hence should be used with care. In this tip we look at how dynamic named sets can be used to overcome the limitation of static named sets.
Solution
Any MDX Script gets evaluated when the SSAS Formula Engine makes a first pass through the MDX Script. The limitation with static named sets is that it is evaluated only once and then for subsequent sessions it does not get re-evaluated even if the scope of the query, i.e. the browsable area of the cube, is changed. Dynamic named sets are evaluated for each query and hence the scope is always considered while evaluating the named set. Also note, that Dynamic named sets were introduced with SQL Server 2008.
In this tip we will test the dynamic and static named sets by means of an example. For the purpose of testing, I used the AdventureWorks sample solution that ships with SQL Server. Open the solution in BIDS and open the Calculations tab of the cube.
You will find a named set called "Top 25 Selling Products", which is a typical example of the kind of data sets a business user needs regularly. This set is a dynamic set. For the purpose of our testing, let's copy this code and remove the "Dynamic" keyword from the definition, which will make this set static by default. I have named this set "Top 25 Selling Products - Static" as shown below.
Deploy the cube and on browsing the cube you should be able to locate these sets in a folder named "Sets" under the Products dimension. Create a new MDX query as shown below and execute the query without the WHERE criteria. Here the query returns the top 25 best selling products for column sales amount on rows and the column axis is ordered by the internet sales amount in descending order.
When you execute this query with either the static or dynamic version of the named set without the WHERE clause you will get the same results.
Now execute the same query using the static set along with a filter criteria. If you carefully analyze the results you will find the same products and for many products you will find that no sales have been recorded for the results. Still these are ranked in the results. The reason for this is that the static named set got evaluated when the session was established and whenever you query this named set with or without a scope (i.e. using a where filter in this case) this named set is not re-evaluated, so the results are invalid.
Now if you query using the same scope with the dynamic named set, you will see different results. Here you will find that the scope is considered while evaluating the named set and you will get different products than the products you get without a "WHERE" criteria.
So when creating a named set, think carefully whether you need a named set that should always consider the scope in which it's being used or if you intend to create a named set which would never be used with a scope, for example to populate drop down values.
Next Steps
- Evaluate whether your static named sets should be converted to dynamic named sets, by assessing its projected use.
- Test the performance of static named sets versus dynamic named sets, by performing a load test scenario.
- Read these other tips related to SSAS
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
