Hierarchies
You can obtain all the details you need for the hierarchies in a specified catalog.
Request
A query for hierarchies must include the following:
- The SELECT statement must start with one or more hierarchy parameters (comma separated).
- After the parameters you must provide the following data source:
FROM $system.MDSCHEMA_HIERARCHIES
- The name of the published catalog (AtScale project) for which you need
hierarchy information must be specified in the
Properties > PropertyList > Catalog
element. For details on obtaining a catalog name, see Catalogs.
You can request the following hierarchy parameters:
- ALL_MEMBER
- CATALOG_NAME
- CUBE_GUID
- CUBE_NAME
- DEFAULT_MEMBER
- DESCRIPTION
- DIMENSION_IS_SHARED
- DIMENSION_IS_VISIBLE
- DIMENSION_TYPE
- DIMENSION_UNIQUE_NAME
- DIMENSION_UNIQUE_SETTINGS
- GROUPING_BEHAVIOR
- HIERARCHY_CAPTION
- HIERARCHY_CARDINALITY
- HIERARCHY_DISPLAY_FOLDER
- HIERARCHY_GUID
- HIERARCHY_IS_VISIBLE
- HIERARCHY_NAME
- HIERARCHY_ORDINAL
- HIERARCHY_ORIGIN
- HIERARCHY_UNIQUE_NAME
- INSTANCE_SELECTION
- STRUCTURE
- STRUCTURE_TYPE
For details about the parameter types, see the xsd:schema
element in
the sample response provided below.
Response
The response contains a <row>
element for each hierarchy in the
specified catalog. Each of these elements contains the values of the
requested parameters.
Sample request
The example here shows how to make a request with the curl
tool:
- The requested data are the members (ALL_MEMBER) and name (HIERARCHY_NAME).
- The request is for the hierarchies in the Sales Insights catalog.
- The token is obtained in advance.
- The query is for the default organization.
- The address of the AtScale system is:
http://example.com:10502/xmla/default
The XML part of the request is as follows:
<?xml version="1.0"?>
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement>SELECT ALL_MEMBER, HIERARCHY_NAME FROM $system.MDSCHEMA_HIERARCHIES</Statement>
</Command>
<Properties>
<PropertyList>
<Catalog>Sales Insights</Catalog>
</PropertyList>
</Properties>
</Execute>
</Body>
</Envelope>
Here is the full form of the curl request:
curl -X POST \
-H "Authorization:Bearer $token" -H 'Content-Type: application/xml' \
-d '<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">\
<Body><Execute xmlns="urn:schemas-microsoft-com:xml-analysis">\
<Command>\
<Statement>SELECT ALL_MEMBER, HIERARCHY_NAME FROM $system.MDSCHEMA_HIERARCHIES</Statement>\
</Command>\
<Properties><PropertyList>\
<Catalog>Sales Insights</Catalog>
</PropertyList></Properties>\
</Execute></Body></Envelope>' \
http://example.com:10502/xmla/default
Sample response
The response for the sample request above contains <row>
elements for
each hierarchy found. As requested, each of these elements contains only
the name and members of the hierarchy:
<row>
<ALL_MEMBER>[Color Dimension].[Color].[All]</ALL_MEMBER>
<HIERARCHY_NAME>Color</HIERARCHY_NAME>
</row>
<row>
<ALL_MEMBER>[Customer Dimension].[Customer Hierarchy].[All]</ALL_MEMBER>
<HIERARCHY_NAME>Customer Hierarchy</HIERARCHY_NAME>
</row>
<row>
<ALL_MEMBER>[Customer Dimension].[Occupation].[All]</ALL_MEMBER>
<HIERARCHY_NAME>Occupation</HIERARCHY_NAME>
</row>
Here is the full response:
<?xml version="1.0"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<as:queryId xmlns:as="http://xsd.atscale.com/soap_v1" soap:mustUnderstand="0">4761fb48-84ca-479a-a29c-6ea27f2bbefb</as:queryId>
</soap:Header>
<soap:Body>
<ExecuteResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
<return>
<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:msxmla="http://schemas.microsoft.com/analysisservices/2003/xmla" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsd:schema xmlns:sql="urn:schemas-microsoft-com:xml-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="row" type="row"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:simpleType name="uuid">
<xsd:restriction base="xsd:string">
<xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="xmlDocument">
<xsd:sequence>
<xsd:any/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="row">
<xsd:sequence>
<xsd:element sql:field="ALL_MEMBER" name="ALL_MEMBER" type="xsd:string" minOccurs="0"/>
<xsd:element sql:field="HIERARCHY_NAME" name="HIERARCHY_NAME" type="xsd:string" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<row>
<ALL_MEMBER>[Color Dimension].[Color].[All]</ALL_MEMBER>
<HIERARCHY_NAME>Color</HIERARCHY_NAME>
</row>
<row>
<ALL_MEMBER>[Customer Dimension].[Customer Hierarchy].[All]</ALL_MEMBER>
<HIERARCHY_NAME>Customer Hierarchy</HIERARCHY_NAME>
</row>
<row>
<ALL_MEMBER>[Customer Dimension].[Occupation].[All]</ALL_MEMBER>
<HIERARCHY_NAME>Occupation</HIERARCHY_NAME>
</row>
</root>
</return>
</ExecuteResponse>
</soap:Body>
</soap:Envelope>
More information
- Request reference: How to make requests to the Data Catalog API.
- Response reference: How to receive and process responses from the Data Catalog API.