MDSCHEMA_LEVELS Rowset

MDSCHEMA_LEVELS Rowset

 

Applies To: SQL Server 2016 Preview

Describes each level within a particular hierarchy.

The MDSCHEMA_LEVELS rowset contains the following columns.

Column nameType indicatorDescription
CATALOG_NAMEDBTYPE_WSTRThe name of the catalog to which this level belongs. NULL if the provider does not support catalogs.
SCHEMA_NAMEDBTYPE_WSTRThe name of the schema to which this level belongs. NULL if the provider does not support schemas.
CUBE_NAMEDBTYPE_WSTRThe name of the cube to which this level belongs.
DIMENSION_UNIQUE_NAMEDBTYPE_WSTRThe unique name of the dimension to which this level belongs. For providers that generate unique names by qualification, each component of this name is delimited.
HIERARCHY_UNIQUE_NAMEDBTYPE_WSTRThe unique name of the hierarchy. If the level belongs to more than one hierarchy, there is one row for each hierarchy to which it belongs. For providers that generate unique names by qualification, each component of this name is delimited.
LEVEL_NAMEDBTYPE_WSTRThe name of the level.
LEVEL_UNIQUE_NAMEDBTYPE_WSTRThe properly escaped unique name of the level.
LEVEL_GUIDDBTYPE_GUIDNot supported.
LEVEL_CAPTIONDBTYPE_WSTRA label or caption associated with the hierarchy. Used primarily for display purposes. If a caption does not exist, LEVEL_NAME is returned.
LEVEL_NUMBERDBTYPE_UI4The distance of the level from the root of the hierarchy. Root level is zero (0).
LEVEL_CARDINALITYDBTYPE_UI4The number of members in the level.
LEVEL_TYPEDBTYPE_I4Type of the level:

 MDLEVEL_TYPE_GEO_CONTINENT (0x2001)

 MDLEVEL_TYPE_GEO_REGION (0x2002)

 MDLEVEL_TYPE_GEO_COUNTRY (0x2003)

 MDLEVEL_TYPE_GEO_STATE_OR_PROVINCE (0x2004)

 MDLEVEL_TYPE_GEO_COUNTY (0x2005)

 MDLEVEL_TYPE_GEO_CITY (0x2006)

 MDLEVEL_TYPE_GEO_POSTALCODE (0x2007)

 MDLEVEL_TYPE_GEO_POINT (0x2008)

 MDLEVEL_TYPE_ORG_UNIT (0x1011)

 MDLEVEL_TYPE_BOM_RESOURCE (0x1012)

 MDLEVEL_TYPE_QUANTITATIVE (0x1013)

 MDLEVEL_TYPE_ACCOUNT (0x1014)

 MDLEVEL_TYPE_CUSTOMER (0x1021)

 MDLEVEL_TYPE_CUSTOMER_GROUP (0x1022)

 MDLEVEL_TYPE_CUSTOMER_HOUSEHOLD (0x1023)

 MDLEVEL_TYPE_PRODUCT (0x1031)

 MDLEVEL_TYPE_PRODUCT_GROUP (0x1032)

 MDLEVEL_TYPE_SCENARIO (0x1015)

 MDLEVEL_TYPE_UTILITY (0x1016)

 MDLEVEL_TYPE_PERSON (0x1041)

 MDLEVEL_TYPE_COMPANY (0x1042)

 MDLEVEL_TYPE_CURRENCY_SOURCE (0x1051)

 MDLEVEL_TYPE_CURRENCY_DESTINATION (0x1052)

 MDLEVEL_TYPE_CHANNEL (0x1061)

 MDLEVEL_TYPE_REPRESENTATIVE (0x1062)

 MDLEVEL_TYPE_PROMOTION (0x1071)
