How to retrieve data from mdx query in c#?

Question

I am trying to get data from an MDX query using the Adomdclient library. I relied on this example http://www.yaldex.com/sql_server/progsqlsvr-CHP-20-SECT-6.html.

MDX query:

SELECT {[Measures].[Cantidad Vta],[Measures].[Monto Vta],[Measures].[ExistenciaHistorica],[Measures].[Valor Inventario historico]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[DIM SUBMARCA].[Código].[All]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM (SELECT ({[DIM TIENDA].[JERARQUIA TIENDA].[Región].&[Bodega],[DIM TIENDA].[JERARQUIA TIENDA].[Región].&[Cadena],[DIM TIENDA].[JERARQUIA TIENDA].[Región].&[Outlet]}) ON COLUMNS FROM [JUGUETRONHQ]) WHERE ([DIM FECHA VENTA].[JERARQUIA FECHA VENTA].[Time].&[2012-01-01T00:00:00],[DIM FECHA EXISTENCIA].[JERARQUIA FECHA EXISTENCIA].[All]) CELL PROPERTIES VALUE

Like other namespaces such as SqlClient, use a connection, a command and a datareader:

using Microsoft.AnalysisServices.AdomdClient;

...

using (AdomdConnection con = new AdomdConnection(connection_string))
{
  con.Open();

  using (AdomdCommand command = new AdomdCommand(query, con)) 
  {
    using (AdomdDataReader reader = command.ExecuteReader()) 
    {
      while (reader.Read())
      {
        for (int i = 0; i < reader.FieldCount; i++)
          Console.Write(reader[i] + (i == reader.FieldCount - 1 ? "" : ", "));
          Console.WriteLine("");

      }
    }
  }
}

However, this snippet only shows 4 of 5 columns correctly:

[DIM SUBMARCA].[Código].[All], , , 3, 825

It must be:

115200081, , , 3, 825

Perhaps need a cast but I don't know how to do it.

score 1 · Answer 1 · answered Jan 10 '12 at 16:10

1

This looks like a problem with the MDX query, not the retrieval of the data. It's not correctly constraining on the [DIM SUBMARCA].[Código] dimension.

answered Jan 10 '12 at 16:10

Bas

26,772
8
53
86

I get that query using this plugin http://olappivottableextend.codeplex.com/ on excel. The spreadsheet shows the query correctly (115200081, , , 3, 825 ) – auraham Jan 10 '12 at 16:12

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

Retrieve data from MDX query

added the reference for Microsoft.AnalysisServices.AdomdClient.dll
AdomdConnection steps

AdomdConnection con = new AdomdConnection("connectionstring"); // connect DB con.Open(); AdomdCommand cmd = new AdomdCommand("MDX query", con); //query

AdomdDataReader reader = cmd.ExecuteReader(); //Execute query

    while (reader.Read())   // read
        {
            Data dt = new Data();  // custom class
            dt.Gender = reader[0].ToString();

            dt.Eid = reader[1].ToString();
            dt.salary = reader[2].ToString();
            data.Add(dt);
        }

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 20 '17 at 07:15

user3904340

19

connection string for windows authentication connectionString = "Data Source=ServeName;Integrated Security=SSPI;Initial Catalog=DBName;"; – user3904340 Sep 20 '17 at 07:16

score 0 · Answer 3 · answered Jan 11 '12 at 19:18

Your query has 1 [ALL] Level Dimension and 4 measures:

[DIM SUBMARCA].[Código].[All], 
[Measures].[Cantidad Vta],
[Measures].[Monto Vta],
[Measures].[ExistenciaHistorica],
[Measures].[Valor Inventario historico]

This retrieves 1 [ALL] column and 4 Values:

[DIM SUBMARCA].[Código].[All], , , 3, 825

115200081 is a key value? You could get this value using "DIMENSION PROPERTIES MEMBER_HEY".

How to retrieve data from mdx query in c#?

3 Answers3

Retrieve data from MDX query