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]}

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.

 

 

Why I no longer suck at MDX

I used to suck at MDX. Don’t get me wrong – I knew enough to get by, and was able to compose queries when needed. But there was a certain amount of trial and error along the way, as conceptually I didn’t really understand all that was going on. I read a number of books on the subject – some good, some bad. I read many an article and blog post as well. And over the years I had a number of Eureka moments where the light-bulbs went on and everything started to make sense. I thought I’d share some of these Eureka moments with you, along with some basic definitions to put them into context.

What is a Member?

A member is an item in a dimension (Note. This is true, but not exact. I will be more exact later on in this post)

For example, [Aaron A. Allen] is a member of the AdventureWorks [Customer] dimension –

Member

And [Chain] is a member of the [Product] dimension –

Product Member

What is a Tuple?

A tuple is a slice of data from a cube.

We all did graphs at school where we drew an X and a Y axis and then plotted points. And when asked to express a point we did so as – (2, 5).

Now if each dimension is represented by an axis on our graph, and rather than having contiguous (a sequence of) numbers along each axis, we instead have the members of the dimension, then we could express a tuple in a similar manner. For example, if we drew a customer and a product axis then we could express a tuple as – ([Aaron A. Allen], [Chain]).

We use the term “slice” rather than “point” because a tuple typically defines a set of data points rather than a single point in our cube. More about this in the next section.

Eureka Moment 1 : The importance of default members

AdventureWorks has many more dimensions in addition to customer and product. Each can be thought of a a separate axis in our multi-dimensional graph. So let’s add the date dimension into the mix –

Date Member

We now have 3 dimensions – customer, product, and date – and can express a tuple as such – ([Aaron A. Allen], [Chain], [January 3, 2005])

Each of the 3 dimensions has a member explicitly identified in the tuple. However, in the previous example – ([Aaron A. Allen], [Chain]) – only the customer and product dimensions have a member explicitly identified. It is however still a valid tuple. The reason being is that a tuple has a member for each and every dimension, and where a member is not explicitly identified, the tuple will use a dimensions default member.

So, as a member for the date dimension is not explicitly specified the tuple uses the default member for the dimension. In this case the default member is [All Periods] and so  ([Aaron A. Allen], [Chain]) is implicitly ([Aaron A. Allen], [Chain], [All Periods]).

Which member we define as the default member for a dimension is therefore very important. It will affect the results of all MDX queries that do not explicitly specify a member for the dimension. And if the default member is any other than the “All” member, queries will not aggregate over the whole dimension when a member is not explicitly specified.

Eureka Moment 2 : Each dimension hierarchy has its own axis

I’ve purposely been a little inaccurate so far in saying that each dimension is represented by an axis on our graph. This is what I believed before I had eureka moment 2.

More accurately, each dimension has one or more dimension hierarchies, and each dimension hierarchy is represented by a separate axis on our graph.

In the case of the date dimension, what is actually shown in the image above is the [Date] hierarchy of the [Date] dimension. There are in fact 18 distinct hierarchies in the date dimension – shown below, 16 in the customer dimension, and 24 in product. There are 21 dimensions in total in the AdventureWorks 2014 model. I’m not counting up the total number of hierarchies, as I do have a life to live. Let’s just raise our hands in the air and say thank-you for default members.  And let’s raise our hands and say thank-you again, as the total number of members that can be explicitly specified in a tuple is limited to 128. The number of dimension hierarchies in AdventureWorks surely far exceeds this.

Date Hierachies

So correcting some of my statements above –

[Aaron A. Allen] is a member of the AdventureWorks [Customer] hierarchy, which is one of 16 hierarchies in the [Customer] dimension. It’s a little confusing having a hierarchy with the same name as the dimension. But it’s standard practice and you soon get used to it.

[Chain] is a member of the [Product] hierarchy, in the [Product] dimension.

[January 3, 2005] is a member of the [Date] hierarchy, in the [Date] dimension.

And we define a default member for each hierarchy rather than for each dimension.

