Serializing CellSet Adodm MDX Query Result Set to JSON

• Download source files - 10.4 KB

Introduction

Execture MDX query against Microsoft Analysis Service into CellSet and serialize native Adomd.Client.CellSet object it into JSON using standard JsonConverter and JsonResolver.

Background

We can query Microsoft Analysis Service from .NET C# using MDX and we get back standard data reader (AdomdDataReader) or CellSet (Adodm.Client.CellSet). While data reader is a very convenient way to get flat data response, CellSet is what we need to use to get Axis information with all the necessary information to present it on the UI in a human form (for example, Pivoted Grid with expandable hierarchies). Serializing Adodm.Client.CellSet directly into JSON allows us to create your standard response from WebAPI for the client Angular or React or whatever, application to consume.

Dependencies

• Microsoft Analysis Service obviously, in the example, we are using AdventureWorks database
• NuGet package Unofficial.Microsoft.AnalysisServices.AdodmClient
• NewtonSoft.Json

Executing MDX Queries from .NET

First, let's execute a MDX query against Microsoft Analysis Service.

using Microsoft.AnalysisServices.AdomdClient;


static void CellSetExec(string Query, string ConnectionString) {    
  using (AdomdConnection conn = new AdomdConnection(ConnectionString))
  {
    conn.Open();
    AdomdCommand cmd = new AdomdCommand(Query, conn);
    CellSet cs = cmd.ExecuteCellSet();
    conn.Close();
  }
}

While the connection is open, we can read data from CellSet, Columns Axis and Rows Axis and associated Cell values. For demonstration purposes, let's just output what we have in the cellset onto the console output.

static void CellSetToConsole(string Query, string ConnectionString)
        {
            StringBuilder builder = new StringBuilder();

            using (AdomdConnection conn = new AdomdConnection(ConnectionString))
            {
                conn.Open();
                AdomdCommand cmd = new AdomdCommand(Query, conn);

                CellSet cs = cmd.ExecuteCellSet();        //execute MDX into cellset 

                TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;    // that's our 
                                                                 // tuples on the column axis
                for (int i = 0; i < cs.Axes[0].Set.Hierarchies.Count; i++)  // if we have 
                        // crossjoin we will have multiple hierarchies on the Column Axis, 
                        // let's iterate through all of them
                {
                    List<string> vals = new List<string>();
                    foreach (Microsoft.AnalysisServices.AdomdClient.Tuple column 
                              in tuplesOnColumns)    // iterate through every tuple 
                                                     // and add caption to the vals array
                    {
                        vals.Add(column.Members[i].Caption);
                    }
                    builder.AppendLine(string.Join(",", vals));    //add comma delimited 
                                                                   //line to the output
                }
                
                TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;                
                for (int row = 0; row < tuplesOnRows.Count; row++) // here we need to 
                                                                   // iterate for all rows
                {
                    List<string> vals = new List<string>();

                    for (int i = 0; i < cs.Axes[1].Set.Hierarchies.Count; i++) // each row 
                                  // will have Row Axis and if we have cross join in the 
                                  // query we will have multiple levels on the rows
                    {
                        vals.Add(tuplesOnRows[row].Members[i].Caption);//output each caption 
                                                            // of each attribute on each level 
                    }                    
                    for (int col = 0; col < tuplesOnColumns.Count; col++)
                    {
                        vals.Add(cs.Cells[col, row].FormattedValue); //add each FormattedValue
                                                                  // to the row array of values 
                    }
                    builder.AppendLine(string.Join(",",vals));    // add comma delimited line
                }                
                conn.Close();
            }
            Console.Write(builder.ToString());
        }

Serializing CellSet into JSON, or Not

How about we just try to convert CellSet into JSON? That won't work because CellSet is a) full of circular references b) full of internal properties that we don't need and some of which will even cause an error. Both we could in theory configure JsonConvert to ignore or avoid, however we will still end up with a JSON full of unnecessary data, making the JSON really really fat. Let's try anyway just to see what would happen:

static void CellSetToJsonWillFail(string Query, string ConnectionString) {
            using (AdomdConnection conn = new AdomdConnection(ConnectionString))
            {
                conn.Open();
                AdomdCommand cmd = new AdomdCommand(Query, conn);

                CellSet cs = cmd.ExecuteCellSet();
                JsonConvert.SerializeObject(cs);
                conn.Close();
            }
        }

The above will fail miserably with:

Self referencing loop detected for property 'ParentConnection' with type 
'Microsoft.AnalysisServices.AdomdClient.AdomdConnection'. Path 
'Axes[0].Set.Hierarchies[0].ParentDimension.ParentCube.ParentConnection.Cubes[0]'.

