Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.