The past month has been extremely busy for me so I didn’t get to write more blogs. Today we’ll resume the exploration of MDX calculation engine. In this post I am going to describe a common mistake made by people when writing MDX calculations that can be very hard to diagnose.
WARNING: The MDX overwrite behavior I am going to describe in this post applies to overwriting to a physical member or the [All] member. Overwriting to a calculated member has its own set of rules which are not covered here.
WARNING: The MDX overwrite behavior I am going to describe in this post applies to overwriting to a physical member or the [All] member. Overwriting to a calculated member has its own set of rules which are not covered here.
An Example
Let’s start with a simple example using Adventure Works DW 2008 database. First create a calculated measure, [m], that returns [Internet Sales Amount] for the first quarter of fiscal year 2004.
create member [Adventure Works].[Measures].[m] as
([Internet Sales Amount], [Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2004])
Next send a query to check the value of [m].
select [m] on 0
from [Adventure Works]
m |
$2,744,340.48 |
Now send another query calculating [m] but start with different calendar quarters.
select [m] on 0,
[Date].[Calendar].[Calendar Quarter].members on 1
from [Adventure Works]
m | |
Q3 CY 2001 | (null) |
Q4 CY 2001 | (null) |
Q1 CY 2002 | (null) |
Q2 CY 2002 | (null) |
Q3 CY 2002 | (null) |
Q4 CY 2002 | (null) |
Q1 CY 2003 | (null) |
Q2 CY 2003 | (null) |
Q3 CY 2003 | $2,744,340.48 |
Q4 CY 2003 | (null) |
Q1 CY 2004 | (null) |
Q2 CY 2004 | (null) |
Q3 CY 2004 | (null) |
Q4 CY 2006 | (null) |
Why don’t we see the value, $2,744,340.48, in all cells? Didn’t the MDX formula of [m] change whatever calendar quarter to the first quarter of fiscal year 2004? The answer lies in MDX overwrite semantics and attribute relationship of the [Date] dimension.
Examine the Example Based on MDX Overwrite Rules
Before we delve into details, let’s establish some terminologies first. MDX attribute relationship defines functional dependency between two attributes, e.g.
[Calendar Quarter] → [Calendar Semester] → [Calendar Year].
If A → B, we say B is related to A and A is relating to B. Therefore, [Calendar Semester] and [Calendar Year] are related attributes of [Calendar Quarter], and reversely [Calendar Quarter] and [Calendar Semester] are relating attributes of [Calendar Year]. On the other hand, [Fiscal Week] and [Calendar Quarter] are not related to each other.
Every MDX expression is evaluated in a context. In a simple case, the context includes the current cell which is defined by the current coordinates of all attributes in the cube. When an MDX expression, like ([Internet Sales Amount], [Date].[Calendar Year].[CY 2004]), overwrites the coordinates of some attributes to non-All members, it also overwrites the coordinates of all related and relating attributes. The following diagram illustrates how MDX overwrite semantics work when you set non-All slices to some attributes.
The common mistake I referred to at the beginning of the post is when people forget about the slices left over at the unrelated attributes. When dimension autoexists is applied and the new slices set by the MDX expression do not exist with the slices left over on the unrelated attributes, you end up with cells that don’t exist. Note that we don’t have this problem when there is only a simple linear attribute relationship defined on the dimension.
In case of a single linear relationship, all attributes in the dimension are always overwritten, either explicitly or implicitly, when any attribute is set to a non-All member. The problem arises only when there is a complex attribute relationship, one with tree-like structure.
Going to back to the Adventure Works example, to calculate the value of any cell, an initial cell coordinate is set to a quarter from the [Calendar] hierarchy. Moreover, all related and relating attributes are set implicitly as well. See a simplified version of attribute relationship below. Every attribute in the diagram, except for [Month Name] is set to a slice that corresponds to a calendar quarter.
After applying the MDX expression associated with calculated measure [m], some attribute slices change to new ones, shown as colored boxes below, other attribute slices retain the previous values, shown as white boxes below.
create member [Adventure Works].[Measures].[m] as
(
[Internet Sales Amount],
[Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2004],
[Date].[Calendar].[All]
)
Official Rules
The above example is based on overwriting the current coordinate to a non-All member. But what about the other cases? Well, the following table lists all combinations of how overwriting one attribute can affect its related or relating attributes.
Assume A → B.
Explicit Overwrite | Result |
A.All to A.All | B unaffected |
A.x to A.All | B to B.All |
A to A.x | B to Exists(B.members, A.x) |
B.All to B.All | A to A.All |
B.x to B.All | A to A.All |
B to B.x | A to A.All |
An implicit overwrite is when an attribute is moved because of an overwrite on a relating or related attribute. When an attribute is impacted by both an explicit overwrite and an implicit overwrite, the explicit takes precedence. Implicit overwrites do not overwrite related or relating attributes.
Parent Child Dimension
According to the overwrite rules discussed so far, overwriting the key attribute of a dimension to a non-All member should overwrite all other attributes in the dimension implicitly. But this does not work for parent child dimensions.
If you run query
with member measures.x as
(
[Measures].[Amount],
[Account].[Account].[Work in Process]
)
select x on 0,
([Account].[Account].[Raw Materials], [Account].[Account Number].[1162]) on 1
from [Adventure Works]
x | ||
Raw Materials | 1162 | (null) |
You get back null result even though the calculation overwrites the key attribute [Account]. You have to explicitly overwrite [Account Number] attribute to get back the [Amount] for [Work in Process].
with member measures.x as
(
[Measures].[Amount],
[Account].[Account].[Work in Process],
[Account].[Account Number].[All]
)
select x on 0,
([Account].[Account].[Raw Materials], [Account].[Account Number].[1162]) on 1
from [Adventure Works]
x | ||
Raw Materials | 1162 | $1,393,582.00 |
The official rule here is that:
Overwriting key attribute of parent child hierarchy does not overwrite other attributes except that of its parent.
Complex Relationships Are Very Common
Unless you define a single linear relationship for a dimension, you will end up with a complex relationship. Specifically, when there are more than two attributes in a dimension and you didn’t define any relationship, the default one is a tree-like relationship.
MDX Writeback with Custom Weight Expression
Users are likely to make the mistake we have discussed so far when the current cell coordinates contain many slices on numerous attributes. This is particularly true when it comes to MDX writeback with custom weight expression. When an MDX UPDATE CUBE statement is sent to Analysis Services, the desired result is allocated to leaf level nodes. So if you are writing a custom weight formula, the current context is always a leaf level cell with a slice on every regular attribute. If you are not careful overwriting all attributes in a dimension, your weight expression is likely to return NULL values and you won’t get the allocation you desired.
Conclusion
The complex rules of MDX overwrite semantics combined with a tree-like attribute relationship can easily lead to some very frustrating bugs in your MDX formula. You must make sure to overwrite slices on all attributes you don’t want to keep and preserve the slices on all attributes you do want to keep. Remember that the rules are different for parent child dimensions. If you have a simple tuple like MDX formula that returns unexpected NULLs, you should check whether you have violated the guidelines given in this blog post.
