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


Developing Named Sets



Named sets return a dataset based on defined logic. They are primarily useful to create datasets that are often requested from the cube. Named sets are of two types: Static and Dynamic. The difference between these two is that static named sets are calculated when they are requested the first time in a session and dynamic named sets are calculated each time a query references it. In this section we will look at how to create dynamic named sets. Note that dynamic named sets were not introduced until SQL Server 2008.


Open the cube designer, and click on the Calculations tab. Click on “New Named Set” from the toolbar and key in the values as shown in the below screenshots.

Here we are creating two named sets, Internet Sales Top 25 and Reseller Sales Top 25. In these named sets, we are returning the Top 25 products based on Internet Sales and Reseller Sales. In this formula, TopCount, the MDX function returns top 25 records from the dataset.

In the Type selection, we can select whether we want the named set to be static or dynamic. We have selected Dynamic as we want to create a dynamic named set.

In the Display folder selection, we can specify where the named sets will appear. By default named sets appear in the last dimension that is used in the formula. Here we have used an attribute hierarchy from Product dimension, so the named sets should appear in the same dimension under “Named Sets” directory.

Save and deploy the solution, and then re-connect to the cube in the “Browser” pane. You should be able to see the calculated measure and named sets as shown in the below screenshot.

Last Update: 5/3/2011

More SQL Server Solutions

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