Create Customer Ranking in Cube

Hey guys,

is there a way i can create a Rank Member or measure in my cube based on sales for that customer, no matter what time heirarchy we are looking at.

I have reporting requirements where they want to see all customers, ordered by Sales Amount. It could be at a year level, month level, or even a week/day level..

Im new to SSAS and MDX, i have looked at a few examples posted here, but cannot work out where i put the script in my cube. Is it a measure or do i do it as a calculated column in the DSV.

Any help is much appreciated.

Thanks

Scotty

Here's an Adventure Works ranking query, which uses a query-scoped calculated measure (assuming that the Customer hierarchy is on the rows of the query):

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select [Date].[Fiscal Year].Members *

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

NON EMPTY [Customer].[Customer Geography].[Country].Members on 1

from [Adventure Works]

--

All Periods All Periods FY 2002 FY 2002 FY 2003 FY 2003 FY 2004 FY 2004 FY 2005 FY 2005
Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank
Australia $9,061,000.58 2 $2,568,701.39 1 $2,099,585.43 1 $4,383,479.54 2 $9,234.23 3
Canada $1,977,844.86 6 $573,100.97 3 $305,010.69 6 $1,088,879.50 6 $10,853.70 2
France $2,644,017.71 5 $414,245.32 6 $633,399.70 4 $1,592,880.75 5 $3,491.95 6
Germany $2,894,312.34 4 $513,353.17 5 $593,247.24 5 $1,784,107.09 4 $3,604.83 5
United Kingdom $3,391,712.21 3 $550,507.33 4 $696,594.97 3 $2,140,388.50 3 $4,221.41 4
United States $9,389,789.51 1 $2,452,176.07 2 $1,434,296.26 2 $5,483,882.67 1 $19,434.51 1

>>

|||Please remove this line, as it is wrong:

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Obviously, even if [Internet Sales Amount] is NULL, the rank will never be NULL - it is always an integer value.

|||

Ok guys, thanks for the replys..

I added the following into a calculated measure that i called CustSalesRank .. (taken from the 1st line of code) is that Correct?

When i added this to the expression box in the calculated member, i get a red undeline on the "with" statement.. What am i doing wrong?

CREATE MEMBER CURRENTCUBE.[MEASURES].[CustSalesRank]

AS with member [measures].[CustSalesRank] as

Rank([Customer].[Customer Group Name].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Group Name]),

[Measures].[Sales Amount], BDESC)),

select [Date].[Fiscal Hierarchy].Members *

{[Measures].[Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Group Name].Members on 1

from [Sales By Market],

VISIBLE = 1 ;

--

|||

the error i get when i try to deploy is:

Error 2 MdxScript(Sales By Market) (17, 5) Parser: The syntax for 'with' is incorrect. 0 0

|||

Hi Mosha,

You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))

select

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

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Brunswick (null) 5
Manitoba (null) 6
Ontario $36.96 4
Quebec (null) 7

versus:

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select

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

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Ontario $36.96 4

>>

|||

> You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

Deepak - this is very very dangerous path. NON_EMPTY_BEHAVIOR is not a semantic feature, it is a performance hint. It doesn't cause rows to be removed by NON EMPTY. They might get removed sometimes when NEB is defined incorrectly, like in the example above, but it is purely a hint, and in other situations they won't get removed. So you will get inconsistant and even wrong results.

To properly remove rows with no sales, there must be specific IIF for that, i.e.

with member [Measures].[CustSalesRank] as

IIF( IsEmpty([Measures].[Internet Sales Amount]), NULL, Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Now NON_EMPTY_BEHAVIOR is correctly defined. Another idea is to optimize performance here would be to remove Order from inside Rank, and use version of Rank with 3 parameters.

|||

Mosha,

Thanks for your input.. Could you please kindly answer my question for me.. I just need to know where i put this code, i put it in a calculated measure and it didnt work. I removed all things from the select statement and down, but im not sure if it is working correctly..

Also, with all this code talk, im not 100% sure what i should be putting in now...

Thanks

Scotty

|||

For Adventure Works, you can put something like that inside MDX Script:

CREATE [CustSalesRank] = Rank([Customer].[Customer Geography].CurrentMember,[Customer].[Customer Geography].CurrentMember.Level.Members,[Measures].[Internet Sales Amount]);

|||

Thanks Mosha.. this worked great..

If i may ask for one more piece of advise, when i do this,, all the customers that have 0 sales are still shown with the lowest rank number.. in my case 81 . what i can i add to expression that will supress customers with 0 sales..?

Thanks

scotty

|||

Thanks for clarifying that, Mosha - I had (mistakenly) assumed that, in AS 2005, NON_EMPTY_BEHAVIOR would cause those rows to be removed.

On the use of Rank() with 3 parameters in lieu of Order() - has the semantics of Rank() changed in AS 2005, because BOL still says the following:

http://msdn2.microsoft.com/en-us/library/ms144726.aspx

>>

SQL Server 2005 Books Online

Rank (MDX)

Updated: 17 July 2006

...

The Rank function does not order the set.

>>

|||

The comment that Rank function doesn not order the set is correct, although I can see how it can be misleading. It probably refers to the fact that internal implementation of Rank doesn't need to order the set in order to find the rank. Please see detailed discussion about Rank and algorithms behind it in this blog:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/03/14/mdx_ranking.aspx

|||

Thanks - since the same comment also appeared in AS 2000 BOL, it could be confusing. But the MDX Solutions chapter, referenced in your blog entry, draws this distinction:

"..The semantics for this function have changed between Analysis Services 2000 and 2005. When the expression is provided, it is used to determine if ties exist and what the right rank should be. In Analysis Services 2000, the expression was used when the tuple was found to search neighbors in the set and determine fair ranking numbers.."