search

akavalar / SSAS-on-a-shoestring

Instructions on how to set up a free (as in free beer) Analysis Services server and then optionally access it using Python
21 stars 5 forks source link

Quering on SSAS Tabular with Python #3

Closed JonatanTorres closed 5 years ago

JonatanTorres commented 5 years ago

Hello, My Enterprise has SQL Server Enterprise with SSAS on Tabular mode. How can I query the SSAS instance with Python in this case?

Can you give me an example? Thank you!

akavalar commented 5 years ago

I have no way of checking if the code below works, but couldn't you just modify the runQuery() function and point it at your server? Specifically, change the first three lines of runQuery() from:

def runQuery(query,port,flag):

ADOMD assembly`

ADOMDConn=ADOMD.AdomdConnection("Data Source=localhost:{0}".format(port))

to:

def runQuery(query,ip_address,port,flag):

ADOMD assembly

ADOMDConn=ADOMD.AdomdConnection("Data Source={0}:{1}".format(ip_address, port))

An example would be running the following command once you've made the appropriate adjustments:

df = runQuery("EVALUATE dbo_DimProduct", 192.168.1.200, 60000, 0)

This assumes you have access to the server and don't need to authenticate in some other way, etc. If you do, you will probably need to modify the "Data Source=192.168.1.200:60000" connection string in some way - see this: https://docs.microsoft.com/en-us/sql/analysis-services/instances/connection-string-properties-analysis-services?view=sql-server-2017.

Again, I have no idea if this works or not, I've never tried anything like this. Good luck!

JonatanTorres commented 5 years ago

Hello,

Thanks so much! I tryed another solution and I had success.

I used the adodbapi and it worked!

You can look the code below:

import adodbapi

conn = adodbapi.connect("Provider=MSOLAP.8;Integrated Security=SSPI;Persist Security Info=True;\ Data Source=ServerName;\ Update Isolation Level=2;\ Initial Catalog=CatalogName")

cursor = conn.cursor()

numrows = cursor.execute(DaxQuery)

I hope that it helps someone. Thanks a lot!