I have a query I'm trying to optimise. I have read an article here http://web.archive.org/web/20100823233821/http://www.biblogs.com/2008/03/30/optimizing-countfilter-expressions-in-mdx/ about optimising the Filter expression, but having no overall improvements. Wondering if anyone else has any ideas.
Basically, the initial idea was to do this;
WITH MEMBER [test] AS COUNT( FILTER( [Dimension].[Hierarchy].[Level], [Measures].[Total Orders] = 1 ) ), FORMAT_STRING = "#,#0" select [test] on 0 from [InPatient Episodes]
This takes about 15 seconds to run. Following the steps in the article above, I rewrote it like so, as I read that using Filter stops SSAS from using Block Calculation mode:
WITH MEMBER [test] AS Sum ( [Dimension].[Hierarchy].[Level] ,IIF ( [Measures].[Total Orders] = 1 ,1 ,NULL ) ) ,FORMAT_STRING = "#,#0" SELECT [test] ON 0 FROM [InPatient Episodes];
But this takes 15 seconds also.
The Dimension level has around 4 million members in it, and our Fact (Orders) has approx 16million rows, so it's not particularly huge.