Retrieving Data Using the CellSet

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:

1. 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.

2. 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.

3. 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.

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
}