business intelligence consulting
 

Creating a New Named Set using SQL Server Analysis Services 2012 Cube Designer

by Hans Esquivel 5. February 2013 06:31

In this session we'll show you how to create a Named Set in Analysis Services 2012 Cube Designer. We'll explain what a Named Set is, the difference between Static and Dynamic Named Sets and finally, demonstrate how to define a Named Set using Analysis Services 2012 Cube Designer.

This article assumes you have the following:

  • Installed SQL Server 2012 or 2008 R2, with Analysis Services.
  • Attached AdventureWorksDW2012 Data File.
  • Experience with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Note: For this session we'll be using SSDT.
  • Experience with deploying and attaching to an exisitng Analysis Services database using SSDT or Business Intelligence Development Studio (BIDS).
  • Some experience developing with Multidimensional Expressions (MDX) language.

Note: A download of the demo is available at the end of this session.

 

What is a Named Set?

A Named Set is reusable set of dimension members or a defined set expression, using Multidimensional Expression (MDX). You define Named Sets by combining cube data, arithmetic operators, numbers and functions. For example, you can define a Named Set to render the Top Ten Products, based on highest values of the Sales measure.

Named Sets can be defined in one of the following scopes:

  • Query Scope - The visibility and lifetime of the set is limited to the query.
  • Session Scope - The visibility and lifetime of the set is limited to the session in which it is created.

Static Named Sets

A Static Named Set gets evaluated at the moment of the MDX Script creation and will no longer be reevaluated if changes are made to the scope of the query.  

Dynamic Named Sets 

A Dynamic Named Set doesn't share the limitation of Static Named Sets. The content of a Dynamic Named Set is evaluated any time there are changes to the scope/WHERE clause of the query.

 

Defining a Named Set

1) Open the attached demo file.

2) From within the Solution Explorer, expand the Cubes folder and double click on Analysis Services Tutorial.cube.

3) Select the Calculations tab.

 

4) Right click anywhere in the Script Organizer pane and select New Named Set. This will open the Named Set designer.

5)  Enter the following values in the Named Set designer:

  1. Name = [Top 10 Products]
  2. Expression =

    TopCount

    (

        [Product].[Product Name].[Product Name].Members,

        10,

        [Measures].[Internet Sales-Sales Amount]

    )
  3. Type = Static
  4. Display Folder = Sets

You should have something similar to the image below.

 

6) Save your work.

7) Create another Named Set and follow steps 4-6, but this time change Name value to [Top 10 Products Dynamic] and select "Dynamic" for the Type value.

8) From the menubar, click Build >> Process...

9) Once the cube processing has completed successfully, click on the Browser tab.

Note: You will have to click Reconnect at the bottom of the Browser pane. This refreshes the browser with changes made during cube processing.

 

10)  From within the Measure Group pane, expand the Measures >> Internet Sales and drag [Measures].[Internet Sales-Sales Amount] to the browser grid.

11) Expand Product >> Sets and drag [Top 10 Products] to the Filter pane.

12) Expand Product and drag [Product].[Product Name] to the left of Internet Sales Amount in the browser grid.

Note: In the Static Named Set, notice the top 10 values all time. Now see what happens when we add a Filter the query.

13) Add the following addtional filter to the query: [Date].[Calendar Date].[Calendar Year].&[2006]

Note: Once you add the new filter, right click on it and move it above the Top 10 Products Named Set filter. You will also need to enable Show Empty Cells from the toolbar presented in the image below.

As you can see, the query renders the same list of Product Name values from step 12, but this time some of them have no sales amount. Coincidentally, the Static Named Set was evaluated during during creation of the session, so our WHERE clause is not reevaluted, thus rendering null/incorrect values.

14) Change the Named Set from Top 10 Products to Top 10 Products Dynamic.

Note: you can perform this step easily by placing your cursor in the Filter Expression dropdown box and changing the value.

The change in scope is now recognized, as the WHERE clause of Dynamic Named Set is reevaluted. You will notice different Product Name and Internet Sales Amount values.

Note: You may reference a dynamic set inside any MDX Query, because the dynamic set will be evaluated at query scope.

Download: Analysis Services Tutorial.zip

 

Cheers!

 

In this session we'll show you how to create a Named Set in Analysis Services 2012 Cube Designer. We'll explain what a Named Set is, the difference between Static and Dynamic Named Sets and finally, demonstrate how to define a Named Set using Analysis Services 2012 Cube Designer.

SQL Server | Analysis Services | Multidimensional Expressions (MDX)

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud