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

MDX Query Performance Tunning Expand / Collapse
Author
Message
Posted Sunday, August 24, 2014 10:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:42 AM
Points: 15, Visits: 120
Hi ,

Below MDX Query its taking long time.Can you please help me, how to reduce time?.
Give me some points.

WITH MEMBER [Measures]. [CurrentDate] as 'NOW()'
MEMBER DateDif as vba!datediff("d",[Invoice Sent Date].[Date] .currentmember.member_value,
VBAMDX.Format(VBAMDX.Now(),"MM/dd/yy"))
Member [DueBucket] as IIF (DateDif > 180, "Above 180",
IIF(DateDif>120, "121-180",
IIF(DateDif>90, "91-120",
IIF(DateDif>60, "61-90",
IIF(DateDif>30, "31-60",
IIF(DateDif>=0, "0-30", "Not Due"))))))
SELECT NON EMPTY {[Measures].[Amount] ,CurrentDate,DateDif , [DueBucket] } ON COLUMNS,
NON EMPTY {([Customer].[Customer Name].[Customer Name].ALLMEMBERS *
[Lob].[Lob Code].[Lob Code].ALLMEMBERS *
[Date].[Month Name].[Month Name].ALLMEMBERS *
[Invoice Sent Date].[Date].[Date].ALLMEMBERS ) } ON ROWS
FROM [iWise]

Regards,
Raghav.
Post #1606962
Posted Tuesday, August 26, 2014 3:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 635, Visits: 1,213
Firstly, have a read of this article by Chris Webb about the performance of DateDiff: http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/

Secondly,
It doesn't seem like you are doing anything that is particularly dynamic so, depending on your processing model, you may wish to take all of this:
WITH MEMBER [Measures]. [CurrentDate] as 'NOW()'
MEMBER DateDif as vba!datediff("d",[Invoice Sent Date].[Date] .currentmember.member_value,
VBAMDX.Format(VBAMDX.Now(),"MM/dd/yy"))
Member [DueBucket] as IIF (DateDif > 180, "Above 180",
IIF(DateDif>120, "121-180",
IIF(DateDif>90, "91-120",
IIF(DateDif>60, "61-90",
IIF(DateDif>30, "31-60",
IIF(DateDif>=0, "0-30", "Not Due"))))))

...and make it into an expression in your fact table dsv. Then you could join it to a DueBucket dimension which would make it both much faster and more flexible for people browsing the cube.

Again this second part depends on your processing model - for example this wouldn't work if you were processing your measure group incrementally. Have a read of the article though it should provide some pointers.





I'm on LinkedIn
Post #1607386
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse