I have the following MDX Query:

with

set [main]
as
'{[Measures].[cGrossSales],
[Measures].[cPYGrossSales],
[Measures].[pGrossSalesGrowth%],
[Measures].[cCYPtdGrossSales],
[Measures].[cPYPtdGrossSales],
[Measures].[pPtdGrossSalesGrowth%],
[Measures].[cCYQtdGrossSales],
[Measures].[cPYQtdGrossSales],
[Measures].[pQtdGrossSalesGrowth%],
[Measures].[cCYYtdGrossSales],
[Measures].[cPYYtdGrossSales],
[Measures].[pYtdGrossSalesGrowth%]}'

set[rowfilter]
as
'(FILTER({[Customer].[Chain].[lCustNbr].Members}, not
isempty([Measures].[cCYYtdGrossSales] +
[Measures].[cPYYtdGrossSales])))'
member [Customer].[Chain].[Total] as 'sum({rowfilter})'

SELECT
{[main]}

ON COLUMNS,
{[rowfilter], [Customer].[Chain].[Total]} ON ROWS

FROM CusItmWk_Dev WHERE([Time_Partial].[All Time_Partial].[2006].[Qtr2
- 2006].[Period 05 - 2006].[Week 18 - 2006],
[Customer].[Trade].[sMajTrade].&[26],
[Customer].[Type].[sType].&[VHVW])




The query takes over 1 minute to process and return. I am having to
sift through about 500,000 customers to get sales data. This Query
works, but I was wondering if someone could give me some pointers on
this. I have only worked in MDX for one project and I do not have any
formal training on it so this is one of the few queries that I have
constructed.


The result I am looking for is only customers in trade channel 26 with
type VHVW and their sales history to the current date.

Any pointers would be greatly appreciated.

The only thing I would suggest is to put the customer filter in where-clause
in your rowfilter. I am afraid that your rowfilter is calculating values for
all customers instead of the ones you want. Otherwise, I don't see anything
wrong.



"rush_edward (AT) eddierush (DOT) net" wrote: