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