Retrieving Data Type of Measures from an MDX Query/CellSet in C#

Question

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

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.

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.

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?

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.

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

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

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.

Rick Rick · Accepted Answer · 2012-04-13T19:20:26

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.

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

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

Retrieving Data Type of Measures from an MDX Query/CellSet in C#

1 Answers