Purple Frog Systems

Being a business intelligence consultant, I like to spend my time designing data warehouses, ETL scripts and OLAP cubes. An unfortunate consequence of this is having to write the documentation that goes with the fun techy work. So it got me thnking, is there a slightly more fun techy way of automating the documentation of OLAP cubes…

There are some good tools out there such as BI Documenter, but I wanted a way of having more control over the output, and also automating it further so that you don’t have to run an overnight build of the documentation.

I found a great article by Vincent Rainardi describing some DMVs (Dynamic Management Views) available in SQL 2008 which got me thinking, why not just build a number of SSRS reports calling these DMVs, which would then dynamically create the cube structure documentation in real time whenever the report rendered..

This post is the first in a 3 part set which will demonstrate how you can use these DMVs to automate the SSAS cube documentation and user guide.

• Part 1 – Creating the DMV stored procs
• Part 2 – Create the SSRS reports
• Part 3 – Use spatial data and maps to create a star schema view
• Download Source Code

UPDATE: I presented a 1 hour session at SQLBits 8 covering all of this work, you can watch the video here.

There’s a full list of DMVs available in SQL 2008 R2 on the msdn site.

The primary DMVs that are of interest are:

When querying DMVs we can use SQL style SELECT statements, but executed against the cube in a DMX window.

SELECT *
FROM $SYSTEM.MDSCHEMA_CUBES

This returns a dataset like any other SQL query.

We can even enhance it with DISTINCT and WHERE clauses, although they are more restricted than basic SQL. One of the main limitations is the lack of a JOIN operator. A number of the queries that I’ll perform below need to use JOIN, so to get around this I wrap up each query in an SQL OPENROWSET command, executed against a SQL database with a linked server to the cube. This enables me to perform JOINs using queries such as

