Tuesday, February 22, 2011

MDX Overwrite Semantics and Complex Attribute Relationship

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.
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.

You get a valid new cell only when the new slices correspond to the first quarter of fiscal year 2004 exist with the untouched old slices derived from the original calendar quarter; otherwise you get a cell that doesn’t exist hence a NULL value for the MDX formula. In this case only the third quarter of calendar year 2003 produces valid cell coordinates. To force setting the current coordinate to the first quarter of fiscal year 2004 regardless of the current calendar quarter, you have to explicitly overwrite all attributes related to the [Calendar] hierarchy like 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.

8 comments:

  1. excelent post Jeffrey, I really enjoyed reading it!

    I just have one question that is not entirely clear to me:
    why does "Fiscal Semester" gets explicitly overwritten wheras "Fiscal Quarter of Year" gets implicitly overwritten?
    both are direclty related to "Fiscal Quarter" and therefor from an "attribute-relationship point-of-view" they are both the same, right?

    regards,
    Gerhard Brückl

    ReplyDelete
  2. Note that the query uses the [Fiscal] user defined hierarchy instead of the [Fiscal Quarter] attribute hierarchy. Therefore all levels that have non-trivial slices in the [Fiscal] hierarchy are explicitly overwritten, this includes the [Fiscal Semester] attribute.

    ReplyDelete
  3. Jeffrey, your posts are such a delight to read! The images made it even more easier to understand...

    Cheers,
    Jason Thomas

    ReplyDelete
  4. so explicit overwrites happen for related attribtes within the queried user-hierarchy whereas implicit overwrites happen for related attributes that are not in the queried user-hierarchy?

    ReplyDelete
  5. Explicit overwrites depend on the MDX expression. [Fiscal Quarter].[Fiscal Quarter].[Q1 FY 2004] would explicitly overwrite [Fiscal Quarter] attribute while implicitly overwrite [Fiscal Semester] and [Fiscal Year] attributes. [Fiscal].[Fiscal Quarter].[Q1 FY 2004] would explicitly overwrite all three attributes since they all belong to the [Fiscal] hierarchy and have non-trivial slices based on the given MDX expression. Another way to explicitly overwrite all three attributes is to write MDX tuple expression ([Fiscal Quarter].[Fiscal Quarter].[Q1 FY 2004], [Fiscal Semester].[Fiscal Semester].[H1 FY 2004], [Fiscal Year].[Fiscal Year].[FY 2004]).

    ReplyDelete
  6. The previous explanation is unsatisfactory. I tried to simplify things a bit too much which seemed to cause some confusion. I updated the pictures in the post to more accurately reflect the overwrite semantics when a user defined hierarchy is involved. When an MDX expression uses a user defined hierarchy, all levels in that hierarchy are explicitly overwritten. In the given example, although both [Month Name] and [Date] attributes are also explicitly overwritten, they are overwritten from All to All, according to the rules, overwritting from All to All will not blow away slices on related attributes, so they have no effect in this case.

    ReplyDelete
  7. Hi,

    Thanks for your great post. I know this is an old post but I just recently came across a case that the information you provided was very useful for.

    I have two questions in regards to some of the comments provided at the end of the blog:

    In the comments section, when using a user defined hierarchy example, you mention:
    "...
    Therefore all levels that have non-trivial slices in the [Fiscal] hierarchy are explicitly overwritten".

    I have two questions:

    1) What is a non-trivial slice? Is it a NON-ALL slice?

    2) When all levels of the UDH hierarchy attributes are explicitly overwritten, do their related and relating attributes get implicitly overwritten in turn as well? For example, in the case provided in this blog, do related/relating attributes of the [Month Name] and [Date] levels also get overwritten to ALL? In your example, the change would be from ALL to ALL but if the row axis of the query had a particular day or month in it, then they would have individual values and the change would be from a slice to ALL. In that case, would attributes like [Day of Week], [Month of Year], etc. also get implicitly set to ALL?

    Thanks,

    Shabnam Watson

    ReplyDelete
  8. The answers to both your questions are yes.

    ReplyDelete