Saturday, February 25, 2012

mdx performance issue

I have the following mdx code. When i added in the top "with member" statement the performance drop off pretty good. Any Suggestions?

with member measures.sales as iif(not isempty([Measures].[Billed Sales Amount])> 5000, [Measures].[Billed Sales Amount], Null)

SELECT

NON EMPTY {measures.sales}ON COLUMNS,

NON EMPTY { [CUSTOMER JOB].[By Cust-Title-Job].[Title Name].ALLMEMBERS} ON ROWS

FROM [DW PREP ARCHIVE]

WHERE ( (lastperiods(12,tail(filter({[Job Complete Date].[Calendar Full].[Month].members* [Measures].[Billed Sales Amount]} as s, Not IsEmpty(s.current)),1).item(0).item(0))), (EXCEPT([PREP CONTROL HDR].[Bill Formats].[Bill Formats],({[PREP CONTROL HDR].[Bill Formats].&[7],[PREP CONTROL HDR].[Bill Formats].&[19]}))))

here is how I re-wrote the code to get better performance, it involved using a number of selects

SELECT NON EMPTY { [Measures].[Billed Sales Amount] } ON COLUMNS,

NON EMPTY { ([CUSTOMER JOB].[By Cust-Title-Job].[Title Name].ALLMEMBERS ) } ON ROWS

FROM ( SELECT ( FILTER([CUSTOMER JOB].[By Cust-Title-Job].[Title Name].MEMBERS,[Measures].[Billed Sales Amount] > 5000) ) ON COLUMNS

FROM ( SELECT ( (lastperiods(12,tail(filter({[Job Complete Date].[Calendar Full].[Month].members* [Measures].[Billed Sales Amount]} as s, Not IsEmpty(s.current)),1).item(0).item(0))) ) ON COLUMNS

FROM ( SELECT ( -{ [PREP CONTROL HDR].[Bill Formats].&[19], [PREP CONTROL HDR].[Bill Formats].&[7] }) ON COLUMNS FROM [DW PREP ARCHIVE])))

No comments:

Post a Comment