I’m back! I swear! You know how when you put off a task for so long it just becomes THAT much harder to start it? I’m awfully bad at it, and blogging has become one of those things where I know I just need to sit down and do it, but as the days go on it gets harder and harder. I even have topics! Just a massive case of i-don’t-wanna-write-it-block.
The solution to this? An easy peasy short tidbit that I recently learned.
Mom has been teaching an MDX class and learned something she’d never known before – which is a shock in and of itself! But then, right after she told me about what she learned, I actually had occasion to use it! So now I’m going to share what we learned with you.
I hope you are familiar with member properties in MDX, as they are tremendously useful. A common example is the NAME property: [Product].[Product].CurrentMember.NAME or [Product].[Product].CurrentMember.Properties(“Name”), which returns the string that is the name of the member.
Similarly, you can return the key value of the member with the following syntax:
NB: You can’t use [Product].[Product].CurrentMember.Key. Doesn’t work. When in doubt, use the Properties(“”) syntax.
Now for the property I didn’t know (which is not surprising) AND that Stacia Misner didn’t know (which is VERY surprising) – in the case of a composite key, there are Key0, Key1, Key2 properties that you can use to call the individual parts of the key! When this was first mentioned to me I thought hey that’s cool, but as Mom pointed out, she rarely uses composite keys so it simply hadn’t come up. However, just a few days later I had an occasion to use this new-found knowledge and I can see it being very useful in the future!
One place where composite keys seem to pop up commonly is the nefarious Date dimension, in a Calendar hierarchy. A particular month, say June 2011, will have the unique member representation [Date].[Calendar].[Month].&&. In my case, I wanted to split up the month and year info, and have the month represented as a number. That information is very simply represented in the key, and now I had the tool to extract it!
[Date].[Calendar].[Month].&&.Properties(“Key0”) returns 2011.
[Date].[Calendar].[Month].&&.Properties(“Key1”) returns 7.
Easy as pie!
And now I’m going to go spend some time reading this MSDN page on Intrinsic Member Properties.