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

Surfacing SSAS Data With Google Charts

In this post I’m going to demonstrate how to surface SSAS data in an MVC view using ADOMD.NET and Google Charts.

This is what I’m going to create –

charts

1. ASP.NET MVC Project

First step is to create a new ASP.NET MVC Web Application project.

Then add a reference to the ADOMD.NET client components library (microsoft.analysisservices.adomdclient.dll).

Unfortunately, the library is not available through NuGet – don’t ask me why. Instead we need to download and install the SQL Server feature pack from the following link –

https://www.microsoft.com/en-gb/download/details.aspx?id=42295

And then reference the library in the following folder –

C:\Program Files\Microsoft.NET\ADOMD.NET\120\Microsoft.AnalysisServices.AdomdClient.dll

2. ReportData data-access class

Next step is to create a Data folder and then add the following data-access class  –

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

namespace Charts.Data
{
    public class ReportData
    {
        public static List<object> SalesByYear()
        {
            string command = @" SELECT 
                                    NON EMPTY [Date].[Calendar].[Calendar Year] ON ROWS, 
                                    {[Measures].[Reseller Sales Amount], [Measures].[Internet Sales Amount]} ON COLUMNS
                                FROM 
                                    [Adventure Works]";

            return GetData(command);
        }

        public static List<object> InternetSalesByCategory()
        {
            string command = @" SELECT 
                                    NON EMPTY [Product].[Product Categories].[Category] ON ROWS, 
                                    {[Measures].[Internet Sales Amount]} ON COLUMNS
                                FROM 
                                    [Adventure Works]
                                WHERE
                                    [Date].[Calendar].[Calendar Year].&[2013]";

            return GetData(command);
        }

        public static List<object> ResellerSalesByCategory()
        {
            string command = @" SELECT 
                                    NON EMPTY [Product].[Product Categories].[Category] ON ROWS, 
                                    {[Measures].[Reseller Sales Amount]} ON COLUMNS
                                FROM 
                                    [Adventure Works]
                                WHERE
                                    [Date].[Calendar].[Calendar Year].&[2013]";

            return GetData(command);
        }

        private static List<object> 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 ConvertDataTableToObjectList(ds.Tables[0]);
        }

        private static List<object> ConvertDataTableToObjectList(DataTable dt)
        {
            List<object> data = new List<object>();
            int columnCount = dt.Columns.Count;

            string[] columnObject = new string[columnCount];
            for (int i = 0; i < columnCount; i++)
            {
                string name = dt.Columns[i].ColumnName.Replace(".[MEMBER_CAPTION]", "");
                string[] nameParts = name.Split(new char[] { '.' });

                columnObject[i] = nameParts[nameParts.Count() - 1].Replace("[", "").Replace("]", "");
            }
            data.Add(columnObject);

            foreach (DataRow row in dt.Rows)
            {
                data.Add(row.ItemArray);
            }

            return data;
        }
    }
}

There are 3 data-access methods, 1 per chart –

  1. SalesByYear
  2. InternetSalesByCategory
  3. ResellerSalesByCategory

Each of these methods defines an MDX statement, and then passes the statement to the GetData method.

The GetData method opens a connection to the SSAS database, and then executes the MDX statement, utilizing the AdomdDataAdapter.Fill method to populate a DataSet with the results.

The DataSet contains a collection of DataTable objects. The code assumes that the first in the collection is populated with the results from the query. I’m not sure under what circumstances there would be more than one DataTable?

The ConvertDataTableToObjectList method converts the results into a format that Google charts will understand, populating a list of objects with column names and values from the DataTable.

The column names contain dimension, hierarchy, level information. And additionally are surrounded by curly brackets, and may be suffixed with “‘[MEMBER_CAPTION]”. For example, here are the column names from the 1st query –

  • {[Date].[Calendar].[Calendar Year].[MEMBER_CAPTION]}
  • {[Measures].[Reseller Sales Amount]}
  • {[Measures].[Internet Sales Amount]}

The ConvertDataTableToObjectList strips off the superfluous information, leaving the level names –

  • Calendar Year
  • Reseller Sales Amount
  • Internet Sales Amount

