A CUBEMEMBERPROPERTY Equivalent With PowerPivot

I was recently asked how to use the CUBEMEMBERPROPERTY function with a Power Pivot model, to get the Customer Name with a formula by using a unique code such as the Customer Code. I can’t say that I use this function much, but I thought I could recall using it at some point. However, a little testing made me wonder if I had ever used it as I could not get it to work. A quick Google suggested that other people had the same problem,  member properties cannot be defined in the model so maybe it can’t work.

I sat down and tried to figure out another way to emulate this, and this is what this post describes.

I am using the AdventureWorksDWR2008 database to demonstrate this technique, and in concert with the question originally asked I will get a value the Customer table such as email address or gender, given a known customer key. I have also added a customer column in that table with a simple formula of
=IF([MiddleName]=””,[FirstName]&” “&[LastName],[FirstName]&” “&[MiddleName]&” “&[LastName])

You are probably aware that you can easily get a CUBEMEMBER with a formula such as
=CUBEMEMBER(“PowerPivot Data”,”[ProductCategory].[Product Category Name].[Bikes]”)
This is a bit restrictive as you have to know the item to retrieve, it can be done more dynamically using CUBESET and CUBERANKEDMEMBER, but the point of showing this is to show that CUBEMEMBER will do what we want, if only we could put some logic in the expression that determines the member using another value held in a ell on the worksheet.

And we can. All (sic!) we have to do is included a bit more MDX, using the EXISTS function. The syntax of EXISTS on Technet is
Exists( Set_Expression1 , Set_Expression2 [, MeasureGroupName] )
and is described as …

Returning the set of tuples of the first set specified that exist with one or more tuples of the second set specified. If the optional <Measure Group Name> is provided, the function returns tuples that exist with one or more tuples from the second set and those tuples that have associated rows in the fact table of the specified measure group.

The MeasureGroupName would be useful if we were looking only for customers with sales, but we don’t care whether the customer has sales or not, so we can omit that optional parameter.

The first set expression will define the set we want returned, the second will define the variable condition that we are applying.

Assuming that we have our customer key in D17, we can use EXISTS within a CUBEMEMBER function to get the customer name,
=CUBEMEMBER(“PowerPivot Data”,”EXISTS([Customer].[Customer].Children,[Customer].[CustomerKey].[“&D17&”])”)
or the customer’s email address
=CUBEMEMBER(“PowerPivot Data”,”EXISTS([Customer].[EmailAddress].Children,[Customer].[CustomerKey].[“&D17&”])”)

To make it a little tidier, in case we have a wrong customer key, we can wrap it in IFERROR
=IFERROR(CUBEMEMBER(“PowerPivot Data”,”EXISTS([Customer].[Customer].Children,[Customer].[CustomerKey].[“&D17&”])”),””)

That is about it. The only thing to be aware of is that using .Children could result in multiple tuples being returned. In our example, it was safe to assume that we would only get one result as we were using the key to retrieve from the customer table,  but if the function could return multiple tuples, we have to use CUBESET and CUBERANKEDMEMBER to pick a value out of the set.

Advertisements
This entry was posted in CUBE Functions and tagged , , , , , , . Bookmark the permalink.

9 Responses to A CUBEMEMBERPROPERTY Equivalent With PowerPivot

  1. That’s really nifty Bob! Have you considered the tradeoffs against using a text measure to fetch these, in combo with CUBEVALUE? FIRSTNONBLANK(InsertMemberPropColumnNameHere, 1)? I used that technique in this post: http://www.powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/#more-8965 but obviously not with cube formulas.

    I’m asking at face value – as one who has NOT weighed the two techniques against each other. (I did not even know about the EXISTS statement). I have a built-in tendency toward text measures because they strike me as cool, but that’s about it 🙂

    • wessexbob says:

      I have to admit that I hadn’t even considered it Rob. I am aware of the text measure technique, and I agree that it is a nifty technique, but I have never used it in anger (have you?), but this is something I have used a few times.
      Maybe worth looking into further for a later post 🙂

  2. Michael says:

    If pulling a cubeset, can you use more than just two set expressions?

  3. Use cube formulae like Lookups to rerieve data from model into spreadsheet = Gold dust
    Many thanks!

  4. Lily says:

    I am having trouble using the except and descendants functions nested in a cubeset formula – I just can’t get it to work any help would be SO appreciated…..here is my formula
    =CUBESET($A$1,CONCATENATE(“except(descendants([companyalt].[lu – distribution (base line)],2),([companyalt].[coporate – liberty utilities – usd]))”),”States – Level 2 from LU Co”)

    Best regards,

    • wessexbob says:

      Lily,
      EXCEPT expects a set, so try using braces

      except(descendants([companyalt].[lu – distribution (base line)],2),{[companyalt].[coporate – liberty utilities – usd]})

  5. Imke Feldmann says:

    This is really useful – thanks a million!

    WARNING if you’re using localized excel-versions, where you would normally replace the separating commas by semikolons:
    Don’t replace them within the EXISTS-Statement, it will not work!

  6. Hi Bob, thanks a lot for this nice piece of information.

    I’d love to know if you could tell us how to use the formular if theres multiple tuples turning up. Fro example tell Excel to grab the first Email adress is there’s more then one.

    Thanks a lot!!

  7. Bob, great article. I have yet to really try the Exists part yet, but, do love to use the cube member formula (what I cannot get as a member quickly turns into a nice cubeset for filtering). Another I have found useful (since descendants is mentioned) is parent. I really find this useful when you have returned data and want to know where it is from (recently used it for fiscal periods where I needed to know exactly what fiscal year the date 1/1/2015 falls into (even tho it ended up being the parent of a parent of a parent of a parent, all wrapped up in one cubemember after another)) I sincerely hope we get to see more of these examples – for us who use them often (work GREAT with OLAP’s) we look forward to more insight!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s