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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a query I'm trying to optimise. I have read an article here 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;

        [Measures].[Total Orders] = 1

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:

  MEMBER [test] AS 
        [Measures].[Total Orders] = 1
   ,FORMAT_STRING = "#,#0" 
  [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


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

Browse other questions tagged or ask your own question.