Home > Business Intelligence > MDX for Generating a List from Member Names – Norsk SSUG

MDX for Generating a List from Member Names – Norsk SSUG

Tusen takk til Norsk SQL Server User Group! Min kone og jeg hadde en fantastisk tid på møtet! Vi er tilbake til Minnesota neste uke, men vi håper vi skal komme tilbake til Norge snart.

Here is the solution to the MDX problem that we worked on at the meeting. It was a great problem. Thanks to the group member who brought it in and to everybody who helped work on it.

The following script has the solution in one query and a second query that displays the data in a way that shows that the solution has worked. We ran this at the meeting with Adventure Works for 2012 Multidimensional. The same query could also be used with Adventure Works for 2008.

An explanation of the query follows the code.

with member measures.test

as

generate

(

extract

(

nonempty

(

[Date].[Calendar].currentmember

*

[Product].[Category].[Category].members

*

[Measures].[Internet Sales Amount]

)

,[Product].[Category]

)

,[Product].[Category].currentmember.name, “,”

)

select {[Measures].[Internet Sales Amount], measures.test} on 0,

[Date].[Calendar].[Calendar Year].members

on 1

from [adventure works]

go

select {[Measures].[Internet Sales Amount]} on 0,

non empty

[Date].[Calendar].[Calendar Year].members

*

[Product].[Category].[Category].members

on 1

from [adventure works]

Here is a description of the problem we were trying to solve:

1. A cube has free text descriptions. These descriptions exist in the cube as the name property of one of the attributes.

2. For any subset of the data, the MDX must concatenate all the individual text descriptions into a string.

We worked on the problem using Adventure Works, using the Category attribute hierarchy from the Product dimension in place of the free text description.

It is easiest to understand the calculation when looking at the innermost function and working outwards:

1. First the nonempty function applied to the crossjoin of three sets:

nonempty

(

[Date].[Calendar].currentmember

*

[Product].[Category].[Category].members

*

[Measures].[Internet Sales Amount]

)

In our query we are putting calendar years on rows. So we crossjoin the current year with all the values from category and with the Internet Sales Amount measure. If there isn’t a sale for one of the categories for that year, it will be empty, and will be eliminated by the nonempty function. What will be returned by the nonempty function is a set of tuples – all of them will have the year, one of the products, and the Internet Sales Amount measure.

2. But we are not interested in a set of tuples, we are only interested in the values for Product Category. So we have to extract the values for that particular hierarchy from our tuples. We do that with the Extract function:

extract

(

nonempty

(

[Date].[Calendar].currentmember

*

[Product].[Category].[Category].members

*

[Measures].[Internet Sales Amount]

)

,[Product].[Category]

)

Extract takes two parameters – a set of tuples and the name of one of the hierarchies used in those tuples. We created the set of tuples with the crossjoins (the set multiplication). We filtered those tuples with nonempty. Now we’re getting the individual members from the Product.Category hierarchy that are included in those tuples. For each row this will be the set of categories that had sales in that particular year.

3. But we still have a set of members, and what we really want to see is a list of string values concatenated together. We do that with the generate function. Generate is an over-loaded function. It has two meanings. In one meaning (probably the most commonly used) generate is used with two sets to create a third set, with the membership of the second set being created using each member of the first set. In the second meaning, generate is used to create a string. That’s the form of generate that we are using here.

This form of generate has three parameters – the set, in our case a set of categories, a string that is used as the source for the output string, and a delimiter to be used for separating each of the string values. The source string for this example is the name of the current member of Product.Category. The delimiter is a comma.

The CurrentMember function is usually used in a calculation to see where we are in a cellset. That’s the meaning of CurrentMember in our calculation where we use it with Date.Calendar. In each row there will be a different year, which will be a different CurrentMember.

The Generate function creates a loop. We add another part to the string with each loop. The CurrentMember used in the string is the CurrentMember for each Category in the set as we go through the loop. So, if we have three products, each of those products will take their turn as the current member and will have their member name added to the string.

generate

(

extract

(

nonempty

(

[Date].[Calendar].currentmember

*

[Product].[Category].[Category].members

*

[Measures].[Internet Sales Amount]

)

,[Product].[Category]

)

,[Product].[Category].currentmember.name, “,”

)

The value displayed by the calculation would look like this for a year when these three categories had sales:

Accessories,Bikes,Clothing

Takk igjen! Neste gang kanskje jeg kan snakke norsk. Kanskje ikke. Lykke til med MDX! Vi sees!

Categories: Business Intelligence
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: