Section 20.6. Accessing Data by Using ADOMD.NET


20.6. Accessing Data by Using ADOMD.NET

ADOMD.NET is a .NET Framework data provider that lets client applications access multidimensional data sources such as SSAS. ADOMD.NET uses XMLA 1.1 to communicate with data sources. The ADOMD.NET namespace is Microsoft.AnalysisServices.AdomdClient, which is implemented in the Microsoft.AnalysisServices.AdomdClient.dll assembly. This is located by default in the C:\Program Files\Microsoft.NET\ADOMD.NET\90 directory.

This section shows how to use ADOMD.NET to retrieve data and metadata from an SSAS instance. You need a reference to the Microsoft.AnalysisServices.AdomdClient assembly to compile and run the examples.

20.6.1. Querying an SSAS Database

This section shows how to use ADOMD.NET to retrieve multidimensional data from a managed client application.

The first example shows how to use the ADOMD.NET data provider to retrieve data from the Adventure Works cube by using an MDX query that populates a data reader:

     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 results shown in Figure 20-7 correspond to those from executing the same query in the "Multidimensional Expressions (MDX)" section earlier in this chapter.

Figure 20-7. Results for ADOMD.NET query example


The AdomdConnection class represents a connection to a multidimensional data source. The AdomdDataReader class retrieves a forward-only, read-only stream of data from a data source and is similar to other data reader classes in ADO.NET. The stream of results is returned as the query executes, letting you access data as soon as the first row is available, rather than waiting for the entire set of 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 next row of results.

The CellSet class in ADOMD.NET is an in-memory cellset that can be manipulated while disconnected and later synchronized to the data source. The CellSet class is analogous to the DataSet class in ADO.NET. This example shows how to use the CellSet class to manipulate data extracted from the Adventure Works cube:

     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);             CellSet cs = cmd.ExecuteCellSet(  );             // iterate over the rows and column positions             foreach (Position pRow in cs.Axes  [1].Positions)             {                 foreach (Position pCol in cs.Axes[0].Positions)                 {                     // get the formatted value based on the row and column positions                     Console.Write(                         cs[pCol.Ordinal, pRow.Ordinal].FormattedValue + ", ");                 }                 Console.WriteLine(  );             }             conn.Close(  );             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 20-8.

Figure 20-8. Results for ADOMD.NET CellSet example


The results correspond to those from the first query executed in the "Multidimensional Expressions (MDX)" section earlier in this chapter.

The CellSet class contains a collection of Cell objects hierarchically organized to the tuples and axes specified by the Axes and FilterAxis properties. The cells in the cellset are accessed by index, a pair of indexes corresponding to the column and row, or an array of indexes corresponding to the dimensions of the cellset containing more than two dimensions.

The following example retrieves a cellset as an XmlReader object and uses an XmlDocument object to output the contents of the XmlReader object to the console:

     using System;     using System.Data;     using System.Xml;     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);             XmlReader xr = cmd.ExecuteXmlReader(  );             XmlDocument xd = new XmlDocument(  );             xd.Load(xr);             Console.WriteLine(xd.InnerXml);             xr.Close(  );             conn.Close(  );             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

The data portion of the resulting XML document is shown in Figure 20-9.

Figure 20-9. Results for ADOMD.NET XmlDocument example


The ExecuteXmlReader( ) property of the AdomdCommand class returns the cellset as an XmlReader object. As with the example in the "XML for Analysis (XMLA)" section earlier in this chapter, the results are returned in the <CellData> element of the XML document.

20.6.2. Retrieving Schema Information

A schema rowset contains metadata, monitoring, and support information from an SSAS instance. This section shows how to use ADOMD.NET to retrieve schema information from a managed client application.

This example shows how to use the AdomdDataReader class to get schema information about a cellset:

     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);             // retrieve the schema information into a table             DataTable dt = dr.GetSchemaTable(  );             foreach (DataRow row in dt.Rows)             {                 foreach (DataColumn col in dt.Columns)                     Console.WriteLine(col.ColumnName + " = " + row[col].ToString(  ));                 Console.WriteLine(  );             }             dr.Close(  );             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 20-10.

Figure 20-10. Partial results for retrieving cellset schema example


The GetSchemaTable( ) method of the AdomdDataReader class returns a DataTable object containing schema information about the cellset. Each row of the DataTable corresponds to a column in the cellset. The columns of the DataTable contain properties for the cellset column.

The connection to the SSAS instance can also be used to retrieve schema information for the objects in an SSAS instance. This example shows how:

     using System;     using System.Data;     using Microsoft.AnalysisServices.AdomdClient;     class Program     {         static void Main(string[] args)         {             AdomdConnection conn = new AdomdConnection(                 "Data Source=localhost;Catalog=Analysis Services Tutorial");             conn.Open(  );             DataSet ds = conn.GetSchemaDataSet(AdomdSchemaGuid.Dimensions, null);             DataTable dt = ds.Tables[0];             foreach(DataRow row in dt.Rows)             {                 foreach (DataColumn col in dt.Columns)                     Console.WriteLine(col.ColumnName + " = " + row[col].ToString(  ));                 Console.WriteLine(  );             }             conn.Close(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 20-11.

Figure 20-11. Partial results for retrieving schema information example


The GetSchemaDataSet( ) method of the AdomdConnection class returns schema information for the objects specified by the method arguments. The preceding example uses an overload of the GetSchemaDataSet( ) method that takes two arguments. The first argument identifies the object type by using its GUID as a static field from the AdomdSchemaGuid class. The second contains an array of restrictions used to return information for a subset of the objects.



Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net