Referencing attributes of a distant relation (MDX)

Question

Referencing attributes of a distant relation (MDX)

Sam Peascod

SSCrazy

Points: 2497
More actions
November 11, 2007 at 10:25 pm

#122206

I have a product category hierarchy in my item dimension. It has three levels of product category groupings followed by the item code.
An item code has a parent packing group (which is not a level in this hierarchy - it has a separate hierarchy, if that's of any use). The packing group has a number of attributes such as bottles per wrap.
I have a FactSales table which links to the item code. One of the measures on FactSales is line_qty.
So, I want to write a calculated measure which returns the number of bottles sold. Keeping in mind that packing group is in a different hierarchy, how can I achieve this? Would I be better off moving the attributes onto the item and away from the packing group?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sam Peascod SSCrazy Points: 2497 More actions · Answer 1

I nutted it out using the LinkMember function:

[Measures].[Line Qty] * LinkMember([Current Items].[Product Category].CurrentMember, [Packing Group]).Parent.Parent.Properties("Bottles Per Wrap")

That works, but I've read that the Properties() function is taboo now and another syntax should be used (such as DIMENSION PROPERTIES) as you can get better performance. Is there a more efficient way to write this?

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 2

You shouldn't need to use LinkMember. If I'm reading your dimension information right, you probably should be including [Packing Group] as part of [Current Items] dimension. Alternatively, you could build it as a referenced dimension (check your diemsnin usage tab).

I'll also point out that you'll have trouble aggregating this measure, the way I expect you'll want to. Better to perform the multiplication as part of the data feed, and then the fact simply SUMs across all dimensions.

Sam Peascod SSCrazy Points: 2497 More actions · Answer 3

Hi Dave,

Thanks for the tip. I've added a named calculation to the DSV and am using that instead. What are the performance considerations in doing this? I quite like this technique and have solved a couple of issues with it previously (maybe because I'm better at SQL than MDX). I was able to get some degree of aggregation working by using SUM(DESCENDANTS(...)...), but that was only against the one hierarchy and led me to post my other question about the stored procedure.

Sam

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 4

While actually, you'll find this a better (correct??) implementation. It doesn't have anything to do with your MDX skills:

- In general, approach cube design by ensuring you have aggregatable facts. For instance, it's better to use "Sales Units" and "Sales Dollars" than "Sales Units" and "Price". Derive Price (or "Average Price") via "Sales Dollars" / "Sales Units"

- Always remember that the DSV simply tells AS how to join tables. The actual relationships implemented depends on the dimension relationships and the Dimension Usage information. (BTW, MSAS won't navigate backwards through a DSV relationship. If you need to do that, try turning it around.)

- I prototype Named Calculations in the DSV all the time. But for various support reasons, I've strongly recommend implementing this logic via a view. In fact, I've taken to always implementing views for all tables, because of some underlying audit fields that are hidden via the view. (It's best to only reference those fields you need, in particular from the data table.)

- Because you've implemented an aggregatable fact, performance actually improves. If you tried to work with non aggregatable facts (and you can do it by the way), performance starts to suffer as you override default handling to get the correct results.

Good luck!