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.