3. Home Controller

using Charts.Data;
using System.Collections.Generic;
using System.Web.Mvc;

namespace Charts.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public JsonResult SalesByYear()
        {
            List<object> data = ReportData.SalesByYear();
            return Json(data, JsonRequestBehavior.AllowGet);
        }

        public JsonResult InternetSalesByCategory()
        {
            List<object> data = ReportData.InternetSalesByCategory();
            return Json(data, JsonRequestBehavior.AllowGet);
        }

        public JsonResult ResellerSalesByCategory()
        {
            List<object> data = ReportData.ResellerSalesByCategory();
            return Json(data, JsonRequestBehavior.AllowGet);
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

The following action methods have been added to the Home controller –

  • SalesByYear
  • InternetSalesByCategory
  • ResellerSalesByCategory

Each of the methods gets a List<object> containing the chart data from the appropriate data access method, and then returns this data serialized as JSON.

4.Home Index View

@{
    ViewBag.Title = "Home Page";
}

<h1>Google Charts Demo</h1>

<div class="row">
    <div class="col-md-12">
        <div class="chart"
             data-url="/Home/SalesByYear"
             data-chart-type="Line"
             data-options='{ "title": "Sales by Year", "vAxis": { "format": "$#,###" } }'>
        </div>
    </div>
</div>
<div class="row">
    <div class="col-md-6">
        <div class="chart"
             data-url="/Home/InternetSalesByCategory"
             data-chart-type="Pie"
             data-options='{ "title": "2013 Internet Sales by Category", "is3D": "true" }'>
        </div>
    </div>
    <div class="col-md-6">
        <div class="chart"
             data-url="/Home/ResellerSalesByCategory"
             data-chart-type="Pie"
             data-options='{ "title": "2013 Reseller Sales by Category", "is3D": "true" }'>
        </div>
    </div>
</div>

@section scripts
{
    <script type="text/javascript"
            src="https://www.google.com/jsapi?autoload={
            'modules':[{
              'name':'visualization',
              'version':'1',
              'packages':['corechart']
            }]
          }"></script>

    <script type="text/javascript">
        $().ready(function () {
            google.setOnLoadCallback(drawCharts);
        });

        function drawCharts() {
            var chart = $('div.chart');
            $.each(chart, drawChart);
        }

        function drawChart() {
            var $this = $(this);
            var url = $this.data('url');
            var chartType = $this.data('chart-type');
            var options = $this.data('options');
            var chartArea = $this[0];

            $.get(url)
             .done(function (jsonArray) {
                 var chart;
                 var data = google.visualization.arrayToDataTable(jsonArray);
                 var defaultOptions = {
                     title: 'Chart',
                     chartArea: { left: '100' },
                     height: '400'
                 };
                 $.extend(true, defaultOptions, options);

                 switch (chartType) {
                     case "Pie":
                         chart = new google.visualization.PieChart(chartArea);
                         break;
                     default:
                         chart = new google.visualization.LineChart(chartArea);
                 }

                 chart.draw(data, defaultOptions);
             });
        }
    </script>
}

After the DOM has loaded, the google.setOnLoadCallback(drawCharts) function is called, ensuring that the Google API is fully loaded prior to calling the drawCharts function.

The drawCharts function gets all <div> elements with the “chart” class, and for each calls the drawChart function.

The drawChart function interrogates the target <div> elements data- attributes to get the following information –

  • data-url
    • the controller action to call to get the data for the chart
  • data-chart-type
    • currently either “Pie” or “Line”, but can easily be extended to include other Google chart types.
  • data-options
    • A Json string representing an options object. Used to specify chart specific options.

The drawChart function then makes an Ajax get request to the url, specifying a callback function which receives the JSON result.

The callback function calls the google.visualization.arrayToDataTable function to convert the JSON result to a google.visualization.DataTable object.

It then uses the chart specific options to extend a default set of options, and then initializes either a Pie or Line chart before drawing the chart based on the data and the options.

And bobs your uncle.