Power BI enables organizations to adopt a data-driven culture where every person can get value from data. Front and center are business users creating reports and dashboards, sharing insights, making decisions, and taking action. But organizations must also be able to govern this world of self-service BI effectively. Among other things, IT administrators must handle licensing and capacity provisioning, and take care of increasing security, governance, and regulatory requirements. The General Data Protection Regulation (GDPR) in Europe is just one example of emerging regulations and compliance requirements impacting organizations' analytics deployments. Power BI service administrators need visibility into every aspect of their BI environments more than ever.
Today, we are releasing new Power BI Admin APIs, along with a .NET SDK and a PowerShell module, that enable administrators to discover artifacts in their Power BI tenant, as well as take administrative actions. These new capabilities unlock new possibilities for administrators that go beyond what’s currently available in the Power BI Admin Portal. Over time, we expect to incorporate some of these capabilities into the Power BI Admin Portal as well, to enable common scenarios without having to use PowerShell.
Read on for an example of how the Power BI Management module for Windows PowerShell can help Power BI admins in a realistic scenario, or jump to the resources at the bottom for details on documentation and downloads. Also stay tuned for further articles covering the new Power BI APIs and cmdlets in greater detail.
Walkthrough: Enumerating datasets and reports in a Power BI tenant
Imagine your Sales database is under heavy load because so many people are building Power BI reports on it. It is great that the data is useful, but maybe some report optimizations could help to lower the query load. As a Power BI administrator, you want to identify the reports and datasets and contact the owners to start the process, but how do you know who they are when many people have access to the Sales database and could have built the reports?
In order to answer this question, you must identify the datasets and reports that use the Sales database. The task is to enumerate all these resources across all workspaces at the Power BI tenant level. As a follow-up, you could then contact the dataset owners to discuss possible optimization opportunities.
Step 1: Installing the Power BI management cmdlets and logging in to Power BI
The Power BI management cmdlets are available as a module from the PowerShell Gallery. You can install them by using the command Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser. Among other things, this module includes a Login-PowerBI cmdlet to start a session with the Power BI service. Remember to log in with an account that has Power BI service administrator rights.
Step 2: Enumerating all datasets that use a SQL Server database
By using the Get-PowerBIDataset and Get-PowerBIDatasource cmdlets, you can enumerate all those datasets across your Power BI tenant that use a data source referencing a database name Sales hosted on a Server called SQLDB01. Note that you must specify Organization as the scope of the operation so that the cmdlets include all datasets and data sources. Only Power BI admins can use this scope. Power BI users without service admins rights are confined to Individual scope, which includes only those resources that the user has explicit permissions to access.
$datasetIds = Get-PowerBIDataset -Scope Organization -ErrorAction SilentlyContinue | Foreach {$dsId = $_.Id; Get-PowerBIDatasource -DatasetId $dsId -Scope Organization -ErrorAction SilentlyContinue | Where-Object {$_.DatasourceType -eq 'Sql' -and ($_.ConnectionDetails.Server -like 'sqldb01' -and $_.ConnectionDetails.Database -like 'sales')} | Foreach { $dsId }}
Note also that this command can take a long time to run if you are dealing with a large number of datasets in your Power BI tenant. Assuming 2-3 seconds per dataset, it might take 30-50 minutes to process 1,000 datasets. If you want to count them upfront, you can use the command $(Get-PowerBIDataset -Scope Organization | measure).Count.
Step 3: Enumerating all reports that use the identified datasets
With the dataset Ids in hand, you can now enumerate all reports that use the datasets by using the Get-PowerBIReport cmdlet. Note that you must again specify Organization as the scope of operation because the operation is supposed to be performed at the Power BI tenant level. The OData filter expression for the Get-PowerBIReport cmdlet retrieves only those reports that use the relevant datasets.
$reports = $datasetIds | Foreach { Get-PowerBIReport -Filter "datasetId eq '$_'" -Scope Organization }
Step 4: Contacting the creators of the datasets
You can also determine the creators of the datasets to discuss performance optimizations with them. The dataset objects provide this information through the ConfiguredBy property. The PowerShell session still has the relevant IDs available in the $datasetIds variable, which can be passed to the -Id parameter of the Get-PowerBIDataset cmdlet, as the following snippet illustrates. As it turns out, there is four Power BI users in this imaginary scenario creating datasets against the Sales database. It should be no problem to help these users fine tune their datasets to lower the workload on the SQL Server instance.
$datasetIds | Foreach { Get-PowerBIDataset -Id $_ -Scope Organization } | foreach { $_.ConfiguredBy }
Wrapping things up
The APIs and cmdlets for Power BI administration open entirely new and exciting capabilities. The initial release focuses on the most important needs and covers workspaces, dashboards, reports, datasets, and imports. Subsequent iterations will deliver even deeper and richer capabilities to make Power BI admins more productive in a broader scope. Let us know of any additional requirements you might have through the usual Power BI community channels or as comments to this article below. Stay tuned for more good news coming soon. And thank you very much for supporting Power BI as an admin in your organization!
APIs and cmdlets resources
Use the following table to locate resources that can help you get started with the APIs and cmdlets for Power BI administration.
- Power BI Management module in the PowerShell Gallery https://www.powershellgallery.com/packages/MicrosoftPowerBIMgmt
- Power BI cmdlets documentation https://docs.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps
- Open Source repository for Power BI cmdlets on GitHub https://github.com/Microsoft/powerbi-powershell
- Power BI SDK for .NET https://github.com/Microsoft/PowerBI-CSharp
- Power BI REST API Reference https://docs.microsoft.com/en-us/rest/api/power-bi/
- Quick reference for PowerShell cmdlets, REST APIs, and SDK for Power BI administration https://docs.microsoft.com/en-us/power-bi/service-admin-reference
- Power BI Community site https://community.powerbi.com/