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
– pyguypy, Commented Nov 23, 2020 at 21:33

pyguypy · Accepted Answer · 2020-12-15 04:15:08Z

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, 2020 at 4:15

pyguypy

416 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mzjn Over a year ago

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.

Collectives™ on Stack Overflow

How to use python to run an xmla query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related