ADOMD.NET?

In this article we will see how to use ADOMD to interact with an Analysis Services cube.

ADOMD.NET is an extension of ADO.NET. It's a data provider Microsoft .NET Framework that is designed to communicate with Microsoft SQL Server Analysis Services.

Using this extension, we can read the multidimensional schema, query cubes, and retrieve results.

Orders or queries ADOMD.NET can be sent in multidimensional expressions MDX, Data Mining extensions (DMX), Analysis Services Scripting Language (ASSL) or even a limited syntax of SQL.

The data provider ADOMD.NET is represented by the Microsoft.AnalysisServices.AdomdClient namespace which is included in the Microsoft.AnalysisServices.AdomdClient.dll located in: “C: \ Program Files \ Microsoft.NET \ ADOMD.NET \ 100.”

The following example shows how to use the data provider ADOMD.NET  from a client application to retrieve data from the Adventure Works cube using an MDX query that fills a data reader:

Starting first by downloading the data warehouse and the AdventureWorks cube, from this link.

Once the sources are downloaded and deployed, we start by creating a new Console Application project.

Once the project is created, in the Solution explorer tab, you must add a reference to the ADOMD class library that is in this directory: C: \ Program Files \ Microsoft.NET \ ADOMD.NET \ 110

To call this library in our class, we use the namespace:

using Microsoft.AnalysisServices.AdomdClient;

Paste the following code, which allows you to have the sales amount and the gross margin by product category:

Using System; Using System.Data; Using Microsoft.AnalysisServices.AdomdClient; class Program {static void Main (string [] args) {AdomdConnection conn = new AdomdConnection ("Data Source = localhost; Catalog = Adventure Works DW Standard Edition"); conn.Open (); string commandText = "SELECT {[Measures]. [Sales Amount]," + "[Measures]. [Gross Profit Margin]} ON COLUMNS," + "{[Product]. [Product Model Categories]. [Category]} ON ROWS "+" FROM [Adventure Works] "+" WHERE ([Sales Territory Country]. [United States]) "; AdomdCommand cmd = new AdomdCommand (commandText, conn); AdomdDataReader dr = cmd.ExecuteReader (CommandBehavior.CloseConnection); // output the rows in the DataReader while (dr.Read ()) {for (int i = 0; i <dr.FieldCount; i ++) Console.Write (dr [i] + (i == dr.FieldCount - 1 ? "": ",")); Console.WriteLine (); } dr.Close (); Console.WriteLine (Environment.NewLine + "Press any key to continue."); Console.ReadKey (); }}

The AdomdConnection class represents a connection to a multidimensional data source. The AdomdDataReader class retrieves-only, read-only data stream from a data source and is similar to other data read classes in ADO.NET. The results stream is returned as soon as the query is run, allowing data to be accessed as soon as the first row is available, rather than waiting for all of the results to be returned. The AdomdDataReader object is created by calling the Execute () or ExecuteReader () method of the AdomdCommand object. The Read () method of the AdomdDataReader object retrieves the following row of results.

To visualize the result, just run the project: