Deviations from or Clarifications to the OLE DB for OLAP Specification

This section discusses the ways in which the current release of Microsoft^® PivotTable^® Service either differs from or expands upon the OLE DB or OLE DB for OLAP specifications.

Commands

Accessors are not available from Command objects. This is due to the use of components for rowsets that do not provide for accessor creation outside the scope of a rowset.

OLE DB Properties

OLE DB properties (DBPROPSET) are not fully implemented.

OLAP Properties

Dimension properties (properties derived from a dimension or level) are not supported. None will be returned from the PROPERTIES or MEMBERS schema rowsets. None can be requested in an MDX query.

Member properties are supported. The member properties are not returned from the PROPERTIES rowset. Only the standard cell properties are exposed.

Filter Axes

IMDDataset::GetAxisInfo returns the filter axis as the last element of the MDAXISINFO array. In the filter axis, iAxis=(~0)=MDAXIS_FILTERS.

The AXIS rowset for filter axes can be obtained by requesting either iAxis=cAxis-1 (the last axis), or iAxis=MDAXIS_FILTERS.

Measures

Microsoft PivotTable Service exposes measures as the first dimension (ordinal 0), in the dimensions rowset with the name "Measures". This dimension has one level named "MeasuresLevel". (The OLE DB for OLAP specification suggests, but does not require, that the name of the level be "Measures"; providers can select a different name.)

Microsoft PivotTable Service does not embed level names in the fully-qualified member names that it returns. (This is not an issue with respect to the OLE DB for OLAP specification, because it simply says that providers are to present unambiguous member names.)

Flattened Rowsets

Microsoft PivotTable Service uses the following algorithm to determine whether to create a flattened rowset or a dataset. First, Microsoft PivotTable Service recognizes the IIDs IRowset, IRowsetIdentity, IRowsetInfo, IRowsetLocate, and IRowsetScroll, Then it examines DBPROPSET_Rowset and DBPROP_IRowset, which serve as a means for requesting a flattened rowset rather than a dataset. If these indicators are absent, a dataset is produced. Datasets are the preferred method for navigating the result of an MDX query.

Flattened rowsets report the data type of each column as DBTYPE_VARIANT. Clients still bind columns as strings if appropriate, and the conversion will occur successfully.

For flattened rowsets, Microsoft PivotTable Service returns DBSTATUS_S_ISNULL rather than MDSTATUS_S_NOTONAXIS. Both of these values are versions of NULL, but as a practical matter that is acceptable because the OLE DB for OLAP specification allows multiple versions of NULL in order to support missing sublevel elements. The term "missing sublevel elements" refers to the situation that arises when there are many levels for a dimension and some of the axis tuples do not exist at the lowest level. For example, consider the hierarchy [Country].[State].[City]. If [State] is the finest level of granularity, the [City] member name is returned as DBSTATUS_S_ISNULL. (DBSTATUS_S_ISNULL is the representation expected from a ROLAP implementation.)

IMDFind Interface

The IMDFind interface is not supported.

MDX Syntax

This section describes current deviations from or clarifications to the MDX syntax as described in the OLE DB for OLAP specification.

• Using an ambiguous member name or level name will not generate a warning or error and could return any one of the members that matches the ambiguous name.
  
  
• Defined sets cannot appear inside a WHERE clause. The reason for this limitation is that a defined set may contain a formula (for example, "TOP") that depends upon the context defined by the WHERE clause.
  
  
• Division operations return double-precision floating point values even when both operands are integers; this differs from the behavior of Microsoft^® SQL Server^™.
  
  
• In the Elements topic, <dim-hier>.LEVELS(index) is not supported.
  
  
• In the Expressions topic, <tuple>.VALUE is not supported.
  
  
• In the Set Value Expression topic, the following differences apply:
  
  
• When a virtual dimension of a cube contains more than 1,000 members, the HIERARCHIZE function does not sort members according to their natural order and does not delete duplicate members from a set when sorting it.
  
  
• The TOPCOUNT and BOTTOMCOUNT functions will return incorrect results if the optional third parameter, <numeric value expression> is not specified.
  
  
• The functions PERIODSTODATE, LASTPERIODS, YTD, QTD, MTD, and WTD are not implemented.
  
  
• In the Member Value Expression topic, the following differences apply:
  
  
• The FIRSTCHILD and LASTCHILD functions may return incorrect values.
  
  
• The keyword CURRENTMEMBER is implemented as CURRENT.
  
  
• The optional COUSIN, PARALLELPERIOD, OPENINGPERIOD, and CLOSINGPERIOD functions are not implemented.
  
  
• In the Tuple Value Expression topic, the keyword CURRENTMEMBER is implemented as CURRENT, and specification by <index> is not supported.
  
  
• In the Numeric Value Function topic, the optional MEDIAN, AGGREGATE, COVARIANCE, CORRELATION, LINREGSLOPE, LINREGINTERCEPT, LINREGVARIANCE, LINREG2, and LINREGPOINT functions are not implemented.
  
  
• The FONT_FLAGS member property is not supported in formulas.
  
  
• Formulas support the properties EXPRESSION, SOLVEORDER, and FORMAT_STRING.