ssas脚本组织程序,使用Python查询SQL Server Analysis Services（SSAS）多维数据集数据

There is a SQL analysis service resource in my organization, we can use excel or powerbi to connect to the cube use a server name (tooldata.amr.xxx.com) and get the data.

What i want is use python or excel to automate the data query and output to a csv file for downstream application use (reporting/chart etc.)

I've tried below but failed:

FileNotFoundException Traceback (most recent call last)

in

2. clr.AddReference ("Microsoft.AnalysisServices.AdomdClient.dll")

FileNotFoundException: Unable to find assembly 'Microsoft.AnalysisServices.AdomdClient.dll'.

at Python.Runtime.CLRModule.AddReference(String name)

look like lack of some env. not sure how to proceed. any suggestion ?

import olap.xmla.xmla as xmla

provider = olap.xmla.xmla.XMLAProvider()

connect = provider.connect(location='http://tooldata.amr.xxx.com/OLAP/msmdpump.dll',username='user',password='pwd')

source = connect.getOLAPSource()

print (source.getCatalog("TestCube"))

ConnectionError: HTTPConnectionPool(host='tooldata.amr.xxx.com', port=80): Max retries exceeded with url: /OLAP/msmdpump.dll (Caused by NewConnectionError(': Failed to establish a new connection: [WinError 10060] A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond'))

Looks like need some configuration from server side, but it's out of my control, drop this option.

3.since i can use excel to get the SSAS data, is that possible use python to call excel and refresh the data, then parse out the data from excel? have any one try that?

thanks .

解决方案

Finally, the problem solved based on 1.Microsoft.AnalysisServices.AdomdClient solution.

#use your own DLL path.

clr.AddReference ("r"C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClient\11.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.AdomdClient.dll"")

clr.AddReference ("System.Data")

from Microsoft.AnalysisServices.AdomdClient import AdomdConnection , AdomdDataAdapter

from System.Data import DataSet

#use your own server name or address. and data cube name.

conn = AdomdConnection("Data Source=tooldata.amr.xxx.com;Catalog=ShiftlyCellCube;")

conn.Open()

cmd = conn.CreateCommand()

#your MDX query, if you are not familiar, you can use the excel powerpivot to build one query for you.

cmd.CommandText = "your mdx query"

adp = AdomdDataAdapter(cmd)

datasetParam = DataSet()

adp.Fill(datasetParam)

conn.Close();

# datasetParam hold your result as collection a\of tables

# each tables has rows

# and each row has columns

print (datasetParam.Tables[0].Rows[0][0])