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

SSRS: Dynamic zoom for maps in Reporting Services 2008 R2

The maps in reporting services miss one important feature: dynamic zoom. By this, I mean the ability for a user to zoom into the map while looking at the report and panning around to see the locations of interest. This is necessary for instance when you display the world map and wish to zoom into smaller countries or specific areas.

Unfortunately I did not find any ideal solution to this issue, however I will present here a (somewhat) acceptable solution, that allows the user to tweak zoom level while automatically centering on the locations of interest.

To start, I created a basic report that displays the world map, and visualizes country data from the AdventureWorksDW by color. For simplicity, I removed the legend and color scale, and this is what I have:

Figure 1 - Starting map

The sample includes very few countries but that's okay.

What we will do is allow the user to specify the countries of interest, and the map automatically centers itself between those countries.

I modify my data set to take a list of countries as a parameter, and then add the list of countries as a report parameter.

Figure 2 - Countries parameter

The next step is to enable the automatic-centering between the countries of interest. To show you what I mean, I (for now) manually zoomed into the map and then with the report running, I selected only Australia. Figure 3 shows the result.

Figure 3 - Map automatically centers on Australia

In order to do this, right click on the map (away from the polygons) and choose "Viewport Properties". Go into the "Center and Zoom" tab, and under "Change map view center and zoom options", select "Center map to show all data-bound map elements" as in the screenshot below.

Figure 4 - Center map

Finally we need the ability to dynamically change the zoom level. This is the not-that-nice part, but it is so far the only possible way I found. In the same window as Figure 4, there is a Zoom level field. What we can do is create a report parameter of type integer (with default of 100) (Figure 5), and then link this zoom field to that parameter using expressions.

Figure 5 - ZoomLevel parameter

I called the parameter ZoomLevel, and now the viewport properties looks like Figure 6.

Figure 6 - Map zoom level from parameter

The result is that the users can select countries of interest and then tweak the zoom level as they desire. For instance, I ran the report and chose Germany and United Kingdom as countries, and set the zoom level parameter to 1500, achieving the following result:

Figure 7 - Map centered and zoomed

This solution is still far from the comfort of using the mouse wheel for zooming, and dragging the mouse to pan around. However I have not found any other better way. Some interactivity with the map would be an appreciated feature of Reporting Services.

tags: reporting services report builder 3.0 maps dynamic zoom automatic

SSRS: World Maps for Reporting Services

The map visualization tool in Reporting Services 2008 R2 is a great feature that introduces multiple ways of visualizing data on a map. Some of the possibilities are:

• Coloring countries based on some data
• Showing bubbles or markers over countries that vary in color, shape or size based on data
• Marking exact coordinates on the map based on geospatial data

Figure 1 - Example of USA map, where color of states depict sales value and size of circles represents count (or importance) of customers

Unfortunately however, Reporting Services only comes with a USA map and its states. 

Figure 2 - USA maps only

Surely there will be the need for maps of other geographical locations, most importantly the whole world. I would suggest the following 2 places for this:

