The introduction of Microsoft Fabric at the Build Conference last month has added a whole lot of new functionality that you can use. One of the new items we now have access to is a simple way of running Notebooks. And these Notebooks open up all sorts of interesting options for Data engineering, exploration and ingestion.
One of the things that this makes possible is to call a Power BI REST API and save the results into the Files section of a Fabric Lakehouse.
So lets look at an example of that calling the Get Refreshables API.
Setup
There are a couple of setup steps before you can run the script below.
Firstly, I’ve actually followed best practices here and used Azure Key Vault to secure all the secrets for my script rather than just hard coding them. I’ve added the following 3 secrets to my key vault:
- FabricTenantId – my Power BI tenant ID
- FabricClientId – the client ID for an SPN that I created to run this script
- FabricClientSecret – the client secret for the SPN
I then gave my user access to read those secrets and because the notebook uses the identity of the owner when it executes this also works even when your setup a schedule for your notebook.
If you don’t have access to a key vault you could just hard code your values into this notebook.
Then I’ve created a Lakehouse in my workspace and created a refreshables folder under Files.
Once all that is done you can paste the following script into a new notebook and run it.
#########################################################################################
# Read secretes from Azure Key Vault
#########################################################################################
key_vault = "https://dgosbellKeyVault.vault.azure.net/"
tenant = mssparkutils.credentials.getSecret(key_vault , "FabricTenantId")
client = mssparkutils.credentials.getSecret(key_vault , "FabricClientId")
client_secret = mssparkutils.credentials.getSecret(key_vault , "FabricClientSecret")
#########################################################################################
# Authentication - Replace string variables with your relevant values
#########################################################################################
import json, requests, pandas as pd
import datetime
try:
from azure.identity import ClientSecretCredential
except Exception:
!pip install azure.identity
from azure.identity import ClientSecretCredential
# Generates the access token for the Service Principal
api = 'https://analysis.windows.net/powerbi/api/.default'
auth = ClientSecretCredential(authority = 'https://login.microsoftonline.com/',
tenant_id = tenant,
client_id = client,
client_secret = client_secret)
access_token = auth.get_token(api)
access_token = access_token.token
print('\nSuccessfully authenticated.')
#########################################################################################
# Get Refreshables
#########################################################################################
base_url = 'https://api.powerbi.com/v1.0/myorg/'
header = {'Authorization': f'Bearer {access_token}'}
refreshables_url = "admin/capacities/refreshables?$top=200"
print(refreshables_url)
refreshables_response = requests.get(base_url + refreshables_url, headers=header)
# write raw data to a json file
with open("/lakehouse/default/Files/refreshables/top200.json", 'wb') as f:
f.write(refreshables_response.content)
The Output
If we then open our lakehouse and have a look at the file that was generated, we can see the data returned from our API call.
Conclusion
Most of the script was just involved in getting the authentication working and the core portion that then calls the REST API was relatively simple.
This is all relatively new to me and I’m still learning about Python and Notebooks, so if you see anything where I’m not doing things the “right way” feel free to point them out. I’m planning to do some more Notebook related posts in the future. I’m particularly interested in pushing data into delta tables and then building a Power BI dataset in Direct Lake mode, so stay tuned for that.
Hi Darren and thanks for the guide, the upper parts works well. However nothing is written in the json. I debugged it and when i print “refreshables_response” it says “” … not authorized.
Do you have an idea here?
If you’ve used a service principal this means that it does not have access to call the API.
Hello,
I was able to follow along, and was able to get output and also do a schedule run. So, now I am able to get JSON file daily.
Do you have any update on the next steps to take the JSON and turn it into delta table?
Thanks,
Chris
Hi Chris, I have been working on a project where I push the JSON response into a delta table. I’ll see if I can put together a follow-up post showing the technique I’m currently using to do this.
Darren, Thanks!