.Net C# Algorithm to Parse JSON return from cellset query

Post Reply
User avatar
WilliamSmith
Posts: 37
Joined: Tue Dec 13, 2022 8:54 pm
OLAP Product: TM1 / PA / PAx / PAW
Version: TM1 11
Excel Version: 365

.Net C# Algorithm to Parse JSON return from cellset query

Post by WilliamSmith »

(Mod, please move this to the appropriate forum)

Hi all,

I am .Net guy, and I've recently found myself with professional opportunity to be the primary TM1 guy in our operation.

I've been playing around with the TM1 REST API, and been very interested in the most efficient way to parse cellsets from MDX / view queries (especially very large ones).

Below is a class that will convert the raw cellset JSON returned from the TM1 API into an object, that object can them be converted into a System.Data.DataTable object.

The ToDataTable_old() method is serial, the newer ToDataTable() is fully parallel and can take advantage of CPU multi-threading.

My purpose for converting to System.Data.DataTable is to have an easy SqlBulkCopy ingest into MS SQLServer.

On my machine; the serial parse into object then into DataTable would run at ~2,000 cells per second, and the newer parallel method parses at ~130,000 cells per second.

I am open to suggestions, discussion and collaboration. I hope someone finds this helpful :)

Code: Select all

using System.Data;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace AndromedaTM1Sharp
{
    public class CellsetJSONParser
    {
        public static CellSetModel? ParseIntoObject(string json)
        {
            var model = JsonSerializer.Deserialize<CellSetModel>(json);

            return model;
        }

        public class CellSetModel
        {
            [JsonPropertyName("@odata.context")]
            public string? MetaData { get; set; }

            [JsonPropertyName("ID")]
            public string? Id { get; set; }

            [JsonPropertyName("Axes")]
            public List<Axes>? Axes { get; set; }

            [JsonPropertyName("Cells")]
            public List<Cells>? Cells { get; set; }

            public DataTable? ToDataTable_old()
            {
                long ordinalCounter = 0;
                var dt = new DataTable();

                Axes?[1]?.Hierarchies?.ForEach(x =>
                {
                    dt.Columns.Add(x.Name);
                });

                Axes?[0]?.Tuples?.ForEach(x =>
                {
                    dt.Columns.Add(x?.Members?[0].Name);
                });

                #pragma warning disable CS8604 // Possible null reference argument.
                for (int i = 0; i < Axes?[1]?.Tuples?.Count; i++)
                {
                    var row = dt.NewRow();

                    for (int j = 0; j < Axes?[1]?.Tuples?[i]?.Members?.Count; j++)
                    {
                        row[Axes?[1]?.Hierarchies?[j]?.Name] = Axes?[1]?.Tuples?[i]?.Members?[j]?.Name;
                    }

                    for (int j = 0; j < Axes?[0]?.Tuples?.Count; j++)
                    {

                        row[Axes?[0]?.Tuples?[j]?.Members?[0]?.Name] = Cells?.Where(x => x.Ordinal.Equals(ordinalCounter))?.FirstOrDefault()?.Value;

                        ordinalCounter++;
                    }

                    dt.Rows.Add(row);
                }
                #pragma warning restore CS8604 // Possible null reference argument.

                return dt;
            }

            public DataTable ToDataTable()
            {
                var dt = new DataTable();

                dt.Columns.Add("rowIndex", typeof(int));

                Axes?[1]?.Hierarchies?.ForEach(x =>
                {
                    dt.Columns.Add(x.Name);
                });

                Axes?[0]?.Tuples?.ForEach(x =>
                {
                    dt.Columns.Add(x?.Members?[0].Name);
                });

#pragma warning disable CS8604 // Possible null reference argument.
#pragma warning disable CS8602 // Dereference of a possibly null reference.

                int hierarchyColumns = Axes[1].Hierarchies.Count;

                Parallel.ForEach(Axes[1].Tuples, x =>
                {
                    lock (dt)
                    {
                        int rowPosisiton = (int)x.Ordinal;

                        var row = dt.NewRow();

                        row[0] = rowPosisiton;

                        for (int j = 0; j < hierarchyColumns; j++)
                        {
                            row[j + 1] = x.Members[j].Name;
                        }

                        dt.Rows.Add(row);
                    }
                });

                dt = dt.AsEnumerable().OrderBy(x => x.Field<int>("rowIndex")).CopyToDataTable();
                dt.Columns.Remove("rowIndex");

                int totalColumns = dt.Columns.Count;
                int cellColumns = dt.Columns.Count - Axes[1].Hierarchies.Count;

                Parallel.ForEach(Cells, x =>
                {
                    int rowPosisiton = (int)x.Ordinal / cellColumns;

                    int columnPosition = (int)(x.Ordinal % cellColumns) + hierarchyColumns;

                    lock (dt) dt.Rows[rowPosisiton][columnPosition] = x.Value;
                });

#pragma warning restore CS8602 // Dereference of a possibly null reference.
#pragma warning restore CS8604 // Possible null reference argument.

                return dt;
            }
        }

        public class Axes
        {
            [JsonPropertyName("Ordinal")]
            public long Ordinal { get; set; }

            [JsonPropertyName("Cardinality")]
            public long Cardinality { get; set; }

            [JsonPropertyName("Hierarchies")]
            public List<Hierarchies>? Hierarchies { get; set; }

            [JsonPropertyName("Tuples")]
            public List<Tuples>? Tuples { get; set; }
        }

        public class Hierarchies
        {
            [JsonPropertyName("@odata.etag")]
            public string? MetaData { get; set; }

            [JsonPropertyName("Name")]
            public string? Name { get; set; }
        }

        public class Tuples
        {
            [JsonPropertyName("Ordinal")]
            public long Ordinal { get; set; }

            [JsonPropertyName("Members")]
            public List<Members>? Members {get;set;}
        }

        public class Members
        {
            [JsonPropertyName("Name")]
            public string? Name { get; set; }
        }

        public class Cells
        {
            [JsonPropertyName("Ordinal")]
            public long Ordinal { get; set; }

            [JsonPropertyName("Value"), JsonConverter(typeof(ObjectPrimitiveConverter))]
            public object? Value { get; set; }
        }

        private class ObjectPrimitiveConverter : JsonConverter<object>
        {
            //https://stackoverflow.com/questions/73695510/deserializing-a-data-member-that-could-be-int-or-string-c-sharp

            public override object? Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options) =>
                reader.TokenType switch
                {
                    JsonTokenType.String => reader.GetString(),
                    JsonTokenType.Number when reader.TryGetInt32(out var i) => i,
                    JsonTokenType.Number when reader.TryGetInt64(out var l) => l,
                    JsonTokenType.Number when reader.TryGetDouble(out var d) => d,
                    JsonTokenType.True => true,
                    JsonTokenType.False => false,
                    _ => throw new JsonException(),
                };
            public override void Write(Utf8JsonWriter writer, object value, JsonSerializerOptions options) =>
                JsonSerializer.Serialize(writer, value, value.GetType());
        }
    }
}
Last edited by WilliamSmith on Wed Feb 08, 2023 9:06 pm, edited 1 time in total.
User avatar
WilliamSmith
Posts: 37
Joined: Tue Dec 13, 2022 8:54 pm
OLAP Product: TM1 / PA / PAx / PAW
Version: TM1 11
Excel Version: 365

Re: .Net C# Algorithm to Parse JSON return from cellset query

Post by WilliamSmith »

TM1 endpoints:

Cube View:

Code: Select all

/api/v1/Cubes('" + cubeName + "')/Views('" + viewName + "')/tm1.Execute?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells($select=Ordinal,Value)
MDX:

Code: Select all

/api/v1/ExecuteMDX?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells($select=Ordinal,Value)
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: .Net C# Algorithm to Parse JSON return from cellset query

Post by jim wood »

Moved to the correct forum.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Post Reply