How To Connect Ssas To Python

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

Solution 1:

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. to configure MS SQL Server:

1.1 add SQL user authorization (public roll)

1.2 allow ad hoc

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 fix MSSQL behavior https://www.mssqltips.com/sqlservertip/4582/sql-server-ad-hoc-access-to-ole-db-provider-has-been-denied-error/

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 MSSQLtry:
    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 requestdefget_mdx_query_str(mdx_path,olap_conn_string):
    try:
        withopen(mdx_path, encoding="utf8") as f:
            mdx_string = f.read()            
    except:
        print("exception:......")
        returnFalsefinally:
        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)

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