About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

MVP Logo
Pyramid Analytics

Search

Pyramid Analytics
blank
Report Portal
blank

Using SSRS to report SSAS 2008 database structure using DMVs

March 1st, 2009 by Vidas Matelis

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.

Posted in SSAS 2008 - Katmai | 19 Comments »

19 Responses

  1. Raimana Says:

    Thank you,
    I’ve been looking for a way to document Analysis Services cubes for a couple of months.
    Great job!

  2. Frank Says:

    I keep getting a DMX error when I run this

    select * FROM OPENQUERY(SSAS_Reporter
    , ‘ SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
    WHERE DIMENSION_NAME “Measures” ‘
    )
    do you have any thoughts as to why it is throwing a DMX error?
    OLE DB provider “MSOLAP” for linked server “SSAS_Reporter” returned message “Error (Data mining): An unknown DMX expression was encountered at line 2, column 27.”.

    Great tool btw!!

  3. Vidas Matelis Says:

    Frank,

    I believe your query is missing = before measures:
    select * FROM OPENQUERY(SSAS_Reporter
    , ‘ SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
    WHERE DIMENSION_NAME = “Measures” ‘
    )
    Also, just in case – this works just on SSAS 2008.

  4. Frank Says:

    sorry, I missed that when I pasted the query in there. I actually do have the = sign in the query I am running, but I am still getting the same error

    select * FROM OPENQUERY(SSAS_Reporter
    , ‘SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
    WHERE DIMENSION_NAME = “Measures” ‘
    )

  5. Vidas Matelis Says:

    Frank,

    Try to execute this DMV query in SSMS (connect to SSAS, new DMX query) and see if you will get any errors:

    SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
    WHERE DIMENSION_NAME = “Measures”

    Make sure you replace double quotes from this website and retype them yourself. My website replace double quotes with special quote symbols that will generate errors in SQL and SSAS.
    If you have no problems executing that query in SSMS (connected to SSAS), that means problem is with linked server. Maybe you should specify MSOLAP.3 and not MSOLAP.
    Is there anything unique to your environment? Maybe servers are case sensitive?

  6. Frank Says:

    it does work but only after I replace the double quotes with single quotes. Maybe some setting to change?

  7. Vidas Matelis Says:

    Frank,

    In such case for “Measures” replace double quotes with 2 single quotes on each side. Then you should be OK. Not sure why there is an issue with double quotes…

  8. Vidas Matelis Says:

    Frank,

    I just reviewed my source code and noticed that I do use everywhere single quotes. Just by blogging software replaces them and it is hard to recognize after that if it is double quote or single quote. Sorry about this confusion… So I would suggest to download my source code and get sample scripts from there.

  9. JB Says:

    Is this available for 2005 at all?

    We can’t upgrade yet as we are still re-developing our 2000 cubes.

  10. Vidas Matelis Says:

    No, DMVs are available just in SQL Server 2008.

  11. SSAS DMV: Join using DataSet « Data Warehousing and Business Intelligence Says:

    [...] Matelis wrote about the Linked Server. Basically it’s using OpenQuery. The example that Midas wrote is using [...]

  12. SSAS DMV (Dynamic Management View) « Data Warehousing and Business Intelligence Says:

    [...] Create a linked server from a SQL Server to the SSAS server, then use OpenQuery. See Midas Matelis post here. [...]

  13. BH Says:

    After deleting the AdventureWorks data files from the solution and attempting to connect to one of my cubes, I found this did not return anything. I chose a specific cube database in the linked server, but all the cubes on the server showed up under the linked server anyway, so how does the report query choose a specific cube from the list? Also, under each cube in the linked server – Tables > System Tables has nothing under it. Should there be something there for this to work? I am using SQL Server 2008 v. 10.0.2723 on Windows Server 2003 SP2 (VM server) and am a sysadmin on the box. Any suggestions you have would be appreciated as the reports look very good and we would like to see how they look on one of our cubes.

  14. Vidas Matelis Says:

    BH,

    Cube name is not a part of connection string. You can apply filters to queries to return just data relevant to specific cube.
    If you have any problems with queries – first step for you would be to run DMV queries directly in SSAS database. Just start SSMS, connect to SSAS and choose to “create new MDX query”. Then enter your DMV query and execute it.

  15. Ben Says:

    (apologies for asking in 2 different places, looks like this is the more appropriate place to ask my question)

    I am interested in using DMVs to surface measure and dimension documentation to the user and was looking at your SSRS solution’s Dimension Detail report where you join Dimension, Level and Hierarchy, however I notice that it returns the dimensions in the database and not in the cube. This means that we do not see role-playing dimensions or the names of the dimensions in the cube (which may be different than those in the database).

    If I replace your where clause WHERE LEFT(CUBE_NAME, 1) = ”$” with one that specifies a cube (or perspective)
    WHERE CUBE_NAME = ”myCube”, I get the cube dimensions but it also is a cross-join of all attributes for each hierarchy and dimension (every attribute is cross joined to every dimension)…not what I expected.

    Any idea why?
    What is the purpose of the $?

    Thanks…

    Ben

  16. Vidas Matelis Says:

    Hi Ben,

    Clause: “WHERE LEFT(CUBE_NAME, 1) = ‘$’” is used to return just database level dimensions as all dimensions start with ‘$’ symbol. If you want to get a list of dimensions in one cube, you would have to use query is SSAS database that looks something like that:

    SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
    WHERE DIMENSION_NAME ‘Measures’
    AND CUBE_NAME = ‘MyCubeName’

    This query will return dimensions for selected cube and for role playing dimensions it will return dimension name in that cube. Also, you can use field DIMENSION_MASTER_NAME to get dimension name in the database.

    Hope this helps. Most of these queries I wrote just by trial/error method – I found very little info about SSAS DMVs…

  17. Anonymous Says:

    How can i see the KPI values from sql server 2008

  18. kumar Says:

    Hi Vidas,

    Great work… but could you please let me know how to get partitions and FactTables and Dimension tables used in the SSRS report? Is that possible?

    Could you please help me on this.

    Thanks,
    kumar

  19. Pascal Says:

    Hi,

    Great job thanks. You did avoid hours and hours doing it manually.

    On question. Is it possible to have also the Roles info of the cube?

    Regs,
    Pascal

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.

XL Cubed