Power BI “as a whole” is a bit of a black box. If you’re like me and used to using SQL Server & its components; SSRS, SSAS etc. you have access to installation directories, Event logs, trace logs, error logs, chocolate logs? You can see full instances & their contents in one go, whether that be databases, reports or cubes. It gives you the control over & responsibility for performance & maintenance. Working with Power BI, like most cloud based services is a whole different game. You need to relinquish the control to Microsoft & make do with limited monitoring & performance analysis. I’ve detailed in a previous article that you can get more information out of the On-Premises data gateway to better understand how Power BI is getting along with your own data sources, bridging the gap between your “kit” and Microsoft’s. In this article, I want to cover what we can get out of the cloud service itself using PowerShell. What we can see & what we can change. Unless you are already an Azure or Power BI admin you won’t really have an accurate picture of licensing or workspaces, report & dashboard usage in your organization. There are a few methods to get information out of Power BI using PowerShell and you don’t need to be an admin to use any of them. Interestingly, as some of the methods connect through Azure & due to the way Power BI is managed, you can get full visibility of things like licensing & workspaces tenant-wide which are handy! There are 3 main methods to connect to Power BI & Azure using PowerShell:

• MSOnline
• AzureAD
• Power BI REST API

MSOnline is the first set of modules to connect to Azure AD. These are now being deprecated. Microsoft considers these version 1 of the Azure PowerShell modules & wants everyone to move to the new AzureAD modules. There is no set date to deprecate MSOnline so you can still use it if you find it easier to work with but I’ll compare both later. The things you can do within Power BI using each method varies with the API method being the most powerful. You can achieve the same thing with both versions of the PowerShell modules, however. Here’s a detailed comparison of each method:

Getting Started

If you’ve never used any of these methods before there are a few prerequisites before you get started. Both modules are far simpler to get started with than the API.

1. The first step for all 3 methods is to get access to the PowerShell gallery using PowerShellGet. This will be available with PowerShell v5.0, built into Windows 10 & Windows Server 2016. If you don’t already have v5.0 I highly recommend it. Alternatively, the PowerShell Gallery can be used with v3.0 and up & can be downloaded here
2. Once you have the PowerShell Gallery installed, it’s as simple as an Install-Module command.
   • For MSOnline use Install-Module -Name MSOnline
   • For AzureAD use Install-Module -Name AzureAD
   • For the Power BI API we need an authentication module. Use Install-Module -Name Microsoft.ADAL.PowerShell
     That’s all the prerequisites we need to run the Azure AD commands. These additional steps are only required to use the Power BI API. I’ll also break these down into 2 methods of authenticating with Power BI. The first is interactive & prompts for credentials while the second performs an unattended authentication using stored credentials.
3. For the interactive method you will need to reference the ADAL Assemblies. Having worked with the Power BI API in C# I’ve found these assemblies can be stored in different locations depending on how they are installed. For these examples, I’ll refer to where the PowerShell module installs them. Here’s the code excerpt for referencing the assemblies. This will be part of the final scripts detailed later though.
   1 2 3 4 5 6 7 8 9

   #---------------------------------------- # Load Active Directory Authentication Library (ADAL) Assemblies $adal = "${env:ProgramFiles}\WindowsPowerShell\Modules\Microsoft.ADAL.PowerShell\1.12\Microsoft.IdentityModel.Clients.ActiveDirectory.dll" $adalforms = "${env:ProgramFiles}\WindowsPowerShell\Modules\Microsoft.ADAL.PowerShell\1.12\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"   [System.Reflection.Assembly]::LoadFrom($adal) [System.Reflection.Assembly]::LoadFrom($adalforms) #----------------------------------------

4. You will also need to setup a Power BI or Azure App. This application is what controls what we can do with the API and we reference it when connecting to Power BI. Go to register a new app or from the Azure Portal. Adam Saxton has produced great documentation on this process.
5. Name your app appropriately, this will be the name you see when authenticating interactively. The user will be asked if they want to allow <Your app name> access to <do all the options you select>
6. App Type is an important option where our choice differs depending on whether we’re using the interactive or unattended authentication methods.
   • Interactive – Choose Native App. This only generates a ClientID. You then need to set the Redirect URL to urn:ietf:wg:oauth:2.0:oob
   • Unattended – Choose Server-side Web App. This will generate a ClientID & Client Secret. As this method is intended for app use you will see an option for both a Redirect URL and a Home Page URL. You can set these to anything as we won’t need them as part of our connection.
    