Ok, we can Google - let's configure JsonConvert to ignore Self Referencing Loop:

static void CellSetToJsonWillFail(string Query, string ConnectionString) {
            using (AdomdConnection conn = new AdomdConnection(ConnectionString))
            {
                conn.Open();
                AdomdCommand cmd = new AdomdCommand(Query, conn);

                CellSet cs = cmd.ExecuteCellSet();
                var settings = new JsonSerializerSettings()
                {                    
                    ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                };

                JsonConvert.SerializeObject(cs, settings);
                conn.Close();
            }
        }

That will also fail on just about anything, for example, I might get an error about incorrect GUID object referencing in various forms, or if you have a mining data set in the cube, it will throw:

Error getting value from 'Value' on 'Microsoft.AnalysisServices.AdomdClient.Property'.

Fixing JSON Serialization

CellSet contains a lot of stuff we don't need, so let's just tell JSON converter what we want in the output and what we want to ignore. If it was our own class, we could add a whole bunch of JSON attributes to properties of CellSet, but because CellSet is not our code, we need to instruct JSON converter externally.

We will do it in a very simplistic way, there are a lot more elegant versions of the same code, but the below will to the trick.

• We need to create a new class CellSetContractResolver, which inherits from Newtonsoft.Json.Serialization.DefaultContractResolver
• Add override for CreateProperty which will tag every property with Ignore = true or Ignore = False during serialization
• During serialization, we will look at the property name and its declaring property and compare it with the dictionary of properties we should serialize. If we find an entry in the dictionary, we will tag the property for serialization, if property does not match our dictionary, we will ignore it.

public class CellSetContractResolver : DefaultContractResolver
{
    /// <summary>
    /// <string> - Declaring type
    /// <List<string>> - list of property names to include
    /// </summary>
    Dictionary<string, List<string>> _include = new Dictionary<string, List<string>>();
    public void AddInclude(string DeclaringTypeName, List<string> PropertyNames) {
        _include[DeclaringTypeName] = PropertyNames;
    }

    protected override JsonProperty CreateProperty
              (MemberInfo member, MemberSerialization memberSerialization)
    {
        JsonProperty property = base.CreateProperty(member, memberSerialization);
        if (_include.ContainsKey((property.DeclaringType) == null ?
                                 "" : property.DeclaringType.Name) &&
            _include[(property.DeclaringType) == null ?
                    "" : property.DeclaringType.Name].Contains(property.PropertyName))
        {
            property.Ignored = false;
        }
        else {
            property.Ignored = true;
        }
        return property;

    }

}

Now we simply declare new ContractResolver, add all propertynames and declaring types we want to include into serialization and provide our ContractResolver to the JSON converter.

static void CellSetToJsonWillNowWork(string Query, string ConnectionString) {
            using (AdomdConnection conn = new AdomdConnection(ConnectionString))
            {
                conn.Open();
                AdomdCommand cmd = new AdomdCommand(Query, conn);

                CellSet cs = cmd.ExecuteCellSet();

                var contractResolver = new CellSetContractResolver();
                // we want Axes and Cells to be serialized from the CellSet
                contractResolver.AddInclude("CellSet", new List<string>() {
                    "Axes",
                    "Cells"
                });

                //In the Asix lets Serialize Set and Name properties
                contractResolver.AddInclude("Axis", new List<string>() {
                    "Set",
                    "Name"
                });

                //... and so on, whatever we need to include in the serialized JSON
                var settings = new JsonSerializerSettings()
                {
                    ContractResolver = contractResolver,
                    ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                };

                string output = JsonConvert.SerializeObject(cs, settings);
                conn.Close();
            }
        }

Hooray! It now works!

CellSet Cells JSON Serializes into Objects, But We Want an Array or Arrays

CellSet contains Cells which made to look like a two dimensional array, in reality, it's not. Underneath it, just an array with an override on Get to return a cell value by [col,row] index. (Decompile CellSet CellCollection to see what is happening there - it's good fun).

Anyhow, JSON serializer will serialize each cell into its own object, which will look something like:

"Cells": [
{
    "Value" : null,
    "FormattedValue": null
},
{
  "Value" : 1,
  "FormattedValue": "$1.00"
}
.... millions of cells ...
]

Obviously, we don't want that. It's too big and also we would like to reference each cell by [col,row]. What we want is something like:

"Cells": [[null,null],[1,1],[2,2],[null,1].....]

