Defining Named Sets

Article
12/03/2008

A named set is a Multidimensional Expressions (MDX) expression that returns a set of dimension members. You can define named sets and save them as part of the cube definition; you can also create named sets in client applications. You create named sets by combining cube data, arithmetic operators, numbers, and functions. Named sets can be used by users in MDX queries in client applications and can also be used to define sets in subcubes. A subcube is a collection of crossjoined sets that restricts the cube space to the defined subspace for subsequent statements. Defining a restricted cube space is a fundamental concept to MDX scripting. MDX scripting is covered in the next topic in this lesson.

Named sets simplify MDX queries and provide useful aliases for complex, typically used, set expressions. For example, you can define a named set called Large Resellers that contains the set of members in the Reseller dimension that have the most employees. End users could then use the Large Resellers named set in queries, or you could use the named set to define a set in a subcube. Named set definitions are stored in cubes, but their values exist only in memory. To create a named set, use the New Named Set command on the Calculations tab of Cube Designer. For more information, see Calculations, Defining a Named Set.

In the tasks in this topic, you will define two named sets: a Core Products named set and a Large Resellers named set.

Defining a Core Products Named Set

To define a Core Products named set

Switch to the Calculations tab of Cube Designer for the Analysis Services Tutorial cube, and then click Form View on the toolbar.
Click [Total Sales Ratio to All Products] in the Script Organizer pane, and then click New Named Set on the toolbar of the Calculations tab.

When you define a new calculation on the Calculations tab, remember that calculations are resolved in the order in which they appear in the Script Organizer pane. Your focus within that pane when you create a new calculation determines the order of the execution of the calculation; a new calculation is defined immediately after the calculation on which you are focused.
In the Name box, change the name of the new named set to [Core Products].

In the Script Organizer pane, notice the unique icon that differentiates a named set from a script command or a calculated member.
On the Metadata tab in the Calculation Tools pane, expand Product, expand Category, expand Members, and then expand All Products.

Note

If you cannot view any metadata in the Calculation Tools pane, click Reconnect on the toolbar. If this does not work, you may have to process the cube or start the instance of Analysis Services.

Drag Bikes into the Expression box.
You now have created a set expression that will return the set of members that are in the Bike category in the Product dimension.
The following image shows the Calculation Expressions pane for the named set that you created in this procedure.

Defining a Large Resellers Named Set

To define a Large Resellers named set

Right-click [Core Products] in the Script Organizer pane, and then click New Named Set.
In the Name box, change the name of this named set to [Large Resellers].
In the Expression box, type Exists().

You will use the Exists function to return the set of members from the Reseller Name attribute hierarchy that intersects with the set of members in the Number of Employees attribute hierarchy that has the largest number of employees.
On the Metadata tab in the Calculation Tools pane, expand the Reseller dimension, and then expand the Reseller Name attribute hierarchy.
Drag the Reseller Name level into the parenthesis for the Exists set expression.

You will use the Members function to return all members of this set. Related topic:Members (Set) (MDX)
After the partial set expression, type a period, and then add the Members function. Your expression should look like the following:
```
Exists([Reseller].[Reseller Name].[Reseller Name].Members)
```
Now that you have defined the first set for the Exists set expression, you are ready to add the second set—the set of members of the Reseller dimension that contains the largest number of employees.
On the Metadata tab in the Calculation Tools pane, expand Number of Employees in the Reseller dimension, expand Members, and then expand All Resellers.

Notice that the members of this attribute hierarchy are not grouped.
Open Dimension Designer for the Reseller dimension, and then click Number of Employees in the Attributes pane.
In the Properties window, change the DiscretizationMethod property to Automatic, and then change the DiscretizationBucketCount property to 5. Related topic:Grouping Attribute Members (Discretization)
On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Services Tutorial.
When deployment has successfully completed, switch to Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect on the toolbar of the Calculations tab.
On the Metadata tab in the Calculation Tools pane, expand Number of Employees in the Reseller dimension, expand Members, and then expand All Resellers.

Notice that the members of this attribute hierarchy are now contained in five groups, numbered 0 through 4. To view the number of a group, pause the pointer over that group to view an InfoTip. The members of this attribute hierarchy are grouped because the DiscretizationBucketCount property is set to 5 and the DiscretizationMethod property is set to Automatic.
In the Expression box, add a comma in the Exists set expression after the Members function and before the closing parenthesis, and then drag 83 - 100 from the Metadata pane and position it after the comma.

You have now completed the Exists set expression that will return the set of members that intersects with these two specified sets, the set of all resellers and the set of resellers who have 83 to 100 employees, when the Large Resellers named set is put on an axis.

The following image shows the Calculation Expressions pane for the [Large Resellers] named set.
On the toolbar of the Calculations tab, click Script View, and then review the two named sets that you have just added to the calculation script.
Add a new line in the calculation script immediately before the first CREATE SET command, and then add the following text to the script on its own line:
```
/* named sets */
```
You have now defined two named sets, which are visible in the Script Organizer pane. You are now ready to deploy these named sets, and then to browse these measures in the Analysis Services Tutorial cube.

Browsing the Cube by Using the New Named Sets

To browse the cube by using the new named sets

On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial.
When deployment has successfully completed, click the Browser tab, and then click Reconnect.
Remove all hierarchies and measures from the Data pane.
Add the Reseller Sales-Sales Amount measure to the data area, and then add the Product Categories user-defined hierarchy to the row area.

Notice that only one member of the category attribute appears. This is because you had previously used this attribute as a filter in this session. All members will appear if you are working in a new session.
Select the drop-down arrow next to Category in the Row area, select the check box next to the (All) level to select all members of this level (if it is not already selected), and then click OK.
Expand Bikes to view the members of the Subcategory level, as shown in the following image.
In the Metadata pane, in the Product dimension, right-click Core Products, and select Add to Subcube Area.

Notice that only the Bike member of the Category attribute and members of the Bike subcategories remain in the cube. This is because the Core Products named set is used to define a subcube, the properties of which appear in the Filter pane, which is the pane above the Data pane. This subcube limits the members of the Category attribute in the Product dimension within the subcube to those members of the Core Product named set, as shown in the following image.
In the Metadata pane, expand Reseller, right-click Large Resellers, and then select Add to Subcube Area.

Notice that the Reseller Sales Amount measure in the Data pane only displays sales amounts for large resellers of bikes. Notice also that the Filter pane now displays the two named sets that are used to define this particular subcube, as shown in the following image.