Convert Microsoft ADOMD Cell Set to JSON

Introduction

Hi all, how are you all? This is me, Sibeesh Venu. Today I came across a requirement for converting Microsoft ADOMD cell sets to JSON. So I thought of sharing that with you all. I hope you will like it.

Please read this article in my blog.

Background

For the past few months I have been working with Microsoft ADOMD data sources. And I have written some article also that will describe the problems I have encountered so far. If you are new to ADOMD I strongly recommend that read my previous articles that you may find useful when you work with ADOMD data sources. You can find those article links here.

• How to Convert Microsoft ADOMD Data Source to JSON
• Convert CellSet to HTML Table and From HTML to JSON and to Array

Why

You might think, why am I again using the methods described in the preceding two articles. I will answer that. I have encountered some issues with those methods. When you use a data adapter or data reader as explained in the first link (How to Convert Microsoft ADOMD Data Source to JSON) you always get the values as normal values instead of formatted values. For example even if the value contains $ or %, you will always get values without those symbols. So your application won't let the user identify which one is currency or which one is %. In my case it was high chart and high maps. When the user hovers over a specific area, I need to show the measure values in the tooltip.

So in that case I was forced to use the cell set again, where there is an option that we can select the formatted value. I will show you that in my function.

Using the code

The following is the function that does what was explained above.

private string BuildBubbleMap(CellSet cst)
{
    try
    {
        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);
        string columnName = string.Empty;
        string fieldVal = string.Empty;
        //check if any axes were returned else throw error.
        int axes_count = cst.Axes.Count;
        if (axes_count == 0)
            throw new Exception("No data returned for the selection");

        //if axes count is not 2
        if (axes_count != 2)
            throw new Exception("The code support only queries with two axes");

        //if no position on either row or column throw error
        if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0))
            throw new Exception("No data returned for the selection");

        int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
        row_dim_count = 0;

        //Number of dimensions on the column
        col_dim_count = cst.Axes[0].Positions[0].Members.Count;

        //Number of dimensions on the row
        if (cst.Axes[1].Positions.Count > 0)
        {
            if (cst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = cst.Axes[1].Positions[0].Members.Count;
        }
        //Total rows and columns
        row_count = cst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
        col_count = cst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

        using (JsonWriter myJson = new JsonTextWriter(sw))
        {
            myJson.WriteStartArray();
            for (cur_row = 0; cur_row < row_count-1; cur_row++)
            {
                myJson.WriteStartObject();
                for (cur_col = 0; cur_col < col_count-1; cur_col++)
                {
                    //Looping for dimension headers
                    columnName = cst.Axes[1].Positions[cur_row].Members[cur_col].ParentLevel.ToString().Replace("{", "").Replace("}", "").Trim();
                    fieldVal = cst.Axes[1].Positions[cur_row].Members[cur_col].Caption.Replace(",", " ");
                    //If the value is null, I dont need that to be included
                    if ((columnName == null || columnName == "" || columnName.ToLower() == "undefined" || columnName.ToLower() == "null" ||
                        columnName.ToLower() == "(null)" || columnName.ToLower() == "unknown")||(fieldVal == null || fieldVal == "" ||
                        fieldVal.ToLower() == "undefined" || fieldVal.ToLower() == "null" ||
                        fieldVal.ToLower() == "(null)" || fieldVal.ToLower() == "unknown"))
                        break;
                    //Map expect the header as lat and lon, so here we are changing that.
                    if (columnName.ToLower() == "latitude")
                        columnName = "lat";
                    else if (columnName.ToLower() == "longitude")
                        columnName = "lon";

                    myJson.WritePropertyName(columnName);
                    myJson.WriteValue(fieldVal);
                }
                //Looping for measure headers
                myJson.WritePropertyName(cst.Axes[0].Positions[0].Members[0].Caption.Replace(",", " ").Trim());
                myJson.WriteValue(cst[cur_row].FormattedValue);
               //Please be noted that we are using FormattedValue here.
                myJson.WriteEndObject();
            }
            myJson.WriteEndArray();
        }
        cst = null;
        return sw.ToString();
    }
    catch (Exception)
    {
        cst = null;
        throw;
    }
}

You can see that this function expects the parameter cell set. Here we are finding the axis (Axis0 and Axis 1). According to my ADOMD query my dimensions are in Axis 1, so that I need to determine the caption for each dimension from the cell set. Once I determine the dimensions I am writing that to the StringBuilder using the method WritePropertyName() of the JSON Writer class. To use this class you must include the Newtonsoft DLL as in the following.

using Newtonsoft.Json;

Once that is over, I am writing the value for those dimensions using the WriteValue() method of the JSON writer class.

I am doing that for the measures also. You can see these implementations in my code.

Finally I am creating the proper JSON here and returning that.

To use this function you need to build the cell set first. You can do that as follows.

using (AdomdConnection conn = new AdomdConnection(myConnection))
{
    conn.Open();
    using (AdomdCommand cmd = new AdomdCommand(query, conn))
    {
    cmd.CommandTimeout = connectionTimeout;
    cst = cmd.ExecuteCellSet();
    }
 }

Once you get the cell set, just pass the cell set to the preceding function, it will return the JSON you need. You can easily bind it to any client-side tool (for example, High Chart and High Maps).

Conclusion

I hope someone finds this useful. Have happy coding experiences. Please provide your valuable suggestions and comments. Thanks in advance.

Kindest Regards,
Sibeesh Venu