ADOMD.NET Client Functionality
New: 5 December 2005
ADOMD.NET, as with other Microsoft .NET Framework data providers, serves as a bridge between an application and a data source. However, ADOMD.NET is unlike other .NET Framework data providers in that ADOMD.NET works with analytical data. To work with analytical data, ADOMD.NET supports functionality that is very different than other .NET Framework data providers. ADOMD.NET not only allows you to retrieve data, but also to retrieve metadata and change the structure of the analytical data store:
Retrieving Metadata
Applications can learn more about the data that can be retrieved from the data source through metadata retrieval, using either schema rowsets or the object model. Information such as the types of each key performance indicator (KPI) that are available, the dimensions in a cube, and the parameters needed by the mining models are all discoverable. Metadata is most important to dynamic applications that require user input to determine the type, depth, and scope of data to be retrieved. Examples include Query Analyzer, Microsoft Excel, and other querying tools. Metadata is less critical to static applications that perform a predefined set of actions.For more information: Retrieving Metadata from an Analytical Data Source.
Retrieving Data
Data retrieval is the actual retrieval of the information stored in the data source. Data retrieval is the primary function of "static" applications, which know the structure of the data source. Data retrieval is also the end result of "dynamic" applications. The value of the KPI at a given time of day, the number of bicycles sold within the last hour for each store, and the factors governing the annual performance of employees are all examples of data that can be retrieved. Retrieving data is vital for any querying application.For more information: Retrieving Data from an Analytical Data Source.
Changing the Structure of Analytical Data
ADOMD.NET can also be used to actually change the structure of the analytical data store. Though this is usually done through the Analysis Management Objects (AMO) object model, you can use ADOMD.NET to send Analysis Services Scripting Language (ASSL) commands to create, alter, or delete objects on the server.For more information: Executing Commands Against an Analytical Data Source, Analysis Management Objects (AMO), Analysis Services Scripting Language (ASSL)
Retrieving metadata, retrieving data, and changing data structure each occur at a specific point in the workflow of a typical ADOMD.NET application.
Typical Process Flow
Traditional ADOMD.NET applications usually follow the same workflow when working with an analytical database:
- First, a connection is made to the database, using the AdomdConnection object. When you open the connection, the AdomdConnection object exposes metadata about the server to which you have connected. In a dynamic application, some of this information is typically shown to the user so that the user can make a selection, such as which cube to query. The connection created during this step can be reused multiple times by the application, reducing overhead.
For more information: Establishing Connections in ADOMD.NET - Once a connection has been made, a dynamic application would then query the server for more specific metadata. For a static application, the programmer knows in advance which objects the application will be querying, and thus will not need to retrieve this metadata. Metadata that is retrieved can be used by the application and the user for the next step.
For more information: Retrieving Metadata from an Analytical Data Source - The application then runs a command against the server. This command can be for the purpose of retrieving additional metadata, retrieving data, or modifying the database structure. For any of these tasks, the application could use a previously-determined query, or make use of newly retrieved metadata to create additional queries.
For more information: Retrieving Metadata from an Analytical Data Source, Retrieving Data from an Analytical Data Source, Executing Commands Against an Analytical Data Source - Once the command has been sent to the server, the server begins to return the metadata or data to the client. This information can be viewed by using a CellSet object, an AdomdDataReader object, or a System.XmlReader object.
To illustrate this traditional workflow, the following example contains a method that opens a connection to the database, executes a command against a known cube, and retrieves the results into a cellset. The cellset then returns a tab-delimited string containing column headers, row headers, and cell data.
string ReturnCommandUsingCellSet()
{
//Create a new string builder to store the results
System.Text.StringBuilder result = new System.Text.StringBuilder();
//Connect to the local server
using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;"))
{
conn.Open();
//Create a command, using this connection
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
WITH MEMBER [Measures].[FreightCostPerOrder] AS
[Measures].[Reseller Freight Cost]/[Measures].[Reseller Order Quantity],
FORMAT_STRING = 'Currency'
SELECT
[Geography].[Geography].[Country].&[United States].Children ON ROWS,
[Date].[Calendar].[Calendar Year] ON COLUMNS
FROM [Adventure Works]
WHERE [Measures].[FreightCostPerOrder]";
//Execute the query, returning a cellset
CellSet cs = cmd.ExecuteCellSet();
//Output the column captions from the first axis
//Note that this procedure assumes a single member exists per column.
result.Append("\t");
TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
foreach (Tuple column in tuplesOnColumns)
{
result.Append(column.Members[0].Caption + "\t");
}
result.AppendLine();
//Output the row captions from the second axis and cell data
//Note that this procedure assumes a two-dimensional cellset
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
for (int row = 0; row < tuplesOnRows.Count; row++)
{
result.Append(tuplesOnRows[row].Members[0].Caption + "\t");
for (int col = 0; col < tuplesOnColumns.Count; col++)
{
result.Append(cs.Cells[col, row].FormattedValue + "\t");
}
result.AppendLine();
}
conn.Close();
return result.ToString();
} // using connection
}