Schema Rowsets

This section describes the contents of schema rowsets, as defined in the current implementation of Microsoft^® PivotTable^® Service. Each rowset is described by a table. Note that:

Some of the values in the "Description" column are listed as NULL because they are unknown. (The OLE DB for OLAP specification may require that some columns be non-NULL.)
The term "fully-qualified name" means that all parent elements are included in the name, up to but not including, the cube name, and that all elements are enclosed in brackets. For members, all parents are included and level names are excluded – for example:
```
[HighwaysDim].[LocationHier].[USA].[Washington].[Seattle]
```

AXIS Rowset

Note The AXIS Rowset is not a true schema rowset because it will not be returned by IDBSchemaRowset.

The current OLE DB for OLAP specification provides two separate names for columns appearing in AXIS rowsets. Microsoft PivotTable Service uses the names shown in the following table.

Name	Description
MEMBER_UNIQUE_NAME	Fully-qualified member name, for example: `[HighwaysDim].[LocationHier].[USA]. [Washington].[Seattle]`
MEMBER_CAPTION	Naked member name, for example: `Seattle`
LEVEL_UNIQUE_NAME	Level unique name, for example: `[HighwaysDim].[LocationHier].[CityLevel]`
LEVEL_NUMBER	Level depth, zero-based
CHILDREN	Can be one of the following: Zero, if it is guaranteed that no children exist The actual child count A large constant if the count is unknown. This constant is 1000 in the current implementation.

Member properties are also supported and will appear in the AxisRowset if specified in the query. Member properties can be specified for each axis but not for each dimension. For each dimension in the axis where member properties are specified, the corresponding columns from the MEMBERS schema rowset will appear. Consumers can use the information in AxisInfo to navigate this information.

CUBES Rowset

Name	Description
CATALOG_NAME	Database name.
SCHEMA_NAME	Always NULL
CUBE_NAME	Cube name
CUBE_TYPE	Either "CUBE" or "VIRTUAL CUBE"
CUBE_GUID	Always NULL
CREATED_ON	Always NULL
LAST_SCHEMA_UPDATE	Time when last cube or dimension was processed
SCHEMA_UPDATED_BY	Always NULL
LAST_DATA_UPDATE	Time when last cube or dimension was processed
DATA_UPDATED_BY	Always NULL
DESCRIPTION	Always NULL

DIMENSIONS Rowset

Name	Description
CATALOG_NAME	Database name
SCHEMA_NAME	Always NULL
CUBE_NAME	Cube name
DIMENSION_NAME	Dimension name
DIMENSION_UNIQUE_NAME	Fully-qualified dimension name.
DIMENSION_GUID	Always NULL
DIMENSION_ORDINAL	Zero-based ordinal. The measures dimension is represented by the ordinal 0.
DIMENSION_CAPTION	Dimension name
DIMENSION_TYPE	One of the following values: MD_DIMTYPE_MEASURE MD_DIMTYPE_TIME MD_DIMTYPE_OTHER
DIMENSION_CARDINALITY	Count of members. For dimensions with many members, the count may be an approximation.
DEFAULT_HIERARCHY	Returns the first hierarchy in the list for this dimension; NULL if this hierarchy has no name, otherwise a fully-qualified hierarchy name.
DESCRIPTION	Always NULL

HIERARCHIES Rowset

Name	Description
CATALOG_NAME	Database name
SCHEMA_NAME	Always NULL
CUBE_NAME	Cube name
DIMENSION_NAME	Dimension name
DIMENSION_UNIQUE_NAME	Fully-qualified dimension name
HIERARCHY_NAME	Hierarchy name
HIERARCHY_UNIQUE_NAME	Fully-qualified hierarchy name. (This is just the dimension name if there is only one hierarchy). All elements are enclosed in brackets.
HIERARCHY_GUID	Always NULL
DIMENSION_TYPE	One of the following values: MD_DIMTYPE_MEASURE MD_DIMTYPE_TIME MD_DIMTYPE_OTHER
HIERARCHY_CAPTION	Dimension name <space> <Hierarchy name>. (If no hierarchy exists, then just the dimension name.)
DEFAULT_MEMBER	Always returns the very first member
ALL_MEMBER	The name of the default member if the first level is "All"; otherwise NULL.
DESCRIPTION	Always NULL

LEVELS Rowset

Name	Description
CATALOG_NAME	Database name
SCHEMA_NAME	Always NULL
CUBE_NAME	Cube name
DIMENSION_UNIQUE_NAME	Fully-qualified dimension name
HIERARCHY_UNIQUE_NAME	Hierarchy unique name
LEVEL_NAME	Level name
LEVEL_UNIQUE_NAME	Fully-qualified level name
LEVEL_GUID	Always NULL
LEVEL_CAPTION	Level name
LEVEL_NUMBER	Zero-based ordinal of level of this dimension
LEVEL_TYPE	One of the following values: MDLEVEL_TYPE_ REGULAR MDLEVEL_TYPE_ALL MDLEVEL_TYPE_CALCULATED MDLEVEL_TYPE_TIME MDLEVEL_TYPE_RESERVED1 MDLEVEL_TYPE_TIME_YEARS MDLEVEL_TYPE_TIME_HALF_YEAR MDLEVEL_TYPE_TIME_QUARTERS MDLEVEL_TYPE_TIME_MONTHS MDLEVEL_TYPE_TIME_WEEKS MDLEVEL_TYPE_TIME_DAYS MDLEVEL_TYPE_TIME_HOURS MDLEVEL_TYPE_TIME_MINUTES MDLEVEL_TYPE_TIME_SECONDS MDLEVEL_TYPE_TIME_UNDEFINED MDLEVEL_TYPE_UNKNOWN
DESCRIPTION	Always NULL

