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 2005 Analysis Services (SSAS).

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_USER_DEFINED identifies user defined hierarchies, and has a value of 0x0000001.
  • MD_SYSTEM_ENABLED identifies attribute hierarchies, and has a value of 0x0000002.
  • MD_SYSTEM_INTERNAL identifies attributes with no attribute hierarchies, and has a value of 0x0000004.

A parent/child attribute hierarchy is both MD_USER_DEFINED and MD_SYSTEM_ENABLED.

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

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

Help and Information

Getting SQL Server 2005 Assistance