CODEWITHSUNDEEP
sqlsql-servert-sqlssas
mohan111
mohan111

Reputation: 8865

How to get Column Data types of particular table in SSAS using MDX

I have a query where I'm getting all tables and column names, but how to get Data Types and length values of columns of particular table?

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]

Upvotes: 1

Views: 2316

Answers (1)

SouravA
SouravA

Reputation: 5243

Your friend is $SYSTEM.MDSCHEMA_PROPERTIES.

The query would probably be somewhat like : 

select [HIERARCHY_UNIQUE_NAME], [LEVEL_UNIQUE_NAME], DATA_TYPE 
from $SYSTEM.MDSCHEMA_PROPERTIES 
where [Dimension_Unique_Name] = '[Accident Cause]'
and [Property_Name] = 'MEMBER_VALUE'
and [CUBE_NAME] = 'Adventure Works'

To understand what the values in column DATA_TYPE mean, refer this msdn link.

Upvotes: 1

Related Questions