How can I use SQL in XMLA for SSAS?

Question

I have a problem with usage of SQL in XMLA for SSAS. It is required for Python library that interacts with SSAS server using SOAP protocol. There are 2 ways to reach data: MDX (multidimentional mode) and SQL (tabular mode). I have a simple task to select data from 1 table. So SQL should fit best, but it has too many limitations: there is no groups, havings, limits, also WHERE clause is limited to "=" operator. So it cannot be used for large tables in real world.

Example of code:

<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns="urn:schemas-microsoft-com:xml-analysis">
  <soap-env:Body>
    <Execute>
      <Command>
        <Statement>
          SELECT [Customer Id] as [Customer.Customer Id] ,[Title] as [Customer.Title]
          FROM [Adventure Works Internet Sales Model].[$Customer]
          WHERE ([Customer Id]="11000" OR [Customer Id]="11001" OR [Customer Id]="11002" OR [Customer Id]="11003" OR [Customer Id]="11004")
        </Statement>
      </Command>
      <Properties>
        <PropertyList>
          <Format>Tabular</Format>
          <AxisFormat>TupleFormat</AxisFormat>
          <MaximumRows>2</MaximumRows>
        </PropertyList>
      </Properties>
    </Execute>
  </soap-env:Body>
</soap-env:Envelope>

Also, "MaximumRows" doesn't works here. Result is next:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ExecuteResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
  <return>
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msxmla="http://schemas.microsoft.com/analysisservices/2003/xmla">
      <xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" 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="Customer.Customer Id" name="Customer.Customer_x0020_Id" type="xsd:string" minOccurs="0" />
            <xsd:element sql:field="Customer.Title" name="Customer.Title" type="xsd:string" minOccurs="0" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:schema>
      <row>
        <Customer.Customer_x0020_Id>11000</Customer.Customer_x0020_Id>
      </row>
      <row>
        <Customer.Customer_x0020_Id>11001</Customer.Customer_x0020_Id>
      </row>
      <row>
        <Customer.Customer_x0020_Id>11002</Customer.Customer_x0020_Id>
      </row>
      <row>
        <Customer.Customer_x0020_Id>11003</Customer.Customer_x0020_Id>
      </row>
      <row>
        <Customer.Customer_x0020_Id>11004</Customer.Customer_x0020_Id>
      </row>
    </root>
  </return>
</ExecuteResponse>

</soap:Body> </soap:Envelope>

So, my next investigation was using MDX query. I know it's more powerful. And I also can use XMLA for that. But I cannot realize how to get data in "tabular" mode. Something like that:

      select {[Customer].members} on columns
      from [Adventure Works Internet Sales Model]

But I cannot reach this goal for now. Can someone help me?

How can I use SQL in XMLA for SSAS?

0 Answers0