Function CUBEMEMBERPROPERTY always return #N/A

[frederic]

Hi All,

I'm working on reports partially based on Excel cube formulas. I have no problem with all cube functions except CUBEMEMBERPROPERTY. This function always returns #N/A.

I have created a linked table in Excel imported in PowerPivot to explain you the trouble. It's french data, all fields name have the same meaning in English except Ville which means town.

Région Département Ville Population
PACA 13 MARSEILLE 1000000
PACA 83 TOULON 200000
PACA 83 NICE 150000
RHONE ALPES 69 LYON 1500000
RHONE ALPES 42 ST ETIENNE 250000
RHONE ALPES 38 GRENOBLE 300000
RHONE ALPES 1 BOURG 100000


If i understood well, this function gives the value of a property for a specific cube member. So in my case i'm trying to get the property value for property "Région" for the member of the cube ville (town) LYON. The name of the table in PowerPivot is Tableau1.

To get this, i wrote these formulas with absolutely no success :
=CUBEMEMBERPROPERTY("PowerPivot Data","[Tableau1].[Ville].[LYON]","Région")
=CUBEMEMBERPROPERTY("PowerPivot Data","[Tableau1].[Ville].[LYON]","[Tableau1].[Région]")
(i have translated the french formulas by remplacing ";" by "," and the function name PROPRIETEMEMBRECUBE).
In the same time the function =CUBEMEMBER("PowerPivot Data","[Tableau1].[Ville].[LYON]") is working fine and even CUBESET and CUBEVALUE or RANKEDCUBEMEMBER.

So i'm asking you're help for this function which is not very well documented in the web. I even wonder if i understood well the function, if i'm not totally wrong about it.

Regards.
PS : Excuse me for my poor English.

 

[frederic]

Nobody could help me ?

 

[RoryA]

I could be wrong but I don't believe PowerPivot supports creating member properties so CUBEMEMBERPROPERTY will always return #N/A

 

[frederic]

I could be wrong but I don't believe PowerPivot supports creating member properties so CUBEMEMBERPROPERTY will always return #N/A

But CUBEMEMBERPROPERTY is a cube function like CUBESET and it should work with PowerPivot Cube and Tables.
If i use CUBESET, another Cube function, it's work's so why i get #N/A with CUBEMEMBERPROPERTY ?

 

[RoryA]

Because that function relates to a particular feature of cubes that PP doesn't support.

 

[frederic]

Because that function relates to a particular feature of cubes that PP doesn't support.

In that video posted by a BI pro, this function seems to work with PowerPivot. It is used to get a name related to a code in a single table.
http://www.youtube.com/watch?<wbr>v=x9pcMa2GwJ8

Thank you

 

[RoryA]

I do not see PowerPivot in use in that video (judging by the tabs, it's not even loaded) - it's a straight data connection to an OLAP cube. (I admit I did not watch right to the end)

 

[frederic]

So RoryA you think that among the 7 cube functions in Excel, one is not working with PowerPivot ? I'll check this even if i have doubts.
I believed that data in PowerPivot are stocked and accessed as an OLAP Cube.
In the other side in all articles about cube functions there is nothing about CUBEMEMBERPROPERTY. Example : Cooking With CUBEs - Excel Do, Dynamic Does.

Thank you again.

 

[RoryA]

Have a look at slide 15 here: Microsoft SQL Server PowerPivot

 

[frederic]

Thank you, in slide 16 it said that this function is not relevant for PowerPivot.