Python: Dynamic Power BI PDFs with REST API

TABLE OF CONTENTS

• INTRODUCTION
• PREREQUISITES
  • Programming Tools
  • Environment Settings
  • Azure Entra Application
  • SMTP Settings
  • Additional Information
• PROCESS & SCRIPTS
  • Required Python Packages
  • File Parameters
  • Main Scripts
  • Supporting Scripts
  • Scheduling with Task Scheduler
• CONCLUSION

INTRODUCTION

Sometimes you have a requirement to export large volumes of static reports to users who may or may not have Power BI licenses. One way of accomplishing this is through email subscriptions. The issue being that a large population (say, 50+ users) would each need their own subscription managed per report! No good (think, 50 users per 10 reports is a nightmare to manage).

Another non-optimal option is to provision per user licenses, but this can be tricky for the mid-size company. 100 users at $12/month can be costly for single report consumption (i.e., $14k+ annually). Double that if you use fancy features like paginated reporting.

We will review a solution where a report is iteratively filtered by 'employee' and sent automatically to a manager, where notes can be added, and the final write up is forwarded to the intended audience. Hold on, this will be fun!

Note: the respective GitHub repo with all code can be found here: GitHub, Python Dynamic Power BI PDFS with REST API.

PREREQUISITES

Programming Tools

• Python
• Powershell
• VS Code (highly recommended)

Environment Settings

Microsoft License Requirements:

• You will need a minimum Fabric Trial license. Ultimately this will cost ~$250/month once the trial ends after 60 days. PPU will NOT work.
• Microsoft Doc

Microsoft Fabric Admin Settings:

• "Export reports as PowerPoint presentations or PDF documents – Enabled by default." – imply search for 'PowerPoint' in your admin settings.
• "Export reports as image files – Required only for .png and disabled by default." – simply search for "image" in your PBI admin settings.
• Microsoft Doc

Azure Entra Application

To use the Power BI REST API, an Azure Entra application is required. This application allows you to authenticate and authorize access to the Power BI service. The good news is that it's generally free with a Microsoft subscription and easy to set up.

Steps to Set Up an Azure Entra Application:

1. Register an Azure Entra Application:

• Go to the Azure portal
• Navigate to "Azure Active Directory" > "App registrations".
• Click "New registration".
• Fill in the required details (e.g., Name, Supported account types) and register the application.
• Note the Application (client) ID and Directory (tenant) ID.

2. Add API Permissions:

• In the registered application, go to "API permissions".
• Click "Add a permission" > "APIs my organization uses" > search for "Power BI Service".
• Add the following permissions:
• Dataset.ReadWrite.All
• Report.ReadWrite.All
• Click "Grant admin consent" to grant the permissions.

3. Add a Client Secret:

• Go to the "Certificates & secrets" section.
• Click "New client secret" and create a secret.
• Note the client secret value, as you will need it for authentication

Please note the following for the script: Display Name, Application (client) ID, Object ID, Directory (tenant) ID

ADD USERS

• Go to EntraID > Enterprise Applications > (Select the application)
• Go to Users and Groups > Add User/Group (add the user/service accounts)

SMTP Settings

Setting up a SMTP server is slightly outside of my scope; I can tell you a quality tech can provision this quickly 😊 However, there are important settings that will need to be adjusted so your script will work appropriately.

SMTP Server

The following is required, please get this from you 'tech': Server Address, Port, User Name, Password. Best practice is to use a Service Account for the user.

Enable the SMTP Client Authentication settings:

• Sign in to the Microsoft 365 admin center.
• Go to Settings > Org settings.
• Under Services, select Modern authentication.
• Ensure that Allow Basic authentication is enabled for the SMTP protocol.
  • https://aka.ms/smtp_auth_disabled

Adding an 'App' password to the Service Account

• log into M365 with service account
• Click on profile picture top right and select 'My Account'
• left-hand navigation, click "Security info"
• Under 'Security Info" click "Add Method"
• Select "App Password" Note: We had to enable MFA to enable the app password.

Additional Information

Familiarize yourself with everything in these Microsoft Docs:

• Reports – Export To File, Microsoft Learn
• Export Power BI report to file, Microsoft Learn

PROCESS & SCRIPTS

Required Python Packages

• Run the following in python to get all required libraries: pip install requests adal pandas

File Parameters

Parameters: PBI Config

The following breaks down where each parameter is sourced.

• Azure Entra App: client_id, client_secret, tenant_id
• Microsoft Credentials: Username, password
  • This is the user that will be used to log into Power BI / Fabric. Make sure they have permissions to the workspace and data (RLS).
• Microsoft Environment: Workspace id, report id
  • We will run a script to find this information (see supporting scripts)
• SMTP Environment: smtp_server, smtp_port, smtp_username, smtp_password
  • Remember your tech friend 😊
