Power BI Admin Datasets

If you’re a Power BI Administrator, and particularly if you’re responsible for administering a large and varied Power BI environment, you may consider investing time into the development of a Power BI administration dataset. Just like normal datasets or analytical data models generally, the purpose of this work is to provide a platform for simple and consistent analysis of data as represented in facts and dimensions. In this case, you’re putting yourself and possibly other stakeholders in a position to quickly answer common questions about your Power BI environment.

Typical facts to be included in the Power BI admin dataset are the Office 365 Audit Log events for Power BI Activities (e.g. view report) and the Performance Monitor Counters for the On-Premises Data Gateway. The dimensions for the dataset include app workspaces, users, premium capacities, and more. By creating a Power BI Admin dataset (or possibly a few, targeted Admin datasets) with relationships between these facts and dimensions and with simple calculation metrics (e.g. count of workspaces), a basic package of reports and dashboards targeted at common administrative questions can be built and published to Power BI.

Tools for Power BI Admins

Before you head down the path of building a custom administrative solution, you should definitely be familiar with the capabilities (and limitations) of tools provided for Power BI administrators. A couple examples of this include the relatively new Workspaces page in the Power BI Admin Portal and the Power BI Premium Capacity Metrics app.

Power BI Administration is clearly a priority for the Power BI team, particularly given Microsoft’s strategy of embedding enterprise BI capabilities (Analysis Services, Reporting Services) into the Power BI platform, and thus new administrative features and capabilities are being added frequently. However, and again this is mostly for large and varied Power BI environments (e.g. several hundred to thousands of active users), I find that custom admin solutions are valuable, if not essential, and my sense is they will likely remain so (in some form or another) for the next 18-24+ months.

This past Saturday I spoke at the Orange County SQL Saturday in Los Angeles, California. The PowerPoint file from this presentation which provides more details on existing Power BI Administration tools as well as common custom admin solutions is available for download from my GitHub repository.

From Script to Data to Dataset

The bad news is that yes, as a custom admin solution for a Microsoft application some degree of PowerShell scripting is required. The good news is that given the Power BI Management module and particularly the Invoke-PowerBIRestMethod cmdlet for calling the Power BI REST API, these PowerShell scripts need not be very complex at all. Assuming you’ve already been assigned the Power BI service administrator role, minimal to no experience with PowerShell is required.

Essentially your PowerShell script(s) will write data to CSV or JSON files per the following image:

These script(s) will retrieve from Power BI (obviously) and likely the Azure Active Directory module as well given the importance of Azure AD security groups for implementing data governance policies. Of the files above, Users, Groups, GroupOwners, and GroupMembers are all retrieved from Azure Active Directory via their respective cmdlets.

The capacity JSON files reflect a single command to retrieve all Power BI Premium capacities for the tenant (‘Capacities’) and the distinct Power BI Premium workloads enabled for each of six distinct premium capacities. A workload is a relatively new but very important concept in Power BI as the resources (CPU, RAM) from a premium capacity node can now be allocated to ‘workloads’ beyond Power BI datasets such as Dataflows, Paginated Reports, and AI.

Power BI Desktop of course has a rich and mature set of capabilities for accessing files/folders including JSON format, optionally transforming and enhancing this data, and then modeling it to support visualization and analysis. There are plenty of other blogs, documents, and books that cover the Power Query Editor connect and transform capabilities and given the length of this blog post I’ll skip ahead to just show the Fields List and a few schemas within a single but fairly robust custom Power BI admin dataset:

Per the slides above, there are natural one-to-many relationships between many of these artifacts which can you model in the new Modeling view in Power BI Desktop. In these examples, I’m not including the fact tables for Power BI audit log events or gateway performance monitor counters but you can image relating an audit log event to a user table from Azure AD.

You can use the ‘Dataset Configured By’ field from the Datasets artifact table to create a relationship to a Users table from Azure AD or possible de-normalize these user columns (e.g. User Name, Department) into the datasets table. As just two example use cases, this allows you to create reports that break out which data sources are being used by which department (ie finance, purchasing) and which user is, via the dataset he/she configured, associated with the most reports.

Typically I wouldn’t recommend a bidirectional cross-filtering relationship unless it was absolutely necessary and we fully understood the implications for behavior and performance. Why two bi-direct relationships are shown above is outside the scope of this post.

Power BI Artifact Script

Both sample scripts (Power BI Artifacts, Azure AD) from this post have been uploaded to my GitHub repository. Please note that you may not need all or many of these tables, at least not at the moment. For example, if you don’t have Power BI Premium capacity yet, simply having every workspace, dataset, data source, report, and dashboard for your tenant could be a significant benefit. When you’re just getting started, simply having the count of these items and watching the counts grow (or not) over time can provide a basic indication of the scale and growth of Power BI in your organization.

With all of this said, let’s take a look at one of these scripts:

#1. Connect to Power BI with credential of Power BI Service Administrator

$User = "somebody@bigcompany.com"
$PW = "abcdef"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

#2. Declare variables for export paths and date data was retrieved

$RetrieveDate = Get-Date 
$BasePath = "C:\Users\Brett Powell\PowerShell Scripts\ArtifactExport\"

#ExportPaths
$DatasetsPath = $BasePath + "Datasets.csv"
$WorkspacesPath = $BasePath + "Workspaces.csv"
$ReportsPath = $BasePath + "Reports.csv"
$DashboardsPath = $BasePath + "Dashboards.csv"
$DatasourcesPath = $BasePath + "DataSources.csv"
$CapacitiesPath = $BasePath + "Capacities.json"
$CapacityAWorkloadPath = $BasePath + "CapacityAWorkloads.json"
$CapacityBWorkloadPath = $BasePath + "CapacityBWorkloads.json"
$CapacityCWorkloadPath = $BasePath + "CapacityCWorkloads.json"
$CapacityDWorkloadPath = $BasePath + "CapacityDWorkloads.json"
$CapacityEWorkloadPath = $BasePath + "CapacityEWorkloads.json"
$CapacityFWorkloadPath = $BasePath + "CapacityFWorkloads.json"

#3. Export Power BI Premium capacities and capacity workloads

#Premium Capacity ID variables
$CapacityA = "The Capacity ID for Premium Capacity A"
$CapacityB = "The Capacity ID for Premium Capacity B"
$CapacityC = "The Capacity ID for Premium Capacity C"
$CapacityD = "The Capacity ID for Premium Capacity D"
$CapacityE = "The Capacity ID for Premium Capacity E"
$CapacityF = "The Capacity ID for Premium Capacity F"

#Premium Capacity Workload URL variables
$CapacityAURL = 'capacities/' + $CapacityA + '/Workloads'
$CapacityBURL = 'capacities/' + $CapacityB + '/Workloads'
$CapacityCURL = 'capacities/' + $CapacityC + '/Workloads'
$CapacityDURL = 'capacities/' + $CapacityD + '/Workloads'
$CapacityEURL = 'capacities/' + $CapacityE + '/Workloads'
$CapacityFURL = 'capacities/' + $CapacityF + '/Workloads'

#Export Premium capacities
Invoke-PowerBIRestMethod -Url 'capacities' -Method Get | Out-File $CapacitiesPath

#Export EA Prod Capacity Workloads
Invoke-PowerBIRestMethod -Url $CapacityAURL -Method Get | Out-File $CapacityAWorkloadPath

Invoke-PowerBIRestMethod -Url $CapacityBURL -Method Get | Out-File $CapacityBWorkloadPath

Invoke-PowerBIRestMethod -Url $CapacityCURL  -Method Get | Out-File $CapacityCWorkloadPath

Invoke-PowerBIRestMethod -Url $CapacityDURL  -Method Get | Out-File $CapacityDWorkloadPath

Invoke-PowerBIRestMethod -Url $CapacityEURL   -Method Get | Out-File $CapacityEWorkloadPath

Invoke-PowerBIRestMethod -Url $CapacityFURL -Method Get | Out-File $CapacityFWorkloadPath

#4. Export Power BI Artifacts

Get-PowerBIDataset -Scope Organization | Select *, @{Name="Date Retrieved";Expression={$RetrieveDate}} | Export-Csv -Path $DatasetsPath

Get-PowerBIReport -Scope Organization |  Select *, @{Name="Date Retrieved";Expression={$RetrieveDate}} | Export-Csv -Path $ReportsPath

Get-PowerBIDashboard -Scope Organization |  Select *, @{Name="Date Retrieved";Expression={$RetrieveDate}} | Export-Csv -Path $DashboardsPath

Get-PowerBIWorkspace -Scope Organization -All |  Select *, @{Name="Date Retrieved";Expression={$RetrieveDate}} | Export-Csv -Path $WorkspacesPath

Get-PowerBIDataset -Scope Organization | Foreach {$dsID = $_.Id; Get-PowerBIDatasource -DatasetId $dsID -Scope Organization} | `
    Select *, @{Name = "DatasetID"; Expression={$dsID}}, @{Name = "Date Retrieved"; Expression={$RetrieveDate}} | `
    Export-Csv -Path $DatasourcesPath

Per the code comments the script above follows the following four steps:

1. Authenticate to Power BI
2. Define the file paths as variables
3. Export Power BI Premium capacity metadata
4. Export Power BI artifacts

As an alternative to a user credential (a user assigned to the PBI Admin role), you could also use a service principal with the Connect-PowerBIServiceAccount cmdlet. Please also note that the retrieval of the data sources (Get-PowerBIDatasource) at the end of the script can take hours to complete.

Azure Active Directory Script

Whether it’s the Power BI artifacts or the Power BI audit log event data or maybe something separate from Power BI altogether you probably want to analyze/slice the data based on user profile attributes. Moreover, it’s quite the benefit to be able to quickly identify which users are in which security groups, who the owner(s) of those groups are, etc without having to access a specific portal/page or create a one-time adhoc script.

So let’s look at a script for retrieving Users, Groups, Group Owners, and Group Members:

#1. Authenticate to Azure AD with service principal and certificate thumbprint

$tenant = "abcdef"
$application = "ghijkl"
$thumb = "mnopqrs"

Connect-AzureAD -TenantId $tenant -ApplicationId $application -CertificateThumbprint $thumb

#2. Define variables for users, groups, export paths and date retrieved

$RetrieveDate = Get-Date 
$ADGroups = Get-AzureADGroup -All $true
$ADUsers = Get-AzureADUser -All $true 

$BasePath = "C:\Users\Brett Powell\PowerShell Scripts\ArtifactExport\"

$AzureADUserPath = $BasePath + "Users.csv"
$AzureADGroupPath = $BasePath + "Groups.csv"
$AzureADGroupOwners = $BasePath + "GroupOwners.csv" 
$AzureADGroupMembers = $BasePath + "GroupMembers.csv"

#3. Export Azure AD Users and Groups

$ADUsers |  Select *, @{Name="Date Retrieved";Expression={$RetrieveDate}} | Export-Csv -Path $AzureADUserPath

$ADGroups |  Select *, @{Name="Date Retrieved";Expression={$RetrieveDate}} | Export-Csv -Path $AzureADGroupPath

#4. Export Azure AD Group Owners and Group Members

#Group Owners
$ADGroups | ForEach {$GroupID = $_.ObjectId; Get-AzureADGroupOwner -ObjectId $GroupID} | ` 
    Select DisplayName, UserPrincipalName, @{Name="GroupMembershipType";Expression={"Owner"}}, `
    @{Name="GroupObjectID";Expression={$GroupID}}, @{Name="Date Retrieved";Expression={$RetrieveDate}} | `
    Export-Csv $AzureADGroupOwners

#Group Members
$ADGroups | ForEach {$GroupID = $_.ObjectId; Get-AzureADGroupMember -ObjectId $GroupID -All $true} | ` 
    Select DisplayName, UserPrincipalName, @{Name="GroupMembershipType";Expression={"Member"}}, `
    @{Name="GroupObjectID";Expression={$GroupID}}, @{Name="Date Retrieved";Expression={$RetrieveDate}} | `
    Export-Csv $AzureADGroupMembers

Per the code comments the script above follows the following four steps

1. Authenticate to Azure AD
2. Define variables for users, groups, and the export paths
3. Export the users and groups
4. Export the group owners and group members

To setup authentication with a service principal per this example you can follow the following process from MS Docs. As an alternative remote authentication approach for the Connect-AzureAD cmdlet, you can create a PowerShell credential based on the identity of a user like the following snippet:

$TenantID = "abcdef"
$User = "somebody@bigcompany.com"
$PW = "somebody@bigcompany.com"

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

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

Connect-AzureAD -TenantId $TenantID -Credential $myCred

Similar to the Get-PowerBIDatasource example from the Power BI artifact script, the Azure AD script iterates over the Azure AD groups to get each group owner and each group member for each group. Unlike the Power BI artifact data, all Azure AD data can be written out to CSV files thus leaving making it easy to import and optionally transform/enhance this data in Power BI.

Admin Dataset Reporting Samples

Just like normal BI projects, almost all of the ‘heavy lifting’ for the Power BI Admin dataset is in the ETL (extract-transform-load) process. Once you’re relatively comfortable and stable in terms of accessing the Power BI data you need on a schedule, perhaps via SQL Server Agent or Azure Automation, the analytical and visualization layer is fairly straight forward even if you’re not that experienced with Power BI or Analysis Services.

Nonetheless, before I wrap up this post I’ll share a few sample report pages which can be developed on top of the admin dataset containing the tables from the two scripts.

It’s always a good idea to follow sound visualization practices (ie. alignment, distribution) and to take advantage of Power BI’s core features such as the new filter pane. However, the audience for Power BI Admin reports may be only a small group of internal BI/IT users or stakeholders looking for a few basic data points so I wouldn’t get too carried away with the aesthetics.

Wrapping Up

Maybe a time will come when the Power BI Admin portal or perhaps a dedicated admin app provides rich analytics of your tenant such that the solution described in this post is unnecessary. Likewise, maybe there will soon be out-of-the-box monitoring capabilities for data gateways, integration with Azure AD, and easy access to the O365 audit log event data. However, my experience suggests that every organization or deployment has its unique admin/monitoring needs and thus even if an out-of-the-box solution is provided by Microsoft at some point it may not have everything you need and certainly won’t be as flexible/extensible as a homegrown custom solution. In summary, though of course we’re all stretched for capacity/resources to take on new work, you might consider a Power BI admin dataset(s) to aid your administrative efforts.

Also, I’m sorry for the long delay since my last post. I’ve been very busy but I’ll try to start posting again more regularly.