Implementing Calculations in SSAS using MDX – Part 2

In the Part I of this series we have seen how to create a calculated member using MDX expression. In this part, I am going to show the second option in the calculations tab which is “Named Sets”.

A named set is a set of dimension members or a set expression created for the purpose of reusability in MDX queries. Named sets can be created by combining cube data, arithmetic operators, numbers, and functions. We can define named sets and save them as a part of the cube definition. Also they can be directly created in client applications.

Named set can be created using the New Named Set option in the Calculations tab of Cube Designer. We have to specify the name of the named set and the expression that produces the named set. Alternatively, a named set can be created either using a CREATE SET statement or a WITH SET statement. In SSAS 2005 the only option available was to create static named sets but in SSAS 2008 we can create either static or dynamic named sets.

• Static Named Sets – Members of the Static named sets are fixed till the time the cube is processed. For example, a set defining South Asian Countries consists of India, Nepal, Pakistan, Sri Lanka, Bangladesh etc. The contents of Static named sets are evaluated at the time of creation i.e. when the CREATE SET statement is executed or immediately after the WHERE clause is resolved. Suppose we wanted to have a sales report for all the regions in United States, instead of filtering the regions manually we can simply create a Static Named Set as shown below,

Exists
(
    [Sales Territory].[Sales Territory Region].[Sales Territory Region].Members,
    [Sales Territory].[Sales Territory Country].&[United States]
)

Using this named set we can directly filter the regions in United States as follows to get the Sales Amount.

• Dynamic Named Sets – The dimension members of a Dynamic named set gets filtered dynamically based on some aspect of the measures. For example, top 10 products by Sales Amount. Here, based on the Sales Amount, the product list may change from time to time. The contents are evaluated in the context of each query that refers the named set. A Dynamic set can be created by adding DYNAMIC keyword just before the set definition. The following is the definition for top 10 products by Internet Sales Amount.

       CREATE DYNAMIC SET CURRENTCUBE.[Top10BestSellingProductsIS]
       AS TopCount
       (
           [Product].[Product Name].[Product Name].Members,
           10,
           [Measures].[Sales Amount]
       );

Top 10 best selling products in Southeast Region,

Top 10 best selling products in Southwest Region,

Notice that the product list changes based on the Sales Territory Region seleted.

Hope you are enjoying the series. In the concluding part of this series, i.e. Part 3, we will see the third option which is to write Script Commands (Scope Assignments).

Also a request, if anybody has created calculated members, named sets or script commands for any practical scenario; do share the knowledge with the community.

Regards

Amit Karkhanis

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook