如何将SSAS连接到Python

Question

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

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

但在导入时会出现错误“没有名为xmla的模块”。所以，我尝试运行“pip安装xmla”，但它给出了错误“无模块名客户端”

请建议如何在Python dataframe中导入SSAS数据

Answer 1

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

1. 要配置MS SQL Server，请执行以下操作：

1.1添加SQL用户授权（公共滚动）

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/

转到python，我们的想法是使用构造“SELECTOLAP.*from OpenRowset”（“+olap\u conn\u string+”，“+mdx\u string+”）“+”作为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字符串格式化方法将参数传递给查询