peshkova - Fotolia

Tip

The Power BI-PowerShell cmdlet cheat sheet

DBAs can manage Power BI data sets, workspaces and reports with PowerShell. Using the two tools together makes for a more efficient and effective workflow.

Power BI management features are available in the tool's Admin portal, or through Office 365, Azure Active Directory, administrative APIs and SDKs, or PowerShell cmdlets. SQL Server and Power BI administrators may prefer the Power BI-PowerShell combination to perform scripted operations, either for automation or to execute a large number of tasks.

To perform PowerShell management tasks, you'll need a computer that runs PowerShell version 3.0 and has version 4.6.1 of the .NET framework installed. In addition, most of the Power BI cmdlets require the use of a PowerShell session in which administrative permissions are allowed; this is also referred to as an elevated PowerShell session. An elevated session can be launched by right-clicking on the PowerShell icon, and then choosing the "More" and "Run as Administrator" commands from the shortcut menu.

Acquire the Power BI PowerShell module

Native PowerShell's library of cmdlets is only sufficient for managing the Windows operating system. It doesn't include support for external products like Power BI, meaning that users will need to download Microsoft's Power BI management module for PowerShell before they can perform any Power BI management tasks from the command line.

In PowerShell, a module is a collection of cmdlets related to a specific product or task. As such, the Power BI management module contains all of the cmdlets necessary to manage Power BI from PowerShell. You can download the module from Microsoft's PowerShell website.

Install the module

If you're new to working with PowerShell modules, it's important to know that installing one isn't like installing an application on your computer. When you install a new application, it remains available for your use until you uninstall it; in the case of PowerShell modules, a new module remains on your computer as you would expect, but the module only remains installed for the duration of the session. The next time you need to use the module, you'll have to reinstall. The command used to do so is:

Install-Module -Name MicrosoftPowerBIMgmt

Once the module is installed, you can see a list of all the cmdlets that are included by entering the following command:

Get-Command -Module MicrosoftPowerBIMgmr

Connect to Power BI with PowerShell

Once the required module is installed, you'll have to connect PowerShell to your Power BI service account. The exact method used to do this varies depending on whether you're using a set of credentials or a certificate, and whether you need to specify an environment. If you want to log in using a set of credentials, you can do so with this command:

Connect-PowerBIServiceAccount -ServicePrincipal -Credential (Get-Credential)

If you want to log in by specifying a certificate thumbprint, you would use this command instead:

Connect-PowerBIServiceAccount -ServicePrincipal -CertificateThumbprint <certificate thumb print number>

You can find the full syntax of the Connect-PowerBIServiceAccount cmdlet in Microsoft's documentation.

Once you've successfully made the Power BI-PowerShell connection, there are three main things that you can manage from PowerShell -- data sets, workspaces and reports.

Cmdlets to manage data sets

You can use PowerShell to create and manage Power BI data sets. For example, if you wanted to create a new data set, you could do so by using the New-PowerBIDataset cmdlet. If you wanted to see a list of the existing data sets, you could use the Get-PowerBIDataset cmdlet. PowerShell also includes functionality to create tables and columns, and to manage sources, as shown in the table below.

Data-related PowerShell cmdlets

Add-PowerBIDataset

Creates a new data set in Power BI

Get-PowerBIDataset

Returns a list of Power BI data sets

Get-PowerBIDatasource

Returns a list of Power BI data sources

Get-PowerBITable

Returns a list of Power BI tables

New-PowerBIColumn

Creates a new Power BI column object

New-PowerBIDataset

Creates a new Power BI data set object

New-PowerBITable

Creates a new Power BI table object

Manage workspaces

You can get PowerShell to display a list of all of the Power BI workspaces with the Get-PowerBIWorkspace cmdlet. Although PowerShell doesn't include a cmdlet to create new workspaces, you can use PowerShell to add or remove user permissions from a workspace, or to update a workspace.

Workspace-related PowerShell cmdlets

Add-PowerBIWorkspaceUser

Allows a specified user to access a Power BI workspace

Get-PowerBIWorkspace

Returns a list of Power BI workspaces

Remove-PowerBIWorkspaceUser

Removes workspace permissions from specified users

Restore-PowerBIWorkspace

Restores a deleted Power BI workspace

Set-PowerBIWorkspace

Updates a Power BI workspace

Manage reports

The main report-related tasks that most people will probably want to perform through PowerShell are to list and then export reports. These tasks can be accomplished with the Get-PowerBIReport and Export-PowerBIReport cmdlets. Cmdlets also are available for listing PowerBI imports, dashboards and tiles, which are data snapshots that can be pinned to a dashboard.

Report-related PowerShell cmdlets

Export-PowerBIReport

Exports Power BI reports to the .pbix file format

Get-PowerBIDashboard

Returns a list of Power BI dashboards

Get-PowerBIImport

Returns a list of Power BI imports

Get-PowerBIReport

Returns a list of Power BI reports

Get-PowerBITile

Returns a list of Power BI tiles to use in dashboards

Users should experiment with the available Power BI-PowerShell cmdlets to figure out which combinations work best for their business and their BI application needs.

Dig Deeper on Database management

Business Analytics
SearchAWS
Content Management
SearchOracle
SearchSAP
Close