MDSCHEMA_HIERARCHIES Rowset

  • Article

Describes each hierarchy within a particular dimension.

Rowset Columns

The MDSCHEMA_HIERARCHIES rowset contains the following columns.

Column name

Type indicator

Length

Description

CATALOG_NAME

DBTYPE_WSTR

The name of the catalog to which this hierarchy belongs. NULL if the provider does not support catalogs.

SCHEMA_NAME

DBTYPE_WSTR

Not supported

CUBE_NAME

DBTYPE_WSTR

(Required) The name of the cube to which this hierarchy belongs.

DIMENSION_UNIQUE_NAME

DBTYPE_WSTR

The unique name of the dimension to which this hierarchy belongs. For providers that generate unique names by qualification, each component of this name is delimited.

HIERARCHY_NAME

DBTYPE_WSTR

The name of the hierarchy. Blank if there is only a single hierarchy in the dimension. This will always have a value in Microsoft SQL Server Analysis Services.

HIERARCHY_UNIQUE_NAME

DBTYPE_WSTR

The unique name of the hierarchy.

HIERARCHY_GUID

DBTYPE_GUID

Not supported

HIERARCHY_CAPTION

DBTYPE_WSTR

A label or a caption associated with the hierarchy. Used primarily for display purposes. If a caption does not exist, HIERARCHY_NAME is returned. If the dimension either does not contain a hierarchy or has just one hierarchy, this column will contain the name of the dimension.

DIMENSION_TYPE

DBTYPE_I2

The type of the dimension. Valid values include the following values:

  • MD_DIMTYPE_UNKNOWN (0)

  • MD_DIMTYPE_TIME (1)

  • MD_DIMTYPE_MEASURE (2)

  • MD_DIMTYPE_OTHER (3)

  • MD_DIMTYPE_QUANTITATIVE (5)

  • MD_DIMTYPE_ACCOUNTS (6)

  • MD_DIMTYPE_CUSTOMERS (7)

  • MD_DIMTYPE_PRODUCTS (8)

  • MD_DIMTYPE_SCENARIO (9)

  • MD_DIMTYPE_UTILIY (10)

  • MD_DIMTYPE_CURRENCY (11)

  • MD_DIMTYPE_RATES (12)

  • MD_DIMTYPE_CHANNEL (13)

  • MD_DIMTYPE_PROMOTION (14)

  • MD_DIMTYPE_ORGANIZATION (15)

  • MD_DIMTYPE_BILL_OF_MATERIALS (16)

  • MD_DIMTYPE_GEOGRAPHY (17)

HIERARCHY_CARDINALITY

DBTYPE_UI4

The number of members in the hierarchy.

DEFAULT_MEMBER

DBTYPE_WSTR

The default member for this hierarchy. This is a unique name. Every hierarchy must have a default member.

ALL_MEMBER

DBTYPE_WSTR

The member at the highest level of the rollup.

DESCRIPTION

DBTYPE_WSTR

A human-readable description of the hierarchy. NULL if no description exists.

STRUCTURE

DBTYPE_I2

The structure of the hierarchy. Valid values include the following values:

  • MD_STRUCTURE_FULLYBALANCED (0)

  • MD_STRUCTURE_RAGGEDBALANCED (1)

  • MD_STRUCTURE_UNBALANCED (2)

  • MD_STRUCTURE_NETWORK (3)

IS_VIRTUAL

DBTYPE_BOOL

Always returns False.

IS_READWRITE

DBTYPE_BOOL

A Boolean that indicates whether the Write Back to dimension column is enabled.

Returns TRUE if the Write Back to dimension column that represents this hierarchy is enabled.

DIMENSION_UNIQUE_SETTINGS

DBTYPE_I4

Always returns MDDIMENSIONS_MEMBER_KEY_UNIQUE (1).

DIMENSION_MASTER_UNIQUE_NAME

DBTYPE_WSTR

Always returns NULL.

DIMENSION_IS_VISIBLE

DBTYPE_BOOL

Always returns true. If the dimension is not visible, it will not appear in the schema rowset.

HIERARCHY_ORDINAL

DBTYPE_UI4

The ordinal number of the hierarchy across all hierarchies of the cube.

DIMENSION_IS_SHARED

DBTYPE_BOOL

Always returns TRUE.

HIERARCHY_IS_VISIBLE

DBTYPE_BOOL

A Boolean that indicates whether the hieararchy is visible.

Returns TRUE if the hierarchy is visible; otherwise, FALSE.

HIERARCHY_ORIGIN

DBTYPE_UI2

A bit mask that determines the source of the hierarchy:

  • MD_ORIGIN_USER_DEFINED identifies levels in a user defined hierarchy.

  • MD_ORIGIN_ATTRIBUTE identifies levels in an attribute hierarchy.

  • MD_ORIGIN_INTERNAL identifies levels in attribute hierarchies that are not enabled.

  • MD_ORIGIN_KEY_ATTRIBUTE identifies levels in a key attribute hierarchy.

HIERARCHY_DISPLAY_FOLDER

DBTYPE_WSTR

The path to be used when displaying the hierarchy in the user interface. Folder names will be separated by a semicolon (;). Nested folders are indicated by a backslash (\).

INSTANCE_SELECTION

DBTYPE_UI2

A hint to the client application on how to show the hierarchy. Valid values include the following values:

  • MD_INSTANCE_SELECTION_NONE

  • MD_INSTANCE_SELECTION_DROPDOWN

  • MD_INSTANCE_SELECTION_LIST

  • MD_INSTANCE_SELECTION_FILTEREDLIST

  • MD_INSTANCE_SELECTION_MANDATORYFILTER

GROUPING_BEHAVIOR

DBTYPE_I2

An enumeration that specifies the expected grouping behavior of clients for this hierarchy. Possible values are the following:

  • EncourageGrouping (1)

  • DiscourageGrouping (2)

STRUCTURE_TYPE

DBTYPE_WSTR

Indicates the type of hierarchy. Valid values include the following values:

  • Natural

  • Unnatural

  • Unknown

The rowset is sorted on CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_NAME.

Restriction Columns

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

Column name

Type indicator

Restriction State

CATALOG_NAME

DBTYPE_WSTR

Optional.

SCHEMA_NAME

DBTYPE_WSTR

Optional.

CUBE_NAME

DBTYPE_WSTR

Optional.

DIMENSION_UNIQUE_NAME

DBTYPE_WSTR

Optional.

HIERARCHY_NAME

DBTYPE_WSTR

Optional.

HIERARCHY_UNIQUE_NAME

DBTYPE_WSTR

Optional.

HIERARCHY_ORIGIN

DBTYPE_UI2

(Optional) A default restriction is in effect on MD_USER_DEFINED and MD_SYSTEM_ENABLED.

CUBE_SOURCE

DBTYPE_UI2

(Optional) A bitmap with one of the following valid values:

  • 1 CUBE

  • 2 DIMENSION

Default restriction is a value of 1.

HIERARCHY_VISIBILITY

DBTYPE_UI2

(Optional) A bitmap with one of the following valid values:

  • 1 Visible

  • 2 Not visible

Default restriction is a value of 1.

See Also

Reference

OLE DB for OLAP Schema Rowsets