The image above shows the [Fiscal] hierarchy of the [Date] dimension. [January 3, 2005] is a member of the [Fiscal] hierarchy. It is not necessarily the same as the [January 3, 2005] member of the [Date] hierarchy. It may have the same properties and may be sourced from the same field in the data warehouse, but this is not necessarily so. They are members of separate dimensions and so the former could, for example, be renamed [FY January 3, 2005] without affecting the latter.

[January 2005] is also a member of the [Fiscal] hierarchy. And so is [FY 2005] and [All Periods]. Aggregable hierarchies have at minimum 2 levels – with an “All” level being an aggregate of all members at the lowest level. A user-defined hierarchy may have many more levels as is the case with the [Fiscal] hierarchy. And each level contains one or more members which can be explicitly specified as the member to use for the hierarchy in question in a tuple. In our graph analogy, the fiscal axis can be thought of as having the following members – [All Periods], [FY 2005], [H2 FY 2005], [Q3 FY 2005], [January 2005], [January 1, 2005], [January 2, 2005], … , [FY 2006], [H1 FY 2006], etc.

When we write an MDX query, we typically identify a member using the [Dimension].[Hierarchy].[Member] syntax. Or in the case of user-defined hierarchies we identify a member using the [Dimension].[Hierarchy].[Level].[Member] syntax. This is to account for the possibility of having members at different levels in the hierarchy with the same name and/or key. A parent-child hierarchy, such as Employee, where there is a a parent key representing a relationship between the employee and their manager, may have this. Hence the need for [Level] to uniquely identify a member.

SELECT
FROM
 [Adventure Works]
WHERE
 [Date].[Date].[January 3, 2005] 

SELECT
FROM
 [Adventure Works]
WHERE
 [Date].[Fiscal].[Fiscal Year].[FY 2005]
Eureka Moment 3 – measures have their own axis

As mentioned above, each dimension hierarchy is represented by a separate axis on our graph. In addition, the measures should be thought of as an additional axis, with each measure being a member of the axis.

The AdventureWorks’ measures are shown below. They are grouped into measure groups, 3 of which – Internet Customers, Internet Order, and Internet Sales – I have expanded out to display the measures. Each of these measures is a member of [Measures], and as such one will be explicitly or implicitly specified in a tuple.

Measures

Please note that the measure groups are simply logical groupings for the measures. Ignore these groups. All measures in the cube are members of the single [Measures] axis.

Eureka Moment 4 – An MDX query simply defineS a tuple, or set of tuples

An MDX query is structured as follows –

SELECT [<axis_specification>
       [, <axis_specification>...]]
  FROM [<cube_specification>]
[WHERE [<slicer_specification>]]

It is comprised of zero or more axis specifications, a slicer specification, and a cube specification.

The axis and slicer specifications combine to define a tuple, or set of tuples. It does not matter whether a member is specified in an axis statement or in the slicer statement – the same tuple will be defined and same slice queried. So the following statements all query the same slice –

SELECT
FROM
 [Adventure Works]
WHERE
 ([Customer].[Customer].[Aaron A. Allen],
 [Delivery Date].[Date].[December 14, 2011],
 [Product].[Product].[Mountain-100 Silver, 44],
 [Measures].[Internet Sales Amount])
SELECT
 ([Customer].[Customer].[Aaron A. Allen],
 [Delivery Date].[Date].[December 14, 2011],
 [Product].[Product].[Mountain-100 Silver, 44],
 [Measures].[Internet Sales Amount]) ON 0
FROM
 [Adventure Works]
SELECT
 ([Customer].[Customer].[Aaron A. Allen], 
 [Delivery Date].[Date].[December 14, 2011]) ON 0,
 ([Product].[Product].[Mountain-100 Silver, 44]) ON 1
FROM
 [Adventure Works]
WHERE
 ([Measures].[Internet Sales Amount])

They statements do of course differ in terms of the fields displayed by the client.

MDX queries SHOULD Now BE easy to understand

As I said in the preamble. It took me a while to have the 4 eureka moments described above. I wish someone would have explained them to me from the outset. I would certainly have sweated less with project dead-lines looming.

