yehoshuadimarsky / python-ssas Goto Github PK

In the example, it should be server instead of ssas_server

import ssas_api
...:
...: conn = ssas_api.set_conn_string(
...: ssas_server='<YOUR_SERVER>',
...: db_name='<YOUR_DATABASE>',
...: username='',
...: password=''
...: )

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

Can you give me an example using your API?
Thank you!

When invoking SaveChanges it would be better if we could define the maximum parallelism for SSAS to process the objects.

This attribute is available within SaveOptions object:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.model.savechanges?view=analysisservices-dotnet

Also this method could be adapted to support receiving an array of objects for example (partitions) and these would be processed in parallel.

I am getting following error while connecting to Azure server.
My username and password are correct and I am able to connect to Azure manually from SSMS.
However, unable to connect via this python code.

Can someone please let me know about resolution ?

code
import ssas_api

conn = ssas_api.set_conn_string(
ssas_server='<YOUR_SERVER>',
db_name='<YOUR_DATABASE>',
username='',
password=''
)

dax_string = '''
EVALUATE CALCULATETABLE (LOBdd)
'''
df = ssas_api.get_DAX(connection_string=conn, dax_string=dax_string)
print ('df=' ,df)

error:

Microsoft.AnalysisServices.AdomdClient.NonInteractiveLoginException: Unable to obtain authentication token using the credentials provided. If your Active Directory tenant administrator has configured Multi-Factor Authentication or if your account is a Microsoft Account, please remove the user name and password from the connection string, and then retry. You should then be prompted to enter your credentials.
at Microsoft.AnalysisServices.AdomdClient.AadAuthenticator.AcquireToken(String resource, String dataSource, String identityProvider, String tenantId, String userId, String password, Boolean useAdalCache, Boolean useAdTranslation)
at Microsoft.AnalysisServices.AdomdClient.ConnectionInfo.ResolveHTTPConnectionPropertiesForPaaSInfrastructure(Uri& dataSourceUri, Boolean acquireAADToken, Boolean returnCloudConnectionAuthenticationProperties, String& paasCoreServerName, CloudConnectionAuthenticationProperties& cloudConnectionAuthenticationProperties)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenHttpConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

Hi, I'm using your script and it works fine, but I've encountered a DateTime parsing issue.

I'm Italian, so the date format is usually DD/MM/YYYY.
By using the locale dependent ToString() it creates some issues with mixed locale environment like mine (software may use Italian or US format and mix up day and months)

I've solved by using the sortable DateTime format in the previous snippet: x.ToString('s')

Hi,

I am curious to know whether this code template can be used to connect to Azure Analysis Services as well as SQL Server Analysis Services since in the Quickstart it shows DAX queries being fired off against SSAS?

This is not an issue, still I think more options on the refresh type would be great as you are already using Microsoft.AnalysisServices.Tabular namespace.

Enum for refresh types is available here:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.refreshtype?view=analysisservices-dotnet

Thanks for making this available!

Hi,

I have spent a few days trying to understand and get this working but to no avail.

I think one of the possible root causes to this is not being able to properly import pythonnet, possibly due to the version or some other issue. I think this could be the reason why some of the clr references in the code don't seem to be working (as I have tried explaining in the other posts #12 ).

Would you be able to:

• Provide a requirements.txt to ensure that it is known which exact packages are required to use this?
• Verify/Confirm that all the steps in the README.md are all that is required to get it running (and perhaps expand on minor subtleties if there are any)? As I feel I have followed the instructions however still am coming up with issues (certain lines in ssas.py "line 71-74" not being read properly by my IDE).

Any further assistance on this matter would be helpful.

Hey, i really thank you for this API because i was really looking for something related.
The maximum thing i found was: https://github.com/S-C-O-U-T/Pyadomd
but didn't work for Azure Analysis Services just for local models.

If i could import automatically the API from PyPI instead of putting the file on the same folder, would be really great!

Again, Thank you!

Hi, I followed the steps in the README.md file and imported the required dll extension (and edited the required version info in the python file path snippet provided as well).

However, once I go to run the a version of the Quickstart I run into an error: NameError: name 'DataTable' is not defined.

Here is the python file path snippet with the correct version and path that I have on my machine:

base = "C:/Program Files/PackageManagement/NuGet/Packages/Microsoft.AnalysisServices"
_version = "19.20.1.0"  # at time of this writing
AMO_PATH = f"{base}.retail.amd64.{_version}/lib/net45/Microsoft.AnalysisServices.Tabular.dll"
ADOMD_PATH = f"{base}.AdomdClient.retail.amd64.{_version}/lib/net45/Microsoft.AnalysisServices.AdomdClient.dll"

My question is:

• Do I need to edit the actual ssas_api.py script with the file path locations of the .dll files? Not entirely sure what would be required to edit from this in order for the Quickstart snippet to run. Any advice on editing the ssas_api.py script as per the python file path snippets above would be useful.
• I also noticed midway down the ssas_api.py script I have on my local machine (line 70-73) the following commands aren't working (greyed out in PyCharm Editor):

import System
from System.Data import DataTable
import Microsoft.AnalysisServices.Tabular as AMO
import Microsoft.AnalysisServices.AdomdClient as ADOMD

Not sure if I need to install a package for this to work. I tried performing pip install System but that didn't seem to work, it gave the following error ERROR: Could not find a version that satisfies the requirement System (from versions: none)

QuickStart Sample Code:
.....
conn = ssas_api.set_conn_string(
ssas_server='<YOUR_SERVER>',
db_name='<YOUR_DATABASE>',
username='',
password=''
)
.....

the first server parameter name should change to simply "server",not "ssas_server"

conn = ssas_api.set_conn_string(
server='<YOUR_SERVER>',
db_name='<YOUR_DATABASE>',
username='',
password=''
)

thanks by the way~

We've got the following error on some queries:

AttributeError: 'float' object has no attribute 'ToString'

at the following line:

ser = df.loc[:, dtt].map(lambda x: x.ToString('s'))

The error seems to happen when we have a DateTime column in the resultset that have some BLANK values. In those cases the variable seems to be float instead of .NET DateTime.

Greetings,

I am trying to test the ssas_api module, but get an error when I try to import ssas_api??

The error message

"C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\python.exe" C:/Users/user/PycharmProjects/PowerBI/SSAS.py
Traceback (most recent call last):
File "C:/Users/user/PycharmProjects/PowerBI/SSAS.py", line 132, in
@_assert_dotnet_loaded
File "C:/Users/user/PycharmProjects/PowerBI/SSAS.py", line 103, in _assert_dotnet_loaded
import ssas_api
ModuleNotFoundError: No module named 'ssas_api'

Process finished with exit code 1