Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Why you should learn more about the MDX Generate function

Posted by thomasivarssonmalmo on December 13, 2007

Generate() can be a little bit tricky to understand but can create information that a grid in a user interface have some problems with. If you use it together with TopCount you can add a lot of business value.
 
Let us start with a simple MDX TopCount select and later add it to the Generate function and see how the value of TopCount will change.
 
SELECT
{[Measures].[Internet Sales Amount]} On 0,
TOPCOUNT(
DESCENDANTS(
[Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Customer]
),1,([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Year].&[2003])
) On 1
From [Adventure Works];
 
You will see Janet Munoz with 12 418.17 in [Internet Sales Amount] for 2003. I have included the year 2003 with the measure in a tuple in order to get the customer with the highest internet sales amount for that year:([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Year].&[2003]) . Without the year in TopCount you will ask for the [All Year] customer or the default year if you have one. Most of the SSAS2005 clients can do this like Excel 2007 and ProClarity 6.3 .
 
But if we make the scenario a little bit more complicated and ask for the top 1 customer in all countries you will have a problem in ProClarity 6.3 as the picture shows below. I can only see the top 5 customers in the same country as the top 1.

TopCount in ProClarity

ProClarity have an add-in called ProClarity Selector that can help you with building named sets for this but in Professional it is not possible without writing you own MDX.

The solution is from the SQL Server 2000 resource kit but rewritten from Foodmart to Adventure Works. We will use the TopCount above and place it within the Generate function like this:

WITH MEMBER
Measures.[Country Name]
AS
‘Ancestor([Customer].[Customer Geography].CurrentMember, [Customer].[Customer Geography].[Country]).Name’
SELECT
{Measures.[Country Name],[Measures].[Internet Sales Amount]}
ON COLUMNS,
GENERATE(
[Customer].[Customer Geography].[Country].MEMBERS,
TOPCOUNT(
DESCENDANTS(
[Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Customer]
),1,([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Year].&[2003])
)
)
ON ROWS
FROM
[Adventure Works];

Country Name is added as a calculated measure in the grid. You can see the result in the last picture.

Generate with TopCount

I think the business case is very strong for SSAS2005 clients to be able to use the Generate function with TopCount in tis way. If you know about one do not hesitate to mention it.
 

Advertisements

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: