John Hennesey


Does anyone know if it is possible to query (MDX or XMLA, or whatever) a cube or dimension to retrieve a list of USER DEFINED hierarchies Is there any kind of property that will differentiate hierarchies vs. normal attributes

Also, using c# will work too.

Thanks so much,

John




Re: Query to retrieve list of hierarchies in a dimension

Deepak Puri


Using XMLA Discover(), if you retrieve the MDSCHEMA_HIERARCHIES Rowset, the HIERARCHY_ORIGIN property has a value of 1 for user-defined hierarchies:

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.

So, if you execute this XMLA:

Code Block

Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>MDSCHEMA_HIERARCHIES</< FONT>RequestType>

<Restrictions>

<RestrictionList>

<CATALOG_NAME>Adventure Works DW</< FONT>CATALOG_NAME>

<CUBE_NAME>Adventure Works</< FONT>CUBE_NAME>

</< FONT>RestrictionList>

</< FONT>Restrictions>

<Properties>

<PropertyList>

<Catalog>Adventure Works DW</< FONT>Catalog>

<Format>Tabular</< FONT>Format>

</< FONT>PropertyList>

</< FONT>Properties>

</< FONT>Discover>

the [Date].[Calendar] hierarchy returns:

Code Block

<row>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<DIMENSION_UNIQUE_NAME>[Date]</DIMENSION_UNIQUE_NAME>

<HIERARCHY_NAME>Calendar</HIERARCHY_NAME>

<HIERARCHY_UNIQUE_NAME>[Date].[Calendar]</HIERARCHY_UNIQUE_NAME>

<HIERARCHY_CAPTION>Date.Calendar</HIERARCHY_CAPTION>

<DIMENSION_TYPE>1</DIMENSION_TYPE>

<HIERARCHY_CARDINALITY>1226</HIERARCHY_CARDINALITY>

<DEFAULT_MEMBER>[Date].[Calendar].[All Periods]</DEFAULT_MEMBER>

<ALL_MEMBER>[Date].[Calendar].[All Periods]</ALL_MEMBER>

<DESCRIPTION />

<STRUCTURE>0</STRUCTURE>

<IS_VIRTUAL>false</IS_VIRTUAL>

<IS_READWRITE>false</IS_READWRITE>

<DIMENSION_UNIQUE_SETTINGS>1</DIMENSION_UNIQUE_SETTINGS>

<DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE>

<HIERARCHY_ORDINAL>2</HIERARCHY_ORDINAL>

<DIMENSION_IS_SHARED>true</DIMENSION_IS_SHARED>

<HIERARCHY_IS_VISIBLE>true</HIERARCHY_IS_VISIBLE>

<HIERARCHY_ORIGIN>1</HIERARCHY_ORIGIN>

<HIERARCHY_DISPLAY_FOLDER>Calendar</HIERARCHY_DISPLAY_FOLDER>

<GROUPING_BEHAVIOR>1</GROUPING_BEHAVIOR>

</row>