I've faced such a problem:
I have a dimension [Project_sales] with one hierarchy [Default] in my cube [Sales_planning_RP].
I've created a custom property for this dimension and named it "Object". When I created it I was asked for:
1)Property Key
2)Property value
Now I Have Source-table for this dimension with 2 additional fields:
1) [Object_code] ex:('O01') <-This is what I need (see question below)
2) [Object_name] ex: ('Object # 3213, editable') <-This is What I get
But I see only one property in OLAP:
[Project_sales].[Default].CurrentMember.Properties("Object")
Which gives me just Object_name.
So my question is how can I get the key of my property "Object" with MDX?
My dimension properties: https://i.sstatic.net/N2Aej.png
My dimension has the following "parent_child" hierarchy:
Project->Object->Element_of_area
Every element of this hierarchy has as attribute "Object", which can be called in such a way:
[Project_sales].[Default].CurrentMember.Properties("Object")
In the properties window (in my picture) of the attribute "Object" there are 2 properties: 1)keyColumns 2)NameColumns
I repeat: "Object" here is not a member of dimension, it's an attribute! And it has its own key and name.
I can get its name, but can't understand how to get its key, which is also loaded into cube.
Look at my member_properties list: image_2
And here is a part of XMLA-code of my dimension to makes things clear:
<Attribute>
<Annotations>
<Annotation>
<Name>TypeOfInformation</Name>
<Value>1</Value>
</Annotation>
<Annotation>
<Name>TypeOfNameInformation</Name>
<Value>1</Value>
</Annotation>
<Annotation>
<Name>P4SSAMOVersion</Name>
<Value>2</Value>
</Annotation>
</Annotations>
<ID>Object</ID>
<Name>Object</Name>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>40</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>_x0036_Project_sales</TableID>
<ColumnID>Object_code</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>_x0036_Project_sales</TableID>
<ColumnID>Object_name</ColumnID>
</Source>
</NameColumn>
<OrderBy>Key</OrderBy>
<MembersWithData>NonLeafDataHidden</MembersWithData>
<AttributeHierarchyVisible>false</AttributeHierarchyVisible>
</Attribute>
Ok, now it's clear! There are 3 properties of an Attribute that may contain data: 1)Key 2)Name 3)Value
If Name is not empty and Key is not empty, you get Name when call .Properties() function. If Name is empty and Key is not empty, you get Key.
Here is the source: https://www.mssqltips.com/sqlservertip/3271/sql-server-analysis-server-ssas-keycolumn-vs-namecolumn-vs-valuecolumn/