1. MapGallery on Codeplex (http://mapgallery.codeplex.com/).
   Besides having the world map, there are also other geographical locations.
   
   How to use: Maps downloaded from this location are in .RDL format. To be able to use the map from Report Builder/Business Intelligence Development Studio on your workstation, move the file to the MapGallery folder (typically "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery\" for Business Intelligence Development Studio and "C:\Program Files\Microsoft SQL Server\Report Builder 3.0\MapGallery" for Report Builder 3.0). If you have a Report Server and want anyone that loads the Report Builder 3.0 through the ClickOnce, then the map must be in the server, typically in "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\ReportingServices\ReportServer\ReportBuilder\RptBuilder_3\MapGallery".
   
   The map will now be listed in the map gallery (see Figure 2).
   
   Downside: I found that the world map from this site contains missing geographical locations unfortunately.
   
   
2. World Countries Map from Blue Marble Geographics ( http://www.bluemarblegeo.com/products/worldmapdata.php?op=download)
   
   How to use: They offer a free shapefile of the world map. Once the archive is downloaded, you can extract it locally or upload the files on the report server using the report manager so anyone can use it. Then when creating a new map, you choose the "ESRI shapefile" (2nd option in Figure 2) and browse to the SHP file.
   
   This map I have so far found more detailed.

Finally you can visualize data on a world map.

Figure 3 - Some sample data over a world map using AdventureWorksDW

Tip: Country names/codes, continents and states in your data must exactly match with the ones found in the map data. If you are using an RDL file for the map, you can edit the file in WordPad and modify the names to fit your data. For SHP files, you probably need an SHP editor, otherwise you'd have to change your data to fit the map data. From my experience, I found common mismatches such as United States of America and USA, United Arab Emirates and Utd. Arab Emirates, and so on.

Tags: reporting services ssrs report builder maps map gallery esri shapefile import

SSRS: Retrieving Member Properties in Reporting Services (with Analysis Services)

In Reporting Services, it is unclear how, or if it is even possible to retrieve member properties when using an analysis services data source.

Member properties refer to the extra information attributed to members apart from their names. For instance, in the AdventureWorksDW, the Customer dimension contains several member properties including Address, Gender and Phone.

Screenshot 1 - Member Properties

Dragging these properties into the query window in Reporting Services unfortunately does not do the job. From what I've seen so far, it also does not seem possible from the query designer in design mode. However you can make a small change to the MDX code in order to retrieve these properties.

Change the design mode to see the MDX . Within the code, one line reads "DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME" as in the screenshot below.

Screenshot 2 - MDX Code to look for

Following that, add a comma and insert the syntax for the member property that you wish to retrieve. For instance, if I want to retrieve the Customer's Address, I would add [Customer].[Customer].[Address].

Screenshot 3 - Addition to the MDX Code

If you are in doubt about the syntax, look for the property in the left panel and hover your mouse over it. A tooltip will show you the syntax, but you need to modify it slightly.

Screenshot 4 - Tooltip showing Member Property Syntax

Remove the "Properties" and parenthesis, and replace the double quotes with square brackets. So [Customer].[Customer].Properties( "Address" ) would become [Customer].[Customer].[Address]

After successfully doing this and executing the query to test the result, you can freely use the property as you use any other field in the report (Fields!Address.Value).

Screenshot 5 - Result with Customer Address

Few Notes:

• After doing this, you will be unable to use the graphical designer to edit the query as that will undo any changes you made to the MDX code. This could compromise the maintainability of the report especially if you are unfamiliar with MDX.
• An alternative is to have some design changes to the cubes, such that all member properties are separate members themselves. This allows you to easily drag the fields into the query and it's not a bad cube design approach in itself.

Tags: reporting services analysis services mdx member properties dimension properties

MDX: Replacing Null / Empty Values

In the result of an MDX query, you may get some empty or null values which you'd want to treat differently, such as considering them as 0s. For instance in Reporting Services, you might want to filter out any rows with 0 or empty values.

In such cases, you can use the MDX function CoalesceEmpty, which replaces an empty cell value with a given value. For instance:

WITH MEMBER [Measures].[NonEmpty Discount] AS
     COALESCEEMPTY( [Measures].[Discount Amount], 0 )

will replace any empty values of discount amount with zero.

Very useful in certain scenarious.

MSDN Documentation: http://msdn.microsoft.com/en-us/library/ms146080.aspx
Tags: mdx replace replacing get getting null empty values measure zeros

SSRS: Hierarchies issue in report parameters with Analysis Services

Reporting Services offers great support for Analysis Services (getting data from cubes). Admittedly however, you will sooner or later meet with several little annoyances that hinder report development, especially if you are not confident or have no knowledge in MDX.

One common issue occurs when setting a dimension hierarchy as a parameter, as in screenshot 1 (for this example, I will be using Customer Geography hierarchy in the AdventureWorks Cube).

Screenshot 1 - Parameter

Reporting Services automatically creates a new hidden dataset, that retrieves the dimension members and all the members of its levels to use as available values for the report parameter. For instance, if you want the countries as parameter, you also get cities and names along with it, which could make the dropdown list parameter huge and messy, as in screenshot 2. While sometimes this is desired, other times it is not.

Screenshot 2 - Large dropdown list

So what can you do if you want only the countries? One option, of course, is to have each level as a separate hierarchy defined in the cube, as seen in the AdventureWorksDW. This is not always possible however. The steps below show you how to do this in the report, and can be applied for any case.

Solution 1 (if you only need the top level in the hierarchy):

• In the Report Data, right click the Datasets folder and select "Show Hidden Datasets".

Screenshot 3 - Show Hidden Datasets

• Right click the new dataset that appeared, and select "Query...". You'll be shown some scary, unformatted code (called MDX). Don't fret!

Screenshot 4 - MDX with ALLMEMBERS

• In the code, there is one and only one place with the text "ALLMEMBERS", as seen in screenshot 4. This can be safely replaced with "CHILDREN" as in screenshot 5. If you execute the query, you will see that you only get the members without all the lower levels.

Screenshot 5 - MDX with CHILDREN

• Click OK to save the query and preview the report. The dropdown list only shows the countries now, and is much cleaner than before.

Screenshot 6 - Clean dropdown list

Solution 2 (to get a specific level in the hierarchy):

• The first solution works only if you require the members at the highest level (in the example, countries). But what if we want cities only for example? The first thing is to repeat the steps as above until screenshot 4.
• This time, we will leave ALLMEMBERS as it is, and instead replace the statement preceding it. In this case, it is [Customer].[Customer Geography]. In the left column under the Metadata tab, find the level that you require and hover the mouse over it to get the statement you need, as in screenshot 7.

Screenshot 7 - Getting level MDX statement

• We replace the statement in the MDX code as shown in the screenshot above. Finally we get the desired level members.

Screenshot 8 - Dropdown list with cities

Tags: reporting services analysis services mdx report parameters hierarchies level issue problem messy large dropdown list

Microsoft SQL Server Reporting Services vs PerformancePoint Services

These 2 products aren't often compared with each other, and they are in fact different. However there are times where their capabilities overlap and reasons for using one instead of the other may be unclear, especially when using these tools together with Analysis Services.

Since not much information on such aspects seem to be available on the internet, below is a basic list displaying some comparisons.

	Reporting Services	PerformancePoint Dashboards
Product	Standardized reports	Dynamic dashboards
Audience	Few to many users	Power users
Usage	Operational and Management	Strategy and Management
Measures	Fixed	Changeable
Dimensions	Fixed	Changeable
Drilldown	Fixed drilldown or drill through path as defined in report	Flexible drilldown for any report
Styling	Customizable to the lowest detail	Fixed appearance
Distribution	Online, Email, File Share or Printed	Online
Export	CSV, Excel, Image, PDF, Word or XML	Excel, PowerPoint (Individual Dashboard Elements)
Security	At report level. Users see what the report displays	At data warehouse level. Users see all data they have access to

It is certainly possible to implement some tricks or workarounds in order to achieve some capabilities such as styling in PerformancePoint using CSS, but those were the general aspects of the two tools. It should give a good idea of the differences.