使用Python查询SQL Server Analysis Services(SSAS)多维数据集数据 [英] Use Python to Query SQL Server Analysis Services (SSAS) cube Data

问题描述

我的组织中有一个SQL分析服务资源，我们可以使用excel或powerbi使用服务器名称(tooldata.amr.xxx.com)连接到多维数据集并获取数据.

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.

我想要使用python或excel自动化数据查询并输出到csv文件以供下游应用程序使用(报告/图表等)

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.)

我已经在下面尝试过，但是失败了:

1. FileNotFoundException跟踪(最近一次调用) 在

FileNotFoundException Traceback (most recent call last) in

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

FileNotFoundException:无法找到程序集"Microsoft.AnalysisServices.AdomdClient.dll". 在Python.Runtime.CLRModule.AddReference(字符串名称)

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 ?

2.

ConnectionError:HTTPConnectionPool(host ='tooldata.amr.xxx.com'，port = 80):URL超过了最大重试次数:/OLAP/msmdpump.dll(由NewConnectionError(':导致:无法建立新连接: [WinError 10060]连接尝试失败，因为一段时间后连接方未正确响应，或者由于连接的主机未能响应而建立的连接失败'))

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.因为我可以使用excel来获取SSAS数据，是否可以使用python调用excel并刷新数据，然后从excel中解析出数据?有没有人尝试过?

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?

谢谢.

推荐答案

最后，基于

clr是 pythonnet ，您可以通过以下方式安装软件包: pythonnet Github 或 pythonnet pypi

clr is pythonnet, you can install the package via: pythonnet Github or pythonnet pypi

对于 Microsoft.AnalysisServices.AdomdClient.dll ，可能您没有.您可以通过安装 SQL_AS_ADOMD.msi 来获取DLL.

And for the Microsoft.AnalysisServices.AdomdClient.dll probably you don't have it. you can get the DLL by install SQL_AS_ADOMD.msi .

最后，旨在解析来自Cube DataSet的结构化数据集.我使用下面的代码(字段取决于您的DAX查询输出).

Lastly , aim to parse a structured dataset from the Cube DataSet. i use below code (field depends on your DAX query output).

with open ('xx_Pivot.csv','w') as file:
#my MDX only return 7 field as below headers.
header = 'WW,Shift,ShiftID,Factory,Entity,Cell,Data\n'
file.writelines(header)
#iteration the Dataset and get out a structure 2D data table and save to a file.
for row_n in range(len(list(datasetParam.Tables[0].Rows))):
    row = ''
    for column_n in range(7):
        data = datasetParam.Tables[0].Rows[row_n][column_n]
        row = row+str(data)+',' 
    row = row+'\n'
    file.writelines(row)

这篇关于使用Python查询SQL Server Analysis Services(SSAS)多维数据集数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！