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

 

Limitations of Static Named Sets in SQL Server Analysis Services SSAS


By:   |   Last Updated: 2011-04-13   |   Comments   |   Related Tips: > 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.

dynamic named sets were introduced with sql server 2008

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.

execute the query without the where criteria

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.

execute the query using the static set along with a filter criteria

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.

execute the query with the dynamic set and see the results

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


Last Updated: 2011-04-13


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