Vidas Matelis Analysis Services Blog

New SSAS 2008 DMVs allows you to easily access Microsoft SQL Server Analysis Services (SSAS) metadata – information about cubes, dimensions, measure groups, measures, etc. As with DMVs metadata information is returned in the data set format, it is very easy to build Reporting Services reports to generate documentation about your database.

You can execute SSAS DMV queries directly in the Analysis Services environment, but this approach has a lot of limitations – most importantly you can not do joins between DMVs. To go around this limitation, I created linked server from SQL Server to Analysis Services. This way I can do joins between DMVs. Here is the script that was used to create linked server:

EXEC master.dbo.sp_addlinkedserver
   @server = N’SSAS_Reporter’
 , @srvproduct=N’MSOLAP’
 , @provider=N’MSOLAP’
 , @datasrc=N’VIDAS-LENOVO’  /* <<< My Analysis Services server name */
 , @catalog=N’Adventure Works DW 2008′ /* <<< My Analysis Services database name */
 go
 
— Setup security as per your environment requirements.
EXEC master.dbo.sp_addlinkedsrvlogin
   @rmtsrvname=N’SSAS_Reporter’
 , @useself=N’False’
 , @locallogin=NULL
 , @rmtuser=NULL
 , @rmtpassword=NULL
go

For your environment you will need to make sure that linked server above points to the SSAS 2008 database that you would like to run this report on. When you want to do report on a different Analysis Services database, just change your linked server information to point to the different SSAS database and then re-run reports.

In SQL Server 2008 I created Reporting Services solution. In this solution I created one shared data source that was pointing to the same SQL server where I previously created linked server. I used tempdb database, but that does not mater, as all queries for my reporting are based on the linked server. Then I created set of repors that were showing data from the SSAS MDVs:

• Cube List.rdl – list of cubes and perspectives in one Analysis Services 2008 database.
• Cube Measures.rdl – information about measure groups and measures in each cube/perspective in one database.
• Dimension List.rdl – list of dimensions in one database.
• Dimension Details.rdl – information about dimension attributes and hierarchies in one database.
• KPIs.rdl – list of KPIs in the cubes
• Measure Group Dimensions.rdl – list of dimensions for each measure group.
• SSAS DB Structure.rdl – Main report that includes other reports as sub-reports.

You can download SSRS solution with all reports here.  Latest solution with these reports will be always  published here: http://www.ssas-info.com/analysis-services-tools/1365-ssrs-reports-to-document-ssas-2008-db-structure-using-dmvs

When I was building queries on DMVs to return data, I delibarately choose to return all available fields. This makes it easier to customize existing report – in the report designer you will see all available fields that can be included in the report.

When using linked server, textual data to SQL Server is returned with the type “text”. You cannot use that type of field in the joins or ordering – you have to convert it to varchar first. When converting to varchar, specify length of the fields, for example “CONVERT(varchar(500), YourFieldName)”. If you will use “CONVERT(varchar, YourFieldName), then SQL Server will convert your text field to varchar(30) type and your joins in certain cases will be incorrect.

Below I described each report and provided queries I used to get data from SSAS DMV.

Cube List.rdl

This report shows a list of cubes and perspectives in one Analysis Services 2008 database.

Here is the query that was used for this report:

SELECT CONVERT(varchar, CASE WHEN BASE_CUBE_NAME IS NULL THEN CUBE_NAME ELSE BASE_CUBE_NAME END) AS BASE_CUBE_NAME_To_Order
 , CASE WHEN BASE_CUBE_NAME IS NULL THEN CONVERT(varchar, CUBE_NAME)
   ELSE ‘  ‘ + CONVERT(varchar, CUBE_NAME)
   END AS CUBE_NAME_TO_PRINT
 , CASE WHEN BASE_CUBE_NAME IS NULL THEN ‘Cube’ ELSE ‘Perspective’ END AS CubeType
  , *  
 FROM OPENQUERY(SSAS_Reporter
 , ‘SELECT * FROM $system.MDSCHEMA_CUBES WHERE Cube_Source = 1’
 )
ORDER BY 1

Here is the pdf file generated by this report from the Adventure Works database.

Cube Measures.rdl

This report shows information about measure groups and measures in each cube/perspective in one database.

Here is the query that was used for this report:

