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.

 

 

Advertisements