SELECT *
FROM OPENQUERY(CubeLinkedServer,
   'SELECT *
    FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS') mgd
INNER JOIN OPENQUERY(CubeLinkedServer,
   'SELECT *
    FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') mg
ON mgd.XXX = mg.XXX

etc.

I’m therefore going to create a number of stored procs to wrap up this functionality, the SSRS reports can then just call the procs.

Within BIDS, every item (cube, measure group, measure, dimension, attribute, hierarchy, KPI, etc.) has a description in the properties pane which is a multi-line free text property. These are exposed by the DMVs, so I’m going to make use of them and bring them out in the reports. This allows you to create the descriptions within BIDS as you’re developing the cube, meaning they’re version controlled and always in sync with the code.

I should also point out that I’m using SQL Server 2008 R2. All of the queries below will work with SQL 2008, but I want to use the spatial report functionality of SSRS 2008 R2 to generate dynamic star schema visualisations, which is only supported in R2.

In this post I’ll script out the stored procedures used as the basis of the documentation. In my next post I’ll put these into SSRS reports.

Lets get started.

Firstly we need to create our linked server. This script will create a linked server called CubeLinkedServer pointing to the Adventure Works DW 2008R2 OLAP database on the local server.

EXEC master.dbo.sp_addlinkedserver
   @server = N'CubeLinkedServer',
   @srvproduct=N'MSOLAP',
   @provider=N'MSOLAP',
   @datasrc=N'(local)',
   @catalog=N'Adventure Works DW 2008R2'

You’ll have to set up the security according to your requirements. So now lets start creating the source procs.

The first proc lists all of the cubes. The MDSCHEMA_CUBES DMV returns not only cubes, but also dimensions, I’m filtering it to only return cubes by specifying CUBE_SOURCE=1.

CREATE PROCEDURE [dbo].[upCubeDocCubes]
  (@Catalog       VARCHAR(255) = NULL
  )
AS
  SELECT *
  FROM OPENQUERY(CubeLinkedServer,
    'SELECT *
     FROM $SYSTEM.MDSCHEMA_CUBES
     WHERE CUBE_SOURCE = 1')
  WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog
    OR @Catalog IS NULL
GO

The next proc returns all measure groups found within a specified cube.

CREATE PROCEDURE [dbo].[upCubeDocMeasureGroupsInCube]
  (@Catalog       VARCHAR(255)
  ,@Cube          VARCHAR(255)
  )
AS
  SELECT *
  FROM OPENQUERY(CubeLinkedServer,
    'SELECT *
     FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS ')
  WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog
    AND CAST([CUBE_NAME] AS VARCHAR(255))    = @Cube
GO

This next proc returns a list of measures within a specified measure group.

CREATE PROCEDURE [dbo].[upCubeDocMeasuresInMeasureGroup]
  (@Catalog       VARCHAR(255)
  ,@Cube          VARCHAR(255)
  ,@MeasureGroup  VARCHAR(255)
  )
AS
SELECT * FROM OPENQUERY(CubeLinkedServer,
  'SELECT *
   FROM $SYSTEM.MDSCHEMA_MEASURES
     WHERE [MEASURE_IS_VISIBLE]')
   WHERE CAST([CATALOG_NAME] AS VARCHAR(255))      = @Catalog
     AND CAST([CUBE_NAME] AS VARCHAR(255))         = @Cube
     AND CAST([MEASUREGROUP_NAME] AS VARCHAR(255)) = @MeasureGroup
GO

The following proc queries all dimensions available within a specified cube. I’m filtering using the DIMENSION_IS_VISIBLE column to only show visible dimensions.

CREATE PROCEDURE [dbo].[upCubeDocDimensionsInCube]
  (@Catalog       VARCHAR(255)
  ,@Cube          VARCHAR(255)
  )
AS
SELECT * FROM OPENQUERY(CubeLinkedServer,
  'SELECT *
   FROM $SYSTEM.MDSCHEMA_DIMENSIONS
     WHERE [DIMENSION_IS_VISIBLE]')
   WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog
     AND CAST([CUBE_NAME] AS VARCHAR(255))    = @Cube
GO

Then we can query all available attributes within a dimension. This DMV returns a bitmask field (LEVEL_ORIGIN) which defines whether the attribute is a key, attribute or hierarchy. I’m using bitwise AND (&) to split this into three seperate fields for ease of use. I’m also filtering out invisible attributes, as well as those with a level of 0. Level 0 is the [All] member of any attribute, which we can ignore for this purpose.

CREATE PROCEDURE [dbo].[upCubeDocAttributesInDimension]
  (@Catalog       VARCHAR(255)
  ,@Cube          VARCHAR(255)
  ,@Dimension  VARCHAR(255)
  )
AS
  SELECT *
    , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 1 = 1
        THEN 1 ELSE 0 END AS IsHierarchy
    , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 2 = 2
        THEN 1 ELSE 0 END AS IsAttribute
    , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 4 = 4
        THEN 1 ELSE 0 END AS IsKey
  FROM OPENQUERY(CubeLinkedServer,
    'SELECT *
     FROM $SYSTEM.MDSCHEMA_LEVELS
     WHERE [LEVEL_NUMBER]>0
       AND [LEVEL_IS_VISIBLE]')
  WHERE CAST([CATALOG_NAME] AS VARCHAR(255))          = @Catalog
    AND CAST([CUBE_NAME] AS VARCHAR(255))             = @Cube
    AND CAST([DIMENSION_UNIQUE_NAME] AS VARCHAR(255)) = @Dimension
GO

The next proc returns measure groups with their associated dimensions. We have to join two DMVs together in order to get the description columns of both the dimension and measure group.

CREATE PROCEDURE [dbo].[upCubeDocMeasureGroupsForDimension]
    (@Catalog       VARCHAR(255)
    ,@Cube          VARCHAR(255)
    ,@Dimension     VARCHAR(255)
    )
AS
  SELECT
    mgd.*
    , m.[DESCRIPTION]
  FROM OPENQUERY(CubeLinkedServer,
    'SELECT
       [CATALOG_NAME]
       , [CUBE_NAME]
       , [MEASUREGROUP_NAME]
       , [MEASUREGROUP_CARDINALITY]
       , [DIMENSION_UNIQUE_NAME]
     FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
       WHERE [DIMENSION_IS_VISIBLE]') mgd
   INNER JOIN OPENQUERY(CubeLinkedServer,
     'SELECT
       [CATALOG_NAME]
       ,[CUBE_NAME]
       ,[MEASUREGROUP_NAME]
       ,[DESCRIPTION]
     FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') mg
        ON  CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))
           = CAST(mg.[CATALOG_NAME] AS VARCHAR(255))
        AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))
           = CAST(mg.[CUBE_NAME] AS VARCHAR(255))
        AND CAST(mgd.[MEASUREGROUP_NAME] AS VARCHAR(255))
           = CAST(mg.[MEASUREGROUP_NAME] AS VARCHAR(255))
  WHERE CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))            = @Catalog
    AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))               = @Cube
    AND CAST(mgd.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))   = @Dimension
GO

The next proc is similar to the above, but the opposite way around. It returns all dimensions that are related to a measure group.

CREATE PROCEDURE [dbo].[upCubeDocDimensionsForMeasureGroup]
  (@Catalog       VARCHAR(255)
  ,@Cube          VARCHAR(255)
  ,@MeasureGroup  VARCHAR(255)
  )
AS
  SELECT
    mgd.*
    , d.[DESCRIPTION]
  FROM OPENQUERY(CubeLinkedServer,
    'SELECT
        [CATALOG_NAME]
       ,[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 [DIMENSION_IS_VISIBLE]') mgd
  INNER JOIN OPENQUERY(CubeLinkedServer,
    'SELECT
       [CATALOG_NAME]
       ,[CUBE_NAME]
       ,[DIMENSION_UNIQUE_NAME]
       ,[DESCRIPTION]
     FROM $SYSTEM.MDSCHEMA_DIMENSIONS
       WHERE [DIMENSION_IS_VISIBLE]') d
   ON  CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))
       = CAST(d.[CATALOG_NAME] AS VARCHAR(255))
   AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))
       = CAST(d.[CUBE_NAME] AS VARCHAR(255))
   AND CAST(mgd.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))
       = CAST(d.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))
  WHERE  CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))        = @Catalog
     AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))           = @Cube
     AND CAST(mgd.[MEASUREGROUP_NAME] AS VARCHAR(255))   = @MeasureGroup
GO

The next proc builds a BUS matrix, joining every dimension to its related measure groups. Later we’ll use the SSRS tablix control to pivot this into matrix form.

CREATE PROCEDURE [dbo].[upCubeDocBUSMatrix]
    (@Catalog       VARCHAR(255),
     @Cube          VARCHAR(255)
    )
AS
  SELECT
     bus.[CATALOG_NAME]
    ,bus.[CUBE_NAME]
    ,bus.[MEASUREGROUP_NAME]
    ,bus.[MEASUREGROUP_CARDINALITY]
    ,bus.[DIMENSION_UNIQUE_NAME]
    ,bus.[DIMENSION_CARDINALITY]
    ,bus.[DIMENSION_IS_FACT_DIMENSION]
    ,bus.[DIMENSION_GRANULARITY]
    ,dim.[DIMENSION_MASTER_NAME]
    ,1 AS Relationship
  FROM
    OPENQUERY(CubeLinkedServer,
      'SELECT
        [CATALOG_NAME]
        ,[CUBE_NAME]
        ,[MEASUREGROUP_NAME]
        ,[MEASUREGROUP_CARDINALITY]
        ,[DIMENSION_UNIQUE_NAME]
        ,[DIMENSION_CARDINALITY]
        ,[DIMENSION_IS_FACT_DIMENSION]
        ,[DIMENSION_GRANULARITY]
       FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
        WHERE [DIMENSION_IS_VISIBLE]') bus
    INNER JOIN OPENQUERY(CubeLinkedServer,
      'SELECT
        [CATALOG_NAME]
        ,[CUBE_NAME]
        ,[DIMENSION_UNIQUE_NAME]
        ,[DIMENSION_MASTER_NAME]
       FROM $SYSTEM.MDSCHEMA_DIMENSIONS') dim
    ON CAST(bus.[CATALOG_NAME] AS VARCHAR(255))
     = CAST(dim.[CATALOG_NAME] AS VARCHAR(255))
    AND CAST(bus.[CUBE_NAME] AS VARCHAR(255))
     = CAST(dim.[CUBE_NAME] AS VARCHAR(255))
    AND CAST(bus.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))
     = CAST(dim.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))
  WHERE  CAST(bus.[CATALOG_NAME] AS VARCHAR(255)) = @Catalog
     AND CAST(bus.[CUBE_NAME] AS VARCHAR(255)) = @Cube
GO

Next, in order to make it easier for users to find items within the cube, I’ve created a searching proc which will scour a number of the DMVs for anything containing the search term.

CREATE PROCEDURE [dbo].[upCubeDocSearch]
    (@Search        VARCHAR(255)
    ,@Catalog       VARCHAR(255)=NULL
    ,@Cube          VARCHAR(255)=NULL
    )
AS
  WITH MetaData AS
  (
   --Cubes
    SELECT CAST('Cube' AS VARCHAR(20))            AS [Type]
      , CAST(CATALOG_NAME AS VARCHAR(255))     AS [Catalog]
      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Cube]
      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Name]
      , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description]
      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Link]
    FROM OPENQUERY(CubeLinkedServer,
      'SELECT [CATALOG_NAME], [CUBE_NAME], [DESCRIPTION]
       FROM $SYSTEM.MDSCHEMA_CUBES
       WHERE CUBE_SOURCE = 1')
    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))
       = @Catalog OR @Catalog IS NULL)

    UNION ALL

   --Dimensions
    SELECT CAST('Dimension' AS VARCHAR(20))         AS [Type]
      , CAST(CATALOG_NAME AS VARCHAR(255))       AS [Catalog]
      , CAST(CUBE_NAME AS VARCHAR(255))             AS [Cube]
      , CAST(DIMENSION_NAME AS VARCHAR(255))        AS [Name]
      , CAST(DESCRIPTION AS VARCHAR(4000))   AS [Description]
      , CAST(DIMENSION_UNIQUE_NAME AS VARCHAR(255)) AS [Link]
    FROM OPENQUERY(CubeLinkedServer,
      'SELECT [CATALOG_NAME], [CUBE_NAME]
          , [DIMENSION_NAME], [DESCRIPTION]
          , [DIMENSION_UNIQUE_NAME]
       FROM $SYSTEM.MDSCHEMA_DIMENSIONS
         WHERE [DIMENSION_IS_VISIBLE]')
    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))
        = @Catalog OR @Catalog IS NULL)
      AND (CAST([CUBE_NAME] AS VARCHAR(255))
        = @Cube OR @Cube IS NULL)
      AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1)
        <>'$' --Filter out dimensions not in a cube

    UNION ALL

   --Attributes
    SELECT CAST('Attribute' AS VARCHAR(20))         AS [Type]
      , CAST(CATALOG_NAME AS VARCHAR(255))       AS [Catalog]
      , CAST(CUBE_NAME AS VARCHAR(255))             AS [Cube]
      , CAST(LEVEL_CAPTION AS VARCHAR(255))         AS [Name]
      , CAST(DESCRIPTION AS VARCHAR(4000))   AS [Description]
      , CAST(DIMENSION_UNIQUE_NAME AS VARCHAR(255)) AS [Link]
    FROM OPENQUERY(CubeLinkedServer,
      'SELECT [CATALOG_NAME], [CUBE_NAME]
         , [LEVEL_CAPTION], [DESCRIPTION],
         , [DIMENSION_UNIQUE_NAME]
       FROM $SYSTEM.MDSCHEMA_LEVELS
       WHERE [LEVEL_NUMBER]>0
         AND [LEVEL_IS_VISIBLE]')
    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))
         = @Catalog OR @Catalog IS NULL)
      AND (CAST([CUBE_NAME] AS VARCHAR(255))
         = @Cube OR @Cube IS NULL)
      AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1)
         <>'$' --Filter out dimensions not in a cube

    UNION ALL

   --Measure Groups
    SELECT CAST('Measure Group' AS VARCHAR(20))   AS [Type]
      , CAST(CATALOG_NAME AS VARCHAR(255))     AS [Catalog]
      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Cube]
      , CAST(MEASUREGROUP_NAME AS VARCHAR(255))   AS [Name]
      , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description]
      , CAST(MEASUREGROUP_NAME AS VARCHAR(255))   AS [Link]
    FROM OPENQUERY(CubeLinkedServer,
       'SELECT [CATALOG_NAME], [CUBE_NAME]
          , [MEASUREGROUP_NAME],
          , [DESCRIPTION]
        FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS')
    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))
       = @Catalog OR @Catalog IS NULL)
     AND (CAST([CUBE_NAME] AS VARCHAR(255))
       = @Cube OR @Cube IS NULL)
     AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1)
       <>'$' --Filter out dimensions not in a cube

    UNION ALL

   --Measures
    SELECT CAST('Measure' AS VARCHAR(20))         AS [Type]
      , CAST(CATALOG_NAME AS VARCHAR(255))     AS [Catalog]
      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Cube]
      , CAST(MEASURE_NAME AS VARCHAR(255))        AS [Name]
      , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description]
      , CAST(MEASUREGROUP_NAME AS VARCHAR(255))   AS [Link]
    FROM OPENQUERY(CubeLinkedServer,
      'SELECT [CATALOG_NAME], [CUBE_NAME]
         , [MEASURE_NAME], [DESCRIPTION],
         , [MEASUREGROUP_NAME]
       FROM $SYSTEM.MDSCHEMA_MEASURES
          WHERE [MEASURE_IS_VISIBLE]')
    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))
          = @Catalog OR @Catalog IS NULL)
      AND (CAST([CUBE_NAME] AS VARCHAR(255))
          = @Cube OR @Cube IS NULL)
      AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1)
          <>'$' --Filter out dimensions not in a cube

    )
    SELECT *
    FROM MetaData
    WHERE @Search<>''
        AND ([Name] LIKE '%' + @Search + '%'
          OR [Description] LIKE '%' + @Search + '%'
        )
GO

We can now use these procs to form the basis of a number of SSRS reports which will dynamically query the DMVs to generate the SSAS cube documentation. I’ll be covering this stage in my next post.

• Part 1 – Creating the DMV stored procs
• Part 2 – Create the SSRS reports
• Part 3 – Use spatial data and maps to create a star schema view
• Download Source Code