How to use python to run an xmla query

Question

My requirement is to use python to connect to Analysis Services server, run an xmla query, such as

<Discover xmlns='urn:schemas-microsoft-com:xml-analysis'>
    <RequestType>MDSCHEMA_CUBES</RequestType>
    <Restrictions/>
    <Properties>
        <PropertyList>
            <Catalog>Q2C Analysis</Catalog>
        </PropertyList>
    </Properties>
</Discover>

get the results which will be

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <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="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" />
          <xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="CUBE_NAME" name="CUBE_NAME" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="CUBE_TYPE" name="CUBE_TYPE" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="CUBE_GUID" name="CUBE_GUID" type="uuid" minOccurs="0" />
          <xsd:element sql:field="CREATED_ON" name="CREATED_ON" type="xsd:dateTime" minOccurs="0" />
          <xsd:element sql:field="LAST_SCHEMA_UPDATE" name="LAST_SCHEMA_UPDATE" type="xsd:dateTime" minOccurs="0" />
          <xsd:element sql:field="SCHEMA_UPDATED_BY" name="SCHEMA_UPDATED_BY" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="LAST_DATA_UPDATE" name="LAST_DATA_UPDATE" type="xsd:dateTime" minOccurs="0" />
          <xsd:element sql:field="DATA_UPDATED_BY" name="DATA_UPDATED_BY" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="DESCRIPTION" name="DESCRIPTION" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="IS_DRILLTHROUGH_ENABLED" name="IS_DRILLTHROUGH_ENABLED" type="xsd:boolean" minOccurs="0" />
          <xsd:element sql:field="IS_LINKABLE" name="IS_LINKABLE" type="xsd:boolean" minOccurs="0" />
          <xsd:element sql:field="IS_WRITE_ENABLED" name="IS_WRITE_ENABLED" type="xsd:boolean" minOccurs="0" />
          <xsd:element sql:field="IS_SQL_ENABLED" name="IS_SQL_ENABLED" type="xsd:boolean" minOccurs="0" />
          <xsd:element sql:field="CUBE_CAPTION" name="CUBE_CAPTION" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="BASE_CUBE_NAME" name="BASE_CUBE_NAME" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="CUBE_SOURCE" name="CUBE_SOURCE" type="xsd:unsignedShort" minOccurs="0" />
          <xsd:element sql:field="PREFERRED_QUERY_PATTERNS" name="PREFERRED_QUERY_PATTERNS" type="xsd:unsignedShort" minOccurs="0" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:schema>
    <row>
      <CATALOG_NAME>Q2C Analysis</CATALOG_NAME>
      <CUBE_NAME>Q2C Thor</CUBE_NAME>
      <CUBE_TYPE>CUBE</CUBE_TYPE>
      <LAST_SCHEMA_UPDATE>2020-11-20T07:07:10.88</LAST_SCHEMA_UPDATE>
      <LAST_DATA_UPDATE>2020-11-20T07:14:47.023333</LAST_DATA_UPDATE>
      <DESCRIPTION />
      <IS_DRILLTHROUGH_ENABLED>true</IS_DRILLTHROUGH_ENABLED>
      <IS_LINKABLE>false</IS_LINKABLE>
      <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>
      <IS_SQL_ENABLED>false</IS_SQL_ENABLED>
      <CUBE_CAPTION>Q2C Thor</CUBE_CAPTION>
      <CUBE_SOURCE>1</CUBE_SOURCE>
      <PREFERRED_QUERY_PATTERNS>1</PREFERRED_QUERY_PATTERNS>
    </row>
  </root>
</return>

And then extract the values I need from the relevant tag element.

Is there a way to achieve this? Thanks in advance.

I read that I can use olapy module but the documentation is very light on details on how to connect to the AS Server and run the query. I did manage to instantiate an MDXEngine but it seems it was expecting an MDX query rather than xmla query (in hindsight it does say MDXEngine, in the olapy module) so perhaps an oversight on my part. — pyguypy, Nov 23 '20 at 21:33

score 1 · Answer 1 · answered Dec 15 '20 at 04:15

1

So I finally found a way to solve this issue and it was surprisingly easy (or at least to me it was).

First install adodbapi and pyodbc Second specify the connection strings for Olap - you can find the correct connectionstring pattern here Third, define the DMV query that needs to be run on the analysis services server Fourth, get the result and parse it into a list ready to be inserted into a SQL data or required output.

answered Dec 15 '20 at 04:15

pyguypy

29
4

1

Seems like an incomplete answer. The question title is "How to use python to run an xmla query" and it is tagged "python". But there is not a single line of Python code in the question or in this answer. – mzjn Dec 15 '20 at 07:54

How to use python to run an xmla query

1 Answers1