• General: from_email, to_email
  • This is where you want to send your email to and from whom. Note that you can string multiple emails in here.
• save_locally: true or false
  • This binary parameter will save all the files to an auto-generated folder called 'PDF Reports'.

Parameters: PS_Start_Automation

• $scriptDirectory: This is the directory of your python script you want to execute
• $pythonExecutable: This is the location of your python installation
• $scriptPath: Add the name of your python file

Parameters: AutomatedReports

The only parameters required are your filters. You will see that I used an 'employees' table to loop through the list. Also, your filter expression will need to be updated. I have a column in the semantic model Employee [Employee Last First Name]. This is the column I'm filtering on each loop. Note that you will need to update this based on your semantic model. Make sure to maintain the correct syntax per this Microsoft doc (Filter a report using query string parameters in the URL).

Main Scripts

Effectively, there are three main scripts:

• PBIconfig.json: This is the config file that holds your sensitive/pertinent information. (GitHub, PBIconfig.json)
• PS_Start_Automation.ps1: This is the PowerShell script that executes your python script. This will be launched by task scheduler (see below). (GitHub, PS_Start_Automation.ps1)
• AutomatedReports.py: Thisis the python script that does the heavy lifting: connecting to the report via the app, looping and filtering the report, sending the emails, etc. (GitHub, AutomatedReports.py)

Working in tandem, the scripts should be housed in a shared folder. Once the PS_Start_Automation is updated for this location, the AutomatedReports.py script will connect to the PBIconfig.json file automatically. Again, if the 'save_locally' parameter is true, a 'PDF_Reports' folder will be created, and the generated PDFs will be saved here.

Full scripts and code can be found here: GitHub, Main Scripts

Supporting Scripts

A Jupiter notebook has been added so users can walk through the process of testing scripts and establishing their environments. A quick breakdown is below.

• Connect to a Workspace: This script effectively tests whether a user has set their environment correctly.
• Get Environment Information: This is a script exports a table showing 'WorkspaceName', 'WorkspaceID', 'ReportName', 'ReportID'. Feel free to uncomment and update the csv file location for a print out.
• Download a single report: This is a quick test to validate you are successfully connecting to and downloading a PDF of your report. Note, you will need to update the 'output_path' to save to your desired location.
• Test SMTP Configuration: A neat script that will test your SMTP configuration and show either a quick summary or detailed log for results (output_mode = 1 or 2)
• Loop Reports and Send Email: This is the same script found in AutomatedReports.py. It's here so a user can test, etc.

Full scripts and code can be found here: GitHub, Supporting Scripts or GitHub, SupportingScripts.ipynb

Scheduling with Task Scheduler

As mentioned earlier, the scripts are tied and will work in concert when the PowerShell script is launched. However, task scheduler is used to kick off the PowerShell job at certain intervals. Below walks you through the process of setting this up.

Task Scheduler Setup

1. Open Task Scheduler by typing Task Scheduler in the Windows search bar and selecting the application.
2. Click on Create Task… in the right-hand pane.
3. In the General tab, provide a name for your task (e.g., Run Python Script Daily) and configure the task to run with the highest privileges if needed.
4. In the Triggers tab, create a new trigger:

• Click New…
• Set the Begin the task dropdown to On a schedule
• Choose frequency (e.g., daily, weekly) and set the start time (e.g., 2:00 AM)
• Click OK

5. In the Actions tab, create a new action:

• Click New…
• Set the Action dropdown to Start a program
• In the Program/script field, enter powershell.exe
• In the Add arguments (optional) field, enter the path to your PowerShell script (e.g., -File "C:\Scripts\MyPythonScript\run_python_script.ps1")
• Click OK

6. In the Conditions and Settings tabs, adjust any additional settings as needed.
7. Click OK to create the task.

CONCLUSION

This guide should guide you through the full process of implementing your own 'automated Power BI PDF download and publisher' deployment. This script was built with the scope that all reports are sent to a single user so they could forward the reports with notes. However, I may create a part two where scripts are sent to the appropriate recipients. Stay tuned!

Again, the respective GitHub repo with all code can be found here: GitHub, Python Dynamic Power BI PDFS with REST API.

About the Author:William Rodriguez is the founder and principal consultant of Analytical Ants, an analytics company focused on procuring insights via data engineering, data science, and data analysis. William has led multi-million-dollar construction projects, acted as financial controller managing million-dollar monthly check productions, and built and deployed BI infrastructures at companies large ($30B Revenue) and 'small' ($60M Revenue). Academically, he has his master's in business, two undergraduate business degrees, seven active Microsoft certifications, and over 70 accredited SQLBI training hours. William loves spending time with his amazing family and knows that he would be nowhere without Christ.

Reach out to Analytical Ants LLC for all your data/analytical needs. We specialize in #DataEngineering #DataScience, and #DataAnalysis.