MS Analysis Services OLAP API for Python [closed]

Question

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.

Answer 1

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)

Answer 2

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.