Levels

You can obtain all the details you need for the levels in a specified catalog.

Request

A query for levels must include the following:

The SELECT statement must start with one or more level parameters (comma separated).
After the parameters you must provide the following data source: FROM $system.MDSCHEMA_LEVELS
The name of the published catalog (AtScale project) for which you need level information must be specified in the Properties > PropertyList > Catalog element. For details on obtaining a catalog name, see Catalogs.

You can request the following level parameters:

CATALOG_NAME
CUBE_GUID
CUBE_NAME
CUSTOM_ROLLUP_SETTINGS
DATASET_NAME
DESCRIPTION
DIMENSION_UNIQUE_NAME
HIERARCHY_UNIQUE_NAME
IS_PRIMARY
LEVEL_ATTRIBUTE_HIERARCHY_NAME
LEVEL_CAPTION
LEVEL_CARDINALITY
LEVEL_DBTYPE
LEVEL_DBTYPE_NAME_COLUMN
LEVEL_DBTYPE_SORT_COLUMN
LEVEL_GUID
LEVEL_IS_VISIBLE
LEVEL_KEY_CARDINALITY
LEVEL_KEY_SQL_COLUMN_NAME
LEVEL_MASTER_UNIQUE_NAME
LEVEL_NAME
LEVEL_NAME_SQL_COLUMN_NAME
LEVEL_NUMBER
LEVEL_ORDERING_PROPERTY
LEVEL_ORIGIN
LEVEL_SECONDARY_ATTRIBUTE_FOLDER
LEVEL_SORT_SQL_COLUMN_NAME
LEVEL_TYPE
LEVEL_UNIQUE_NAME
LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME
LEVEL_UNIQUE_SETTINGS
PARENT_LEVEL_ID
SCHEMA_NAME

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 level 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 level name (LEVEL_NAME), the dimension name (DIMENSION_UNIQUE_NAME), and the hierarchy name (HIERARCHY_UNIQUE_NAME).
The request is for the levels 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 LEVEL_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME FROM $system.MDSCHEMA_LEVELS</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 LEVEL_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME FROM $system.MDSCHEMA_LEVELS</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 level found. As requested, each of these elements contains only the level, dimension, and hierarchy name:

<row>
  <LEVEL_NAME>Customer Name</LEVEL_NAME>
  <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
  <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Customer Hierarchy]</HIERARCHY_UNIQUE_NAME>
</row>
<row>
  <LEVEL_NAME>(All)</LEVEL_NAME>
  <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
  <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Occupation]</HIERARCHY_UNIQUE_NAME>
</row>
<row>
  <LEVEL_NAME>Occupation</LEVEL_NAME>
  <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
  <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Occupation]</HIERARCHY_UNIQUE_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">b80ade26-df1d-430a-a733-fb5a8fe3297c</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="LEVEL_NAME" name="LEVEL_NAME" type="xsd:string" minOccurs="0"/>
                <xsd:element sql:field="DIMENSION_UNIQUE_NAME" name="DIMENSION_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>
                <xsd:element sql:field="HIERARCHY_UNIQUE_NAME" name="HIERARCHY_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>
              </xsd:sequence>
            </xsd:complexType>
          </xsd:schema>
          <row>
            <LEVEL_NAME>Customer Name</LEVEL_NAME>
            <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
            <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Customer Hierarchy]</HIERARCHY_UNIQUE_NAME>
          </row>
          <row>
            <LEVEL_NAME>(All)</LEVEL_NAME>
            <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
            <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Occupation]</HIERARCHY_UNIQUE_NAME>
          </row>
          <row>
            <LEVEL_NAME>Occupation</LEVEL_NAME>
            <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
            <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Occupation]</HIERARCHY_UNIQUE_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.

Additional Reference