SSAS – Data Dictionary

Data dictionary is a common requirement in any Enterprise BI projects to understand the data models. Here planning to use few DMV at SSAS to get the required data definitions to include in Cube data dictionary.
Please consider, Here I used “Tabular Model 2012 ” and unfortunately we don’t have any description of every attribute properties so we expect an empty descriptions. In general, it is best practice to include the description of every attribute/column while developing Cubes.

Step 1: Retrieve all dimensions information which include the Description of every field

-- Dimensions details
SELECT  [CUBE_NAME], 
		[CATALOG_NAME], 
		[DIMENSION_UNIQUE_NAME] AS Dim_Name, 
		LEVEL_NAME AS Attribute_Name, 
		[LEVEL_UNIQUE_NAME] AS Dimension_Attribute_Level, 
		[LEVEL_CAPTION], 
		[DESCRIPTION] AS [Description]
FROM $SYSTEM.MDSCHEMA_LEVELS
WHERE [LEVEL_NUMBER]>0
AND   [LEVEL_IS_VISIBLE]
AND   [CUBE_NAME]= 'Internet Operation'
AND   [CATALOG_NAME] = 'AdventureWorks Tabular Model SQL 2012'

Step 2: Retrieve all measures information which include the Description as well as any calculated formulae of every field

-- Measures details

SELECT  [CUBE_NAME],
		[CATALOG_NAME],
		[MEASURE_UNIQUE_NAME],
		[MEASURE_NAME],
		[MEASURE_UNIQUE_NAME],
		[MEASURE_CAPTION],
		[DESCRIPTION] + [EXPRESSION] AS [Description]
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE [CUBE_NAME]= 'Internet Operation'
AND   [CATALOG_NAME] = 'AdventureWorks Tabular Model SQL 2012'

Step 3: Retrieve all dimensions hierarchy information which include the Description of every field

-- Hierarchy details
SELECT  [CUBE_NAME],
		[CATALOG_NAME],
		[DIMENSION_UNIQUE_NAME] AS Dim_Name,
		[HIERARCHY_NAME],
		[HIERARCHY_UNIQUE_NAME],
		[HIERARCHY_CAPTION],
		[DESCRIPTION] AS [Description]
FROM $system.MDSchema_hierarchies
WHERE CUBE_NAME  ='Internet Operation'
AND HIERARCHY_ORIGIN = 1
AND   [CATALOG_NAME] = 'AdventureWorks Tabular Model SQL 2012'
ORDER BY [DIMENSION_UNIQUE_NAME]

Points to consider:

1. We don’t have any defined descriptions in the attributes/columns properties so we have empty results in the descriptions
2. Haven’t formatted the above results to a single dictionary
3. It could also possible to create data dictionary as a report to share with end-users
4. We could also use asstoredprocedures to get any additional properties
5. Here is an interesting discussion regarding few data dictionary requirements