PDA

View Full Version : Return parent from cubemember



Jesse
2011-03-30, 01:22 AM
Is there a way to return the parent from a cubemember?

Ie I have store locations that are referenced via:

=cubemember("Sales","[Region].[District].[Store].&[10]")

Which returns the store name for location number 10. I want to return the District name for store number 10.

Jesse

Will Riley
2011-03-30, 09:02 AM
Try this

=cubemember("Sales","[Region].[District].[Store].&[10].Parent")

The Parent function will (as it suggests) the parent of a specified member from the specified hierarchy, in your case Region.

Hope this helps.

Will

Jesse
2011-03-31, 07:48 AM
Awesome, thanks for the help Will.

Jesse

Jesse
2011-04-01, 02:05 AM
When I do this it returns "STORE" as opposed to the district name which is what I'm looking for.

Ken Puls
2011-04-01, 03:07 AM
I'm curious... my cube knowledge is pretty close to nothing, but what about...
=cubemember("Sales","[Region].[District].[Store].&[10].Parent.Parent")

or is there a property that you can tag with the parent, like as

=cubemember("Sales","[Region].[District].[Store].&[10].Parent.Parent.&[Name]")

Totally guessing, but that's what I'd try... Course I'd probably fail too, but...

Will Riley
2011-04-01, 09:14 AM
Hmmm, This could have something to do with the limited support for hierarchies in Powerpivot. The function syntax I gave you was based on my "upbringing" with MDX and SSAS cubes and is the correct way to navigate a dimension hierarchy in MDX. If we have the AdventureWorks SSAS cube, we can have =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3]") This gives us a value of "Central" Using the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent") We return "United States" which is the correct parent for "Central" (FYI Ken, the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent.Parent") will return "North America", the parent of "United States" ) If I get the time today, I'll explore this with Powerpivot (which is I assume what you are using?) - I remember some colleagues in the PPVT world commenting on a lack of support for hierarchies so this might be the sort of thing they were referring to! Cheers, Will

Will Riley
2011-04-01, 09:22 AM
Ken... not sure what went wrong with the above rendering... looked fine until I posted it but the formatting seems to get all screwed? Seemed to be working fine yesterday

Will Riley
2011-04-01, 09:23 AM
Ken... not sure what went wrong with the above rendering... looked fine until I posted it but the formatting seems to get all screwed? Seemed to be working fine yesterday

Edit: Yeah... now it never posts as per the WYSIWYG editor .... I tried FF4, IE8 & 9 and same results

Will Riley
2011-04-01, 09:27 AM
(Ken... this is using the standard text editor as opposed to WYSIWYG - maybe a bug?)

Hmmm,

This could have something to do with the limited support for hierarchies in Powerpivot.

The function syntax I gave you was based on my "upbringing" with MDX and SSAS cubes and is the correct way to navigate a dimension hierarchy in MDX.

If we have the AdventureWorks SSAS cube, we can have

=CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3]")

This gives us a value of "Central"

Using the following formula

=CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent")

We return "United States" which is the correct parent for "Central"

(FYI Ken, the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent.Parent") will return "North America", the parent of "United States" )

If I get the time today, I'll explore this with Powerpivot (which is I assume what you are using?) - I remember some colleagues in the PPVT world commenting on a lack of support for hierarchies so this might be the sort of thing they were referring to!

Cheers,

Will

Will Riley
2011-04-01, 10:11 AM
Well, I tried to do this in PowerPivot and it seems you can't (at least not using the Parent MDX function)

Reading around various posts on the interweb, it seems that there is no inherent hierarchy support in PPVT like there is in SSAS, so even though dragging related attributes from a PPVT table or related tables (Country, Region, City etc) does "appear" to give the same visual hierarchy effect, the fact is that under the covers, there is no hierarchy established between say Country & Region, it merely places them in the grid in a one-many left to right order so you get the same effect.

Therefore, the CUBEMEMBER function, has no idea that Country is a parent of City, as with Powerpivot, such a hierarchy is not supported.

Until Denali and the new BISM designer, where you will be able to author PPVT and SSAS from the same semantic model, I think you're stuffed. I'll keep playing, but i'm not sure you'll be able to do this easily with a single function, which is a real shame.

Ken Puls
2011-04-01, 06:53 PM
Ken... not sure what went wrong with the above rendering... looked fine until I posted it but the formatting seems to get all screwed? Seemed to be working fine yesterday

Not sure, Will. I haven't changed anything since I installed Tapatalk 3 days ago. The WebApp tags I posted about last night have actually been in place for over a week...

Appreciate your diligence in attempting to make it work. I'll have to look into this some more...

Jesse
2011-04-01, 08:17 PM
Thanks for looking Will. I'm not using PPVT, just the cube functions pulling from OLAP in Office 2007. Being able to return parent would remove the last vestige of static reference in my reporting other than the single set of location numbers.

Clearly some MDX education is in my future. Can you recommend any good online resources?

Jesse

Will Riley
2011-04-23, 12:41 AM
Thanks for looking Will. I'm not using PPVT, just the cube functions pulling from OLAP in Office 2007. Being able to return parent would remove the last vestige of static reference in my reporting other than the single set of location numbers.

Clearly some MDX education is in my future. Can you recommend any good online resources?

Jesse

I'm curious then - the code I gave you should have worked based on an SSAS cube (as per the AdventureWorks example I gave)

Good online resources - look up the following
Chris Webb's Blog
Mosha Paumansky's blog
BillPearson did a good MDX series on SQLServerCentral.com
SSAS-Info.com

And buy this book by George Spofford
http://www.amazon.co.uk/MDX-Solutions-Microsoft-Analysis-Services/dp/0471400467#reader_0471400467

Will