Power Shell and Power BI – beginning of a journey

Sue Bayes

February 17, 2022

Governance and Monitoring of Power BI with Power Shell

As part of any Power BI development it is important to understand how users are engaging with Power BI and how they are using it. This allows you to understand what matters to them and to allow governance of the activity. To be able to manage an activity you need to measure it. Unfortunately, at the time of writing, this is not easy to do, particularly if you don’t have a person/team who understands that this is required and how to do it.

There is a new Power BI Adoption RoadMap that has been developed to help businesses understand what steps they need to follow to ensure that they are able to monitor their journey with Power BI.

Power BI administrators often use a combination of Power BI-PowerShell to write scripts either for automation or to execute a large number of tasks. This post is a collection of steps on how to use Power Shell to access Power BI Activity Logs. As I haven’t used PowerShell before, but have used command line scripting before in DOS and Linux, albeit minimally, there will be a lot of research required and wanted to keep a track of things I learnt along the way.

Requirements
Need to be a Power BI Administrator
Service Principal Account (best practise) but seems quite complex to set up and out of my control at the moment
Install PowerShell and understand how to use it
Install Visual Studio Code
PowerBI Management Module – add to Visual Studio Code

Steps – Ensure set up with Power BI Admin rights
Use skyline to understand Power Shell.
Use Melissa Coates scripts to attach to Tenant as a user with Power BI Admin rights. The scripts need to be saved as a .ps1 file within Visual Studio code and can then be run.
Pull out Activity logs using Melissa Coates script. These will place a json file in a folder for each day of activities.

Power BI can then access the files within the folder to create a dataset to allow analysis of Activity Usage.

1. Create a query to access Folder.Files within Power Query (I like to use a parameter called FilePath for the filepath).

2. Expand the data from the folder and perform normal cleaning task:

Split date and column,
Choose only the columns you need,
Change to appropriate data type.

Additional “things” I learnt and want to remember:

Spaces in names causes splits. We are encouraged to name things “human friendly” however many different programming languages don’t like it. Power Shell included. To tell Power Shell to ignore the space in a name in it, put an & in front and wrap the name in ” “, eg:
& “C:\Windows Services\Myservice.exe”