将CellSet Adodm MDX查询结果集序列化为JSON

• 下载源文件 - 10.4 KB

引言

将 MDX 查询执行到 Microsoft Analysis Service 并获得 CellSet，然后使用标准的 JsonConverter 和 JsonResolver 将其序列化为 JSON。

背景

我们可以使用 MDX 从 .NET C# 查询 Microsoft Analysis Service，并获得标准的数据读取器 (AdomdDataReader) 或 CellSet (Adodm.Client.CellSet)。虽然数据读取器是获取平面数据响应的便捷方式，但 CellSet 是我们需要用来获取轴信息，以及所有必要信息以人类可读形式呈现到 UI 的内容（例如，带有可展开层级的透视网格）。直接将 Adodm.Client.CellSet 序列化为 JSON，可以让我们为客户端 Angular、React 或其他应用程序创建标准的 WebAPI 响应。

依赖项

• Microsoft Analysis Service，在示例中，我们使用了 AdventureWorks 数据库。
• NuGet 包 Unofficial.Microsoft.AnalysisServices.AdodmClient
• NewtonSoft.Json

.NET 中执行 MDX 查询

首先，让我们执行一个 MDX 查询到 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();
  }
}

在连接打开时，我们可以读取 CellSet、Columns Axis 和 Rows Axis 以及相关的单元格值。出于演示目的，我们仅将 cellset 中的内容输出到控制台。

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

将 CellSet 序列化为 JSON，还是不序列化？

我们直接尝试将 CellSet 转换为 JSON 怎么样？这行不通，因为 CellSet a) 充满了循环引用，b) 充满了我们不需要的内部属性，其中一些甚至会导致错误。虽然理论上我们可以配置 JsonConvert 来忽略或避免这些，但我们最终还是会得到一个包含大量不必要数据的 JSON，导致 JSON 非常庞大。我们还是试试看会发生什么。

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

上面的代码会惨败，并给出

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

好吧，我们可以谷歌一下——让我们配置 JsonConvert 来忽略自引用循环。

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

这也将失败，例如，我可能会收到关于各种形式的 GUID 对象引用不正确的错误，或者如果你的立方体中有挖掘数据集，它会抛出

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

修复 JSON 序列化

CellSet 包含很多我们不需要的东西，所以我们只需要告诉 JSON 转换器我们想要输出什么，以及我们想要忽略什么。如果这是我们自己的类，我们可以在 CellSet 的属性上添加很多 JSON 属性，但由于 CellSet 不是我们的代码，我们需要在外部指示 JSON 转换器。

我们将以一种非常简单的方式来做，虽然有更优雅的代码版本，但下面的代码可以完成任务。

• 我们需要创建一个新的类 CellSetContractResolver，它继承自 Newtonsoft.Json.Serialization.DefaultContractResolver。
• 添加对 CreateProperty 的重写，它会在序列化期间为每个属性标记 Ignore = true 或 Ignore = False。
• 在序列化期间，我们将查看属性名称及其声明属性，并将其与我们应该序列化的属性字典进行比较。如果我们找到字典中的条目，我们将标记该属性进行序列化；如果属性与我们的字典不匹配，我们将忽略它。

    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;

        }

    }

现在我们只需声明新的 ContractResolver，将所有我们想要包含在序列化中的属性名称和声明类型添加到其中，并将我们的 ContractResolver 提供给 JSON 转换器。

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

太棒了！现在它奏效了！

CellSet 单元格 JSON 被序列化为对象，但我们想要数组或数组

CellSet 包含 Cells，它看起来像一个二维数组，但实际上不是。它只是一个数组，通过重写 Get 方法，可以通过 [col,row] 索引返回单元格值。（反编译 CellSet CellCollection 来看看那里发生了什么——很有趣）。

无论如何，JSON 序列化器会将每个单元格序列化为一个对象，看起来会是这样的：

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

显然，我们不想要这样。它太大了，而且我们还想通过 [col,row] 来引用每个单元格。我们想要的是这样的：

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

基本上，就是数组的数组的值。我们需要一个自定义转换器，它会将 Cells 转换为字符串数组的数组（是的，字符串，因为 MDX 单元格在技术上是维度的交集，所以它可以包含任何东西，整数、小数、日期、字符串、鲨鱼，应有尽有——都在那里！）。

• 让我们创建一个新的类 CellSetJsonConverter，它继承自 JsonConverter。
• 在构造函数中，我们需要告诉我们有多少列和多少行，因为当我们命中 CellSet 对象的 Cells 属性时，这些计数不容易获得。

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.");
        }

    }

并告诉 JsonConvert 使用我们新的转换器。所以最终将 CellSet 序列化为格式精美的 JSON 的代码是：

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

附注

考虑一下我们可以针对 AdventureWorks OLAP 数据库运行的 MDX。

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]

这将在轴 0（列）上返回 3 个层级的成员，第一个是 Calendar Year，我们期望是 2013 年，第二个是 Sales Territories，第三个是实际的度量（两个），因此出现了额外的成员级别。我们还在 Axis 1 上有两层：Currency 和 Product Category。我们想在 JSON 中输出轴数据，详细说明所有列数据和所有行标题，以及单元格本身作为数组的数组。这样，我们就可以构建一个网格或透视表，让任何用户都惊叹不已。

历史

• 2019 年 11 月 29 日：初始版本
• 2019 年 12 月 1 日：更新了项目文件，缺少 nuget 包配置