DESCRIPTIONDBTYPE_WSTRA human-readable description of the level. NULL if no description exists.
CUSTOM_ROLLUP_SETTINGSDBTYPE_I4A bitmap that specifies the custom rollup options:

 MDLEVELS_CUSTOM_ROLLUP_EXPRESSION (0x01) indicates an expression exists for this level. (Deprecated)

 MDLEVELS_CUSTOM_ROLLUP_COLUMN (0x02) indicates that there is a custom rollup column for this level.

 MDLEVELS_SKIPPED_LEVELS (0x04) indicates that there is a skipped level associated with members of this level.

 MDLEVELS_CUSTOM_MEMBER_PROPERTIES (0x08) indicates that members of the level have custom member properties.

 MDLEVELS_UNARY_OPERATOR (0x10) indicates that members on the level have unary operators.
LEVEL_UNIQUE_SETTINGSDBTYPE_I4A bitmap that specifies which columns contain unique values, if the level only has members with unique names or keys. The Msmd.h file defines the following bit value constants for this bitmap:

 MDDIMENSIONS_MEMBER_KEY_UNIQUE (1)

 MDDIMENSIONS_MEMBER_NAME_UNIQUE (2)

 

Note that the key is always unique in Microsoft SQL Server Analysis Services. The name will be unique if the setting on the attribute is UniqueInDimension or UniqueInAttribute
LEVEL_IS_VISIBLEDBTYPE_BOOLA Boolean that indicates whether the level is visible.

Always returns True. If the level is not visible, it will not be included in the schema rowset.
LEVEL_ORDERING_PROPERTYDBTYPE_WSTRThe ID of the attribute that the level is sorted on.
LEVEL_DBTYPEDBTYPE_I4The DBTYPE enumeration of the member key column that is used for the level attribute.

Null if concatenated keys are used as the member key column.
LEVEL_MASTER_UNIQUE_NAMEDBTYPE_WSTRAlways returns NULL.
LEVEL_NAME_SQL_COLUMN_NAMEDBTYPE_WSTRThe SQL representation of the level member names.
LEVEL_KEY_SQL_COLUMN_NAMEDBTYPE_WSTRThe SQL representation of the level member key values.
LEVEL_UNIQUE_NAME_SQL_COLUMN_NAMEDBTYPE_WSTRThe SQL representation of the member unique names.
LEVEL_ATTRIBUTE_HIERARCHY_NAMEDBTYPE_WSTRThe name of the attribute hierarchy providing the source of the level.
LEVEL_KEY_CARDINALITYDBTYPE_UI2The number of columns in the level key.
LEVEL_ORIGINDBTYPE_UI2A bit map that defines how the level was sourced:

 MD_ORIGIN_USER_DEFINED identifies levels in a user defined hierarchy.

 MD_ORIGIN_ATTRIBUTE identifies levels in an attribute hierarchy.

 MD_ORIGIN_KEY_ATTRIBUTE identifies levels in a key attribute hierarchy.

 MD_ORIGIN_INTERNAL identifies levels in attribute hierarchies that are not enabled.

The rowset is sorted on CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, LEVEL_NUMBER.

The MDSCHEMA_LEVELS rowset can be restricted on the columns listed in the following table.

Column nameType indicatorRestriction State
CATALOG_NAMEDBTYPE_WSTROptional.
SCHEMA_NAMEDBTYPE_WSTROptional.
CUBE_NAMEDBTYPE_WSTROptional.
DIMENSION_UNIQUE_NAMEDBTYPE_WSTROptional.
HIERARCHY_UNIQUE_NAMEDBTYPE_WSTROptional.
LEVEL_NAMEDBTYPE_WSTROptional.
LEVEL_UNIQUE_NAMEDBTYPE_WSTROptional.
LEVEL_ORIGINDBTYPE_UI2(Optional) A default restriction is in effect on MD_USER_DEFINED and MD_SYSTEM_ENABLED
CUBE_SOURCEDBTYPE_UI2(Optional) Default restriction is a value of 1. A bitmap with one of the following valid values:

1 CUBE

2 DIMENSION
LEVEL_VISIBILITYDBTYPE_UI2(Optional) Default restriction is a value of 1. A bitmap with one of the following values:

1 Visible

2 Not visible

OLE DB for OLAP Schema Rowsets

Community Additions

ADD
Show:
© 2016 Microsoft