Authentication Error

[N-SPEC]

Hello Team, Greetings of the Day!!! Thank You in Advance for any Solution or Suggestion.

We are getting Authentication Error of Azure AD when PowerBI User is verified.

Code : conn_str = 'Provider=MSOLAP;Data Source=localhost;Catalog=AdventureWorks;' query = """EVALUATE Product"""

Error :

PS C:\Users\ND\Documents\Data Analytics\Assignmentsa\Python> python .\art.py System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Identity.Client.WsTrust.CommonNonInteractiveHandler.d__6.MoveNext()

The above exception was the direct cause of the following exception:

Microsoft.Identity.Client.MsalClientException: MSAL.Desktop.4.43.0.0.MsalClientException: ErrorCode: parsing_wstrust_response_failed Microsoft.Identity.Client.MsalClientException: There was an error parsing WS-Trust response from the endpoint. This may occur if there is an issue with your ADFS configuration. See https://aka.ms/msal-net-iwa-troubleshooting for more details. Error Message: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Identity.Client.WsTrust.CommonNonInteractiveHandler.d6.MoveNext() --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.Authentication.MsalAuthenticationHandle.WaitAndGetActualAsyncTaskResult[TResult](Task1 task) at Microsoft.AnalysisServices.AdomdClient.Authentication.MsalAuthenticationHandle.UsernamePasswordAuthenticationHandle.AcquireTokenImpl(IPublicClientApplication app, IEnumerable1 scopes, String userId, SecureString password) Inner Exception: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Identity.Client.WsTrust.CommonNonInteractiveHandler.d6.MoveNext()

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "C:\Users\ND\Documents\Data Analytics\Assignmentsa\Python\art.py", line 8, in with Pyadomd(conn_str) as conn: File "C:\Users\ND\AppData\Local\Programs\Python\Python311\Lib\site-packages\pyadomd\pyadomd.py", line 158, in enter self.open() File "C:\Users\ND\AppData\Local\Programs\Python\Python311\Lib\site-packages\pyadomd\pyadomd.py", line 140, in open
self.conn.Open() Microsoft.AnalysisServices.AdomdClient.Authentication.AuthenticationException: Unable to obtain authentication token using the credentials provided. ---> Microsoft.Identity.Client.MsalClientException: There was an error parsing WS-Trust response from the endpoint. This may occur if there is an issue with your ADFS configuration. See https://aka.ms/msal-net-iwa-troubleshooting for more details. Error Message: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Identity.Client.WsTrust.CommonNonInteractiveHandler.d__6.MoveNext() --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.Authentication.MsalAuthenticationHandle.WaitAndGetActualAsyncTaskResult[TResult](Task1 task) at Microsoft.AnalysisServices.AdomdClient.Authentication.MsalAuthenticationHandle.UsernamePasswordAuthenticationHandle.AcquireTokenImpl(IPublicClientApplication app, IEnumerable1 scopes, String userId, SecureString password) --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.Authentication.MsalAuthenticationHandle.UsernamePasswordAuthenticationHandle.AcquireTokenImpl(IPublicClientApplication app, IEnumerable`1 scopes, String userId, SecureString password) at Microsoft.AnalysisServices.AdomdClient.Authentication.MsalAuthenticationHandle.MsalAuthenticationService.AuthenticateUser(AuthenticationOptions options, AuthenticationInformation authInfo, String userId, SecureString password) at Microsoft.AnalysisServices.AdomdClient.Authentication.AuthenticationManager.Authenticate(AuthenticationOptions options, String identityProvider, String resource, String tenantId, String userId, String password, Boolean isForAsAzureRedirection)
at Microsoft.AnalysisServices.AdomdClient.ConnectionInfo.AcquireToken(IConnectivityOwner owner, String resource, String tenantId, Boolean isForAsAzureRedirection) at Microsoft.AnalysisServices.AdomdClient.ConnectionInfo.ResolveHTTPConnectionPropertiesForPaaSInfrastructure(IConnectivityOwner owner, 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.OpenConnectionAndCheckIfSessionTokenNeeded(ConnectionInfo connectionInfo) 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() at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open() PS C:\Users\ND\Documents\Data Analytics\Assignmentsa\Python>

[N-SPEC]

Hello Team, Greetings of the Day!!! Thank You in Advance for any Solution or Suggestion.

I made some modifications to the code, and it runs without errors. However, it is not displaying the PowerBI data in JSON format as it should.

Code:

from sys import path
path.append('\\Program Files\\Microsoft.NET\\ADOMD.NET\\160')

from pyadomd import Pyadomd
from flask import Flask, jsonify

app = Flask(__name__)

@app.route('/Alpino')
def get_data():
    conn_str = 'Provider=MSOLAP;User ID=Alexw@dettol.com;Data Source=powerbi://api.powerbi.com/v1.0/myorg/Power BI Model [Test];initial catalog=PBI_Model_20230121;Password=Alexw#2023;Persist Security Info=True;Impersonation Level=Impersonate;'
    query = """EVALUATE Project"""

    with Pyadomd(conn_str) as conn:
        with conn.cursor().execute(query) as cur:
            data = cur.fetchall()
            print(data)
            return jsonify(data)

if __name__ == '__main__':
    app.run()

Output :

Default Comment

No PowerBI Data are fetched & Return with 404 Error

[N-SPEC]

Hello Team, Thank You in advance to developers & readers

I have made changes into code & issue has been resolved. Please find below code.

Code :

from sys import path
path.append('\\Program Files\\Microsoft.NET\\ADOMD.NET\\160')

from pyadomd import Pyadomd
from flask import Flask, jsonify

# Create a Flask app
app = Flask(__name__)

# Define an API endpoint
@app.route('/alpino')
def alpino():

# Connect to Power BI and execute query
    conn_str = 'Provider=MSOLAP;User ID=Alexw@dettol.com;Data Source=powerbi://api.powerbi.com/v1.0/myorg/Power BI Model [Test];initial catalog=PBI_Model_20230121;Password=Alexw#2023;Persist Security Info=True;Impersonation Level=Impersonate;'
    query = 'EVALUATE ROW("ProjectRowCount",COUNTROWS(Project) )'

    with Pyadomd(conn_str) as conn:
        with conn.cursor().execute(query) as cur:
            data = cur.fetchall()
            column_names = [column[0] for column in cur.description]
            # Convert query result to a list of dictionaries
            result = [dict(zip(column_names, row)) for row in data]
            # Convert the list of dictionaries to a JSON string
            json_result = jsonify(result)
            return json_result

if __name__ == '__main__':
    app.run()

Output :

Query Explanation :

This code defines a Flask API endpoint that connects to a Power BI data source and executes a query. Here's a step-by-step breakdown of the code:

• The first two lines import the necessary libraries: sys.path and pyadomd for working with the Power BI data source, and flask for building the API endpoint.

from sys import path path.append('\Program Files\Microsoft.NET\ADOMD.NET\160')

from pyadomd import Pyadomd from flask import Flask, jsonify

• The next line creates a Flask app instance with Flask(name).

app = Flask(name)

• The @app.route('/alpino') decorator is used to define the API endpoint. In this case, the endpoint URL is http:///alpino.

@app.route('/alpino')

• The def alpino(): function defines the behavior of the API endpoint. It first sets up a connection to the Power BI data source with the given connection string and then executes a query using the Pyadomd library.

def alpino():

• The cur.fetchall() method retrieves all the data returned by the query.

  with Pyadomd(conn_str) as conn: with conn.cursor().execute(query) as cur: data = cur.fetchall()

• The column_names variable is set to the list of column names returned by cur.description.

column_names = [column[0] for column in cur.description]

• The result variable is set to a list of dictionaries, where each dictionary represents a row in the query result. The zip() and dict() functions are used to map the column names to the row values.

result = [dict(zip(column_names, row)) for row in data]

• The jsonify() function is used to convert the result variable to a JSON string.

json_result = jsonify(result)

• Finally, the function returns the JSON response using return json_result.

return json_result

In summary, this code sets up a Flask API endpoint that connects to a Power BI data source and returns the result of a query in JSON format. It uses the pyadomd library to connect to the data source, and the flask library to define the API endpoint and return the JSON response.

Stay Healthy #Stay Safe

Hope your query got resolved. If you have any query, Feel free to contact us.

|| Jay Hind Jay Bharat ||