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.