Retrieving Data Using the CellSet
New: 5 December 2005
When retrieving analytical data, the CellSet object provides the most interactivity and flexibility. The CellSet object is an in-memory cache of hierarchical data and metadata that retains the original dimensionality of the data. The CellSet object can also be traversed in either a connected or disconnected state. Because of this disconnected ability, the CellSet object can be used to view data and metadata in any order and provides the most comprehensive object model for data retrieval. This disconnected capability also causes the CellSet object to have the most overhead, and to be the slowest ADOMD.NET data retrieval object model to populate.
Retrieving Data in a Connected State
To use the CellSet object to retrieve data, you follow these steps:
- Create a new instance of the object.
To create a new instance of the CellSet object, you call the Execute or ExecuteCellSet method of the AdomdCommand object. - Identify metadata.
Besides retrieving data, ADOMD.NET also retrieves metadata for the cellset. As soon as the command has run the query and returned a CellSet, you can retrieve the metadata through various objects. This metadata is needed for client applications to display and interact with cellset data. For example, many client applications provide functionality for drilling down on, or hierarchically displaying the child positions of, a specified position in a cellset.
In ADOMD.NET, the Axes and FilterAxis properties of the CellSet object represent the metadata of the query and slicer axes, respectively, in the returned cellset. Both properties return references to Axis objects, which in turn contain the positions represented on each axis.
Each Axis object contains a collection of Position objects that represent the set of tuples available for that axis. Each Position object represents a single tuple that contains one or more members, represented by a collection of Member objects. - Retrieve data from the cellset collection.
Besides retrieving metadata, ADOMD.NET also retrieves data for the cellset. As soon as the command has run the query and returned a CellSet, you can retrieve the data by using the Cells collection of the CellSet. This collection contains the values that are calculated for the intersection of all axes in the query. Therefore, there are several indexers for accessing each intersection, or cell. For a list of indexers, see Item.
Example of Retrieving Data in a Connected State
The following example makes a connection to the local server, and then runs a command on the connection. The example parses the results by using the CellSet object model: the captions (metadata) for the columns are retrieved from the first axis, and the captions (metadata) for each row are retrieved from the second axis, and the intersecting data is retrieved by using the Cells collection.
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
}
Retrieving Data in a Disconnected State
By loading XML returned from a previous query, you can use the CellSet object to provide a comprehensive method of browsing analytical data without requiring an active connection.
Note
Not all properties of the objects that are available from the CellSet object are available while in a disconnected state. For more information, see LoadXml.
Example of Retrieving Data in a Disconnected State
The following example is similar to the metadata and data example shown earlier in this topic. However, the command in the following example runs with a call to ExecuteXmlReader, and the result is returned as a System.Xml.XmlReader. The example then populates the CellSet object by using this System.Xml.XmlReader with the LoadXml method. Although this example loads the System.Xml.XmlReader immediately, you could cache the XML that is contained by the reader to a hard disk or transport that data to a different application through any means before loading the data into a cellset.
string DemonstrateDisconnectedCellset()
{
//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 an XmlReader
System.Xml.XmlReader x = cmd.ExecuteXmlReader();
//At this point, the XmlReader could be stored on disk,
//transmitted, modified, cached, or otherwise manipulated
//Load the CellSet with the specified XML
CellSet cs = CellSet.LoadXml(x);
//Now that the XmlReader has finished being read
//we can close it and the connection, while the
//CellSet can continue being used.
x.Close();
conn.Close();
//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();
}
return result.ToString();
} // using connection
}
See Also
Reference
Retrieving Data from an Analytical Data Source
Retrieving Data Using the AdomdDataReader
Retrieving Data Using the XmlReader