Home > SSAS > SSAS – Data Dictionary

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

Advertisements
Categories: SSAS Tags:
  1. bhavikmerchant
    April 11, 2013 at 9:54 pm

    Thanks Prav, this was useful for me as I needed it for a presentation 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: