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.

19 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
  9. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
    Thanks & Regards,
    VRIT Professionals,
    No.1 Leading Web Designing Training Institute In Chennai.

    And also those who are looking for
    Web Designing Training Institute in Chennai
    SEO Training Institute in Chennai
    Photoshop Training Institute in Chennai
    PHP & Mysql Training Institute in Chennai
    Android Training Institute in Chennai

    ReplyDelete
  10. Sehar News is a wide area that envelops pakistan news , kashmir news , International News, Sports News, Arts and
    Entertainment News, Science and Technology, Business News, latest news in urdu , Education News and today news Columns.
    The perusers can snatch most recent urdu news dependent on different political and get-together
    occurring in the nation. Sehar News covers the most recent and up and coming news features, Read today urdu news and top stories from different backgrounds and carries it to the viewers



    wanna know latest pakistan news ? click pakistan news and know more.

    Read latest news in urdu and know more .

    read all the latest urdu news in this site.

    you dont know ? about today news click here and know more.

    know the current news of kashmir news check here.

    read all about today urdu news and gain knowledge.

    ReplyDelete
  11. Established in 2016 SSDWebHosting.net is providing top quality domain and hosting services worldwide to
    our valued customers and trying to play a little role in their success.We offer about 500 distinctive gTlds and ccTlds
    to look over which includes old master class gTlds like .com, .net and .org, in addition this we also offer newly launched Tlds
    like .xyz, .online, .master, .office, .on, .top and .club. We can assist you with choosing the best fitting name. Let's bring your
    thought or business on the web. visit this site https://SSDWebHosting.net/ to know more.


    Do you wanna buy SSD Web Hosting visit here.
    Find best Cheap Web Hosting here.

    ReplyDelete
  12. Ez battery reconditioning reviews - You can now easily revive your old batteries with this
    Ez battery reconditioning pdf which provides step by step instructions for recondition a battery.
    Ez battery reconditioning blog publishes how Ez battery reconditioning programs works
    and where buy Ez battery reconditioning step by step program online after this
    candid Ez battery reconditioning reviews. Battery reconditioning course is newbie friendly. It may help you
    set up and run your own battery reconditioning business by learning this skill at home.
    How to recondition a battery with Ez battery reconditioning .
    Have you heard about Tom Ericson's Ez battery reconditioning reviews technique and
    are wondering whether it is possible or not. visit https://ezbatteryreconditioninginfo.com/ this site to know more. Thank you every one.

    ReplyDelete
  13. Tech Gadgets reviews and latest Tech and Gadgets news updates, trends, explore the facts, research, and analysis covering the digital world.
    You will see Some Tech reviews below,

    lg bluetooth headset : You will also wish to keep design and assorted features in mind. The most essential part of the design here is the buttonsof lg bluetooth headset .

    Fastest Car in the World : is a lot more than the usual number. Nevertheless, non-enthusiasts and fans alike can’t resist the impulse to brag or estimate according to specifications. Fastest Car in the World click here to know more.

    samsung galaxy gear : Samsung will undoubtedly put a great deal of time and even more cash into courting developers It is looking for partners and will allow developers to try out
    different sensors and software. It is preparing two variants as they launched last year. samsung galaxy gear is very use full click to know more.

    samsung fridge : Samsung plans to supply family-oriented applications like health care programs and digital picture frames along with games It should stick with what they know and they
    do not know how to produce a quality refrigerator that is worth what we paid. samsung fridge is very usefull and nice product. clickcamera best for travel: Nikon D850: Camera It may be costly, but if you’re trying to find the very best camera you can purchase at this time, then Nikon’s gorgeous DX50 DSLR will
    probably mark each box. The packaging is in a vibrant 45.4-megapixel full-frame detector, the picture quality is simply wonderful. However, this is just half the story. Because of a complex 153-point AF system along with a brst rate of 9 frames per minute. camera best specification. click here to know more.

    ReplyDelete
  14. If you want to give your home or office a Luxurious interior design with a small budget kindly
    contact our decor companies in Abu Dhabi.. decor companies in abu dhabi

    ReplyDelete
  15. Ez battery reconditioning reviews - You can now easily revive your old batteries with this
    Ez battery reconditioning pdf which provides step by step instructions for recondition a battery.
    Ez battery reconditioning blog publishes how Ez battery reconditioning programs works
    and where buy Ez battery reconditioning step by step program online after this
    candid Ez battery reconditioning reviews. Battery reconditioning is newbie friendly. It may help you
    set up and run your own battery reconditioning business by learning this skill at home.
    How to recondition a battery with Ez battery reconditioning .
    Have you heard about Tom Ericson's Ez battery reconditioning reviews technique and
    are wondering whether it is possible or not. visit https://ezbatteryreconditioninginfo.com/ this site to know more. Thank you every one.

    ReplyDelete
  16. We provide a complete line of automatic transmission parts, overhaul kits, troubleshooting and overhaul guides to factory re-manufactured automatic transmissions . Shift kits are available, and more importantly shift enhancement kits are available, these enhancement kits fix know problems with automatic transmission. Enhancement kits correct design and manufacturing defects, yes they can be corrected after your vehicle has left the factory. If there is an enhancement kit available for you application be sure you have one installed before your transmission suffers costly failures. automatic transmission parts .

    ReplyDelete
  17. Thank you for excellent article.You made an article that is interesting.
    Best AWS certification training courses. Build your AWS cloud skills with expert instructor- led classes. Live projects, Hands-on training,24/7 support.
    https://onlineidealab.com/aws-training-in-bangalore/

    ReplyDelete
  18. PhenQ Reviews - Is PhenQ a new Scam?
    Does it really work? Read this honest review and make a wise purchase decision. PhenQ ingredients are natural and ...
    It has been deemed fit for use in the market. It is not found to be a Scam weight loss pill.
    By far it is the safest and most effective weight loss pill available in the market today.

    Phenq reviews ..This is a powerful slimming formula made by combining the multiple weight loss
    benefits of various PhenQ ingredients. All these are conveniently contained in one pill. It helps you get the kind of body that you need. The ingredients of
    the pill are from natural sources so you don’t have to worry much about the side effects that come with other types of dieting pills.Is PhenQ safe ? yes this is completly safe.
    Where to buy PhenQ ? you can order online. you don`t know Where to order phenq check this site .

    visit https://mpho.org/ this site to know more about PhenQ Reviews.

    ReplyDelete