Named sets in Analysis Services)

 

A nameset is a very useful element in SSAs. Like calculated members, it can simplify a lot of our work and make some connections between them. So what is named sets? Simply put, it is the name of the members assigned and the metadata set. We define named sets to make it easier for users to build their own queries and help developers write highly readableCode.

Regular named Sets

Let's take an example to see how to use named sets. My users want to create an Excel report to show the sales volume in the current month, the previous month, and the total sales volume in the last three years. If you do not use named set, you need to update the selected date in the report at the beginning of each month to display the data of the last month. To avoid this, we can define a named set that contains the date range that the user wants to see in the report, and the user does not need to manually update it again. The following describes how to do this:

First, we need to determine which months have sales data, because the future date contained in the date dimension certainly does not contain any sales data. This can be calculated using the nonempty function:

Create Hidden Set Currentcube. [Existingsaleperiods]As Nonempty( [Date]. [calendar]. [month].Members, Measures. [sales amount] );

 

We set this nameset to hide it to the user because it will only be used in the intermediate step without being presented to the user.

Next, we will create a date nameset for the previous month. Because we are still interested in the sales data of the previous month, and we have defined all the date sets with sales data so far, therefore, we can easily get the definition of the previous month:

Create Set Currentcube. [Lastsalemonth]As Tail(Existingsaleperiods, 1 );

With the definition of January, we are one step closer to the target. Of course, it is also useful for users, so we set it to be visible to users. The last nameset is a set of members at the year level. Below we create a nameset that contains sales data for the last year:

Create Set Currentcube. [Lastsaleyear]As Ancestor( Lastsalemonth.Item(0 ), [Date]. [calendar]. [calendar year] );

 

The last step is to create our final nameset:

Create Set Currentcube. Lastsaleanalysisperiod As{ Lastsaleyear.Item(0 ).Lag(2 ): Lastsaleyear.Item(0 ), Lastsalemonth.Item(0 ).Prevmember, Lastsalemonth.Item(0) };

 

Now that the nameset has been successfully created, you can easily use it in the report using the client tool:

The nameset is created and calculated each time it processes the cube. In this way, the sales data of each new month is displayed, the name set will reflect the time required for this year and report display and refresh the report data.

Dynamic named set)

When creating a nameset, you can select static or dynamic. For static ), the data will be retained until the next processing is performed on the cube. In some cases, static queries are useful. For example, you want to improve query performance, but sometimes it is quite frustrating. For example, we need to define a set of 10 best-selling products:

Create set currentcube . best10products as topcount ( product. product. product. Members , 10, measures. [sales amount] );

Since the static name set is only calculated once, the best10products name set will contain all the best-selling products that can be used for all time. In other words, when a nameset is computed, it does not know which query it will be used. However, this nameset will not be used too much in the report, because in the report, it is likely that the first 10 sales of each country every month are counted, so that the static nameset cannot be implemented. For example, the following two queries will always return the same product list even if they are selected for different years:

SelectMeasures. [sales amount]On Columns, Best10productsOn Rows From[Adventure works] Where([Date]. [calendar]. [calendar year]. & [2001])     SelectMeasures. [sales amount]On Columns, Best10productsOn Rows From[Adventure works] Where([Date]. [calendar]. [calendar year]. & [2004])

Analysis Services 2008 introduces a dynamic nameset (dynamic) to solve this problem. The dynamic naming convention is always re-computed in the context of the WHERE clause, that is, the dynamic naming set is re-computed in each query.

Create Dynamic Set Currentcube. [Best10productsdynamic]As Topcount( Product.Members, 10, Measures. [sales amount] );

Now we run the following two queries again:

SelectMeasures. [sales amount]On Columns, Best10productsdynamicOn Rows From[Adventure works] Where([Date]. [calendar]. [calendar year]. & [2001])   SelectMeasures. [sales amount]On Columns, Best10productsdynamicOn Rows From[Adventure works] Where([Date]. [calendar]. [calendar year]. & [2004])

Now we get the correct 10 best-selling products per year.