SELECT
   C.[CATALOG_NAME], C.CUBE_NAME
 , C.IS_WRITE_ENABLED, C.BASE_CUBE_NAME
 , CASE WHEN BASE_CUBE_NAME IS NULL THEN ‘Cube’ ELSE ‘Perspective’ END AS CubeType

 — Measure fields
 , CONVERT(varchar(500), M.MEASUREGROUP_NAME) AS MEASUREGROUP_NAME
 , CONVERT(varchar(500), M.[MEASURE_NAME]) AS MEASURE_NAME
 , CASE M.[MEASURE_AGGREGATOR]
  WHEN 1 THEN ‘Sum’ WHEN 2 THEN ‘Count’ WHEN 3 THEN ‘Min’
  WHEN 4 THEN ‘Max’ WHEN 8 THEN ‘Distinct Count’ WHEN 9 THEN ‘None’ 
  WHEN 10 THEN ‘AverageOfChildren’ WHEN 11 THEN ‘FirstChild’
  WHEN 12 THEN ‘LastChild’ WHEN 13 THEN ‘FirstNonEmpty’
  WHEN 14 THEN ‘LastNonEmpty’ WHEN 15 THEN ‘ByAccount’
  WHEN 127 THEN ‘Calculated measure’
  ELSE ‘N/A’
   END AS AggregateFunction
 , D.[TYPE_NAME] AS DATA_TYPE_NAME
 , D.[COLUMN_SIZE]
 , M.[NUMERIC_PRECISION]
 , M.[NUMERIC_SCALE]
 , M.[EXPRESSION]
 , M.[MEASURE_IS_VISIBLE]
 , M.[DEFAULT_FORMAT_STRING]
 
 FROM OPENQUERY(SSAS_Reporter, ‘SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES’
  ) M
 JOIN OPENQUERY(SSAS_Reporter,
  ‘ SELECT DATA_TYPE, TYPE_NAME, COLUMN_SIZE, IS_FIXEDLENGTH
   FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES — Data types here
 ‘
 ) D ON D.DATA_TYPE = M.DATA_TYPE
 JOIN OPENQUERY(SSAS_Reporter,
‘
SELECT [CATALOG_NAME], CUBE_NAME
 , IS_WRITE_ENABLED, BASE_CUBE_NAME
  FROM $SYSTEM.MDSCHEMA_CUBES
 WHERE LEFT(CUBE_NAME,1) <> ”$”
  ‘) C ON CONVERT(varchar(500), C.CUBE_NAME) = CONVERT(varchar(500), M.CUBE_NAME)
 
WHERE M.[MEASURE_AGGREGATOR] <> 127
ORDER BY CONVERT(varchar(500), C.CUBE_NAME)
 , CONVERT(varchar(500), M.MEASUREGROUP_NAME)
 , CONVERT(varchar(500), M.[MEASURE_NAME])

Here is the pdf file generated by this report from the Adventure Works database.

Dimension List.rdl

This report shows a list of dimensions in one database.

Here is the query that was used for this report:

SELECT CONVERT(varchar, DIMENSION_NAME) AS Dimension
 , *  
 , CASE DIMENSION_TYPE
  WHEN 0 THEN ‘Unknown’ WHEN 1 THEN ‘Time’   WHEN 2 THEN ‘Measure’
  WHEN 3 THEN ‘Other’  WHEN 5 THEN ‘Quantitative’ WHEN 6 THEN ‘Accounts’
  WHEN 7 THEN ‘Customers’ WHEN 8 THEN ‘Products’  WHEN 9 THEN ‘Scenario’
  WHEN 10 THEN ‘Utility’ WHEN 11 THEN ‘Currency’  WHEN 12 THEN ‘Rates’
  WHEN 13 THEN ‘Channle’ WHEN 14 THEN ‘Promotion’ WHEN 15 THEN ‘Organization’
  WHEN 16 THEN ‘Bill of materials’ WHEN 17 THEN ‘Geography’
  ELSE CONVERT(varchar, DIMENSION_TYPE)
   END AS DimensionType 

 FROM OPENQUERY(SSAS_Reporter
 , ‘
 SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
  WHERE LEFT(CUBE_NAME,1) = ”$”
    AND DIMENSION_NAME <> ”Measures”
 ‘
 )
ORDER BY 1

Here is the pdf file generated by this report from the Adventure Works database.

Dimension Details.rdl

This report shows information about dimension attributes and hierarchies in one database.

Here is the query that was used for this report:

