How to automate Power BI using REST API and PowerShell Cmdlets?

On your journey to becoming a Power BI rockstar, you were introduced to XMLA endpoints and how to use them. However, as you grow, you will still find yourself searching for more ways to efficiently monitor, audit and manage your enterprise environment and automate some of the work. Power BI REST API and PowerShell cmdlets open up a whole new world that is going to expand your capabilities.

But why should you know about it?

In the age of data-driven decision-making, mastering tools like Power BI REST API and PowerShell can be a game changer. These tools offer unparalleled capabilities in automating and optimizing analytics workflows.

In this article, I will guide you through the intricacies of Power BI REST API and PowerShell cmdlets. This combination unlocks a new realm of possibilities, from automated report generation to dynamic data refreshes and governance compliance.

1. Decoding REST API

An API, or Application Programming Interface, is a set of rules that define how software applications or devices talk to each other. There are different types of APIs out of which REST API is one of the commonly used.

Without going into too much detail, understand that a REST (Representational State Transfer) API is used to communicate between Clients (a person or application) who want to access information from Servers (applications or databases) over the web or internet.

Note that this is going to tie back with the Power BI Service since workspaces are hosted on servers. More on this later!!

RESTful APIs use HTTP methods (GET, POST, PUT, DELETE, etc.) to perform CRUD (Create, Read, Update, Delete) operations on resources, which are typically identified by URIs (Uniform Resource Identifiers).

As seen in the figure above, any command generally starts with a command, for example, GET followed by the task you want to accomplish for example Get-PowerBIWorkspace. The server answers back in JSON format if it's a valid query else it will throw an error message. This JSON can be parsed to get the information we are interested in.

Thus, Power BI REST API serves as a programmable interface, offering an alternate way to interact with Power BI resources empowering developers to automate tasks like updating datasets, modifying reports, or managing access to workspaces from their preferred coding environment.

But what does this mean for an organization or an individual analyst? It translates to heightened efficiency, better resource management, and the capability to perform complex tasks at scale.

Where does PowerShell come in the picture?

To use Power BI REST APIs, you need a convenient “place” to run the scripts. Out of the many alternatives like Python, C# or Java, PowerShell is another alternative that provides the following advantages:

1. It's easy to learn and use if you are familiar with scripting.
2. It provides a large library of cmdlets to interact with Power BI REST APIs.
3. It can be used to manage multiple Power BI tenants and resources with a single script.
4. Most importantly, it offers a secure way to manage and automate Power BI tasks with certificates and secure credentials.

2. PowerShell Cmdlets

PowerShell and PowerShell cmdlet are often used interchangeably, but they are not the same thing.

PowerShell is a task automation and configuration management framework that is used to perform a wide range of administrative tasks in Windows environments, such as system management, security management, and automation.

On the other hand, PowerShell cmdlet is a term used to refer to the individual commands that are used within the PowerShell framework. These cmdlets are the building blocks of PowerShell scripts. You can automate routine tasks, configure system settings, and manage network components.

Combining the Power BI REST API with PowerShell cmdlets is like assembling a super team for your analytics environment. This integration can lead to streamlined workflows, improved governance, and enhanced consistency across your analytics operations.

Is the Power BI REST API available for all Power BI service plans?

• Power BI Pro: The API is available for users with a Power BI Pro license.
• Power BI Premium: The API is available for users with a Power BI Premium license. Premium capacity allows for better scalability and performance.
• Power BI Embedded: This is a dedicated capacity for embedding reports and dashboards in custom applications. It also includes access to the API.

3. The Journey Begins

Leveraging PowerShell Cmdlets

The best part of using Cmdlets to access the Power BI API is how quickly you can get started.

1. Open Windows PowerShell ISE (if available, else use the normal Windows PowerShell). Remember to start this application as an administrator.
2. Just type the following command to start using the Power BI API

Install-Module -Name MicrosoftPowerBIMgmt

3. Now that the modules are installed, you can authenticate your PowerShell session to the API via the following command and connect your account.

Connect-PowerBIServiceAccount

4. Use the sign-in box to log into your account. If you are a Power BI administrator, it might give you tenant results in your command execution.

5. After successfully authenticating to the service, you can now start answering questions. Workspaces are one of the most basic types of objects in the Power BI API, so getting a list of workspaces that you have access to is a great place to start.

Get-PowerBIWorkspace -All

6. Let's say you want to export this list to a CSV file. If you are a Power BI admin, then you can use the parameter scope and set it to organization to get all the workspaces (even if you don't have access to them).

Get-PowerBIWorkspace -Scope Organization -All | ConvertTo-Csv | Out-File c:\Users\shant\OneDrive\Desktop\PowerBIWorkspaces.csv

You can use any other coding environment, for example, I am using vsCode (Visual Studio Code) to implement the same steps as shown below. Note that you will need to install the PowerShell extension first.

If you’re able to successfully authenticate and perform each of the above steps, then you are ready to take things to the next level. You can go through the various commands available in your arsenal to perform a variety of activities.

But, what is the use of all these?

As mentioned earlier, our goal is to automate processes as much as possible and reduce manual effort. Imagine the amount of time you would have to waste manually going through the Power BI service and noting down all these details.

Imagine being able to use a scheduled job to run commands like these at a fixed time and save the results, plus appending the date and time to the file name while saving. This is just the tip of the iceberg and if you're familiar with scripting then the sky is the limit. Let's see some real-world examples next.

Enjoyed reading it this far!

You can subscribe to our newsletter to see the full post and also get future posts delivered to your inbox for free.

Subscribe

Email sent! Check your inbox to complete your signup.

No spam ever. Unsubscribe anytime. Pinky Promise!!

4. Real-world Scenarios

Dynamic Data Refreshes

Keeping data fresh and relevant is crucial. Setting up automated schedules to refresh your datasets ensures that your reports and analytics reflect the most current data. You obviously have scheduled refresh capability in the Power BI Service but being able to do this without even opening the Web UI is phenomenal.

Import-Module MicrosoftPowerBIMgmt

#1. Provide relevant details

$workspaceName = "Power BI Embedded (Dev)"
$datasetName = "Space Missions"
$workspace = Get-PowerBIWorkspace -Name $workspaceName
$dataset = Get-PowerBIDataset -WorkspaceId $workspace.Id -Name $datasetName

#2. Get the workspace ID of the dataset(s) to be refreshed

$workspace = Get-PowerBIWorkspace -Name 'Power BI Embedded (Dev)'

#3. Get the dataset ID(s) of the datasets to be refreshed 

$dataset = Get-PowerBIDataset -WorkspaceId $workspace.Id -Name $datasetName

#4. Build dataset refresh URLs

$URL = 'groups/' + $workspace + '/datasets/' + $dataset + '/refreshes'

$headers = Get-PowerBIAccessToken

Invoke-PowerBIRestMethod -Url $RefreshDSURL -headers $headers -Method Post -Verbose

Workspace Management

Managing Power BI workspaces can be time-consuming, especially in larger organizations. Automating the creation, updating, and deletion of these workspaces not only saves time but also ensures consistency in the setup and management of these environments.

Import-Module MicrosoftPowerBIMgmt

$workspaceName = "New Workspace"

#1. Create a new workspace and retrieve its Id

New-PowerBIWorkspace -Name $workspaceName
$workspace = Get-PowerBIWorkspace -Name $workspaceName

#2. Add a new user as admin

Add-PowerBIWorkspaceUser -Scope Organization -Id $workspace.Id -UserEmailAddress shantanil@neil-bagchi.com -AccessRight Admin

#3. Remove a user

Remove-PowerBIWorkspaceUser -Scope Organization -Id $workspace.Id -UserEmailAddress shantanil@neil-bagchi.com

Governance and Compliance

Automating governance and compliance checks is essential for any organization dealing with sensitive or regulated data. It might be easier to download activity events. The Get-PowerBIActivityEvent cmdlet takes a StartDateTime and an EndDateTime parameter with the same restrictions as the ActivityEvents REST API, meaning you can only retrieve the activity data for one day at a time.

# Login to Power BI
Connect-PowerBIServiceAccount

Get-PowerBIActivityEvent -StartDateTime '2024-02-02T18:00:00' -EndDateTime '2024-02-02T18:20:00' | ConvertFrom-Json

These examples should act as a starting point for your curiosity. Since I'm not a PowerShell expert and used it only a few times, I am still learning ways of utilizing this newfound power.

5. Best Practices for Effective Implementation

Starting Small

Embarking on your automation journey with simple, low-risk tasks is advisable. This approach allows you to gain familiarity with the tools and their capabilities before tackling more complex challenges.

Emphasizing Documentation

Maintaining detailed records of your scripts and API interactions is crucial. This documentation will serve as an invaluable resource for troubleshooting, future modifications, and training new team members.

Error Handling and Monitoring

Implement robust error handling and monitoring within your scripts and API calls. This ensures that any issues are promptly identified and addressed, maintaining the integrity of your automated processes.

Scalability and Maintenance

As your organization grows, so do your analytics needs. Design your automation solutions with scalability in mind. Regularly review and update your scripts and API implementations to align with evolving business requirements.

Conclusion

This blog post provides an introduction to automating tasks using Power BI REST API and PowerShell cmdlets. Whether you're a data analyst, business intelligence professional, or just looking to level up, mastering the Power BI REST API is a journey worth embarking on.

For more detailed insights and guidance, it's beneficial to explore various resources and examples provided by experts in the field. Check out the following websites that help illustrate more practical applications.

THANK YOU FOR READING !!

If you have found this page useful, then please feel free to share it with your network and don't forget to comment.