CODEWITHSUNDEEP
sqlsql-serversql-server-2012ssasmdx
Bill Blankenship
Bill Blankenship

Reputation: 3356

SSAS -> MDX -> How to create a query that returns a topcount by a date period i.e. week?

I have received a recent request to create a query that returns the top 100 customers by revenue per week. I can obviously and easily return the top 100 customers as a whole but when I add in the week attribute, my query times out. I was just wondering if some out there has run into this or has been able to produce a comparable query?

My query that works without week present. :

     SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS  ) }, 100, [Measures].[Revenue])  ON ROWS 
FROM [DW]

My query with week present that just times out everything completely. :

SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS * [Date Link].[Week].[Week].ALLMEMBERS ) }, 100, [Measures].[Revenue])  ON ROWS 
FROM [DW]

Upvotes: 1

Views: 3708

Answers (1)

Meff
Meff

Reputation: 5999

Wouldn't this give you the top 100 customers and the week they were top in? So customer A could be in 1st position in week one, 5th position in week two, and so on, but you'd get 100 rows in total not 100 per week?

Try something like

SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY 
{
[Date Link].[Week].[Week].ALLMEMBERS *
{
TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS ) }, 100, [Measures].[Revenue])  
}
}
ON ROWS 
FROM [DW]

I think you may need to use GENERATE instead of * with the week members, can't remember and trying to do this off the top of my head ;)

Upvotes: 1

Related Questions