MDX-How can I get attribute key with MDX

MDX-How can I get attribute key with MDX

 

Written by Ramunas Balukonis   

Monday, 11 June 2007 06:38

Q: How can I get attribute key with MDX

A:

To do so, use Member_Key function:

WITH
MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_Key
SELECT {Measures.ProductKey} ON axis(0),
[Product].[Product Categories].Members on axis(1)
FROM [Adventure Works]

Added May 19, 2009 by Vidas Matelis.

You can also get the same results by using Properties("Key") function:

WITH
MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Properties("Key")
SELECT {Measures.ProductKey} ON axis(0),
[Product].[Product Categories].Members on axis(1)
FROM [Adventure Works]

For coposite keys Member_Key and Properties("Key") will return NULL values. You should use Properties("Key0"), Properties("Key1"), etc to get composit keys:

WITH
MEMBER Measures.StateProvinceKey0 AS [Customer].[State-Province].CurrentMember.Properties("KEY0")
MEMBER Measures.StateProvinceKey1 AS [Customer].[State-Province].CurrentMember.Properties("KEY1")

SELECT {Measures.StateProvinceKey0, Measures.StateProvinceKey1} ON axis(0),
[Customer].[State-Province].Members on axis(1)
FROM [Adventure Works]

Here are results for the last query:

 

State-Province	StateProvinceKey0	StateProvinceKey1
All Customers	0	#Error
Alabama	AL	US
Alberta	AB	CA
Arizona	AZ	US
Bayern	BY	DE
Brandenburg	BB	DE
British Columbia	BC	CA
Brunswick	NB	CA
California	CA	US
Charente-Maritime	17	FR
Colorado	CO	US

 

Note: There is performance penalty when you convert dimension key to a measure