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.

37 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
  19. Taldeen is one of the best plastic manufacturing company in Saudi Arabia. They are manufacturing Handling Solutions Plastic products like Plastic Pallets and plastic crates. Here is the link of the product
    Handling Solutions
    Plastic Pallets

    ReplyDelete
  20. Here is the details of best BSc Medical Imaging Technology Colleges in Bangalore. You can get the college details from the below link. BSc Medical Imaging Technology Course is one of the best demanding course in recent times in India
    BSc Medical Imaging Technology Colleges In Bangalore

    ReplyDelete
  21. Christian College Bangalore providing BSc Optometry Course. Here is the link about the details of BSc Optometry. You can click the below link for more information about BSc Optometry. BSc Optometry is one of the most demanding course in recent times.
    Optometry Colleges In Bangalore

    ReplyDelete
  22. Christian College Bangalore providing BSc Medical Imaging Technology Course. Here is the link about the details of BSc Medical Imaging Technology. You can click the below link for more information about BSc Medical Imaging Technology.
    BSc Cardiac Care Technology Colleges In Bangalore

    ReplyDelete
  23. BBA Aviation course is the best (Most Demanded) management course in India. Here, Christian College Bangalore providing BBA Aviation course. You can get the details of Christian College BBA Aviation from the below mentioned link. If you are interested in BBA Aviation, just visit the below link to know about BBA Aviation.
    BBA Aviation Colleges In Bangalore

    ReplyDelete
  24. GrueBleen is one of the Branding and Marketing agency Based in Riyadh- Saudi Arabia. The main functions of GrueBleen is Advertising, Branding, Marketing, Office Branding, Exhibition Management and Digital Marketing. Visit the below link to know more about GrueBleen Creative Club.
    Branding Agency Riyadh
    Marketing Agency Riyadh

    ReplyDelete
  25. Agriculture Solutions – Taldeen is a plastic manufacturing company in Saudi Arabia. They are manufacturing agricultural plastic products like greenhouse cover and hay cover. Visit the below link to know more details
    Agriculture Solutions
    Greenhouse Cover

    ReplyDelete
  26. Medical Imaging Technology – One of the most demanding allied health science course in recent times in India. Check out the details of Best BSc Medical Imaging Technology Colleges Details with the following link.
    BSc Medical Imaging Technology Colleges In Bangalore

    ReplyDelete
  27. BSc Perfusion Technology – If you are looking to study BSc Perfusion Technology in Bangalore, just check out the following link. In that link you can get the details of Best BSc Medical Imaging Technology colleges in Bangalore
    BSc Perfusion Technology Colleges in Bangalore

    ReplyDelete
  28. GrueBleen – One of the best social media marketing agency in Riyadh- Saudi Arabia. Visit here for the all service details of GrueBleen.
    Social Media Marketing Agency

    ReplyDelete
  29. http://www.amazingbaba.com/ - Trusted Website For Best Quality first copy watches In India.


    We have Top Quality first copy watches.Amazingbaba has a trendy and top quality selection in luxury watches,
    first copy watch brands in India,watches for men, with all the top names including Omega, Tag Heuer, Hublot, Bell & Ross, Breitling, Patek Philippe watches,
    mens watches Replica Rolex Submariner, Women's Replica Rolex Date-Just,Rolex Milgauss, these classy watchesare readily available in various shapes, sizes,
    and top quality and easily to browse categories and well-photographed images and videos of the Swiss replica watches.
    And you can buy Watches as gift for men.


    Check Our Website http://www.amazingbaba.com/ .

    ReplyDelete
  30. http://Mayapuri.net/-A 16+ year old Creative Media
    Solutions company.

    Engaged in Practical CreativityAdvertising agencies in chennai for its clients from India,Europe and the US.
    A provenTop Graphic design studios in chennai portfolio of work across
    diverse media for its clients from different domains.Corporate Film Makers in chennai
    An intricate fusion of insightful strategy,3D augmented reality cutting-edge
    ideas, and intelligent media integration is what we callBranding agency in chennai practical creativity.

    Check Our Website http://Mayapuri.net/.

    ReplyDelete
  31. http://karachipestcontrol. com/-Karachi Best Pest Control and Water Tank Cleaning Services.

    M/S. KarachiPestControl has very oldKarachi Pest Control Services Technical Pest Control workers
    thatfumigation services in Karachi live and add your space sevenfumigation in Karachi
    days every week.Pest services in karachiThis implies we are able toTermite Fumigation in Karachi
    be with you actuallytermite proofing in karachi quickly and keep our costs very competitive. an equivalent
    nativeUnique fumigation technician can see yourBed bugs fumigation in Karachi cuss management
    drawback through from begin to complete.Rodent Control Services Karachi Eco friendly technologies isWater tank cleaner in karachi
    also used.We are the firstWater Tank Cleaning Services in Karachi and still only professional water
    tank cleaning company in Karachi.With M/S. KarachiPestControlyou’re totallyBest Fumigation in karachi protected.


    Check Our Website http://karachipestcontrol. com/.

    ReplyDelete
  32. This comment has been removed by the author.

    ReplyDelete
  33. Genyatra provides train tickets, flight tickets, senior citizen yatra , foreign exchange, visa services to its Clients across World.
    train tickets, flight tickets, senior citizen yatra , foreign exchange, visa services

    ReplyDelete
  34. http://www.sapphiregemsstore.com/ Buy Gemstone Online.
    Buy pearl gemstone
    A Pearl (Moti) is a characteristic, Buy Blue Sapphirewhite to pale blue dim shaded, Organic stone made inside the body of a living being called 'Mollusc.'
    The PearlBlue Sapphire Gemstone In USA contains incredible mysterious significance in the Vedic soothsaying and is worn to quiet the planet Moon in the wearer's
    introduction to the world outline and is very successful in Anger, Skin, and Heath.Buy Blue Sapphire Gemstone Online Natural Pearls (Moti) originates from China (refined),
    South Sea,Blue Sapphire gemstone Venezuela (Natural Pearl), and Basra (Natural Pearl). You can easily Buy Pearl Gemstone from any gemstone shop.
    Buy Blue sapphire gemstone
    Blue Sapphire (Neelam)Buy Emerald Gemstones by and large originates from Sri-Lanka(Ceylon), Burma and Kashmir and is worn for planet Saturn (Shani).
    The stone is incredibly usefulBuy Emerald Stones for people encountering a full time of 'Shani SadheSaati' or 'Dhaiya' Buy Emerald Gemstone Onlinein their lives. Blue sapphire
    has many astrological benefits, and getting original blue sapphire stoneEmerald Gemstone Online USA is very tough, but Sapphire Gems you will buy original
    and certified Blue Sapphires Gemstonebuy ruby gemstones.
    Blue sapphire gemstone in the USA
    Blue Sapphire (Neelam) is the most grounded gemstone. Purple, violet and green are the most widelybuy ruby gemstones online recognized optional tints found in Blue Sapphires.
    VioletRuby Gemstones Online USA and purple can add to the general excellence of the shading, while green view as unmistakably negative. Blue sapphires with up to 15% violet or
    purple are, for the most part said to be of excellent quality. Darkoriginal ruby gemstone price is the typical immersion modifier or veil found in Blue Sapphires. You can quickly
    get orbuy yellow sapphire online buy Blue Sapphire Gemstone in the USA as well as in India.
    Buy Emeraldbuy yellow sapphire gemstone Gemstones
    EmeraldsNatural Yellow Sapphire Gemstones are valuable gems well known for their numerous favorable circumstances. You can find this gemstone in a few countries like Russia, Brazil,
    Zambia, India, Pakistan, and so forth. On the off chance that you are searching for Emerald gemstones, at that point, a standout amongst other gemstone
    vendorsyellow sapphire gemstone online in the market is Sapphire Gems.

    Check our website- http://www.sapphiregemsstore.com/

    ReplyDelete