by Madan Thota

Azure Power BI Automation - Basics and Connect

This is the forth post in KPI's Power BI blog series. In this blog, we provide a high-level overview of PowerShell and how to connect it to the Power BI service. You can read the other posts in the series here:

1. Visual Interactivity in a Power BI Report
2. Dynamic Selection of Measures in Power BI
3. Security and Collaboration in Power BI
4. What is PowerShell and Connect to Power BI Service
5. Dataset refresh and other dataset operations from PowerShell

What is PowerShell and Connect to Power BI Service.

We all know Power BI is an interactive visualization tool, but now it became a collection of services, apps, and connectors. Then from the admin/development perspective, multiple activities need to be run in the background to get the latest data processed and transformed for reports. Some examples of these activities are data set refresh( on-demand and schedule), download pix files from services to back up locations, Export report output to a local network in excel or pdf format. You may do all these admin activities from services with a series of steps involved through browser navigation, what if you write a script and execute in a click.  

This series of articles is going to cover how you can use powershell, Power BI cmdlets, and also REST API to make few Power BI tasks automated. The prerequisite for the below article is to have a Power BI Service account, you have access to datasets and reports. If you don’t have one, plese create one using Power BI sign in page and create datasets and reports.

What is PowerShell:  

PowerShell is a cross-platform task automation and configuration management framework, consisting of a command-line shell and scripting language. Read more about Powershell and installation instructions.

Install Power Shell:

1. Windows:  https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell?view=powershell-7
2. Unix/Linux: https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell-core-on-linux?view=powershell-7
3. Mac:  https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell-core-on-macos?view=powershell-7

Microsoft PowerBI Mgmt cmdlets which give predefined API calls to make power shell coding easier.   This will install all the functionality related to reports, capacities, data, workspaces, and admin activities.

Install Power BI Cmdlet: 

These allow us to call Power BI cmdlets or Rest API and login from power shell

Run below command from Power Shell: 

    Install-Module -Name MicrosoftPowerBIMgmt

Microsoft Power BI Mgmt install cmdlets which give predefined API calls to make powershell coding easier. This will install all the functionality related to reports, capacities, data, workspaces, and admin activities.

Copy below code to any text editor and save the file as ConnectionTest.ps1

$User = "XXX@XX.com"

$PW = "XXXXX"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

Update credentials in the first two lines of PowerShell Script:    

The powershell uses credentials for connecting Power BI cloud. This we are testing with Personal creds if you have any service principal created for CLI jobs you can use that.

Run the script from PowerShell.  This will trigger the connection to Power BI service and log in with credentials

.\ConnectionTest.ps1

By executing this, you are opening a session and a successful connection provides session details like below.

Once you have a connection you can run any other cmdlet calls to do further actions, which I will cover in next article of Power BI automation.

Ref of Power BI CmdLets:

https://docs.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps

Check out our case studies to see how we've helped our clients:
Masterlock
Williams Sonama

Do you enjoy the solving technology problems and helping people meet their data analytics challenges? Maybe you would be a good fit for our team. See our job openings.

---

Madan Thota is a Director of Technology at KPI Partners and leads project delivery of  Big Data, Cloud and Analytics applications.  His professional focus is in the world of pre sales, enterprise decision support, business intelligence, and data architecture. Check out Madan's blog at KPIPartners.com.