SELECT CONVERT(varchar, D.DIMENSION_NAME) AS DIMENSION_NAME
 , D.[CATALOG_NAME]
 , CASE D.DIMENSION_TYPE
  WHEN 0 THEN ‘Unknown’ WHEN 1 THEN ‘Time’   WHEN 2 THEN ‘Measure’
  WHEN 3 THEN ‘Other’  WHEN 5 THEN ‘Quantitative’ WHEN 6 THEN ‘Accounts’
  WHEN 7 THEN ‘Customers’ WHEN 8 THEN ‘Products’  WHEN 9 THEN ‘Scenario’
  WHEN 10 THEN ‘Utility’ WHEN 11 THEN ‘Currency’  WHEN 12 THEN ‘Rates’
  WHEN 13 THEN ‘Channel’ WHEN 14 THEN ‘Promotion’ WHEN 15 THEN ‘Organization’
  WHEN 16 THEN ‘Bill of materials’ WHEN 17 THEN ‘Geography’
  ELSE CONVERT(varchar, D.DIMENSION_TYPE)
   END AS DimensionType 

 , D.DIMENSION_CARDINALITY , D.DimensionDescription
 , D.IS_READWRITE AS DimensionIsReadWrite

 — Hierarchy fields
 , H.HIERARCHY_NAME, H.HIERARCHY_CAPTION, H.DIMENSION_TYPE, H.HIERARCHY_CARDINALITY, H.DEFAULT_MEMBER
 , H.ALL_MEMBER, H.[DESCRIPTION] AS HierarchyDescription, H.Structure, H.IS_VIRTUAL
 , H.IS_READWRITE, H.DIMENSION_UNIQUE_SETTINGS, H.DIMENSION_MASTER_UNIQUE_NAME, H.DIMENSION_IS_VISIBLE
 , H.HIERARCHY_ORDINAL
 , H.DIMENSION_IS_SHARED — Always true
 , H.HIERARCHY_IS_VISIBLE
 , H.HIERARCHY_ORIGIN
 , H.HIERARCHY_DISPLAY_FOLDER
 , H.INSTANCE_SELECTION
 , H.GROUPING_BEHAVIOR
 , CONVERT(bit,CASE WHEN CONVERT(int, H.HIERARCHY_ORIGIN) & 1 = 1 THEN 1 ELSE 0 END) AS UserDefined
 , CONVERT(bit,CASE WHEN CONVERT(int, H.HIERARCHY_ORIGIN) & 2 = 2 THEN 1 ELSE 0 END) AS SystemEnabled
 , CONVERT(bit,CASE WHEN CONVERT(int, H.HIERARCHY_ORIGIN) & 4 = 4 THEN 1 ELSE 0 END) AS SystemInternal
 , CONVERT(bit,CASE WHEN CONVERT(int, H.HIERARCHY_ORIGIN) & 3 = 3 THEN 1 ELSE 0 END) AS ParentChild

 — Level Fields
 , L.DIMENSION_UNIQUE_NAME, L.HIERARCHY_UNIQUE_NAME
 , L.LEVEL_NAME, L.LEVEL_UNIQUE_NAME, L.LEVEL_CAPTION, L.LEVEL_NUMBER, L.LEVEL_CARDINALITY
 , L.LEVEL_TYPE, L.[DESCRIPTION] AS LevelDescription, L.CUSTOM_ROLLUP_SETTINGS, L.LEVEL_UNIQUE_SETTINGS
 , L.LEVEL_IS_VISIBLE, L.LEVEL_ORDERING_PROPERTY, L.LEVEL_DBTYPE, L.LEVEL_MASTER_UNIQUE_NAME
 , L.LEVEL_NAME_SQL_COLUMN_NAME, L.LEVEL_KEY_SQL_COLUMN_NAME, L.LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME
 , L.LEVEL_ATTRIBUTE_HIERARCHY_NAME, L.LEVEL_KEY_CARDINALITY, L.LEVEL_ORIGIN
 , CONVERT(bit, CASE WHEN CONVERT(int, L.LEVEL_ORIGIN) & 1 = 1 THEN 1 ELSE 0 END) AS UserDefinedHierarchy
 , CONVERT(bit, CASE WHEN CONVERT(int, L.LEVEL_ORIGIN) & 2 = 2 THEN 1 ELSE 0 END) AS AttributeHierarchy
 , CONVERT(bit, CASE WHEN CONVERT(int, L.LEVEL_ORIGIN) & 4 = 4 THEN 1 ELSE 0 END) AS KeyAttributeHierarchy
 , CONVERT(bit, CASE WHEN CONVERT(int, L.LEVEL_ORIGIN) & 4 = 8 THEN 1 ELSE 0 END) AS NotEnabledLevelsInHierarchy
 FROM OPENQUERY(SSAS_Reporter
 , ‘
 SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS
 WHERE LEFT(CUBE_NAME,1) = ”$”
   AND [DIMENSION_UNIQUE_NAME] <> ”[Measures]”
    ‘
 ) L
 JOIN OPENQUERY(SSAS_Reporter
 , ‘
SELECT * FROM $SYSTEM.MDSCHEMA_HIERARCHIES
 WHERE LEFT(CUBE_NAME,1) = ”$”
   AND [DIMENSION_UNIQUE_NAME] <> ”[Measures]”
‘) H
 ON CONVERT(varchar(500), H.[HIERARCHY_UNIQUE_NAME]) = CONVERT(varchar(500), L.[HIERARCHY_UNIQUE_NAME])
 
 JOIN OPENQUERY(SSAS_Reporter
 , ‘
 SELECT [CATALOG_NAME], CUBE_NAME, DIMENSION_NAME
 , DIMENSION_TYPE, DIMENSION_CARDINALITY
 , [DESCRIPTION] AS DimensionDescription
 , IS_READWRITE
  FROM $SYSTEM.MDSCHEMA_DIMENSIONS
  WHERE LEFT(CUBE_NAME, 1) = ”$”
    AND DIMENSION_NAME <> ”Measures”
    ‘) D ON CONVERT(varchar(500), D.CUBE_NAME) = CONVERT(varchar(500), H.CUBE_NAME)
