PowerBIPS + Service Principal

Last February Microsoft announced that its possible to authenticate with service principal.

This allows you to perform operations on the REST API without an actual user account (master account) or a Power BI PRO license assignment.

Learn here on how to create the Service Principal and configure it to use in your Power BI Tenant. In resume the steps are:

1. Register an App in Azure AD
2. Create a Security Group and add the App as a Member
3. Enable Service Principals to use the Power BI APIs and add the Security Group above
4. Add the App to the workspace as Admin

Regarding  PowerBIPS to use a Service Principal you should get the token using the following code:

$tenantId = "MyTenant.onmicrosoft.com"
$clientId = "Application Id"
$clientSecret = "Application Secret"

$authToken = Get-PBIAuthToken -clientId $clientId -clientSecret $clientSecret -tenantId $tenantId

With the token you can call any of the cmdlets like this:

$reports = Get-PBIReport -authToken $authToken -groupId "Workspace id"

Note: Service Principals are only supported over new Power BI Workspace model and doesn’t work with admin APIs

Automatically Export PBIX’s using #PowerBIPS

Ever wanted to export all your Power BI reports from a workspace and save a copy without manually going to each one and select the “Download report” option:

My team at DevScope just updated the PowerBIPS powershell module with a simple cmdlet to do just that:

Export-PBIReport –destinationFolder “<local folder>”

With an easy PowerShell script you can download all the reports from your PowerBI workspaces (ex: Backups?):

Install-Module PowerBIPS

# Get the Auth Token

$authToken = Get-PBIAuthToken 

# Define the Workspace you want to download the reports from - Optional, by default downloads from personal workspace

Set-PBIGroup -authToken $authToken -name "Demos - PBIFromTrenches" -Verbose

# Downloads the reports to a destination folder

Export-PBIReport -authToken $authToken -destinationFolder "C:\Temp\PBIReports" -Verbose

Use Power BI Desktop as an ETL Tool

Did you ever faced a scenario were you needed to load a collection of CSV/Text files into SQL Server tables?

What solution did you choose?

• TSQL BULK INSERT?
• SSIS Package (generated from SSMS Tasks->Import Data or manual)
• PowerShell “Import-CSV”

And what if the SQL Server destination tables must be typed (numeric, date, text columns,…) and the CSV file has formatting issues (ex: text columns without quotes, datetimes not in ISO format) and you need to transform the columns into the desired types?

A much quicker solution to transform CSV files into the desired shape is using a PowerBI Desktop query (or PowerQuery), for example in seconds I can:

• Load the CSV
• Replace a value from all the columns (in this case “NULL” from a real null)
• Auto detect the datatypes

Now to load these queries into a SQL Server database, it’s very easy thanks to DevScope powershell module “PowerBIETL” (also available at PowerShellGallery):

Install-Module PowerBIETL
Import-Module PowerBIETL

Export-PBIDesktopToSQL -pbiDesktopWindowName "*sample*" -sqlConnStr "Data Source=.\SQL2014; Initial Catalog=DestinationDB; Integrated Security=SSPI" -sqlSchema "stg" -verbose

The cmdlet “Export-PBIDesktopToSQL” will take care of:

1. Connects to the PBI Desktop and read the tables
2. Automatically create the tables on the SQL Database (if they do not exist)
   • Thanks to DevScope “SQLHelper” powershell module and “Invoke-SQLBulkCopy” cmdlet
3. Bulk copy the data from PBI Desktop into the SQL Table

The cmdlet has 4 parameters:

• -PBIDesktopWindowName (mandatory)
  • A wildcard to find the PowerBI Desktop window
• -Tables (optional, defaults to all the tables)
  • Array of tables to import
• -SQLConnStr (mandatory)
  • Connection to a SQL Server database
• -SQLSchema (optional, defaults to “dbo”)
  • The schema under the tables will be created

As a result all the tables from the PBI Desktop file will get copied into the SQL Server database:

Off course this will only work to those “one-time-only” or manual scenarios, but I assure you that is much quicker than using a SQL Integration Services package

Rui Romano

Power BI Desktop Trace Logs Analyser

In this post I will show you how to analyse Power BI Desktop diagnostic trace files in a more visual way than notepad

First you need to collect some diagnostics by enabling tracing on Power BI Desktop, go to: File –> Options –> Diagnostics –> Enable Tracing

If you click on “Open Traces folder”:

It will open the trace folder with all the trace logs:

PS – Trace log are only generated after you test your power bi report, do some refresh and interactions first to create the trace logs

Now to analyse these logs you could off course open them in notepad:

But is not very easy to read, so what better way to process and visualize this huge amount of text data??? Power BI off course!!!

So I created a Power BI Desktop to process and visualize the trace logs, that will allow you to quickly visualize things like:

• Errors
• Duration of queries
• Performance issues
• etc

Instructions of usage:

• Download and open the Power BI Desktop file
• “Edit Queries” and change the variable “VAR_LogFolder” to point to the trace logs folder:

• Refresh the Report

Rui Romano