在 C# 中从 MDX 查询/CellSet 中检索度量的数据类型 [英] Retrieving Data Type of Measures from an MDX Query/CellSet in C#

问题描述

(我使用的是 C# 4.0.我通过 Microsoft.AnalysisServices.AdomdClient 命名空间/库连接到数据立方体.)

(I am using C# 4.0. I am connecting to a data cube via the Microsoft.AnalysisServices.AdomdClient namespace/library.)

如果我理解正确，MDX SELECT 语句中只有一个轴将包含度量；其他的将基于其他维度.

If I understand correctly there is exactly one axis in an MDX SELECT statement that will contain measures; the others will be based on the other dimensions.

我的直接任务(我相信更大的任务无关紧要)是给出一个 MDX SELECT 语句，这样第一个轴包含所述度量，我需要以编程方式确定与每个度量相关的所有单元格.

My immediate task (the larger task is irrelevant, I believe) is given an MDX SELECT statement such that the first axis contains said measures, I need to programmatically determine the data type of the all of the cells pertaining to each of the measures.

我认为这样做的一种方法是使用 AdomdConnection 并引用 CubeDef 类来获取度量信息，并将其与 Cellset.Set.Tuples.Members 之类的东西相匹配，但是如何解释在 SELECT 中使用WITH"关键字的即时测量?

One way to do this, I think, would be to use the AdomdConnection and reference the CubeDef class to obtain measure information, and match that to Cellset.Set.Tuples.Members sort of thing, but then how does one account for on-the-fly measures from using the "WITH" keyword in a SELECT?

另一种方法是通过执行 SELECT 语句查看与给定度量关联的 Cell 对象的 Value 属性，并在 CellSet 中查找非空 Cell，但我们不能保证找到非空值，所以这不是万无一失的.

Another way could be to look at the Value property of Cell objects associated with a given measure from executing the SELECT statement and find a non-null Cell in the CellSet, but we're not guaranteed to find a non-null value, so this is not fail-proof.

我在 VS 调试器中查看了 CellSet，但没有找到任何提供此信息的属性.

I've looked through a CellSet in the VS debugger and haven't found any properties that give this information.

解决方案:事实证明，一个度量可能有多种数据类型.至少对于使用 WITH 子句定义的度量，如下所示:

Solution: As it turns out, it is possible for a measure to have multiple data types. At least for measures defined with a WITH clause, as follows:

WITH MEMBER [Measures].[Dud] AS CASE WHEN [Measures].[Original] > 500 THEN 'A' ELSE 0 END

WITH MEMBER [Measures].[Dud] AS CASE WHEN [Measures].[Original] > 500 THEN 'A' ELSE 0 END

因此，数据类型信息存储在每个单元格中，而不是存储在某种度量元数据中.随后，学习模式的唯一方法是假设它们都是相同的类型，然后遍历度量维度，直到找到一个非空单元格，然后学习其类型.

Because of this, data type information is stored in each cell, and not in some sort of measures meta data. Subsequently, the only way to learn the schema is to assume that they're all the same type, and then traverse through the measures dimension until you find a non-null cell and then learn its type.

推荐答案

我们实际上基于 ADOMD 数据提供程序编写了自己的 .NET Framework 数据提供程序.我们希望在今年晚些时候开源它，但下面是我如何完成你想要完成的事情的一些摘录.

We actually wrote our own .NET Framework Data Provider based on the ADOMD data provider. We hope to open source it later this year, but below are some excerpts of how I did what you want to accomplish.

我使用了 AdomdCommand 对象的 ExecuteXmlReader.返回的 xml 将包含单元格的一部分.

I used the ExecuteXmlReader of a AdomdCommand object. The xml that is returned will have a portion for the cells.

AdomdCommand command = new AdomdCommand();
command.Connection = new AdomdConnection(connectionString);
command.Connection.Open();
command.CommandText = query;
var doc = XDcoument.Load(command.ExecuteXmlReader());

var cellData = from cell in doc.Root.Elements(_namespace + "CellData").Elements(_namespace + "Cell")
                       select new
                       {
                           Ordinal = (int)cell.Attribute("CellOrdinal"),
                           FormattedValue = cell.Elements(_namespace + "FmtValue").Any() ? cell.Element(_namespace + "FmtValue").Value : cell.Element(_namespace + "Value").Value,
                           Value = cell.Element(_namespace + "Value").Value,
                           Type = (string)cell.Element(_namespace + "Value").Attribute(_xsiNs + "type"),

                       };

每个单元格都有一个数据类型.对于给定的列，我们需要该列中的所有单元格.

Each cell has a data type. For a given column, we need all the cells in that column.

var x = cells.Where(c => ((c.Ordinal + 1) % columnCount) == columnPosition).Select(t => t.Type).Distinct();
 if (x.Count() > 1)
        {
            // if a non number comes back, the type is null, so non numbers are null
            // on counts of greater than 1 and no nulls, we have multiple number types, make them all double to accommodate the differences
            if ( !x.Contains(null) )
            {
                // mix of numbers not doubles, default to int
                if (!x.Contains("xsd:double"))
                {
                    type = typeof(int);
                }
                else
                {
                    type = typeof(double);
                }
            }
            else
            {
                type = typeof(string);
            }
        }
        else
        {
            // entire column maybe null, default to string, otherwise check
            if (x.Count() == 1)
            {
                type = ConvertXmlTypeToType(x.First());
            }               
        }

最后我有了将 Xml 类型转换为 .NET 类型的函数

Finally I have function that converts Xml type to .NET type

private Type ConvertXmlTypeToType(string type)
    {
        Type t = typeof(string);

        switch (type)
        {
            case "xsd:int":
                t = typeof(int);
                break;
            case "xsd:double":
                t = typeof(double);
                break;
            case "xsd:long":
                t = typeof(long);
                break;
            case "xsd:short":
                t = typeof(short);
                break;
            case "xsd:integer":
                t = typeof(int);
                break;
            case "xsd:decimal":
                t = typeof(decimal);
                break;
            case "xsd:float":
                t = typeof(float);
                break;
            default:
                t = typeof(string);
                break;
        }

        return t;
    }               

这篇关于在 C# 中从 MDX 查询/CellSet 中检索度量的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！