0845 643 64 63

OLAP Cube Documentation in SSRS part 2

In my previous post I described how to create a number of stored procedures that use Dynamic Management Views (DMVs) to return the metadata structure of an SSAS 2008 OLAP cube, including dimensions, attributes, measure groups, BUS matrix etc.

In this post I’m going to use those procs to create a set of SSRS 2008 reports that will serve as the automated documentation of your cube. I’m going to make the following assumptions:

  • You’ve read the part 1 post, and already have the stored procs in a database.
  • You know the basics of SSRS 2008

If you haven’t read part 1, you can jump to it here.

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

Firstly I create a basic template report which has an appropriate header, with date/time stamp, server name and logos etc. This means that all of the reports will have a common look and feel. I could of course make use of the new report parts in SSRS 2008 R2 for this, but to maintain compatibility with pre R2 I’ll keep it simple.

The expression in the box on the top right is just to display today’s date in UK format dd/mm/yyyy.

  =FORMAT(Today(),"dd/MM/yyyy")

The reports that we’ll build will include the following:

  • CubeDoc_Cubes.rdl – Entry page, list of all cubes in the database
  • CubeDoc_Cube.rdl – showing all measure groups and dimensions within the selected cube
  • CubeDoc_Dimension.rdl – showing all hierarchies, attributes and related measure groups
  • CubeDoc_MeasureGroup.rdl – showing all measures and related dimensions
  • CubeDoc_Search.rdl – search names and descriptions of cubes, dimensions, measure groups, etc

Create the first report (CubeDoc_Cubes.rdl) which will act as the entry screen and menu of cubes.
Add a dataset dsCubes, and point it at the stored proc dbo.upCubeDocCubes

The proc has a @Catalog parameter, which filters the result set to a specific SSAS database (catalog). We want to return all cubes from all catalogs, so set the parameter value to =Nothing

All we have to do now is add a table and pull in the dataset fields that we want to see.

We can then preview the report to test that it returns the right list of cubes. You should see something like this.

Note that the AdventureWorks database doesn’t contain any descriptions, so you won’t see any in the report but they will be there when you add descriptions to your own cubes.

The next report we’re going to write is the CubeDoc_Cube report, which will list the measure groups, dimensions and BUS matrix of a single cube. We’ll link the two reports together later on.

Create a new report, using the template report you created earlier (select the template report in the solution explorer window, then CTRL+C then CTRL+V) and rename the new file as CubeDoc_Cube.rdl.

Add a report parameter called @Catalog which should be Text. I’ve set mine to default to “Adventure Works DW 2008R2” to make testing easier.

Add a dataset called dsCubes, and point it at the dbo.upCubeDocCubes proc, and link the @Catalog dataset parameter to the @Catalog report parameter.

This dataset will query the available cubes for the selected catalog, and populate a new parameter which we’ll now create, called @Cube. This should also be a text parameter, but this time we’ll set the available values to those returned by the dsCubes dataset.

If you want you can also set the default value of the parameter to the CUBE_NAME field of dsCubes. This parameter is not a multi value parameter, so by defaulting it to the dataset it will just default to the first record.

We can now use @Catalog and @Cube parameters to query the available measure groups and dimensions.
So, create a three new datasets:

  • dsDimensions – pointing to dbo.upCubeDocDimensionsInCube
  • dsMeasureGroups – pointing to dbo.upCubeDocMeasureGroupsInCube
  • dsBusMatrix – pointing to dbo.upCubeDocBUSMatrix

Set each of their @Catalog parameters to the report’s @Catalog parameter, and their @Cube parameters to the report’s @Cube parameter.

Create two tables in the report, one for measure groups and one for dimensions. Drag in the fields that you want to see, and preview the report. You should see something like this.

I’ve added a couple of textbox titles for good measure.

The third dataset, dsBUSMatrix requires something a little more interesting. For those that aren’t familiar with Kimball’s BUS Matrix concept, it’s a grid that shows the relationship and connectivity between facts (measure groups) and their dimensions. As the name suggests, we’ll use SSRS’s Matrix control for this. Once you’ve added a matrix control onto the report, follow these steps (using the dsBUSMatrix dataset):

  • Drag DIMENSION_UNIQUE_NAME onto the Rows of the matrix
  • Drag MEASUREGROUP_NAME onto the columns of the matrix
  • Right click on the Data box of the matrix, and type in the expression below. This checks the cardinality of the dimensions/measures to determine whether it is a regular relationship, a many to many or a degenerate fact
=SWITCH(SUM(Fields!Relationship.Value)=0,"",
   Fields!DIMENSION_IS_FACT_DIMENSION.Value=1,"F",
   Fields!MEASUREGROUP_CARDINALITY.Value="MANY","M",
   True,"X")

This will either show an X if there is a regular relationship, or show an M or F if there’s a many to many or degenerate relationship respectively.
To make it easier to read, I also like to set the background colour of the Data textbox to highlight the type of relationship further.

=SWITCH(SUM(Fields!Relationship.Value)=0,"Transparent",
   Fields!DIMENSION_IS_FACT_DIMENSION.Value=1,"Yellow",
   Fields!MEASUREGROUP_CARDINALITY.Value="MANY","Red",
   True,"CornflowerBlue")

If you preview the report you should see the following

It shows the concept, but it needs a little tidying up. Centering the text in the data textbox helps, but we can also use a fantastic new feature in SSRS 2008 R2 to rotate the column titles. Simply set the WritingMode property in the Localization group to Rotate270, and then shrink the width of the column.

I’ve also added a title, with a key, and level of row grouping using the DIMENSION_MASTER_NAME field, which groups role playing dimensions by their master dimension. It should now look something like this.

That’s it for this report, so save it, then go back to the first report (CubeDoc_Cubes.rdl) and right click, properties on the [CUBE_NAME] textbox. Go to the action tab, and set the action to navigate to the CubeDoc_Cube report, passing through the CATALOG_NAME and CUBE_NAME fields from the dataset as the parameter values. This sets up a hyperlink from one report to the other, allowing users to navigate around the cube doc reports by clicking on what they want to know about.

We then need to do the same for 3 other reports:
CubeDoc_Dimensions

  • dbo.upCubeDocAttributesInDimension results in a table
  • dbo.upCubeDocMeasureGroupsForDimension results in a table
  • Add an extra @Dimension parameter, populated from dbo.upCubeDocDimensionsInCube

CubeDoc_MeasureGroup

  • dbo.upCubeDocMeasuresInMeasureGroup results in a table
  • dbo.upCubeDocDimensionsForMeasureGroup results in a table
  • Add an extra @MeasureGroup parameter, populated from dbo.upCubeDocMeasureGroupsInCube

CubeDoc_Search

  • Add an extra @Search parameter, text, with no default
  • Table containing results from dbo.upCubeDocSearch, using the @Search parameter

Link all appropriate textboxes (measure groups, dimensions, search etc.) to their relevant report using the report action, and hey presto – a fully automated, real time, self-documenting cube report.

In the next and final installment of this series of blog posts, we’ll explore SQL 2008’s spatial data to generate an automated star schema visualisation to add that little something extra to the reports.

15 Responses to OLAP Cube Documentation in SSRS part 2

  • Thanks for the Post, really helpful.

    Have you done anything similar for SSIS projects.

    Thanks,

    Richard Mintz

  • Hi Richard, thanks for your comment.

    I’ve not done anything similar with SSIS as I tend to use BI documenter. It’s a great tool, not without it’s problems but it does the job well for SSIS.

    My reason for creating this blog post was that I’m not a great fan of how BI documenter handles SSAS, although hopefully they’ll keep improving it. It’s also aimed at being a DBA doc tool, whereas I wanted a client/end user doc tool, which isn’t normally necessary for SSIS.

    Regards
    Alex

  • Here are this and some other articles on SSAS Dynamic Management Views: http://ssas-wiki.com/w/Articles#DMV_.28Dynamic_Management_View.29

  • Hi… Thanks for great series of posts. It came handy while documenting my cubes. Only problem I faced with files you provided was that some of them were created in SSRS R2 (CubeDoc_Cube.rdl and CubeDoc_MeasureGroup.rdl), I had success converting CubeDoc_Cube.rdl to SSRS2008 but could not convert the other. Thought I would let you know 🙂

    Thank you.
    Regards,
    Sudarshan

    • Hi Sudarshan.
      Thanks for the comments – glad it’s been useful. I did the project in SQL 2008 R2 as I needed the spatial visualisation map component of SSRS which was only introduced in R2, so I’m afraid there will be no way of downgrading it (unless I took out the star schema). If you create a working set of reports on SQL 2008 please feel free to add a link to them here.
      If it’s of interest, there’s a 1 hour video of me presenting this documentation tool at the last SQLBits conference, now available on their website… http://www.sqlbits.co.uk/Sessions/Event8/Automating_SSAS_cube_documentation_using_SSRS_DMV_and_Spatial_Data

      Alex

      • Thanks for reply.. Now I see why my SSRS 2008 had problem with word Map in that RDL file 😆
        I will check the video for sure..

        Regards,
        Sudarshan

  • Thanks for your post.
    What a shame There is not the size of Cubes and dimensions.
    Do you know how to find it ?
    Thanks for your feedback

    Carlos

  • Thank you for this wonderful series. I had no idea this was even possible and I’m excited at the possibilities this opens up.

  • “The proc has a @Catalog parameter, which filters the result set to a specific SSAS database (catalog). We want to return all cubes from all catalogs, so set the parameter value to =Nothing”

    Quick question about this. My understanding (and testing confirmed) of the cubes DMV is it only returns one catalog. If you don’t specify it will still take a default one and give you only that. Isn’t that right, or did I miss something?

    I’d love to show all cubes across all databases but at the moment that doesn’t seem to be working out well

    • Hi Mark
      You’re right, that the Catalog parameter doesn’t do anything at present. I included it as a future expansion, intending to create a layer of views spanning multiple cubes, however I never got around to it. Let me know how you get on with it though, and post here with a solution if you get one working.
      Thanks
      Alex

  • Thank you Alex for sharing this with us. Thank you for doing all the work. I´m just starting with MDX and and in a new company and this is a fast way to get into the cubes. Thank you.

  • Thank for your code.it realy help me in my works.

  • Wondeful job! Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

364,071 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon