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\u00a0the data set format, it is very easy to build Reporting Services reports to generate documentation about your database.<\/p>\n
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:<\/p>\n
EXEC master.dbo.sp_addlinkedserver
\n\u00a0\u00a0 @server = N’SSAS_Reporter’
\n\u00a0, @srvproduct=N’MSOLAP’
\n\u00a0, @provider=N’MSOLAP’
\n\u00a0, @datasrc=N’VIDAS-LENOVO’\u00a0 \/*\u00a0<<< My\u00a0Analysis Services server name *\/
\n\u00a0, @catalog=N’Adventure Works DW 2008′\u00a0\/* <<< My Analysis Services database name *\/
\n\u00a0go
\n\u00a0
\n— Setup security as per your environment requirements.
\nEXEC master.dbo.sp_addlinkedsrvlogin
\n\u00a0\u00a0 @rmtsrvname=N’SSAS_Reporter’
\n\u00a0, @useself=N’False’
\n\u00a0, @locallogin=NULL
\n\u00a0, @rmtuser=NULL
\n\u00a0, @rmtpassword=NULL
\ngo<\/p><\/blockquote>\nFor 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.<\/p>\n
In SQL Server 2008 I created Reporting Services solution.\u00a0In this solution I created one shared data source that was pointing to the same SQL server where\u00a0I previously created linked server.\u00a0I used tempdb database, but that does not mater, as all\u00a0queries for my reporting\u00a0are based on\u00a0the linked server. Then I created set of repors that were showing data from the SSAS MDVs:<\/p>\n
\n
- Cube List.rdl – list of cubes and perspectives in one Analysis Services 2008 database.<\/li>\n
- Cube Measures.rdl – information about measure groups and measures in each cube\/perspective in one database.<\/li>\n
- Dimension List.rdl – list of dimensions in one database.<\/li>\n
- Dimension Details.rdl – information about dimension attributes and hierarchies in one database.<\/li>\n
- KPIs.rdl – list of KPIs in the cubes<\/li>\n
- Measure Group Dimensions.rdl – list of dimensions for each measure group.<\/li>\n
- SSAS DB Structure.rdl – Main report that includes other reports as sub-reports.<\/li>\n<\/ul>\n
You can download SSRS solution with all reports here<\/a>.\u00a0\u00a0Latest solution with these reports will be always\u00a0 published here: http:\/\/www.ssas-info.com\/analysis-services-tools\/1365-ssrs-reports-to-document-ssas-2008-db-structure-using-dmvs<\/a><\/p>\n
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.<\/p>\n
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\u00a0SQL Server will convert your text field to varchar(30) type and your joins in certain cases will be incorrect.<\/p>\n
Below I described each report and provided queries I used to get data from SSAS DMV.<\/p>\n
Cube List.rdl<\/strong><\/p>\n
This report shows a list of cubes and perspectives in one Analysis Services 2008 database.<\/p>\n
Here is the query that was used for this report:<\/p>\n
SELECT CONVERT(varchar, CASE WHEN BASE_CUBE_NAME IS NULL THEN CUBE_NAME ELSE BASE_CUBE_NAME END) AS BASE_CUBE_NAME_To_Order
\n\u00a0, CASE\u00a0WHEN BASE_CUBE_NAME IS NULL THEN CONVERT(varchar, CUBE_NAME)
\n\u00a0\u00a0\u00a0ELSE ‘\u00a0 ‘ + CONVERT(varchar, CUBE_NAME)
\n\u00a0\u00a0 END AS CUBE_NAME_TO_PRINT
\n\u00a0, CASE WHEN BASE_CUBE_NAME IS NULL THEN ‘Cube’ ELSE ‘Perspective’ END AS CubeType
\n\u00a0 , *\u00a0\u00a0
\n\u00a0FROM OPENQUERY(SSAS_Reporter
\n\u00a0, ‘SELECT * FROM $system.MDSCHEMA_CUBES WHERE Cube_Source = 1’
\n\u00a0)
\nORDER BY 1<\/p><\/blockquote>\nHere is\u00a0the pdf file<\/a> generated by this report from the Adventure Works database.<\/p>\n
Cube Measures.rdl<\/strong><\/p>\n
This report shows information about measure groups and measures in each cube\/perspective in one database.<\/p>\n
Here is the query that was used for this report:<\/p>\n
SELECT
\n\u00a0\u00a0 C.[CATALOG_NAME], C.CUBE_NAME
\n\u00a0, C.IS_WRITE_ENABLED, C.BASE_CUBE_NAME
\n\u00a0, CASE WHEN BASE_CUBE_NAME IS NULL THEN ‘Cube’ ELSE ‘Perspective’ END AS CubeType<\/p>\n\u00a0— Measure fields
\n\u00a0, CONVERT(varchar(500), M.MEASUREGROUP_NAME) AS MEASUREGROUP_NAME
\n\u00a0, CONVERT(varchar(500), M.[MEASURE_NAME]) AS MEASURE_NAME
\n\u00a0, CASE M.[MEASURE_AGGREGATOR]
\n\u00a0\u00a0WHEN 1 THEN ‘Sum’\u00a0WHEN 2 THEN ‘Count’\u00a0WHEN 3 THEN ‘Min’
\n\u00a0\u00a0WHEN 4 THEN ‘Max’\u00a0WHEN 8 THEN ‘Distinct Count’ WHEN 9 THEN ‘None’\u00a0
\n\u00a0\u00a0WHEN 10 THEN ‘AverageOfChildren’ WHEN 11 THEN ‘FirstChild’
\n\u00a0\u00a0WHEN 12 THEN ‘LastChild’\u00a0WHEN 13 THEN ‘FirstNonEmpty’
\n\u00a0\u00a0WHEN 14 THEN ‘LastNonEmpty’\u00a0WHEN 15 THEN ‘ByAccount’
\n\u00a0\u00a0WHEN 127 THEN ‘Calculated measure’
\n\u00a0\u00a0ELSE ‘N\/A’
\n\u00a0\u00a0 END AS AggregateFunction
\n\u00a0, D.[TYPE_NAME] AS DATA_TYPE_NAME
\n\u00a0, D.[COLUMN_SIZE]
\n\u00a0, M.[NUMERIC_PRECISION]
\n\u00a0, M.[NUMERIC_SCALE]
\n\u00a0, M.[EXPRESSION]
\n\u00a0, M.[MEASURE_IS_VISIBLE]
\n\u00a0, M.[DEFAULT_FORMAT_STRING]
\n\u00a0
\n\u00a0FROM OPENQUERY(SSAS_Reporter, ‘SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES’
\n\u00a0\u00a0) M
\n\u00a0JOIN OPENQUERY(SSAS_Reporter,
\n\u00a0 ‘ SELECT DATA_TYPE, TYPE_NAME, COLUMN_SIZE, IS_FIXEDLENGTH
\n\u00a0\u00a0 FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES — Data types here
\n\u00a0‘
\n\u00a0) D ON D.DATA_TYPE = M.DATA_TYPE
\n\u00a0JOIN OPENQUERY(SSAS_Reporter,
\n‘
\nSELECT [CATALOG_NAME], CUBE_NAME
\n\u00a0, IS_WRITE_ENABLED, BASE_CUBE_NAME
\n\u00a0 FROM $SYSTEM.MDSCHEMA_CUBES
\n\u00a0WHERE LEFT(CUBE_NAME,1) <> ”$”
\n\u00a0 ‘) C ON CONVERT(varchar(500), C.CUBE_NAME) = CONVERT(varchar(500), M.CUBE_NAME)
\n\u00a0
\nWHERE M.[MEASURE_AGGREGATOR] <> 127
\nORDER BY CONVERT(varchar(500), C.CUBE_NAME)
\n\u00a0, CONVERT(varchar(500), M.MEASUREGROUP_NAME)
\n\u00a0, CONVERT(varchar(500), M.[MEASURE_NAME])<\/p><\/blockquote>\nHere is\u00a0the\u00a0pdf file<\/a> generated by this report from the Adventure Works database.<\/p>\n
Dimension List.rdl<\/strong><\/p>\n
This report shows a list of dimensions in one database.<\/p>\n
Here is the query that was used for this report:<\/p>\n
SELECT CONVERT(varchar, DIMENSION_NAME) AS Dimension
\n\u00a0, *\u00a0\u00a0
\n\u00a0, CASE DIMENSION_TYPE
\n\u00a0\u00a0WHEN 0 THEN ‘Unknown’\u00a0WHEN 1 THEN ‘Time’\u00a0\u00a0\u00a0WHEN 2 THEN ‘Measure’
\n\u00a0\u00a0WHEN 3 THEN ‘Other’\u00a0\u00a0WHEN 5 THEN ‘Quantitative’\u00a0WHEN 6 THEN ‘Accounts’
\n\u00a0\u00a0WHEN 7 THEN ‘Customers’\u00a0WHEN 8 THEN ‘Products’\u00a0\u00a0WHEN 9 THEN ‘Scenario’
\n\u00a0\u00a0WHEN 10 THEN ‘Utility’\u00a0WHEN 11 THEN ‘Currency’\u00a0\u00a0WHEN 12 THEN ‘Rates’
\n\u00a0\u00a0WHEN 13 THEN ‘Channle’\u00a0WHEN 14 THEN ‘Promotion’\u00a0WHEN 15 THEN ‘Organization’
\n\u00a0\u00a0WHEN 16 THEN ‘Bill of materials’\u00a0WHEN 17 THEN ‘Geography’
\n\u00a0\u00a0ELSE CONVERT(varchar, DIMENSION_TYPE)
\n\u00a0\u00a0 END AS DimensionType\u00a0<\/p>\n\u00a0FROM OPENQUERY(SSAS_Reporter
\n\u00a0, ‘
\n\u00a0SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
\n\u00a0 WHERE LEFT(CUBE_NAME,1) = ”$”
\n\u00a0\u00a0\u00a0 AND DIMENSION_NAME <> ”Measures”
\n\u00a0‘
\n\u00a0)
\nORDER BY 1<\/p><\/blockquote>\nHere is the pdf file<\/a> generated by this report from the Adventure Works database.<\/p>\n
Dimension Details.rdl<\/strong><\/p>\n
This report shows information about dimension attributes and hierarchies in one database.<\/p>\n
Here is the query that was used for this report:<\/p>\n
SELECT CONVERT(varchar, D.DIMENSION_NAME) AS DIMENSION_NAME
\n\u00a0, D.[CATALOG_NAME]
\n\u00a0, CASE D.DIMENSION_TYPE
\n\u00a0\u00a0WHEN 0 THEN ‘Unknown’\u00a0WHEN 1 THEN ‘Time’\u00a0\u00a0\u00a0WHEN 2 THEN ‘Measure’
\n\u00a0\u00a0WHEN 3 THEN ‘Other’\u00a0\u00a0WHEN 5 THEN ‘Quantitative’\u00a0WHEN 6 THEN ‘Accounts’
\n\u00a0\u00a0WHEN 7 THEN ‘Customers’\u00a0WHEN 8 THEN ‘Products’\u00a0\u00a0WHEN 9 THEN ‘Scenario’
\n\u00a0\u00a0WHEN 10 THEN ‘Utility’\u00a0WHEN 11 THEN ‘Currency’\u00a0\u00a0WHEN 12 THEN ‘Rates’
\n\u00a0\u00a0WHEN 13 THEN ‘Channel’\u00a0WHEN 14 THEN ‘Promotion’\u00a0WHEN 15 THEN ‘Organization’
\n\u00a0\u00a0WHEN 16 THEN ‘Bill of materials’\u00a0WHEN 17 THEN ‘Geography’
\n\u00a0\u00a0ELSE CONVERT(varchar, D.DIMENSION_TYPE)
\n\u00a0\u00a0 END AS DimensionType\u00a0<\/p>\n\u00a0, D.DIMENSION_CARDINALITY\u00a0, D.DimensionDescription
\n\u00a0, D.IS_READWRITE AS DimensionIsReadWrite<\/p>\n\u00a0— Hierarchy fields
\n\u00a0, H.HIERARCHY_NAME, H.HIERARCHY_CAPTION, H.DIMENSION_TYPE, H.HIERARCHY_CARDINALITY, H.DEFAULT_MEMBER
\n\u00a0, H.ALL_MEMBER, H.[DESCRIPTION] AS HierarchyDescription, H.Structure, H.IS_VIRTUAL
\n\u00a0, H.IS_READWRITE, H.DIMENSION_UNIQUE_SETTINGS, H.DIMENSION_MASTER_UNIQUE_NAME, H.DIMENSION_IS_VISIBLE
\n\u00a0, H.HIERARCHY_ORDINAL
\n\u00a0, H.DIMENSION_IS_SHARED — Always true
\n\u00a0, H.HIERARCHY_IS_VISIBLE
\n\u00a0, H.HIERARCHY_ORIGIN
\n\u00a0, H.HIERARCHY_DISPLAY_FOLDER
\n\u00a0, H.INSTANCE_SELECTION
\n\u00a0, H.GROUPING_BEHAVIOR
\n\u00a0, CONVERT(bit,CASE WHEN CONVERT(int, H.HIERARCHY_ORIGIN) & 1 = 1 THEN 1 ELSE 0 END) AS UserDefined
\n\u00a0, CONVERT(bit,CASE WHEN CONVERT(int, H.HIERARCHY_ORIGIN) & 2 = 2 THEN 1 ELSE 0 END) AS SystemEnabled
\n\u00a0, CONVERT(bit,CASE WHEN CONVERT(int, H.HIERARCHY_ORIGIN) & 4 = 4 THEN 1 ELSE 0 END) AS SystemInternal
\n\u00a0, CONVERT(bit,CASE WHEN CONVERT(int, H.HIERARCHY_ORIGIN) & 3 = 3 THEN 1 ELSE 0 END) AS ParentChild<\/p>\n\u00a0— Level Fields
\n\u00a0, L.DIMENSION_UNIQUE_NAME, L.HIERARCHY_UNIQUE_NAME
\n\u00a0, L.LEVEL_NAME, L.LEVEL_UNIQUE_NAME, L.LEVEL_CAPTION, L.LEVEL_NUMBER, L.LEVEL_CARDINALITY
\n\u00a0, L.LEVEL_TYPE, L.[DESCRIPTION] AS LevelDescription, L.CUSTOM_ROLLUP_SETTINGS, L.LEVEL_UNIQUE_SETTINGS
\n\u00a0, L.LEVEL_IS_VISIBLE, L.LEVEL_ORDERING_PROPERTY, L.LEVEL_DBTYPE, L.LEVEL_MASTER_UNIQUE_NAME
\n\u00a0, L.LEVEL_NAME_SQL_COLUMN_NAME, L.LEVEL_KEY_SQL_COLUMN_NAME, L.LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME
\n\u00a0, L.LEVEL_ATTRIBUTE_HIERARCHY_NAME, L.LEVEL_KEY_CARDINALITY, L.LEVEL_ORIGIN
\n\u00a0, CONVERT(bit, CASE WHEN CONVERT(int, L.LEVEL_ORIGIN) & 1 = 1 THEN 1 ELSE 0 END) AS UserDefinedHierarchy
\n\u00a0, CONVERT(bit, CASE WHEN CONVERT(int, L.LEVEL_ORIGIN) & 2 = 2 THEN 1 ELSE 0 END) AS AttributeHierarchy
\n\u00a0, CONVERT(bit, CASE WHEN CONVERT(int, L.LEVEL_ORIGIN) & 4 = 4 THEN 1 ELSE 0 END) AS KeyAttributeHierarchy
\n\u00a0, CONVERT(bit, CASE WHEN CONVERT(int, L.LEVEL_ORIGIN) & 4 = 8 THEN 1 ELSE 0 END) AS NotEnabledLevelsInHierarchy
\n\u00a0FROM OPENQUERY(SSAS_Reporter
\n\u00a0, ‘
\n\u00a0SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS
\n\u00a0WHERE LEFT(CUBE_NAME,1) = ”$”
\n\u00a0\u00a0 AND [DIMENSION_UNIQUE_NAME] <> ”[Measures]”
\n\u00a0\u00a0\u00a0 ‘
\n\u00a0) L
\n\u00a0JOIN OPENQUERY(SSAS_Reporter
\n\u00a0, ‘
\nSELECT * FROM $SYSTEM.MDSCHEMA_HIERARCHIES
\n\u00a0WHERE LEFT(CUBE_NAME,1) = ”$”
\n\u00a0\u00a0 AND [DIMENSION_UNIQUE_NAME] <> ”[Measures]”
\n‘) H
\n\u00a0ON CONVERT(varchar(500), H.[HIERARCHY_UNIQUE_NAME]) = CONVERT(varchar(500), L.[HIERARCHY_UNIQUE_NAME])
\n\u00a0
\n\u00a0JOIN OPENQUERY(SSAS_Reporter
\n\u00a0, ‘
\n\u00a0SELECT [CATALOG_NAME], CUBE_NAME, DIMENSION_NAME
\n\u00a0, DIMENSION_TYPE, DIMENSION_CARDINALITY
\n\u00a0, [DESCRIPTION] AS DimensionDescription
\n\u00a0, IS_READWRITE
\n\u00a0 FROM $SYSTEM.MDSCHEMA_DIMENSIONS
\n\u00a0 WHERE LEFT(CUBE_NAME, 1) = ”$”
\n\u00a0\u00a0\u00a0 AND DIMENSION_NAME <> ”Measures”
\n\u00a0\u00a0\u00a0 ‘) D ON CONVERT(varchar(500), D.CUBE_NAME) = CONVERT(varchar(500), H.CUBE_NAME)
\nWHERE CONVERT(varchar, L.LEVEL_NAME) <> ‘(All)’ — Exclude ALL level, need to find a better way to do that
\nORDER BY H.HIERARCHY_ORDINAL, L.LEVEL_NUMBER<\/p><\/blockquote>\nHere is the pdf file<\/a> generated by this report from the Adventure Works database.<\/p>\n
KPIs.rdl<\/strong><\/p>\n
This reports shows a list of KPIs in the cubes.<\/p>\n
Here is the query that was used for this report:<\/p>\n
SELECT K.*
\n\u00a0FROM OPENQUERY(SSAS_Reporter
\n\u00a0, ‘SELECT *
\n\u00a0\u00a0\u00a0 FROM $SYSTEM.MDSCHEMA_KPIS
\n\u00a0‘) K
\n\u00a0JOIN
\n\u00a0\u00a0 OPENQUERY(SSAS_Reporter
\n\u00a0, ‘SELECT *
\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM $SYSTEM.MDSCHEMA_CUBES
\n\u00a0\u00a0\u00a0\u00a0 WHERE LEFT(CUBE_NAME,1) <> ”$”
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND LEN(BASE_CUBE_NAME) = 0
\n\u00a0‘) C ON CONVERT(varchar(500), C.CUBE_NAME) = CONVERT(varchar(500), K.CUBE_NAME)<\/p><\/blockquote>\nHere is the pdf file<\/a> generated by this report from the Adventure Works database.<\/p>\n
Measure Group Dimensions.rdl<\/strong><\/p>\n
This reports shows a list of dimensions for each measure group.<\/p>\n
Here is the query that was used for this report:<\/p>\n
SELECT\u00a0 MD.*
\n\u00a0, D.DIMENSION_NAME
\n\u00a0FROM OPENQUERY(SSAS_Reporter
\n\u00a0, ‘
\nSELECT CUBE_NAME, MEASUREGROUP_NAME, MEASUREGROUP_CARDINALITY
\n\u00a0, [DIMENSION_UNIQUE_NAME], DIMENSION_CARDINALITY
\n\u00a0, DIMENSION_IS_VISIBLE
\n\u00a0, DIMENSION_IS_FACT_DIMENSION, DIMENSION_GRANULARITY
\n\u00a0 FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
\nWHERE LEFT(CUBE_NAME, 1) <> ”$”
\n\u00a0‘
\n\u00a0) MD
\n\u00a0JOIN OPENQUERY(SSAS_Reporter
\n\u00a0, ‘
\nSELECT CUBE_NAME
\n\u00a0 FROM $SYSTEM.MDSCHEMA_CUBES
\n\u00a0WHERE LEFT(CUBE_NAME,1) <> ”$”
\n\u00a0\u00a0\u00a0 AND LEN(BASE_CUBE_NAME) = 0
\n‘) C
\n\u00a0ON CONVERT(varchar(500), C.[CUBE_NAME]) = CONVERT(varchar(500), MD.[CUBE_NAME])
\n\u00a0
\n\u00a0JOIN OPENQUERY(SSAS_Reporter
\n, ‘
\nSELECT [DIMENSION_UNIQUE_NAME], DIMENSION_NAME
\n\u00a0 FROM $SYSTEM.MDSCHEMA_DIMENSIONS
\n\u00a0WHERE LEFT(CUBE_NAME, 1) = ”$”
\n‘) D
\n\u00a0ON CONVERT(varchar(500), D.[DIMENSION_UNIQUE_NAME]) = CONVERT(varchar(500), MD.[DIMENSION_UNIQUE_NAME])
\n\u00a0
\nORDER BY CONVERT(varchar(500), MD.CUBE_NAME)
\n\u00a0, CONVERT(varchar(500), MD.MEASUREGROUP_NAME)
\n\u00a0, CONVERT(varchar(500), D.[DIMENSION_NAME])<\/p><\/blockquote>\nHere is the pdf file<\/a> generated by this report from the Adventure Works database.<\/p>\n