Dynamic Management Views are a very powerful tool that are part of Analysis Services in 2008 and 2008 R2. They give you the ability to monitor Analysis Services usage and metadata.

MSDN articles on SSAS DMVs – http://msdn.microsoft.com/en-us/library/ms126079.aspx

Good post on using SSAS DMVs – http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

A report developer could leverage the capability to read Analysis Services metadata to build a report that can dynamically change the type of information that is displayed on a report.

Let’s walk through a basic example of how we can Dynamic Management Views to build a dynamic Reporting Services report. I’m starting with a basic report that has already been built off of an Analysis Services cube that looks like this:

As you can see it shows Internet Sales by category over several calendar years. The MDX query used for this is pretty standard as you can see.

Select
[Measures].[Internet Sales Amount] ON Columns
,NON EMPTY ([Date].[Calendar].[Calendar Year]
,[Product].[Product Categories].[Category]) ON Rows
From
[Adventure Works]

I start by changing the query to use an in session calculation with the WITH clause so I can basically alias it to [Measures].[Generic Measure]. By aliasing the measure it will make since more later because the field name doesn’t identify with any specific measure.

With
Member [Measures].[Generic Measure]
AS STRTOVALUE("[Measures].[Internet Sales Amount]")

Select
[Measures].[Generic Measure] ON Columns
,NON EMPTY ([Date].[Calendar].[Calendar Year]
,[Product].[Product Categories].[Category]) ON Rows
From
[Adventure Works]

The next step is to create a parameter in the report so I can pass in the measure name to the query dynamically. Eventually I will populate this parameter using a DMV but for now I’ll just have it as a text field that I can type in the measure name.

Now to make this query dynamic with my newly created @MeasureName parameter I replace the dataset query with an expression that looks like this:

="With "
+" Member [Measures].[Generic Measure] "
+" AS STRTOVALUE("""+Parameters!MeasureName.Value+""") "

+"Select "
+" [Measures].[Generic Measure] ON Columns "
+" ,NON EMPTY ([Date].[Calendar].[Calendar Year] "
+" ,[Product].[Product Categories].[Category]) ON Rows "
+"From "
+" [Adventure Works] "

You’ll notice that I’ve basically changed the query to a string in the expression except for the measure name which I’ve changed to use my new parameter. This report will now work dynamically but the user would have to type in the measure they would like to see in the report. Not only would they have to type it but they must know the MDX for it. For example, users would have to type [Measures],[Internet Tax Amount]. Obviously, that’s not going to happen so we need to make this simpler for the user.

This is where Dynamic Management Views help. We can use the mdschema_measures DMV to return back a list of measures from our cube. The following query will return back all my measures names, MDX, and format:

SELECT Measure_Name, Measure_Unique_Name, Default_Format_String
FROM $System.mdschema_measures
WHERE Cube_Name = ‘Adventure Works’
ORDER BY Measure_Name

Unfortunately, not all format strings translate to Reporting Services but this could be manipulated using expressions. Using this query we can create a new dataset and have that dataset populate our @MeasureName parameter. The dataset cannot be used in entered into a dataset using the Query Builder because it’s not really MDX. So you must select to build an expression on the new dataset and paste in the query. It does not require an actual expression so remote the equal sign before the query.

With the dataset completed go back to the @MeasureName properties and change the available values to use the Measure_Unique_Name and Measure_Name fields to populate the parameter dropdown box.

Now that the parameter dropdown box it populated with the DMV query go ahead and preview the report. You’ll see you can select any measure front the dropdown box and it will change the information shown on the report as shown below: