Today I discovered that DefaultMember might result in a member other thant the default dimension member.
Reading documentation, I got the idea that DefaultMember would be always the default member defined for a dimension into a cube. In reality, the default member could change into a subcube that excludes the original default member from a calculation. This is important because writing MDX Scripts you cannot anticipate any possible use of your cube... and you should carefully consider when DefaultMember is the real appropriate syntax on a case-by-case basis. You have to be particularly careful whenever you use DefaultMember on a non-aggregatable attribute.
Let's look an example.
In Adventure Works you can write the following query.
WITH MEMBER Measures.DiffActual
AS (Scenario.Scenario.CurrentMember, Measures.Amount)
- (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT
{ [Scenario].[Scenario].[Forecast],
[Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]
This query provides this result:
I'm not interested in the real meaning of this query. This is only an excuse to see the DefaultMember in action against a non-aggregatable attribute. The DiffActual calculated member has a formula that use Scenario.Scenario.DefaultMember. The default member for Scenario.Scenario attribute is Actual. The DiffActual row shows the difference between the scenario in column and the Actual scenario.
Now, if we encapsulate the previous query into a subquery that apparently should return the same members, we can write this query.
WITH MEMBER Measures.DiffActual
AS (Scenario.Scenario.CurrentMember, Measures.Amount)
- (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT
{ [Scenario].[Scenario].[Forecast],
[Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM
(SELECT
{ [Scenario].[Scenario].[Forecast],
[Scenario].[Scenario].[Budget] } ON COLUMNS
FROM [Adventure Works])
Before executing this query, what is the expected result? First time I thought "it should be the same", but in reality it is different, like shown here:
What happened? Simply, the SSAS calculation engine needs a default member included in the "context" - when you use a subquery, you are defining a subset of the cube space that must have an existing default member for each attribute - if a default member is outside of the subcube, it is replaced by another member of the same attribute.
In the case we are exmining, the default member of the subcube becomes Forecast. This default member depends on the order of members of the same attribute used in the subcube. If we invert Forecast and Budget members only in the subcube, the default member becomes Forecast instead of Budget, changing the calculated member result (based on DefaultMember syntax).
WITH MEMBER Measures.DiffActual
AS (Scenario.Scenario.CurrentMember, Measures.Amount)
- (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT
{ [Scenario].[Scenario].[Forecast],
[Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM
(SELECT
{ [Scenario].[Scenario].[Budget],
[Scenario].[Scenario].[Forecast] } ON COLUMNS
FROM [Adventure Works])
This is the result for the query above:
To avoid this issue you can use a specific member instead of the keyword DefaultMember. In our example, the solution is:
WITH MEMBER Measures.DiffActual
AS (Scenario.Scenario.CurrentMember, Measures.Amount)
- (Scenario.Scenario.[Actual], Measures.Amount)
SELECT
{ [Scenario].[Scenario].[Forecast],
[Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]
With this syntax, any use of this query as a subcube in another query will not have the side effects we have seen before.
Why this is so important? Simply because Excel 2007, often used as a client by end users, makes heavy use of subqueries in MDX generated by PivotTable. I had to substitute all DefaultMember in MDX Scripts with well-known member names (this also affects my DateTool dimension, which I will update soon with this and other improvements).
Final note: a special thanks to Mosha Pasumansky for illuminating me on hidden secrets of DefaultMember. Mosha also said that using the member name of default member (instead of DefaultMember keyword) improves query performance.
Cross-posted from SQLBlog! -
http://www.sqlblog.com