Update for Python3

[kmcnayr]

Not sure if this is the right place: the current version doesn't appear to work with Python3 due to reliance on the suds package. The package suds-jurko appears to be a potential replacement. Any plan to update to work with Python3?

[may-day]

i will update it to work with py3, no timetable tho :(

[harpsoe]

I have been working on getting xmla to work under python 3. And I can almost get it to work. I have updated to suds-jurko and made some other fixes. But the but the Discover function in XMLAConnection keeps tripping me up. I think that the object inheritance in py3 has changes in ways that I have not comprehended. Are you interested in getting a copy of the almost working code?

[may-day]

I already started my own changes :) - see the py3 branch. The goal ist to have a common codebase for python [2.6, 3.x]. The problems i'm facing are the optional dependencies on kerberos and s4u2p packages (both are a must for my usecases). Being the author of s4u2p i can certainly fix that one. For the kerberos package there doesn't seem to exist a python3 version.

[may-day]

Could you try the py3 branch? It works for me now with python2 and python3 (using suds-jurko and the new requests 2.5.1) against mondrian.

[kmcnayr]

Could you walk me through how to compile and install? Do I need to build in the three folders olap/rest/xmla? I'm getting import errors when trying to install.

Edit: I didn't have the py3 branch checked out... I was able to compile, install, and test similar to the samples using SSAS 2012 version 12.0.20000.8 Compiled on: Ubuntu x64 Python 3.4

SUCCESS! Both query and procedural interfaces work.

Thanks for the quick update! I'm very excited.

[harpsoe]

I get the following error when I try to import the py3 branch on windows and python 3.

import olap.xmla.xmla as xmla No s4u2p module, so you won't be able to impersonate a user. Traceback (most recent call last): File "", line 1, in File ".\olap\xmla\xmla.py", line 2, in from .connection import XMLAConnection File ".\olap\xmla\connection.py", line 9, in from . import http File ".\olap\xmla\http.py", line 31, in import mimetools ImportError: No module named 'mimetools'

Here is my attempted fix:

if sys.platform.startswith("win"): import os import email.utils import email.message import mimetypes from io import StringIO

class MyDriveFileHandler(urllib.request.FileHandler):
    def open_local_file(self, req):
        try:
            host = req.get_host()
            filename = req.get_selector()
            # if that bombs, then go on with original method
            localfile = urllib.request.url2pathname(host+filename)
            stats = os.stat(localfile) 
            size = stats.st_size
            modified = email.utils.formatdate(stats.st_mtime, usegmt=True)
            mtype = mimetypes.guess_type(filename)[0]
            headers = email.message.Message(StringIO(
                    'Content-type: %s\nContent-length: %d\nLast-modified: %s\n' %
                    (mtype or 'text/plain', size, modified)))
            origurl = 'file://' + host + filename
            return urllib.responce.addinfourl(open(localfile, 'rb'), headers, origurl)
        except:
            pass
        return urllib.request.FileHandler.open_local_file(self, req)

[kmcnayr]

I also haven't been able to install on windows. It seems like it's still looking for the suds package. I'm not experienced with this so apologies if I don't know what I'm doing. Pretty sure I am using the py3 branch. I noticed the versions.cfg lists suds0.4; not sure if that is an issue or not.

[harpsoe]

I can't remember which file lists the dependencies, but it should probably not depend on suds0.4. Try to remove the reference to suds0.4, then install suds-jurko manually and finally install this package. suds-jurko maps to suds inside python.

[may-day]

@kmcnayr: Umm, you refer to the versions.cfg that is used by buildout - which in turn is horrible out of date in this repo (sorry) . but fear not, here is how to do it:

$ git clone https://github.com/may-day/olap.git
$ cd olap/xmla
$ git checkout py3
$ python setup.py sdist
$ pip install dist/xmla-0.8.0.tar.gz

optionally create/activate your virtual environment before building and installing the package Edit: err, those directives will probably not help on windows - oops. The idea is basically to create a distributable package and then install that.

[may-day]

@kmcnayr and @harpsoe, i got my hands on a windows box and fixed some stuff - so you might want to test again. Also, notice that a new version of kerberos-sspi is now used (if you need kerberos authentication).

[harpsoe]

I pulled the changes and I can now install and load the package in python I followed the steps in import olap.xmla.xmla as xmla

p = xmla.XMLAProvider()
# mondrian
c = p.connect(location="http://localhost:8080/mondrian/xmla")
# to analysis services (if iis proxies requests at /olap/msmdpump.dll)
# you will need a valid kerberos principal of course
# c = p.connect(location="https://my-as-server/olap/msmdpump.dll", 
#               sslverify="/path/to/my/as-servers-ca-cert.pem")
# to icCube
# c = p.connect(location="http://localhost:8282/icCube/xmla", username="demo", 
#               password="demo")