MEASURES Rowset

Name	Description
CATALOG_NAME	Database name
SCHEMA_NAME	Always NULL
CUBE_NAME	Cube name
MEASURE_NAME	Measure name
MEASURE_UNIQUE_NAME	Fully-qualified member name, including dimension. Suitable for use in a query.
MEASURE_CAPTION	Measure name
MEASURES_GUID	Always NULL
MEASURE_AGGREGATOR	Returns one of the following values: MDMEASURE_AGGR_SUM MDMEASURE_AGGR_COUNT MDMEASURE_AGGR_MIN MDMEASURE_AGGR_MAX
DATA_TYPE	OLE DB data type that most closely matches the OLAP Provider type. One of the following values: DBTYPE_I2 DBTYPE_I4 DBTYPE_I8 DBTYPE_R4 DBTYPE_R8
NUMERIC_PRECISION	Numeric precision for OLE DB data type
NUMERIC_SCALE	Numeric scale for OLE DB data type
DESCRIPTION	Always NULL

PROPERTIES Rowset

Microsoft PivotTable Service supports cell properties only in this release.

Name	Description
CATALOG_NAME	Always NULL
SCHEMA_NAME	Always NULL
CUBE_NAME	Always NULL
DIMENSION_UNIQUE_NAME	Always NULL
HIERARCHY_UNIQUE_NAME	Always NULL
LEVEL_UNIQUE_NAME	Always NULL
MEMBER_UNIQUE_NAME	Always NULL
PROPERTY_TYPE	Always MDPROP_CELL. A bitmask indicating that this is a property of the cell. Can be used in the CELL PROPERTIES clause that occurs at the end of a SELECT statement.
PROPERTY_NAME	One of the following values: VALUE FORMATTED_VALUE CELL_ORDINAL FORMAT_STRING FORE_COLOR BACK_COLOR FONT_NAME FONT_SIZE FONT_FLAGS
PROPERTY_CAPTION	Property name
DATA_TYPE	Data type, as listed in the OLE DB specification
CHARACTER_MAXIMUM_LENGTH	255, if appropriate
CHARACTER_OCTET_LENGTH	As appropriate
NUMERIC_PRECISION	As appropriate
NUMERIC_SCALE	As appropriate
DESCRIPTION	Always NULL

MEMBERS Rowset

Name	Description
CATALOG_NAME	Database name
SCHEMA_NAME	Always NULL
CUBE_NAME	Cube name
DIMENSION_UNIQUE_NAME	Dimension unique name
HIERARCHY_UNIQUE _NAME	Hierarchy unique name
LEVEL_UNIQUE _NAME	Level unique name
LEVEL_NUMBER	Zero-based ordinal of level of this dimension
MEMBER_NAME	Member name without parent member
MEMBER_UNIQUE_NAME	Fully-qualified member name, including dimension, hierarchy, and all parent members.
MEMBER_TYPE	One of the following: MDMEMBER_TYPE_REGULAR MDMEMBER_TYPE_ALL MDMEMBER_TYPE_MEASURE MDMEMBER_TYPE_FORMULA
MEMBER_GUID	Always NULL
MEMBER_CAPTION	Member name without parent member
CHILDREN	Can be one of the following: Zero, if it is guaranteed that no children exist The actual child count A large constant if the count is unknown. This constant is 1000 in the current implementation.
PARENT_LEVEL	LEVEL_NUMBER minus 1 (0 for root level).
PARENT_NAME	Parent member name. NULL for root level of dimension.

Schema Rowset Restrictions

TREE_OPERATOR is implemented. Operators are combined with bitwise OR. The resulting rowset is the union of each operation, as required by the OLE DB for OLAP specification.

Note that duplicate rows may be generated when the algorithm for tree operators is used – for example, when retrieving the siblings of all members in a level – but this presents no practical problems.

MEMBER_NAME acts like most other restrictions. You can view the rowset as the result of a WHERE clause constraint on a giant table of all members. (This is the normal model presented by the OLE DB for OLAP specification.) Duplicates will appear. For instance, restricting member name to Portland might yield two rows, with parent names of ME and OR. Note that the member name restriction should not be enclosed in brackets, since it does not appear in the context where brackets would be needed.

The MEMBER_UNIQUE_NAME restriction acts like a WHERE clause. Input to the restriction can be fully qualified with elements enclosed in brackets. It is not an error for MEMBER_UNIQUE_NAME to result in no members – for example, city [XXX], which does not exist, will return an empty rowset.

Giving nonstring restrictions (for example, VT_NULL) for member names will yield an empty rowset.

The MEMBER_UNIQUE_NAME restriction can accept unqualified names, but will return only one matching row for the cube, even if several exist. The restriction is expected to execute more quickly than the MEMBER_NAME restriction because more information is available for use in searching.