SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

PerformancePoint ‘Named Set’ Filter

PerformancePoint provides several options to create filters.  One of the options available is ‘Named Set’.  Within SSAS cubes we can define ‘Named Sets’, which are MDX expressions that return a set of dimension members.  These ‘Named Sets’ can be used to populate filters within PerformancePoint. 

Defining a named set within SSAS is quite simple, an example is provided below.

First, open the appropriate SSAS Multidimensional cube project in BIDS or SSDT.  Then, click on the ‘Calculations tab’.


To create a Named Set, click the ‘New Named Set’ button.


You should then see the following form.


First, give the Named Set a logical name.  In this example, my named set is ‘Work Orders In Process’.  Next, enter in an MDX expression that defines the set.  An example is shown below.


You also need to specify if the Named Set is of type ‘Dynamic’ or ‘Static’.  This blog post provides a good overview of the differences between Dynamic and Static sets and a few cautionary details related to Named Sets. 

A summary of the differences is listed below:

STATIC Sets: MDX query execution context is not passed from “FROM” and “WHERE” clauses to “WITH SET”. They are evaluated during CREATE SET statement execution or at query run time if defined with WITH SET clause. If static named sets are defined in the cube, they are evaluated at the time of cube processing. (http://msbiworld.wordpress.com/2011/06/07/ssas-named-sets-static-vs-dynamic-2/)

DYNAMIC Sets: The MDX query execution context is passed from “FROM” and “WHERE” clauses to “WITH SET”. They are evaluated every time the query is run. It really doesn’t matter where they are defined. (http://msbiworld.wordpress.com/2011/06/07/ssas-named-sets-static-vs-dynamic-2/)

Once you have created a Named Set in the cube, you can proceed to use this set within PerformancePoint.

In PerformancePoint Designer, click the ‘Create’ tab and then click the ‘Filter’ button to create a new Filter.  Select the ‘Named Set’ option and click ‘OK’.


Select a Data Source associated to the cube that contains the Named Set, then click ‘Next’.


Select the Named Set that contains the members to populate the filter.  In this example, the Named Set is called ‘Work Orders In Process’.


Now select a display method.


Click ‘Finish’ to create the filter and give it a name.

The following is an example of how the filter appears when added to a Dashboard.  The filter is populated with the members defined by the Named Set.


Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.


Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...