Monday, June 25, 2012

Documenting Analysis Services Cubes with Reporting Services

Documenting cubes in Microsoft Analysis Services 2008 has never been an easy task, and there are a number of commercial software out there that help you with this task. However, you can create an efficient documentation using Microsoft Reporting Services.

The way to do this is to fill in the description fields of measures, dimensions, hierarchies and levels within the Analysis Services project, then use the DMV query language in Reporting Services to query the metadata of the cubes (this is very simple!). Below I describe some pros and cons of this approach, and then proceed to the steps on how to implement this.

Advantages

  • Once you create the layout of the report, you do not need to update it anymore. The report automatically gets a list of measures and dimensions in the cube, so any changes such as new, changed and removed measures or dimensions are immediately reflected.
  • You can use some basic HTML tags such as <b> for bold, <ul> and <li> for lists and <a> for links.
  • Customizeable layout on the Reporting Services side.

Limitations

  • The description field is a plain text box. It becomes uncomfortable with lengthy descriptions, and it is not possible to include images, videos or other multimedia content directly.

How To

  1. Open the Analysis Services project from within Visual Studio and open the cube structure.
  2. Fill in the description fields of Measures, Dimensions, Hierarchies and Levels. There are several places where you can fill in the description. The following can help you:
    • Measures: Click on any measure and then fill in the Description field from the Properties box (if you do not see the Properties, right click and choose Properties).
      Figure 1 - Measures description
    • Dimensions: Similarly, click on the top-level dimension to add their descriptions.
      Figure 2 - Dimensions description
    • Hierarchies, Levels & Dimension Attributes: Open each dimension (.dim) to edit the descriptions of dimension attributes, hierarchies and levels.
      Figure 3 - Hierarchies, levels and dimension attributes description
    • Calculated Measures: For calculated measures, it's a little different. From the cube structure, go to the Calculations tab and hit the Properties button on the toolbar at the top. The description field can be found in this window.
      Figure 4 - Calculates measures description
  3. Once you filled in the description fields and deployed the cube, open the Report Designer from within the Business Intelligence Studio (Note: The following steps can only be made using Report Designer, and not Report Builder 3.0).
  4. Create a new Data Source to your Analysis Services.
    Figure 5 - Creating a data source
  5. Create a new Data Set based on that data source and open the Query Designer. Locate a pick axe icon on the toolbar and click the button to switch to DMX Designer. Then click on the right-most icon to switch from Design Mode to text mode as the screenshots below.
    Figure 6 - Switch to DMX

    Figure 7 - Switch from Design Mode to Text
  6. Here you can enter some DMV queries to retrieve measures and dimensions metadata from a cube. For instance, to get all the measures and their metadata, use the following query (replacing 'Adventure Works' with the name of your cube)
    SELECT * FROM $System.MDSCHEMA_MEASURES WHERE CUBE_NAME = 'Adventure Works'
    This gives you information such as measure caption, unique name, description and group name which you can use to display the documentation.
  7. You need to create new data sets, one for dimensions, one for hierarchies and one for levels. Use the following queries for each:
    • Dimensions: SELECT * FROM $System.MDSCHEMA_DIMENSIONS where CUBE_NAME = 'Adventure Works'
    • Hierarchies: SELECT * FROM $System.MDSCHEMA_HIERARCHIES where CUBE_NAME = 'Adventure Works'
    • Levels: SELECT * FROM $System.MDSCHEMA_LEVELS where CUBE_NAME = 'Adventure Works'
  8. You can then freely build the report and use this data to display the documentation however you want. I built a sample report that you can see in the screenshot below (I only changed a few descriptions for the sample).
    Figure 8 - Sample report with a few descriptions

Additional Notes

There are additional fields that you can make use of, one of which is visibility. Some measures or dimensions may be hidden in the cube. Each data set contains a property to check whether the field is visible or not.

You can also query for cubes, perspectives and other elements of an Analysis Services project. There are some resources available online about DMV queries; look up for cube metadata using DMV queries.

In Report Builder 3.0, for technical reasons, you cannot use DMv queries, therefore you must use the Visual Studio Report Designer.

Tags: documentation cubes analysis services reporting services dmx dmv query metadata measures dimensions description field as2008