Table of Contents



Introduction

In this article, we will see how to use ADOMD.NET to interact with analysis services cube.

Definition

ADOMD.NET is an extension of ADO.NET and a Microsoft .NET Framework data provider that is designed to communicate with Microsoft SQL Server Analysis services.
Using this extension, we can read the multidimensional schema, make queries on cubes and retrieve the results.

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

Implementation

The following example shows how to use the ADOMD.NET data provider from a client application to retrieve data from the AdventureWorks cube by using an MDX query that populates a data drive :

1. Install the AdventureworksDW sample


The AdventureWorksDW sample data consists of :
  • The AdventureWorksDW database.
  • The AdventureWorksDW OLAP cube

To deploy the AdventureWorksDW database :
  1. Download AdventureWorksDW.
  2. Copy AdventureWorksDW2012_Data.mdf to your default database directory (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data) or other location as designated by your database administrator.
  3. Connect to the database engine in SQL Server Management Studio.
  4. Right-click Databases, and then click Attach.
  5. On the Attach Databases dialog box, click Add.
  6. Navigate to the location where you copied AdventureWorksDW2012_Data.mdf, select the file, and then click OK.
  7. Under “AdventureWorksDW2012” database details, select the row where File Type is Log.
  8. Click Remove.
  9. Click OK.

To configure the AdventureWorksDW OLAP Cube :
  1. Download AdventureWorksDW Multidimensional OLAP Cube.
  2. Unzip the file to a location on the computer running Analysis Services.
  3. In the Enterprise folder, double-click AdventureWorksDW2012Multidimensional-EE.sln.
  4. If the Choose Default Environment Settings dialog box appears, choose the Business Intelligence Settings option, and then click Start Visual Studio.
  5. In Visual Studio, at the top of the Solution Explorer window, right-click AdventureWorksDW2012Multidimensional-EE and click Deploy.

To process the AdventureWorks OLAP Cube :
  1. Expand the database folder, open the Data Source folder under the database name, right-click a data source in Object Explorer and select Properties.
  2. Change the connection string, by specifying the physical location of a database that provides data to a multidimensional model, and the data provider used for the connection.
  3. Right-click the database name, and then click process, chose the Process full in the process options and then click ok.

2. Create the application console project


Once the sources are downloaded and deployed, you first create a new Application Console project in Visual studio.



In the solution explorer tab, you must add a reference to the ADOMD class library that is located in this directory: "C:\Program Files\Microsoft.NET\ADOMD.NET\110".







To use 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 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 only retrieves the data stream from a data source in read-only and similar to other data read classes in ADO.NET.

The result flow is returned as soon as the query is executed, allowing access to the data as soon as the first row is available, rather than waiting for the set of results to return. The AdomdDataReader object is created by calling the Execute() or ExecuteReader() method of AdomdCommand object. The Read() method of the AdomdDataReader object retrieves the next row of the results.

3. Run the project


To see the result, just run the project:



Conclusion

ADOMD.NET commands or queries can also be sent in Data mining (DMX) extensions, Analysis services scripting language (ASSL), or even limited SQL syntax.

See also

Back to top