2

I'm using Python to automate some reporting, but I am stuck trying to connect to an SSAS cube. I am on Windows 7 using Anaconda 4.4, and I am unable to install any libraries beyond those included in Anaconda.

I have used pyodbc+pandas to connect to SQL Server databases and extract data with SQL queries, and the goal now is to do something similar on an SSAS cube, using an MDX query to extract data, but I can't get a successful connection.

This first connection string is very similar to the strings that I used to connect to the SQL Server databases, but it gives me an authentication error. I can access the cube no problem using SQL Server Management Studio so I know that my Windows credentials have access.

connection = pyodbc.connect('Trusted_Connection=yes',DRIVER='{SQL Server}',SERVER='Cube Server', database='Cube')
query = "MDX query"
report_df = pandas.read_sql(query, connection)

Error: ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '*****'. (18456) (SQLDriverConnect)")

When I tried to replicate the attempts at Question1 and Question2 I got a different error:

Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Any help/guidance would be greatly appreciated. My experience with SSAS cubes is minimal, so it is possible that I am on the completely wrong path for this task and that even if the connection issue gets solved, there will be another issue loading the data into pandas, etc.

Analytics_A
  • 21
  • 1
  • 1
  • 2
  • SSAS [doesn't support ODBC clients](https://learn.microsoft.com/en-us/sql/analysis-services/instances/data-providers-used-for-analysis-services-connections). The driver you used is for the database engine – Panagiotis Kanavos Mar 21 '18 at 16:10
  • Ok thanks. Is there a different driver that you recommend? – Analytics_A Mar 21 '18 at 16:13
  • *On the other hand* [it provides HTTP Access through IIS](https://learn.microsoft.com/en-us/sql/analysis-services/instances/configure-http-access-to-analysis-services-on-iis-8-0) which you can use to connect from any client, including SPAs – Panagiotis Kanavos Mar 21 '18 at 16:13
  • Check the [xmla](https://pypi.python.org/pypi/xmla/) package. Notice that the SSAS connection string uses `https://my-as-server/olap/msmdpump.dll", the URL for the SSAS HTTP access endpoint – Panagiotis Kanavos Mar 21 '18 at 16:14
  • perhaps this solution will help you – Сергей Шамсуаров Dec 24 '20 at 06:33

3 Answers3

2

SSAS doesn't support ODBC clients . It does provide HTTP access through IIS, which requires a few configuration steps. Once configured, any client can issue XMLA queries over HTTP.

The xmla package can connect to various OLAP sources, including SSAS over HTTP

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • Thanks for the answer. I just checked with the DBAs to see if they can/will configure HTTP access. Will update with results – Analytics_A Mar 21 '18 at 16:41
  • Check [this guide](https://github.com/akavalar/SSAS-on-a-shoestring). It shows how to use the OLAP engine bundled in Power BI Desktop (which is free) to test until the DBAs decide what to do – Panagiotis Kanavos Mar 21 '18 at 16:46
0

Perhaps this solution will help you https://stackoverflow.com/a/65434789/14872543 the idea is to use the construct on linced MSSQL Server

SELECT olap.* from OpenRowset ('"+ olap_conn_string+"',' " + mdx_string +"') "+ 'as olap'
Сергей Шамсуаров
  • 43
  • 8
0

The Pyadomd package might help you with your problem:

Pyadomd

It is not tested on Windows 7, but I would expect it to work fine :-)

SCOUT
  • 150
  • 1
  • 1
  • 6