如何将 SSAS 连接到 Python [英] How to connect SSAS to Python

问题描述

想从SSAS连接中获取Python Pandas DataFrame中的数据，怎么办?我试过下面的代码

Want to fetch data in Python Pandas DataFrame from SSAS connection, how to do? I tried below code

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

但是在导入时会出现错误No module named xmla".所以，我尝试运行pip install xmla"；但它给出错误没有模块名称客户端"

But when importing its gives error "No module named xmla". So, I try to run "pip install xmla" but it giving error "No module name client"

请建议该怎么做以及如何在 Python pandas 数据框中导入 SSAS 数据

Please suggest what to do and how to import SSAS data in Python pandas dataframe

推荐答案

这个方法在我看来是最简单的.注意:如果您有权访问任何 MS SQL Server 或能够部署它，则可以使用它.

This method seemed to me the easiest. Note: it is Possible to use if you have access to any MS SQL Server or the ability to deploy it.

1. 配置 MS SQL Server:

1.1 添加SQL用户授权(公众号)

1.1 add SQL user authorization (public roll)

1.2 允许临时

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO

1.3 修复 MSSQL 行为 https://www.mssqltips.com/sqlservertip/4582/sql-server-ad-hoc-access-to-ole-db-provider-has-been-denied-error/

1.3 fix MSSQL behavior https://www.mssqltips.com/sqlservertip/4582/sql-server-ad-hoc-access-to-ole-db-provider-has-been-denied-error/

继续使用python，这个想法是使用构造;SELECT olap.* from OpenRowset ('"+ olap_conn_string+"',' " + mdx_string +"') "+ 'as olap'

Moving on to python, the idea is to use the construct " SELECT olap.* from OpenRowset ('"+ olap_conn_string+"',' " + mdx_string +"') "+ 'as olap'

import pandas as pd
import pymssql 
# connect to MSSQL
try:
    connect_mssql = pymssql.connect(server=ip_mssql, user=user_mssql,password=pass_mssql, port=port_mssql)
except:
    print("exception:....")
    sys.exit()

# creating an OLAP query string via linked server MSSQL
# olap_conn_string example "MSOLAP','Provider=MSOLAP.8;Password=Pass;Persist Security Info=True;User ID=login;Data Source=SSAS Server IP or domen;Update Isolation Level=2;Initial Catalog=OLAP BD;"
# mdx_path - this is just the path to the file with the mdx request
def get_mdx_query_str(mdx_path,olap_conn_string):
    try:
        with open(mdx_path, encoding="utf8") as f:
            mdx_string = f.read()            
    except:
        print("exception:......")
        return False
    finally:
        try:
            f.close()  
        except:
            print("....") 
    mdx_query = "SELECT olap.* FROM OpenRowset('"+ olap_conn_string+"','"+ mdx_string +"')"+'as olap'
    return mdx_query

# getting the pandas dataframe
 tempdf = pd.read_sql( \
                       (get_mdx_query_str(mdx_path, olap_conn_string)) \
                        ,connect_mssql)

您可以使用 %s 字符串格式化方法将参数传递给查询

you can pass parameters to the query using the %s string formatting methods

这篇关于如何将 SSAS 连接到 Python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！