I will now use the eureka moments to explain some basic MDX queries –

1. The most basic of queries –

SELECT
FROM
 [Adventure Works]

Result:

$80,450,596.98

No members are explicitly defined. The MDX query engine therefore constructs the tuple using the default members of all dimension hierarchies, and additionally the default member of [Measures] which is [Reseller Sales Amount]. Something like this – ([All Customers], [All Products], [All Dates], … , [Measures].[Reseller Sales Amount]).

2. Querying a different measure  –

SELECT
FROM
 [Adventure Works]
WHERE
 [Measures].[Internet Sales Amount]

Result:

$29,358,677.22

The [Internet Sales Amount] member of [Measures] is explicitly specified. The tuple will therefore use this member rather than the default.

3. Combining axis and slicer specifications –

SELECT
 [Date].[Fiscal Year].[FY 2010] ON 0
FROM
 [Adventure Works]
WHERE
 [Measures].[Internet Sales Amount]

Result:

FY 2010
$3,222,952.62

The [FY 2010] member of the [Date] dimension’s [Fiscal Year] hierarchy is combined with the [Internet Sales Amount] member of [Measures], to form the tuple.

4. Adding in another axis –

SELECT
 [Date].[Fiscal Year].&[2010] ON 0,
 [Customer].[Customer Geography].[Country].&[Canada] ON 1
FROM
 [Adventure Works]
WHERE
 [Measures].[Internet Sales Amount]

Result:

       FY 2010
Canada $143,251.54

I’ve added in another axis, explicitly defining the [Canada] member of the [Country] level in the [Customer Geography] hierarchy. This time the fiscal year and country members were expressed using their member keys rather than names, hence the use of the & before the keys.

5. Defining a set of tuples –

SELECT
 {[Date].[Fiscal Year].&[2010], [Date].[Fiscal Year].&[2011]} ON 0,
 [Customer].[Customer Geography].[Country].&[Canada] ON 1
FROM
 [Adventure Works]
WHERE
 [Measures].[Internet Sales Amount]

Result:

       FY 2010     FY 2011     
Canada $143,251.54 $625,180.65

The &[2010] and &[2011] members of the [Fiscal Year] hierarchy are combined into a set by enclosing them in curly brackets { … }. We are therefore defining 2 tuples, with each member of the set combining with the explicitly/implicitly defined members of the other hierarchies.

6. Specifying members for multiple hierarchies  –

SELECT
 {[Date].[Fiscal Year].&[2010], [Date].[Fiscal Year].&[2011]} ON 0,
 ([Customer].[Customer Geography].[Country].&[Canada], [Product].[Category].[Bikes]) ON 1
FROM
 [Adventure Works]
WHERE
 [Measures].[Internet Sales Amount]

Result:

             FY 2010     FY 2011
Canada Bikes $143,251.54 $625,180.65

This time, members for 2 hierarchies are explicitly defined in the second axis specification.

7. Specifying members for multiple hierarchies of the same dimension –

SELECT
 {[Date].[Fiscal Year].&[2011], [Date].[Fiscal Year].&[2012]} ON 0,
 ([Customer].[Customer Geography].[Country].&[Canada], [Product].[Category].[Bikes]) ON 1
FROM
 [Adventure Works]
WHERE
 ([Measures].[Internet Sales Amount], [Date].[Fiscal Semester of Year].&[FY H1])

Result:

             FY 2011     FY 2012
Canada Bikes $431,898.53 $114,215.44

A set of members for the [Date] dimension’s [Fiscal Year] hierarchy is specified on the 1st axis statement. In addition, a member for the [Date] dimension’s [Fiscal Semester of Year] hierarchy is specified on the slicer statement. This works as the tuple contains a member for each hierarchy in the cube, irrespective of whether the hierarchies are in the same dimension or not.

I will not go on, as I’m sure you’ve got the idea by now. This is not meant to be a MDX tutorial, as that’s the subject for a book rather than a blog post.

Hopefully, if you’re new to MDX, or are muddling through as I did, I hope my eureka moments will make things a little clearer for you.