Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with huge CrossJoin - Improving a Query Expand / Collapse
Author
Message
Posted Sunday, September 01, 2013 12:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:23 AM
Points: 21, Visits: 103
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
Post #1490439
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse