Retrieve Power BI Pro Licensed Users

Managing the assignment of Power BI Pro licenses is a fundamental task of Power BI administrators. The Power BI admin, the BI team, and possibly other stakeholders may all pose a number of common questions such as:

1. Who has been assigned a Power BI Pro license?
2. How many pro licenses have been assigned by department?
3. How many pro licenses have been provisioned for my organization?
4. How many licenses are remaining/available to be assigned to users?

This post shares and describes a PowerShell script which leverages the AzureAD module to retrieve the data necessary to answer these questions.

Why this matters

Per the Power BI licensing documentation, a pro license is required for publishing and editing content in app workspaces. If the app workspace is not assigned to a premium capacity, even the users viewing/consuming the content will require a pro license.

Pro license assignments are also very important from a governance perspective. An organization that has provisioned premium capacity would generally want to limit the number users with pro licenses to users who A) have a clear need for developing and publishing Power BI artifacts (dashboards, reports, dataflows, datasets) on an ongoing basis and B) have received some form of training or certification on using Power BI effectively as well as the organization’s policies for using Power BI.

As one (very) simple example for an organization with premium capacity, two users in a department of ten could be determined to be the content creators for their department – perhaps one will build datasets and the other will build reports and dashboards. These two users, along with maybe a backup user, could be assigned pro licenses. Other users on the team without a pro license could still make development and test related contributions to their team’s projects via Power BI Desktop and the Viewer workspace role but they would rely on the pro users in their department for publishing and distributing content.

Alternative: O365 Admin Center

As an alternative to a PowerShell script, you could use the Office 365 Admin Center to view or export some of this data. The following two images are examples from the Active Users page and the Licensing page:

In this example, 561 pro licenses have been assigned to users out of a total of 645 available (provisioned). Therefore, any script or report built on top of this script to identify the pro users should tie back to 561. (Some organizations assign Power BI Pro via Office 365 E5 licenses so you’d use the count of E5 licenses in this scenario)

Unfortunately there’s not currently a standard ‘view’ in the O365 admin center for Power BI Pro licensed users. If you’re not a global or user management admin, you also can’t create a custom view for pro users. Here’s the documentation if you’re interested in pursuing this option.

The Script

Like always, you can view and download the PowerShell script (.ps1) supporting this post from my GitHub repository.

The script exports three tables (Users, OrgO365Licenses, ProUsers) to their own CSV files. If you only wanted the users with Power BI pro licenses, you could refactor the script to not export the users and remove the Org365 licenses (step #5).

However, I assume you’d want this additional data to support more complete analysis and for validation. For example, you could build a lookup transformation between the users and pro users tables to support a column that identifies whether the given user has a pro license. This enriched user dimension table could become part of a larger administrative data model which supports a collection of administrative reports and dashboards and ad hoc analysis.

Script Steps

The script includes comments and links but here are the essentials of the six step process:

1. A script (AdminCreds.ps1) is executed via dot sourcing to expose its variables to the scope of the ExportUserProLicenses script.
   • In this case, the $PBIAdminUPN, $PBIAdminPW, and $TenantID variables are used for authentication to AzureAD in step #2
   • This approach centralizes the credentials such that they can be reused across multiple scripts. However, Azure Key Vault may be a best option for securing and managing passwords and application secrets.
2. A PowerShell credential is created based on the UPN and password variables and this credential, along with the tenant ID variable is passed to the Connect-AzureAD cmdlet.
   • Note that a comment block references an alternative authentication method with an AzureAD application and certificate thumbprint. You may prefer or be required to walk through this setup process with an application and service principal.
3. The current date, the paths of the CSV files to write to, and the service plan ID to identify a Power BI Pro license is declared as a variable.
   • Note the link to the service plan reference page. The service plan ID used in the script is associated with the BI_Azure_P2 service plan, which is included in Power Pro license and Office 365 E5 Skus.
4. Retrieve and export twelve properties for all users in the organization including the UPN, DisplayName, JobTitle, and Department.
5. Retrieve and export summary level organization licensing data via the Get-AzureADSubscribedSku command.
   • This should align with the Licenses page in the O365 Admin Center though rather than ‘valid’ and ‘assigned’ you’ll see ‘Enabled’ and ‘ConsumedUnits’.
6. Retrieve and export users with Power BI Pro licenses.
   • This final step loops over the AzureAD users from step #4 ($ADUsers) to retrieve user license detail for each user.
   • User license detail is then filtered down to the Power BI Pro service plan ID from step #3.
     • In this example, 561 rows (one for each user with a pro license) was written to the Pro user license CSV file.
   • Given the iterations, you shouldn’t expect this file to be generated instantly. In this example, the organization has approximately 8,000 users and the script took 20 minutes to complete. (Generally acceptable if part of a nightly or regularly scheduled process)
     • To improve performance in much larger organizations you could determine if there are certain departments or other attributes of users that identify users that will never be assigned a pro license. You’d then just add this filtered users variable to use for Step #6.

Here’s a quick look at the user license detail variable from step #6:

Though highlighted out, the same user has been assigned multiple service plans, one of which is a Power BI Pro license (BI_Azure_P2).

To put this script to use you’ll need to figure out how you want to handle authentication to AzureAD (create a PowerShell credential?, Application with service principal?) and of course define the file paths relevant to your environment.

Additionally, you’ll also want to decide what you do with the CSV files to support analysis such as retrieving the files directly into a Power BI dataset or routinely loading the files to a database which in turn serves as the source for Power BI and other tools.

Wrapping Up

With a PowerShell script and a very basic BI solution in place a Power BI Admin can have Power BI Pro user license information readily available. As always feel welcome to leave any comments or questions. If you found this post useful you can subscribe via the Subscribe widget (‘Follow’) on the homepage.