Basically, array of array of values. What we need is a Custom Converter, which will convert Cells into an array of array of strings (yes strings, because MDX cell is techincally is an intersection of dimensions so it can contain just about anything, int, decimals, dates, strings, sharks, you name it - it's all in there).

• Let's create a new class CellSetJsonConverter which inherits from JsonConverter
• In a constructor, we need to tell how many cols and how many rows we have, since these counts are not easily available when we hit Cells property of CellSet object in the serialization.

public class CellSetJsonConverter : JsonConverter         
    {
        int _cols;
        int _rows;
        public CellSetJsonConverter(int cols, int rows) {
            this._cols = cols;
            this._rows = rows;
        }

        public override bool CanConvert(Type objectType)
        {
            return (objectType == typeof(CellCollection));
        }
        public override bool CanWrite
        {            
            get { return true; }
        }

        public override void WriteJson
               (JsonWriter writer, object value, JsonSerializer serializer)
        {
            CellCollection cells = (CellCollection)value;
            
            List<string[]> rows = new List<string[]>();
            for (int i = 0; i < _rows; i++) {
                string[] row = new string[_cols];
                for (int m = 0; m < _cols; m++) {
                    if (cells[m, i].Value != null)
                    {
                        row[m] = cells[m, i].Value.ToString();
                    }
                }
                rows.Add(row);
            }
            writer.WriteRawValue(JsonConvert.SerializeObject(rows));
        }
        public override object ReadJson
        (JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
        {
            throw new NotImplementedException("Unnecessary because CanRead is false. 
                                               The type will skip the converter.");
        }

    }

And let's tell JsonConvert to use our new converter. So the final piece of code that Serializes CellSet into a lovely formatted JSON is:

static void CellSetExec(string outputFile, string Query, string ConnectionString) {        
            using (AdomdConnection conn = new AdomdConnection(ConnectionString))
            {
                conn.Open();
                AdomdCommand cmd = new AdomdCommand(Query, conn);
                
                CellSet cs = cmd.ExecuteCellSet();

                var contractResolver = new CellSetContractResolver();

                contractResolver.AddInclude("CellSet", new List<string>() {
                    "Axes",
                    "Cells"
                });
                contractResolver.AddInclude("Axis", new List<string>() {
                    "Set",
                    "Name"
                });
                contractResolver.AddInclude("Set", new List<string>() {
                    "Hierarchies",
                    "Tuples"
                });
                contractResolver.AddInclude("Hierarchy", new List<string>() {
                    "Caption",
                    "DefaultMember",
                    "Name"
                });
                contractResolver.AddInclude("Tuple", new List<string>() {
                    "Members",
                    "TupleOrdinal"
                });
                contractResolver.AddInclude("Member", new List<string>() {
                    "Name",
                    "Caption",
                    "DrilledDown",
                    "LevelName"
                });
                
                var settings = new JsonSerializerSettings()
                {
                    ContractResolver = contractResolver,
                    ReferenceLoopHandling =  ReferenceLoopHandling.Ignore
                };
                settings.Converters.Add(new CellSetJsonConverter
                         (cs.Axes[0].Set.Tuples.Count, cs.Axes[1].Set.Tuples.Count));
                
                string trySerializae = JsonConvert.SerializeObject
                                       (cs, Formatting.Indented, settings);
                System.IO.StreamWriter wr = new System.IO.StreamWriter(outputFile);
                wr.Write(trySerializae);
                wr.Close();

                conn.Close();
            }            
        }

Side Note

Consider the MDX which we can run against the AdventureWorks OLAP database.

SELECT 
    NONEMPTY(
        NONEMPTYCROSSJOIN
        (
            DrillDownMember(    
                HIERARCHIZE(
                    [Date].[Calendar].[Calendar Year]
                ),
                HIERARCHIZE({
                    [Date].[Calendar].[Calendar Year].&[2013]
                })
            ),
            
            NONEMPTY((HIERARCHIZE([Sales Territory].[Sales Territory].[Group]),{
                [Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]
            }))
        )
            
    ) ON COLUMNS,
    NONEMPTYCROSSJOIN
        (
            NONEMPTY(HIERARCHIZE([Source Currency].[Source Currency].[Source Currency])) 
            ,
            DrillDownMember(
                NONEMPTY(HIERARCHIZE([Product].[Product Categories].[Category])),
                HIERARCHIZE({
                    [Product].[Product Categories].[Category].&[3]
                })
            )
    ) ON ROWS
FROM [Adventure Works]

This returns 3 levels of hierarchy members on the axis 0 (columns), first being Calendar Year where we have expected 2013, second is Sales Territories, and third are the actual measures (two of them), hence the additional level of members on the tupleset. We also have two levels on Axis 1, Currency and Product Category. What we want to do is to output into JSON, Axis data, detailing all column data and all row headers, and Cells themselves as array of arrays. This way, we can build a grid or pivot it into a wonderstuff to the amazement of any user.

History

• 29^th November, 2019: Initial version
• 1^st December, 2019: Updated project file, nuget package config was missing