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());
}
}
}