Getting field datatypes from an SQL Analysis Services MDX SELECT query using .NET AdomdClient

Question

Getting field datatypes from an SQL Analysis Services MDX SELECT query using .NET AdomdClient yesterday Let's say I have this MDX query against SQL Server's Analysis Service Adventure Works sample:

select [Measures].[Customer Count] on columns, 
[Date].[Calendar Quarter of Year] on rows
from [Adventure Works]

And I execute it as:

var reader = new AdomdCommand("...").ExecuteReader();

I would expect that

reader.GetFieldType(0);

would return System.Int32, but it returns System.Object. The same is true if I query schema with reader.GetSchemaTable() which is no surprise as I assume the former gets data from later. I also assume this is probably not an exclusive .NET issue. The same is at least true for .NET Framework. So, is there a way I can get those types when I do a query?

Answer 1

Hi @Miha Markic , Welcome to Microsoft Q&A,

You are right, it returns System.Object.

When working with MDX queries against SQL Server Analysis Services (SSAS) using the .NET AdomdClient, retrieving accurate data types for measures in the result set can be challenging. The AdomdClient may return data as objects, and the underlying type information might not be as straightforward as in regular SQL queries.

Try to Use the GetSchemaTable method on the AdomdDataReader to retrieve schema information about the result set, including column names and types. Analyze the metadata to determine the data types of the columns.

DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
    string columnName = row["ColumnName"].ToString();
    Type dataType = (Type)row["DataType"];
    // Handle the column name and data type information
}

Although you can obtain metadata information using the GetSchemaTable method, type conversions are often required in your code when processing the actual query results. This may involve explicit conversion of the returned System.Object, which may depend on your knowledge of the query results and the expected data type.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.