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

Create a Power BI Push DataSet using Power BI Desktop w/ PowerBIPS.Tools

I have already covered how to create an API Enabled (Push DataSet) Power BI Dataset here and here.

The datasets are created using our open-source powershell module PowerBIPS and you need to write some code like the following:


$dataSetSchema = @{name = $datasetName
; tables = @(
    @{name = "Counters"
	; columns = @( 
		    @{ name = "ComputerName"; dataType = "String"; isHidden = "true"  }
		    , @{ name = "TimeStamp"; dataType = "DateTime"  }	
			, @{ name = "CounterSet"; dataType = "String"  }
		    , @{ name = "CounterName"; dataType = "String"  }
		    , @{ name = "CounterValue"; dataType = "Double"  }
		    )        
    }
	, 
	@{name = "Computers"
	; columns = @( 
		    @{ name = "ComputerName"; dataType = "String"  }
		    , @{ name = "Domain"; dataType = "string"  }	
			, @{ name = "Manufacturer"; dataType = "string"  }		        		        			
		    )
    ;measures = @(
        @{name="Average CPU"; expression="CALCULATE(AVERAGE('Counters'[CounterValue]) / 100, FILTER('Counters', 'Counters'[CounterSet] = ""Processor(_Total)"" && 'Counters'[CounterName] = ""% Processor Time""))"; formatString="0.00%"}
        )
    }
    )
; relationships = @(
    @{
        name = [guid]::NewGuid().ToString()
        ; fromTable = "Computers"
        ; fromColumn = "ComputerName"
        ; toTable = "Counters"
        ; toColumn = "ComputerName"
        ; crossFilteringBehavior = "oneDirection"      

    })
}

$dataSetSchema = New-PBIDataSet -authToken $authToken -dataSet $dataSetSchema -Verbose	

It’s very straightfoward, but if you need to create complex measures and relationships it’s easy to commit some mistakes if you dont have the help of Power BI Desktop.

To make this task easier we added a new cmdlet to our PowerBIPS.Tools module: Get-PBIDataSetFromPBIDesktop

You just need to create a Power BI model as usual, use “Enter Data” option to create your tables (w/ sample data) and then create the relationships, measures, custom columns, write your DAX w/ intellisense:

2018-07-16_1-07-29

2018-07-16_1-10-12

2018-07-16_1-10-59

And in the end use the Power BI Desktop model to create a Power BI Push DataSet like this:


# Get a PBIDataSet schema from PBIDesktop
Get-PBIDataSetFromPBIDesktop -pbiDesktopWindowName "*WindowName*" -datasetName "PushDataSet"

# Create the REST API dataset on powerbi.com
New-PBIDataSet -dataSet $dataSet -groupId "workspace id"

Let me know your opinion!