Hello All,

   Can anyone help me optimize the below query.

member agg as
count(filter ( [Wholesaler].[Wholesaler].currentmember*
[Retail Account].[Retail Account].[Retail Account],Rank([Wholesaler].[Wholesaler].currentmember,order ([Wholesaler].[Wholesaler].[Wholesaler], [Measures].[abc] ,bdesc))=1))


select [Wholesaler].[Wholesaler].[Wholesaler] on 0, agg on 1 from cube

Regards,

Bharath

Can you describe what you are trying to do?

It looks like you are trying to get a count of the Retail Accounts for the wholesaler with the highest value for the measure ABC, but because retail account and wholesaler are independent dimensions you  would most likely need to do a non-empty against one of your measures otherwise you would just get the full count of retail accounts.

member agg as
count(
  NONEMPTY ( 
   [Retail Account].[Retail Account].[Retail Account]
   , [Measures].[abc])

)

select TopCount( [Wholesaler].[Wholesaler].[Wholesaler], 1 measures.[abc]) on 0, agg on 1 from cube

Hi Bharath,

Rank is a function in MDX that can become very slow very fast, but with some slight changes it can mean a huge difference. I would suggest to try creating the set seperatly, and from that set do the count.

https://www.mssqltips.com/sqlservertip/3530/sql-server-analysis-services-rank-and-row-number-ordering/

http://sqlblog.com/blogs/mosha/archive/2006/03/14/ranking-in-mdx.aspx

The post above will help u greatly to understand the rank function/performance.

Hello Darren,

   Am trying to find the count of Retail Account for each wholesaler when it is the primary supplier.Can you help me with it.

Regards,

Bhrath

Below query helped me impove performance from 4 mins to 1 min.

member agg as
count(filter ( [Wholesaler].[Wholesaler].currentmember*
[Retail Account].[Retail Account].[Retail Account],Rank([Wholesaler].[Wholesaler].currentmember,topcount ([Wholesaler].[Wholesaler].[Wholesaler],1, [Measures].[abc]))=1))


select [Wholesaler].[Wholesaler].[Wholesaler] on 0, agg on 1 from cube

Regards,

Bharath