WHERE CONVERT(varchar, L.LEVEL_NAME) <> ‘(All)’ — Exclude ALL level, need to find a better way to do that
ORDER BY H.HIERARCHY_ORDINAL, L.LEVEL_NUMBER

Here is the pdf file generated by this report from the Adventure Works database.

KPIs.rdl

This reports shows a list of KPIs in the cubes.

Here is the query that was used for this report:

SELECT K.*
 FROM OPENQUERY(SSAS_Reporter
 , ‘SELECT *
    FROM $SYSTEM.MDSCHEMA_KPIS
 ‘) K
 JOIN
   OPENQUERY(SSAS_Reporter
 , ‘SELECT *
      FROM $SYSTEM.MDSCHEMA_CUBES
     WHERE LEFT(CUBE_NAME,1) <> ”$”
       AND LEN(BASE_CUBE_NAME) = 0
 ‘) C ON CONVERT(varchar(500), C.CUBE_NAME) = CONVERT(varchar(500), K.CUBE_NAME)

Here is the pdf file generated by this report from the Adventure Works database.

Measure Group Dimensions.rdl

This reports shows a list of dimensions for each measure group.

Here is the query that was used for this report:

SELECT  MD.*
 , D.DIMENSION_NAME
 FROM OPENQUERY(SSAS_Reporter
 , ‘
SELECT CUBE_NAME, MEASUREGROUP_NAME, MEASUREGROUP_CARDINALITY
 , [DIMENSION_UNIQUE_NAME], DIMENSION_CARDINALITY
 , DIMENSION_IS_VISIBLE
 , DIMENSION_IS_FACT_DIMENSION, DIMENSION_GRANULARITY
  FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE LEFT(CUBE_NAME, 1) <> ”$”
 ‘
 ) MD
 JOIN OPENQUERY(SSAS_Reporter
 , ‘
SELECT CUBE_NAME
  FROM $SYSTEM.MDSCHEMA_CUBES
 WHERE LEFT(CUBE_NAME,1) <> ”$”
    AND LEN(BASE_CUBE_NAME) = 0
‘) C
 ON CONVERT(varchar(500), C.[CUBE_NAME]) = CONVERT(varchar(500), MD.[CUBE_NAME])
 
 JOIN OPENQUERY(SSAS_Reporter
, ‘
SELECT [DIMENSION_UNIQUE_NAME], DIMENSION_NAME
  FROM $SYSTEM.MDSCHEMA_DIMENSIONS
 WHERE LEFT(CUBE_NAME, 1) = ”$”
‘) D
 ON CONVERT(varchar(500), D.[DIMENSION_UNIQUE_NAME]) = CONVERT(varchar(500), MD.[DIMENSION_UNIQUE_NAME])
 
ORDER BY CONVERT(varchar(500), MD.CUBE_NAME)
 , CONVERT(varchar(500), MD.MEASUREGROUP_NAME)
 , CONVERT(varchar(500), D.[DIMENSION_NAME])

Here is the pdf file generated by this report from the Adventure Works database.

SSAS DB Structure.rdl

This is main report that includes other reports as sub-reports.

Here is the pdf file generated by this report from the Adventure Works database.

Note: My blog software substitutes single and double quotes with symbols that look similar, but are not recognized by SQL Server. If you will copy any query from this post, you will have to replace quotes with proper symbols.