Use adomd. Net to query multidimensional datasets in analysis servies

Content
1. adomd. .

2. Convert the returned unit set to datatable.

Query content:

Distribution quotas and sales for the four quarters of 2004, corresponding to the mdx

Select
{[Measures]. [reseller sales-sales amount], [measures]. [sales amount
Quota]} on columns, {[date]. [fiscal quarter]. Members} on rows

From [Analysis Services tutorial]

Where [date]. [fiscal year]. & [2004]"

Dataset: the online help document comes with a tutorial. After the seventh lesson, the deployed Multidimensional Dataset Analysis Services tutorial

Query axis:

Column axis: The measurement value corresponding to distributor quota and sales [measures]. [reseller sales-sales amount], [measures]. [sales amount quota]

Line axis: All members of the quarter in the time dimension [fiscal quarter]. Members

Slice axis: year 2004 in the time dimension

Data Structure Conversion

Adomd. the cellset information of the cells returned by the net query. The package inserts the query axis (column axis and row axis) and the measurement value set (1). However, the client application shows the data structure of the table required by the data, columns and rows. for details about Data Structure Conversion, see the comments in the code.

(Figure 1)

(Figure 2)

Code:

Using system;
Using system. collections;
Using system. componentmodel;
Using system. Data;
Using system. drawing;
Using system. text;
Using system. Windows. forms;
Using Microsoft. analysisservices. adomdclient;
Namespace winal
{
Public partial class form1: Form
{
Public form1 ()
{

Initializecomponent ();
}
Public void adomd ()
{
// Connected string
String constr = "provider = msolap; Integrated Security = sspi; Data Source = localhost; Catalog = Analysis Services tutorial ;";
// Create a connection object
Adomdconnection con = new adomdconnection ();
Con. connectionstring = constr;
Con. open ();
// Create commands
Adomdcommand CMM = con. createcommand ();

CMM. commandtext = @ "select {[measures]. [reseller sales-sales amount], [measures]. [sales amount quota]} on columns,
{[Date]. [fiscal quarter]. Members} on rows
From [Analysis Services tutorial]
 
Where [date]. [fiscal year]. & [2004] ";
// Execute the command to return the unit set
Cellset result = CMM. executecellset ();
This. datagridview1.datasource = cellsettotable (result );
Con. Close ();

}

Private datatable cellsettotable (cellset)
{

Datatable table = new datatable ("cellset ");


Axis columns = cellset. Axes [0]; // obtain the column axis
Axis rows = cellset. Axes [1]; // obtain the row axis
Cellcollection valuescell = cellset. cells; // gets the set of measurement units.
// The level title of the row axis is the first column of the table
Table. Columns. Add (rows. Set. hierarchies [0]. Caption );
// The row axis is converted into the table column by the title of each member.
For (INT I = 0; I <columns. Set. tuples. Count; I ++)
{
Table. Columns. Add (New datacolumn (columns. Set. tuples [I]. members [0]. Caption ));
}
Int valuesindex = 0;
Datarow ROW = NULL;
// Fill data in the table
For (INT I = 0; I <rows. Set. tuples. Count; I ++)
{
Row = table. newrow ();
// The first column value of all rows in the table is the title of the corresponding row axis.
Row [0] = rows. Set. tuples [I]. members [0]. Caption;
For (int K = 1; k <= columns. Set. tuples. Count; k ++)
{// Fill the values of the set of measurement value units in the table in order
Row [k] = valuescell [valuesindex]. value;
Valuesindex ++;
}
Table. Rows. Add (ROW );
}

Return table;


}

Private void button#click (Object sender, eventargs E)
{
Adomd ();
}
}
}