With the appropriate password and settings against a SAP BW database. I get a connection by the command

In [14]: c.getDatasources()

AttributeError Traceback (most recent call last)

in () ----> 1 c.getDatasources() H:\python\olap\olap\xmla\olap\xmla\connection.py in (this, _args, *_kw) 74 return lambda this, _args, *_kw: cls.Discover(this, 75 schemaName, ---> 76 _args, *_kw) 77 78 for schemaName in xmla1_1_rowsets: H:\python\olap\olap\xmla\olap\xmla\connection.py in Discover(self, what, restric tions, properties) 124 125 try: --> 126 res = getattr(self.client.service.Discover(what, rl, pl).\ 127 DiscoverResponse["return"].root, "row", [] ) 128 if res: AttributeError: 'return' object has no attribute 'DiscoverResponse' Fails. Where as the command c.client.service.Discover("DISCOVER_SCHEMA_ROWSETS",None,None) Does succeeds and returns data.

[may-day]

i can only guess here: it seems SAP BW doesn't conform to the wsdl i took from Microsoft's website. It should be at: http://yourhost/sap/bw/xml/soap/xmla?wsdl I'd be interested in that one. Another possibility is that SAP BW conforms to the XMLA but needs to be called with special properties set to do so. (The SAP website says XMLA 1.0 is the basis, while the wsdl i use internally is based on XMLA 1.1)

[harpsoe]

Ok, I have retrieved the wsdl file. How do you want it?

[may-day]

best post it here: https://gist.github.com/

[may-day]

Also, did this work with the python2 version of the xmla package (meaning it could be a suds problem as well)?

[harpsoe]

I actually have not tried it on Python2. I will get you the wsdl tomorrow.

[kmcnayr]

@may-day @harpsoe How were you able to use in windows? I installed pywin32 and kerberos-sspi and was able build and install but when importing olap.xmla.xmla as in the example I get a "no module 'connection'" error.

Error occures in olap\xmla\xmla.py; "from connection import XMLAConnection"

I am also interested in connection to SAP BW however I am not familiar with how to use a specific wsdl; will appreciate if you can help guide.

[kmcnayr]

oops, wrong button. sorry.

[harpsoe]

@may-day the wsdl file can be found here. https://gist.github.com/harpsoe/5a2cfb7f1ad33f747e8d

[may-day]

@kmcnayr you have some old versions around, since the xmla.py from the py3 branch reads:

from .connection import XMLAConnection

notice the dot right before the connection

[may-day]

@harpsoe The wsdl you posted looks good (in fact, more detailed than the one i downloaded from microsoft). It also states the missing DiscoverResponse element should be in the answer from the server (but the error message you got says otherwise). So i guess i need to take a look at what was really returned from the server. To do so, please enable the logging in connection.py by uncommenting lines 35-48, i.e.:

#import logging
#logging.basicConfig(level=logging.INFO)
#logging.getLogger('suds.client').setLevel(logging.DEBUG)
#logging.getLogger('suds.transport').setLevel(logging.DEBUG)

becomes:

import logging
logging.basicConfig(level=logging.INFO)
logging.getLogger('suds.client').setLevel(logging.DEBUG)
logging.getLogger('suds.transport').setLevel(logging.DEBUG)

then when you call:

c.getDatasources()

you should see lots of output, among something starting with: b'<soap:Envelop xmlns=.... i would like to see everything from that point until:

</soap:Envelop>

You might want to overwrite the URL that is contained in that output, since it could point to something internal to your company... For instance the output i get is:

(py3) e:\>python
Python 3.4.2 (v3.4.2:ab2c023a9432, Oct  6 2014, 22:15:05) [MSC v.1600 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import olap.xmla.xmla as xmla
No s4u2p module, so you won't be able to impersonate a user.
>>> p = xmla.XMLAProvider()
>>> c = p.connect(location="http://dwh-bi/olap/msmdpump.dll")
>>> c.getDatasources()
DEBUG:suds.client:sending to (http://dwh-bi/olap/msmdpump.dll)
message:
<suds.sax.document.Document object at 0x0332DF70>
DEBUG:suds.client:headers = {'SOAPAction': b'"urn:schemas-microsoft-com:xml-analysis:Discover"', 'Content-Type': 'text/xml; charset=utf-8'}
INFO:olap.xmla.requests_kerberosauth:register handle_401_guarded
INFO:requests.packages.urllib3.connectionpool:Starting new HTTP connection (1): dwh-bi
INFO:requests.packages.urllib3.connectionpool:Resetting dropped connection: dwh-bi
INFO:olap.xmla.requests_kerberosauth:handle_401_guarded
INFO:olap.xmla.requests_kerberosauth:kerberosauth: handle_401...
INFO:olap.xmla.requests_kerberosauth:kerberosauth: neg_value not none
INFO:olap.xmla.requests_kerberosauth:gss_init() succeeded
INFO:olap.xmla.requests_kerberosauth:gss_step() succeeded
INFO:olap.xmla.requests_kerberosauth:REQUESTS version 2.5.1
INFO:olap.xmla.requests_kerberosauth:kerberosauth: handle_401...
INFO:olap.xmla.requests_kerberosauth:kerberosauth: neg_value not none
INFO:olap.xmla.requests_kerberosauth:gss_step() succeeded
INFO:olap.xmla.requests_kerberosauth:auth complete, now cleaning context...
DEBUG:suds.client:HTTP succeeded:
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><DiscoverResponse xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:ddl2="
http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100="http://s
chemas.microsoft.com/analysisservices/2008/engine/100" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http:/
/schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><return><root xmln
s="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:msxml
a="http://schemas.microsoft.com/analysisservices/2003/xmla"><xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-m
icrosoft-com:xml-sql" elementFormDefault="qualified"><xsd:element name="root"><xsd:complexType><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element na
me="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:a
ny/></xsd:sequence></xsd:complexType><xsd:complexType name="row"><xsd:sequence><xsd:element sql:field="DataSourceName" name="DataSourceName" type="xsd:string"/>
<xsd:element sql:field="DataSourceDescription" name="DataSourceDescription" type="xsd:string" minOccurs="0"/><xsd:element sql:field="URL" name="URL" type="xsd:s
tring" minOccurs="0"/><xsd:element sql:field="DataSourceInfo" name="DataSourceInfo" type="xsd:string" minOccurs="0"/><xsd:element sql:field="ProviderName" name=
"ProviderName" type="xsd:string"/><xsd:element sql:field="ProviderType" name="ProviderType" type="xsd:string" minOccurs="0" maxOccurs="unbounded"/><xsd:element
sql:field="AuthenticationMode" name="AuthenticationMode" type="xsd:string" minOccurs="0"/></xsd:sequence></xsd:complexType></xsd:schema><row><DataSourceName>DWH
-BI</DataSourceName><DataSourceDescription/><URL/><DataSourceInfo/><ProviderName>Microsoft Analysis Services</ProviderName><ProviderType>MDP</ProviderType><Prov
iderType>TDP</ProviderType><ProviderType>DMP</ProviderType><AuthenticationMode>Authenticated</AuthenticationMode></row></root></return></DiscoverResponse></soap
:Body></soap:Envelope>'
[(row){
   DataSourceName = "DWH-BI"
   DataSourceDescription = ""
   URL = ""
   DataSourceInfo = ""
   ProviderName = "Microsoft Analysis Services"
   ProviderType[] =
      "MDP",
      "TDP",
      "DMP",
   AuthenticationMode = "Authenticated"
 }]
>>>

please post again to gist (or here if you dont care)

[kmcnayr]

@may-day I was able to build and successfully install on Windows 7 x64 and Python 3.4.0.

I was also able to test against SAP BW 7.4 successfully. I couldn't even begin to tell you why it works and @harpsoe does not. I do know that the wsdl is provided at an address similar to ://:/sap/bw/xml/soap/xmla?wsdl as stated in the BW documentation.

I have not tested any authentication. BW is simple username/password and SSAS is anonymous.

I will try to help more if I can.

[harpsoe]

@may-day The return from c.GetDataSources() as requested: https://gist.github.com/harpsoe/560e3406fba63f85b363

[may-day]

@kmcnayr good to hear, thanks for testing. @harpsoe the xml you posted is not well formed. i made a fork of your gist and fixed the problems, so you can see the differences - here it is: https://gist.github.com/may-day/ce46724d73f8cfd1ad66/revisions

anyhow, i assume this happend when you copied the log output, otherwise you would have gotten a xml parsing error. The fixed xml i can pass as response when requesting the datasources with c.getDatasources() without a problem, as a result i get:

[(row){
   DataSourceName = "SAP_BW"
   DataSourceDescription = "SAP Business Information Warehouse Release 7.4 Support Package VII"
   URL = "http://yourhost/sap/bw/xml/soap/xmla"
   DataSourceInfo = "default"
   ProviderName = "SAP BW"
   ProviderType = "MDP"
   AuthenticationMode = "Integrated"
 }]

Could you try that in your environment please? For that i created a mockingtest at https://gist.github.com/may-day/d76101e8508c231358da Save it as mockingnetweaver.py, also download https://gist.github.com/may-day/ce46724d73f8cfd1ad66 somewhere as discover_datasources.xml. Then run:

pip install httmock
python mocknetweaver.py /path/to/discover_datasources.xml

[harpsoe]

@may-day I tested with the mockingweaver.py as requested and I get the exact same result that you get. (Assuming you are not interested in the DEBUG: information)

[may-day]

@harpsoe did you install xmla as a package but ran the c.getDatasources() from an interpreter session started from the directory you installed the git in? Also check whether you some old version of the package unziped in your /Lib/site-packages/olap directory (the files from the olap package and xmla package end up in the same directory on windows which i often manage to overlook when cleaning up by hand :).

[harpsoe]

@may-day I have removed suds-jurko, zope, olap and xmla from my installation and reinstalled. Now I have: ./xmla-0.8.0-py3.3.egg ./suds_jurko-0.6-py3.3.egg ./olap-0.3-py3.3.egg ./zope.schema-4.4.2-py3.3.egg ./zope.interface-4.1.2-py3.3-win-amd64.egg ./zope.event-4.0.3-py3.3.egg listed as the installed versions. Bit alas the call c.getDataSources does still not work. It seems that it gets a well formed response form the server, but suds does not add the attributes to return object. I ran the mockingnetweaver.py in a different directory than the git directory. Calls like: c.client.service.Discover("DISCOVER_SCHEMA_ROWSETS",None,None) works perfectly.

[may-day]

@harpsoe the thing is, in the mock test i passed the same thing through suds and it worked. Just to sum it up so far: The XML response you pasted has a node-hierarchy of:

<SOAP-ENV:Envelope><SOAP-ENV:Body><DiscoverResponse><return><root>

The call to self.client.service.Discover(what, rl, pl) should return a XML Document whose root-node is <SOAP-ENV:Body>.

But the error message:

 126 res = getattr(self.client.service.Discover(what, rl, pl).\
127 DiscoverResponse["return"].root, "row", []
)
128 if res:

AttributeError: 'return' object has no attribute 'DiscoverResponse'

really hints that the result of self.client.service.Discover(what, rl, pl) has a <return> as root-node. So i would like to see what suds makes of what is returned when you call c.getDatasources. For that please modify the Discover method in connection.py like so:

    def Discover(self, what, restrictions=None, properties=None):
        rl = None
        pl = None
        if restrictions:
            rl = {"RestrictionList":restrictions}
        if properties:
            pl = {"PropertyList":properties}

        try:
            doc = self.client.service.Discover(what, rl, pl)
            print(doc)
            res = getattr(doc.DiscoverResponse["return"].root, "row", [])
            if res:
                res = aslist(res)
        except WebFault as fault:
            raise XMLAException(fault.fault, dictify(fault.fault))
        logger.debug( res )
        return res

[harpsoe]

I have modified the call as requested and I have made a gist with the return. https://gist.github.com/harpsoe/9011808506d94355fde3

[may-day]

looks like i was right about the <return> beeing the root node. I noticed the name of your python installation beeing "special". Did you install some prepackaged python distribution like ActivePython?

[harpsoe]

I think I am using WinPython 3. Not good?

[may-day]

dunno, im just trying to recreate your windows environment. We are about leaving the xmla terrain here and dive into suds/sax parser issues. To investigate further we would need to make some changes to the suds source code. Are you still willing to go into that? If so let me now and i will prepare some suds modifications for you to try out.

[may-day]

@harpsoe i guess i know why ... how do you connect, like so:

c = p.connect("http://yourhost:port/sap/bw/xml/soap/xmla")
# or alternativly
c = p.connect("http://yourhost:port/sap/bw/xml/soap/xmla?wsdl")

If so, try this:

c = p.connect(location="http://yourhost:port/sap/bw/xml/soap/xmla")

[harpsoe]

Ok, it worked. I had used the keyword url not location. Thank you very much for your help. So the issue seems to have been resolved. May I ask you to modify your last post. I don't like to have our internal it structure exposed on the internet

[may-day]

@harpsoe sure - done. So to sum up what happend: By not using the location parameter, you used the URL for the wsdl. (If the wsdl is not given, the default provided with this xmla package will be used.) So in your case the wsdl from SAP was used which differs in the Discover message's parameters (it has no support for Sessions thus has no BeginSession/EndSession parameters). The same is true for the DiscoverResponse message (again: no Session parameter). The suds package in it's default settings will recognize a very simple DiscoverResponse message structur and will by default unwrap it. That is the reason the result from the call to Discover() had no DiscoverResponse but the it's only "return" element as root.