DMV - Key column and Name column properties?

Question

I would expect the below DMV query columns  [ATTRIBUTE_NAME_SQL_COLUMN_NAME] and [ATTRIBUTE_KEY_SQL_COLUMN_NAME]  to give me actual Name column and Key columns from data source view. But it gives me values like Name([$Dimension Name].[Attribute Name]) and Key([$Dimension Name].[Attribute Name]).

SELECT [CATALOG_NAME] as [DATABASE], 
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION], 
LEVEL_CAPTION AS [ATTRIBUTE], 
[LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME], 
[LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME] 
FROM $system.MDSchema_levels 
WHERE CUBE_NAME  ='Adventure Works'
AND level_origin=2 
AND LEVEL_NAME <> '(All)'
Order by [DIMENSION_UNIQUE_NAME]

If this is how it is supposed to be, then is there a way where in I can get the name and key columns of the attributes in SSAS using DMV?

Answer 1

I am not aware of any DMV that would provide this information

instead you could use C# and AMO to get what you want
write your own Assembly that retruns a datatable with the desired results

gerhard

---

- www.pmOne.com -

Answer 2

Hi Gerhard, Yes you may be right. I don't think DMVs can extract DSV layer data. So AMO approach would be better.

In fact, i found one cool project in Codeplex which extracts and stores meta data info in database tables.

http://ssasamodb.codeplex.com/

Cheers !