OLE DB Compliance
This section contains an overview of the differences between the features of this version of Microsoft® SQL Server™ 2000 Analysis Services and the OLAP section of the OLE DB specification of March 1999 (version 2.6).
Analysis Services complies with all but 4 of the 394 mandatory items required by the specification.
Analysis Services implements all but one of the seven optional items identified in the
Analysis Services extends the MDX grammar with 32 functions and aliases.
Analysis Services implements the optional IMDFind interface as defined in the specification.
Noncompliant Items
The following items are part of the OLE DB specification that Analysis Services either implements differently from the specification, or omits entirely.
Mandatory
Of the four mandatory items that do not comply with the OLE DB specification in Analysis Services, one is an MDX function, one is a member function of a COM interface, and the last two are related to MDX grammar. The following table identifies each item and explains the difference between the specification and Analysis Services implementation.
| Item | Analysis Services implementation | OLE DB specification |
|---|---|---|
| Ancestors(«Member», «Level») | «Level» must be a level of the same hierarchy as «Member». | The set of returned members must all be from the same hierarchy, but «Level» need not be a level of the same hierarchy as «Member». |
| Count(«Set»[, EXCLUDEEMPTY | INCLUDEEMPTY]) | Empty cells are counted by default. | Empty cells are not counted by default. |
| Descendants(«Member», «Level»[, «Desc_flags»] | Returns leaf members on all levels between member and specified level. | Returns leaf members irrespective of the level. |
| Descendants(«Member», «Distance»[, «Desc_flags»] | Returns leaf members on all levels between member and specified distance in hierarchy. | Returns leaf members irrespective of the distance. |
| IMDRangeRowset::GetRangeRowset | Returns S_OK only if the requested range returns a single cell or the entire dataset. Any other range returns E_FAIL. | Partial datasets are allowed. |
| Expression delimiters | Single quotation marks are required around expressions that define calculated members in MDX statements. | No delimit characters are required. |
| Cell Property Value delimiters | Single quotation marks are required around cell property values in MDX statements. | No delimit characters are required. |
Optional
Of the seven optional MDX functions identified in the BNF specification, Analysis Services implements all but one. This omitted function is the CASE statement, both simple and searched forms.
Additional Items and Extensions
Analysis Services provides additional functionality that extends the OLE DB specification by implementing an optional interface and providing 32 additional MDX functions and aliases.
IMDFind
IMDFind is an optional interface on the dataset object. This interface contains methods that find the ordinal number of a cell in a dataset and the ordinal number of a tuple on an axis.
| Method | Description |
|---|---|
| FindCell | Finds and returns a cell ordinal based on an array of members and a starting cell ordinal in the dataset. |
| FindTuple | Returns a pointer to a tuple ordinal based on an array of members, a starting ordinal, and an axis identifier. |
MDX Extensions
These MDX functions are available for use with Analysis Services even though they do not appear in the OLE DB specification. Most provide additional functionality, and some act as aliases for existing functions.
| Function | Description |
|---|---|
| AddCalculatedMembers(«Set») | Adds calculated members to a set. |
| «Member».Children | Returns the children of a member. |
| CovarianceN(«Set», «Numeric Expression»[, «Numeric Expression»]) | Returns the covariance of two series evaluated over a set (unbiased). |
| Crossjoin(«Set1», «Set2») | Returns the cross product of two sets. |
| «Set».Current | Returns the current tuple from a set during an iteration. |
| Dimensions(«Numeric Expression») | Returns the dimension whose zero-based position within the cube is specified by a numeric expression. |
| Dimensions(«String Expression») | Returns the dimension whose name is specified by a string. |
| DrilldownLevel(«Set», , «Index») | Drills down into a specified dimension in the set. |
| Head(«Set»[, « Numeric Expression »]) | Returns the first specified number of elements in a set. |
| Levels(«String Expression») | Returns the level whose name is specified by a string expression. |
| «Hierarchy».Levels(«Numeric Expression») | Returns the level whose position in a hierarchy is specified by a numeric expression. |
| Members(«String Expression») | Returns the member whose name is specified by a string expression. |
| «Level».Ordinal | Returns the zero-based ordinal value associated with a level. |
| SetToArray(«Set»[, «Set»]...[, «Numeric Expression»]) | Converts one or more sets to an array for use in a user-defined function. |
| SetToStr(«Set») | Constructs a string from a set. |
| Stddev(«Set»[, «Numeric Expression»]) | Alias for Stdev. |
| StddevP(«Set»[, «Numeric Expression»]) | Alias for StdevP. |
| StdevP(«Set»[, «Numeric Expression»]) | Returns the standard deviation of a numeric expression evaluated over a set (biased). |
| StripCalculatedMembers(«Set») | Removes calculated members from a set. |
| StrToSet(«String Expression») | Constructs a set from a string expression. |
| StrToTuple(«String Expression») | Constructs a tuple from a string. |
| Subset («Set», «Start»[, «Count»]) | Returns a subset of elements from a set. |
| Tail(«Set»[, «Count»]) | Returns a subset from the end of a set. |
| TupleToStr(«Tuple») | Constructs a string from a tuple. |
| «Dimension».UniqueName | Returns the unique name of a dimension. |
| «Level».UniqueName | Returns the unique name of a level. |
| «Member».UniqueName | Returns the unique name of a member. |
| ValidMeasure(«Tuple») | Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level. |
| Variance(«Set»[, «Numeric Expression»]) | Alias for Var. |
| VarianceP(«Set»[, «Numeric Expression»]) | Alias for VarP. |
| VarP(«Set»[, «Numeric Expression»]) | Returns the variance of a numeric expression evaluated over a set (biased). |
| VisualTotals(«Set», «Pattern») | Dynamically totals child members specified in a set using a pattern for the total label in the result set. |