Populating Parameter Lists Using MDX

In a previous post I demonstrated how to surface SSAS data in an ASP.NET MVC website with Google charts.

If you have access to SQL Server Reporting Services (SSRS) then this should be your first port of call when considering how to incorporate charts in your website. It’s an enterprise level solution, providing enterprise level features for monitoring and optimizing report delivery. It also provides a comprehensive set of report authoring tools, and gives the report authors detailed control over most aspects of the reports, through the use of report expressions.

However, there a number of significant limitations with SSRS. Report parameters are restricted to Boolean, DateTime, Integer, Float, and Text. And the controls used to render the parameters are quite basic and cannot be customized. Also, interactivity is limited to drill-throughs and drill-downs. Business users are becoming accustomed to and are demanding rich user experiences. SSRS does not really offer the flexibility to meet these demands, hence the need to consider alternative solutions such as Google Charts.

What I want to do is take the next step from the previous post – where I populated Google graphs – by discussing how to populate parameter lists using MDX. These parameter lists can be rendered in an MVC view using one of a multitude of JQuery UI components, with the richness of the UI and interactivity being only limited by the developer ability and imagination.

The first steps is to query the database for the parameter captions and values. The following script does this for the [Data].[Calendar].[Calendar Year] set –

WITH
	MEMBER [Measures].[ParameterCaption] AS [Date].[Calendar].CURRENTMEMBER.MEMBER_CAPTION
	MEMBER [Measures].[ParameterValue] AS [Date].[Calendar].CURRENTMEMBER.UNIQUENAME
SELECT
	{ [Measures].[ParameterCaption], [Measures].[ParameterValue] } ON COLUMNS,
	{ [Date].[Calendar].[Calendar Year] } ON ROWS
FROM
	[Adventure Works]

Returning the following –

paramlist1

I will be using the above query for the rest of this post. However, if we require members from different levels we can additionally return the level ordinal. This can then be used to help us populate a tree-view for example. The following script does this for the [Date].[Calendar] set, and ensures that only the [All Periods], [Calendar Year], and [Month] levels are returned. Please note that I exclude the unwanted levels, rather than build a set using the wanted levels, so that the remaining members will be returned in the correct order –

WITH
	MEMBER [Measures].[ParameterCaption] AS [Date].[Calendar].CURRENTMEMBER.MEMBER_CAPTION
	MEMBER [Measures].[ParameterValue] AS [Date].[Calendar].CURRENTMEMBER.UNIQUENAME
	MEMBER [Measures].[ParameterLevel] AS [Date].[Calendar].CURRENTMEMBER.LEVEL_NUMBER
SELECT
	{	[Measures].[ParameterCaption],
		[Measures].[ParameterValue],
		[Measures].[ParameterLevel] } ON COLUMNS,
	{	[Date].[Calendar].MEMBERS
		- [Date].[Calendar].[Calendar Semester]
		- [Date].[Calendar].[Calendar Quarter]
		- [Date].[Calendar].[Date] } ON ROWS
FROM
	[Adventure Works]

Which returns –

paramlist2

I want to bind the results to a JQuery control in my view. And I will do this by retrieving the parameter data using a Ajax Get call – as I did to populate the charts in the previous post.

I therefore want a Controller action method as follows –

public JsonResult CalendarYears()
{
    Dictionary<string, string> data = ReportData.CalendarYears();
    return Json(data, JsonRequestBehavior.AllowGet);
}

The controller action method calls the ReportData.CalendarYears method to return a dictionary containing the key and value pairs for the parameter. It then serializes the dictionary into a Json response.

The ReportData.CalendarYears method is shown below –

using Microsoft.AnalysisServices.AdomdClient;
using System.Collections.Generic;
using System.Data;

namespace Charts.Reports
{
    public class ReportData
    {
        public static Dictionary<string, string> CalendarYears()
        {
            string command = @" WITH
                                MEMBER [Measures].[ParameterCaption] AS [Date].[Calendar].CURRENTMEMBER.MEMBER_CAPTION
                                MEMBER [Measures].[ParameterValue] AS [Date].[Calendar].CURRENTMEMBER.UNIQUENAME
                            SELECT
                                { [Measures].[ParameterCaption], [Measures].[ParameterValue] } ON COLUMNS,
                                { [Date].[Calendar].[Calendar Year] } ON ROWS
                            FROM
                                [Adventure Works]";

            return GetData(command).ToDictionary("[Measures].[ParameterValue]", "[Measures].[ParameterCaption]");
        }

        private static DataTable GetData(string command)
        {
            DataSet ds = new DataSet();

            using (AdomdConnection conn = new AdomdConnection("Data Source=localhost"))
            {
                conn.Open();
                using (AdomdCommand cmd = new AdomdCommand(command, conn))
                {
                    AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
                    adapter.Fill(ds);
                }
                conn.Close();
            }

            return ds.Tables[0];
        }
    }
}

The ReportData.CalendarYears method passes the MDX statement to the GetData method, which in turn connects to the database, executes the command, and fills a DataSet with the results. The GetData method then returns the first DataTable in the DataSet.

The ReportData.CalendarYears method converts the DataTable into a Dictionary<string, string> by calling a ToDictionary DataTable extension method. It specifies which columns should be used for the key and value data by specifying the respective column names.

Here’s the ToDictionary Datable extension method –

using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Charts.Reports
{
    public static class DataTableExtensionMethods
    {

        public static Dictionary<string, string> ToDictionary(this DataTable dt, string keyColumnName, string valueColumnName)
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>();

            int keyColumn = dt.Columns.IndexOf(keyColumnName);
            int valueColumn = dt.Columns.IndexOf(valueColumnName);

            foreach (DataRow row in dt.Rows)
            {
                dictionary.Add(row[keyColumn].ToString(), row[valueColumn].ToString());
            }

            return dictionary;
        }
    }
}

I’m not going to demonstrate constructing the view. The view will request the parameter list data using an Ajax Get request. And when the user has made their selection, will use Ajax Post requests to get the chart data based on the selected key values. The selected key values will be used to construct a MDX query – in order to filter the results set. Here’s an example of such a query, which has a WHERE clause restricting the results to the years 2012 and 2013 –

SELECT
    NON EMPTY [Product].[Product Categories].[Category] ON ROWS,
    {[Measures].[Internet Sales Amount]} ON COLUMNS
FROM
    [Adventure Works]
WHERE
    {[Date].[Calendar].[Calendar Year].&[2012],[Date].[Calendar].[Calendar Year].&[2013]}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s