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.

Tags: , , , , , , , , ,

« Previous Post
Next Post »