Defining Named Sets
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.
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, Create Named Sets.
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
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.
Defining 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. For more information, see 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. For more information, see Group Attribute Members (Discretization).
On the Build menu of SQL Server Data Tools (SSDT), 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. For the range 2 -17, the InfoTip should contain [Reseller].[Number of Employees].&[0].
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.
![Calculation Expressions pane for [Large Resellers]](images/ms166594.l6_named_set_02(sql.110).gif "Calculation Expressions pane for [Large Resellers]")
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
On the Build menu of SQL Server Data Tools, click Deploy Analysis Services Tutorial.
When deployment has successfully completed, click the Browser tab, and then click Reconnect.
Clear the grid in the data pane.
Add the Reseller Sales-Sales Amount measure to the data area.
Expand the Product dimension, and then add Category and Subcategory to the row area, as shown in the following image.
.gif "Members of the Subcategory attribute")
In the Metadata pane, in the Product dimension, drag Core Products to the filter 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. 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.
.gif "Members of the Core Product named set")
In the Metadata pane, expand Reseller, add Large Resellers to the filter 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.
.gif "Filter pane containing two named sets")
Next Task in Lesson
Defining Scoped Assignments Using Script Commands