i got a query that consists of a cross join between 3 dims .
dim1- has 15,000 members
dim2- this is the organisation hierarchy
dim 3- has 30,000 members
this is the query:
TOPCOUNT(
FILTER(
(NONEMPTY([DIM1].[Key].[All],[Measures].[Target])
*DIM2.CHILDREN*
NONEMPTY([DIM3].[Key].[All],[Measures].[Target])),
[Measures].[Target]>0
),20,[Measures].[Gap)
as you can imagine -this is a huge cross join but i have to do this ...
i tried functions like "filter" and "non empty" but it doesnt help...it takes more then 30 minutes...
how can i improve it so it will run in a short time?
thank you