Common Power BI Admin Scripts Part 1

Between the Power BI PowerShell modules and the Power BI REST APIs administrators have a rich set of tools to efficiently administer Power BI environments. Custom administrative and monitoring solutions based on these technologies have been featured on this blog before but today we will start the first of a two-part series highlighting simple script examples that Power BI administrators can use to address common scenarios.

The six PowerShell script files (.ps1) for today’s examples are available in my GitHub repo.

Why Script?

As a Power BI Service administrator, you could rely on the Power BI admin portal and graphical interface controls of the Power BI service to carry out these tasks. However, a set of tested scripts aligned to your most common scenarios allows you to resolve issues faster and with less risk of error. Additionally, if you’re relatively new to administering Microsoft technologies, getting started with administrative scripting for Power BI can benefit similar tasks with other services such as any Azure services your organization may use.

Prerequisites

• Power BI Administrator role
• PowerShell (formerly Windows PowerShell)
  • PowerShell 7.1.2 is available for install
  • I use Visual Studio Code and its PowerShell extension

  Be sure that you’ve addressed the TLS 1.2 security requirement.

  You may derive some value from these scripts without some or all of the above prerequisites such as adjusting the parameters to a scope of Individual if you’re not assigned the Power BI admin role.

  Get Users in a Workspace

  Which users, security groups, and/or service principals have access to a particular workspace and with which roles/access rights (admin, member, contributor, viewer)? This information is readily available in the Power BI service but, particularly when there are several identities in the workspace, you may want a file with the user access data in a simple table format. The script in the GitHub repo retrieves the given workspace as an object and exports out a table of its users including their access rights, user principal name, identifier, etc.

  Get Dataset Refresh History and Refresh Schedule

  Let’s assume a production dataset refresh has failed and as a Power BI admin you’re asked to investigate the cause. Assuming you can be provided (or determine on your own) the name of the dataset and its workspace, you can make a quick call to the REST API to retrieve the refresh history to capture the error message(s), time started and completed, etc. You can also retrieve the refresh schedule for the dataset (if there is one) – maybe the timing or frequency of the refresh should be revised.

  Add and Remove Users from a Workspace

  Even if your organization is at least trying to use security groups for provisioning access to workspaces, Power BI admins may receive a high volume of requests to add or remove various identities to power bi workspaces. Resolving these requests without any scripting is possible but, again particularly for workspaces with several (5-7+) identities assigned to distinct workspace roles, a programmatic approach with a data output file confirming the change may be beneficial.

  Get Workspaces Assigned to a Premium Capacity

  Premium capacity is not cheap and a given capacity has limited resources and thus a common question can be which workspaces are assigned to a given capacity. Perhaps a certain team’s workspaces or the workspaces for a particular project should be moved off the given premium capacity. Examples of the actual moving/migrating scripts may be provided in Part II but for now this example captures the current workspaces for the given premium capacity.

  Export the Power BI Activity Log

  The Power BI Activity Log (or O365 Audit log) is an essential resource for analyzing and monitoring Power BI environments. Therefore, even if your organization is fairly new to Power BI and/or your deployment is relatively small at the moment, you probably want to invest in a process for loading this data to a database to support analysis/reporting when needed. That being said, if you don’t already have an activity log history stored in your own database (and maybe monitoring dashboards built on top), an ad hoc script with output to CSV for one day’s worth of activity log events may be just enough to answer your basic questions of what happened in your PBI environment for a given day.

  Get Datasets of a Workspace

  Given the availability of shared datasets and the build permission, you may have a workspace (or a few workspaces (dev, test, prod) dedicated to Power BI datasets that should be used by report authors and analysts. Therefore, you may regularly want to access this workspace to see the properties of the datasets such as the ConfiguredBy (dataset owner) and TargetStorageMode(Abf or PremiumFiles). The ‘-Include’ parameter of the Get-PowerBIWorkspace cmdlet makes it easy to retrieve the artifacts (reports, dashboards, datasets) of the workspace.

  Wrapping Up

  If you’re a Power BI admin and haven’t started scripting yet hopefully these six simple samples encourage you that this is a worthwhile endeavor. In next post (Part II) I’ll share several other script samples including taking action (edit/write) against PBI artifacts and working with Azure AD users and groups. Thanks for visiting Insight Quest.