Using Python Pyadomd Library to get PowerBI Data into Json Format

Question

We have created a API which takes data from PowerBI and provides output in JSON format.

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

Original code: https://pypi.org/project/pyadomd/.

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 [email protected];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()

For better understanding of Pyadomd library, see also the link above.

Output:

No PowerBI Data are fetched & Return with 404 Error:

I think app.route is unable to fetch the file path.

When we have used default code it generates Authentication error & after modification now it is not showing output in JSON format. When we mention alpino in url file path it provides 404 error.

Answer 1

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

Python 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 [email protected];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 : [Json Output] : https://i.sstatic.net/YYbzy.png

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