7

I am looking for a way to connect to a MS Analysis Services OLAP cube, run MDX queries, and pull the results into Python. In other words, exactly what Excel does. Is there a solution in Python that would let me do that?

Someone with a similar question going pointed to Django's ORM. As much as I like the framework, this is not what I am looking for. I am also not looking for a way to pull rows and aggregate them -- that's what Analysis Services is for in the first place.

Ideas? Thanks.

ktdrv
  • 3,602
  • 3
  • 30
  • 45
  • Have you tried to wrap the adomd.dll? I was just about to give it a shot. –  Jun 25 '10 at 20:29

3 Answers3

9

This can be done quite easily using pythonnet:

http://pythonnet.github.io/

You load the Microsoft.AnalysisServices.dll that is provided with SQL Server 2005 and 2008 or get the redistributable package here:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4

search for SQLSERVER2008_ASAMO10.msi

Then you can load it up and use it. Here is an example that simply processes cubes:

import CLR
from CLR.System.Reflection import Assembly

Assembly.LoadWithPartialName("AnalysisServices.DLL")

from CLR.Microsoft.AnalysisServices import Server
from CLR.Microsoft.AnalysisServices import ProcessType

serverName = 'localhost\sql2005'
dbName = 'MyDatabase'

# Connect to server
amoServer = Server()
amoServer.Connect(serverName)

# Connect to database
  amoDb = amoServer.Databases[dbName]
    amoDb.Process(ProcessType.ProcessFull)
denfromufa
  • 5,610
  • 13
  • 81
  • 138
cag
  • 492
  • 3
  • 14
4

I am completely ignorant about Python, but if it can call DLLs then it ought to be able to use Microsoft's ADOMD object. This is the best option I can think of.

You could look at Office Web Components (OWC) as that has a OLAP control than can be embedded on a web page. I think you can pass MDX to it, but perhaps you want Python to see the results too, which I don't think it allows.

Otherwise perhaps you can build your own 'proxy' in another language. This program/webpage could accept MDX in, and return you XML showing the results. Python could then consume this XML.

Magnus Smith
  • 5,895
  • 7
  • 43
  • 64
  • For all I know it can call DLLs. The problem is that they are so poorly documented. Actually, the same applies to the client-OLAP server communication protocol. I would implement a client in Python myself (or try to) but I can't find anything anywhere. Any pointers? – ktdrv Apr 30 '10 at 16:05
  • Find a Python example that shows how to call a DLL. Then install MS SQL Server 'Client Components' on your dev machine, and try to change the Python example to call adomd.dll and use the Microsoft documentation to tell you what classes/objects/properties to use. In VBScript we would do Server.CreateObject("ADOMD.Cellset") – Magnus Smith May 03 '10 at 21:21
1

You can easily connect and access OLAP cubes with the help of python package xmla. xmla plays a vital role in communicating with OLAP and performs all functions with the cubes.

Install xmla package either by

python -m pip install xmla --user

or

python -m venv xmlaenv
# python -m venv --without-pip xmlenv - use if the above commands throws error
cd xmlaenv
source bin/activate
git clone https://github.com/may-day/olap
cd olap/xmla
# optional if you have it already
pip install pipenv
pipenv install -dev
python setup.py develop

After installation, Connect to OLAP XMLA Cubes using location, username and password parameters.

import olap.xmla.xmla as xmla
provider = xmla.XMLAProvider()
connect = provider.connect(location='http://localhost/OLAP/msmdpump.dll',
 username = 'test', password = 'test')
source = connect.getOLAPSource()
Vishnu Mohan
  • 31
  • 6