7. Depending on your requirements, select the appropriate APIs you want the app to access
8. Complete the process by selecting Register App and make a note of the ClientID (& Client Secret if creating an unattended authentication app)

NOTE: You can always get the ClientIDs from the Azure Portal (detailed next) if you lose them but Client Secret will only be shown on this screen so make a note of it before closing the window! To get the ClientID from the Azure Portal. In the left-hand navigation pane, choose More Services, then select App Registrations under Security + Identity

Power BI Licensing

The first example I want to present is the simplest and is a good demonstration of the differences between the Azure PowerShell modules. As detailed earlier, this information cannot be found using the API. The first example is using the MSOnline module, the second uses AzureAD. Both return the same data but you can see they go about it a different way. This is likely due to the expanded functionality with v2 (AzureAD) meaning the module had to be restructured. Authentication As you can see in both code examples, I’m using a stored password in an encrypted text file. I find this a good method to execute PowerShell scripts that require authentication, unattended as only the user who encrypted the file can decrypt & use the credentials. Encryption: Decryption: MSOnline Module AzureAD Module Regardless of which script you execute, you’ll see the same output for each. The AzureAD script does return a summary when you authenticate that contains your Tenant ID. This is particularly useful as it is needed for the unattended API authentication & means you don’t have to trawl the Azure Portal for it. Tenant Licenses: User Power BI Licenses:

Other Module Examples

Retrieving workspaces & their members/admins is a little more of a gray area with the Azure modules. Azure treats Power BI Workspaces as “groups”. Azure AD Distribution groups are also “groups” meaning there’s no clear distinction with these commands. If you are lucky enough to not have any distribution groups in AAD or they’re copied from your on-premises AD then these examples will work well for you & you can now see all workspaces created by anyone in your tenant, even workspaces you do not have access to. Note the use of $_.SecurityEnabled -eq $false and $_.OnPremisesSecurityIdentifier -eq $null to exclude AAD security groups and those copied over from on-premises AD. All AD Groups All AAD Groups, their members & owners

The Power BI API

Interactive authentication Assuming you have your App set up & have a ClientID let’s take a look at getting some information out the Power BI API. Starting simple, we want to see all Workspaces (that my user has access to), all of their datasets & the last refresh information for each dataset. As soon as you execute this script you’ll be prompted to log in. You can see the name of the Power BI app we set, on this prompt Once you’ve authenticated you will see the API calls in the output window followed by the data you requested. My example datasets aren’t great as I have no data refreshes but you get the idea. Unattended Authentication For this method to work, you will need your ClientID & Client Secret generated by the Server-side Web App you created. You will also need your tenantID which you can get, as I described earlier, from the AzureAD auth output summary or from the Azure Portal:

• To find your Office 365 tenant ID in the Azure AD portal
  • Log in to Microsoft Azure as an administrator.
  • In the Microsoft Azure portal, click Azure Active Directory.
  • Under Manage, click Properties. The tenant ID is shown in the Directory ID box.

This script also uses the encrypted password file I mention earlier but requires an additional step to return the password to clear text which is then passed to the API. This isn’t an ideal method from a security standpoint but as the encrypted file can only be decrypted by the person who created it, anyone else running the script would not see the clear text password or be able to authenticate anyway. As mentioned in a comment section of this script, if you are not the user who created the app, your first authentication against it has to be interactive so you can grant the app permission as shown here: You can, however; also grant permissions from the Azure Portal if you have no way to interactively login with this app.

• Go to Azure Active Directory -> App Registrations -> find your app -> Required Permissions -> Grant Permissions.

Other API Examples

Here are some other examples of API calls. You will find a list of all its capabilities in the API Reference with some example calls & C# code too. I hope these excerpts help you get started with connecting to & querying Power BI. I hope to expand on these scripts & actually present & analyze the data in a future post. Please comment below or come find me on Twitter or my blog if you have any scripts I’ve not covered here or a better authentication method.

Downloads

• PowerBI_API_Datasets
• PowerBI_API_Datasets_Unattend
• PowerBI_Licenses_AzureAD
• PowerBI_Licenses_msOnline

References

• PowerShellGet reference
• PowerShell Gallery
• MSOnline module documentation
• AzureAD module documentation
• Power BI REST API reference
• Power BI App Registration
• Power BI App Documentation
• Azure Portal
• Craig Porteous’ blog