Overview
A Set is an expression that defines one or more dimension members. These sets are created using the MDX (Multidimensional Expressions) query language. Using the CREATE SET statement one can alias the set for reuse. This article provides an example, constructing a named set and applying it to a project.
Process
-
Create your Set
The first task is to build a query that contains your set. This is usually done by creating a query within SQL Server Management Studio. A set can use any function that is supported by the MDX query language. As such, the possibilities are endless. Sets can be simple or complex.
In this example, we have created a named set called "Top 10 Customers" that sorts by sales amount in descending order. Notice how our query uses the alias "Top 10 Customers" on rows instead of the Sell-to Customer dimension.
-
Apply Set Statement to Project
The statement above is said to be query scoped. This means that the query is only available for this session. The goal is to have this query available to all users when they create their own session. In order to do this we need to copy the above set statement into a script within the Jet Data Manager.
From the Cubes tab, expand the cube to which the query should be applied and right click Script Commands → Add Script Command
Then enter your set expression. Here we use the CREATE SET statement in our cube script.
We have also mapped the referenced objects to protect our script against name changes. This can be done by right clicking on the object in the right hand pane and adding the object as a parameter.
Once finished click OK.
The last step is to rebuild the cube to which the script was applied.
Conclusion
The dimension will now have a folder named Sets that contains your named set. This can now be used on rows or columns as desired.
Comments