Emulate Aggregation Type 'None' in MDX

Question

How can the Aggregation Type = None setting be emulated using pure MDX?

---

Josh Ash

Answer 1

delete the Calculate statement from the beginning of your MDX Script. Without this statement, no aggregation will take place within the cube and you'll only see data at the intersection of the leaves of every dimension, as if every measure had AggregateFunctionNone.

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 2

That will do the entire cube. Not just a single measure.

Can you think of a way of being able to do a single measure, using an MDX Calculated Member?

---

Josh Ash

Answer 3

I don't think you have any other choice than to scope your measure.  Unfortunately, you'll probably need to have quite a few scope statements but you might be able to shorten it all using the DESCENDANTS function.  something like this:

SCOPE([Measures].[Your Measure Here]);
    SCOPE(DESCENDANTS([Your Dimension].[All],[Your Dimension].[Name of the leaf level],BEFORE);
        This=Null;
    END SCOPE;
    --Repeat for all your dimensions
END SCOPE;

You'll have to and inner scopes for all you dimensions.  I haven't tried it, but in theory, it should work

Let me know if it does!

Answer 4

Thanks David, using SCOPE was the approach that worked. We did end up with quite bit of code though!

---

Josh Ash