Describing and Fetching Cell Data

OLE DB Programmer's Reference

Before the application can actually fetch cell data, it needs to know exactly what data is available in each cell, the data type of each cell, and cell properties.

A Conceptual Model for Cell Data

Each cell in the dataset has the following pieces of data associated with it: the data value and two required properties. OLE DB for OLAP requires all providers to support the FORMATTED_VALUE and CELL_ORDINAL properties. Optional cell properties can be selected and retrieved using the CELL PROPERTIES clause.

Each cell in the dataset is modeled as a row, and each column in this row represents a cell property. Therefore, the dataset itself becomes a set of rows, with each row containing the data for a cell. Modeling a dataset in this fashion enables the reuse of existing methods and structures in OLE DB for describing and binding to data.

The following SalesData dataset example illustrates this model:

Describing Cell Properties

Because properties of a cell are modeled as columns in a row, you can use the interface IColumnsInfo to determine the name, data type, number of bytes required to transfer the data, and other related information about each property.

Data Type of VALUE

The VALUE property of a cell can contain data of different types. The VALUE property is cell-specific. For example, consider the following query:

SELECT NON EMPTY Products.MEMBERS DIMENSION PROPERTIES Products.SKU, Products.SRP ON ROWS, NON EMPTY CROSSJOIN(Years, (Sales, BudgetedSales, PercentChange)) ON COLUMNS FROM SalesCube WHERE (January, SalesPerson.[All], Geography.USA)

The dataset that the application displays appears as follows:

The NON EMPTY clause suppressed the PercentChange column for 1996 because the company did not track its financials before 1996. Therefore, this measure makes no sense.

The heavily shaded regions are displayed using the information from the axes rowsets. The rest is a display of cells. Among these, the data type of the cells in the lightly shaded region is double and the data type of the cells in the unshaded region is integer.

Because each cell is modeled as a row, the rowset corresponding to the dataset has 20 rows. The ordinal number of each cell is in parentheses below the cell data. The data type of the VALUE column in rows 4, 9, 14, and 19 is double, and the data type of the other rows is integer. IColumnsInfo is not designed to describe rowsets for which the data type of a column varies based on the row. (This problem does not exist for any other cell properties defined in OLE DB for OLAP.)

To solve this problem, OLE DB for OLAP treats the VALUE property of a cell as the DBTYPE_VARIANT data type of OLE DB. This data type is capable of holding data of many different types. The consumer can look at the vt column of the VARIANT structure, determine the actual type of VALUE, and interpret it accordingly.

Information About Supported Conversions

When fetching cell data, the consumer can ask the provider to perform data conversion to a desired target type. To do this, the consumer needs to know what data type conversions the provider supports. This information is available through the required dataset object interface IConvertType::CanConvert. This method takes a source type and a target type and returns S_OK if the source type can be converted to the target type, or S_FALSE otherwise.

Fetching Cell Data

OLE DB for OLAP uses the same model that OLE DB uses for retrieving data. In this model, the consumer follows these steps:

1. Allocates a buffer to hold the data it wants to fetch. The size of this buffer is determined by the data type, precision, scale, and length of the column data. This information is available through the methods in the IColumnsInfo interface.
2. Chooses the columns (cell properties) in the dataset's rowset that it wants to retrieve.
3. Allocates an array of DBBINDING structures, one structure for each column in the rowset. This structure is populated with the following information:
   • The column ordinal that corresponds to this structure.
   • The desired data type. This is the type that the application wants the data to be in—that is, the target type. If the target type is different from the source type, the provider performs the necessary data type conversion.
   • Offset in the buffer where the data for this column is to be placed.
   • Offset in the buffer where the number of retrieved bytes is to be placed (for string and other variable-length data).
   • Offset in the buffer where the status indicator is to be placed.
   • Other pieces of information not relevant to this discussion.
4. Uses IAccessor::CreateAccessor to create an accessor and associate it with the set of bindings (that is, the array of DBBINDING structures). This method returns a handle to the created accessor.
5. Calls IMDDataset::GetCellData. This method takes the following arguments:
   • The accessor created in step 4.
   • The address of the buffer that was allocated in step 1.
   • The cell ordinal that indicates the first cell of the cell set.
   • The cell ordinal that indicates the last cell to fetch.

   Using the binding information contained in the accessor, the provider populates the consumer's buffer.

See Part 1 of the OLE DB Programmer's Reference for more information about accessors, the DBBINDING structure, and data types.

1998-2001 Microsoft Corporation. All rights reserved.