Create a Real-Time IT Dashboard with PowerBIPS

Last week we published on GitHub a powershell module for the new PowerBI developer REST API’s named “PowerBIPS”, download it here:

https://github.com/DevScope/powerbi-powershell-modules/

In this post I will demonstrate the ease of use of this module and show you step-by-step how to build a powershell script that upload server stats to PowerBI allowing the users to build a real-time IT dashboard like this:

image

The script is divided in the following steps (full script):

  1. Import the PowerBIPS module
  2. Get the Authentication Token
  3. Create a PowerBI DataSet
  4. Upload data to PowerBI

Import the PowerBIPS module

The script starts by importing the powershell module:

# Module need to be installed on %USERPROFILE\WindowsPowershell\Modules\
Import-Module PowerBIPS -Force

Or by pointing directly to the .psm1 file:

Import-Module <path to module>\PowerBIPS.psm1 –Force

After this all the PowerBIPS cmdlets are available for use in the PowerShell console.

Get the Authentication Token

Next we need to get the authentication token that is needed to communicate with the API, this token identifies your PowerBI tenant and grants access to it:

$authToken = Get-PBIAuthToken -clientId "<your client id>"

This cmdlet require you to provide the Client Id of the Native Application that you need to create in the windows azure active directory of yout tenant:

PS -Follow this guide to create an Azure Client App (thanks to mattmcnabb)

image

When you execute this cmdlet the following popup is shown asking for the credentials to connect to the PowerBI account:

image

It’s also possible to set in the cmdlet the username and password which is the ideal mode for automation jobs.

Create a PowerBI DataSet

Before creating the DataSet we will test if a DataSet with name “IT Server Monitor” exists in PowerBI, if not create a new one:


$dataSetMetadata = Get-PBIDataSet -authToken $authToken -dataSetName "IT Server Monitor"

if (-not $dataSetMetadata)
{
  # If cannot find the DataSet create a new one with this schema	
	
  $dataSetSchema = @{
	name = "IT Server Monitor"
	; tables = @(
		@{name = "Processes"
		; columns = @( 
			@{ name = "ComputerName"; dataType = "String"  }					
			, @{ name = "Date"; dataType = "DateTime"  }
			, @{ name = "Hour"; dataType = "String"  }
			, @{ name = "Id"; dataType = "String"  }
			, @{ name = "ProcessName"; dataType = "String"  }
			, @{ name = "CPU"; dataType = "Double"  }
			, @{ name = "Memory"; dataType = "Double"  }
			, @{ name = "Threads"; dataType = "Int64"  }					
			) 
		})
  }	

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

The $dataSetSchema variable is a hierarchical hashtable that define the schema of the dataset (a System.Data.DataSet is also supported)

The “New-PBIDataSet” cmdlet in case of success returns the PowerBI dataset metadata with the internal id of the dataset.

Upload data to PowerBI

In this script we will start by deleting all the data in the “Processes” table (optional)

Clear-PBITableRows -authToken $authToken -dataSetId $dataSetMetadata.Id -tableName "Processes"

And each 10 seconds cycle the servers and for each one:

  1. Get all the running processes and related stats: CPU, Memory,…
  2. Upload the collected data to PowerBI
$computers = @("DSRomano")

while($true)
{	
    $computers |% {
		
	$computerName = $_
		
	$timeStamp = [datetime]::Now
		
	# Dates sent as string because of an issue with ConvertTo-Json (alternative is to convert each object to a hashtable)
		
	Get-Process -ComputerName $computerName | Select  @{Name = "ComputerName"; Expression = {$computerName}} `
		, @{Name="Date"; Expression = {$timeStamp.Date.ToString("yyyy-MM-dd")}} `
		, @{Name="Hour"; Expression = {$timeStamp.ToString("HH:mm:ss")}} `
		, Id, ProcessName, CPU, @{Name='Memory';Expression={($_.WorkingSet/1MB)}}, @{Name='Threads';Expression={($_.Threads.Count)}} `
	| Add-PBITableRows -authToken $authToken -dataSetId $dataSetMetadata.Id -tableName "Processes" -batchSize -1 -Verbose
		
     }
	
     sleep -Seconds 10
}

After you run the script the following DataSet should appear on your PowerBI account:

image  image

And you can start searching, pining, slicing & dicing with this real time data:

image

image

image

The full PowerShell script can be downloaded here.

Advertisements

48 thoughts on “Create a Real-Time IT Dashboard with PowerBIPS

  1. Hi, this is a great blog post. I’ve been able to use the Invoke-SqlCmd command to pull data from a SQL database and refresh a PowerBI dataset. One issue I’m having (and I don’t kno wif you can help) is the Get-PBIAuthToken command, I’d like to automate the process but when I use -username and -password I get an error “AADSTS65001: No permission to access user information is configured for clientid”

    I’m wondering if you’ve come across this?

    Many thanks

    • Just a quick update on this. I got -username and -password working automatically using Get-PBIAuthToken. I used a user that I created in my Azure domain where the app I created to get a clientid resided. Previously I had been using a different domain user when pushing data.

      • Hi Andy,

        Thank you! 😉

        I was just replying to ask you if the user that you are using exists in the ActiveDirectory of the client application created in Azure AD.

        I need to confirm this,but I think to authenticate with username/password the username provided must be in the same domain of the Application.

  2. Mourad says:

    Hello Rui, I tried to test this sample.
    I got this error trying to execute ITRealTimeMonitor.ps1:
    VERBOSE: Getting DataSets
    The ‘Content-Type’ header must be modified using the appropriate property or method.
    Parameter name: name
    At D:\RealTimePowerBI\PowerBIPS\PowerBIPS.psm1:line:172 char:13
    + $result = I <<<< nvoke-RestMethod -Uri $pbiAPIDataSetsUrl -Headers $headers -Method Get
    Do you have any idea how to fix it ?
    Thanks

  3. eric vogelpohl says:

    Thanks for creating. I can successfully run as-is, with my PowerBI uid and pw, your default ClientID. (My company appears not to have Azure AD). The script runs successfully for 60 minutes, then I get error that my token has expired in the PS window – stops. Is this due to the fact that I’m using an incorrect ClientID? Sorry, newbie. -Eric.

    • Hi Eric, thank you 😉 I believe the error you are getting is related to the expiration of the authtoken. I never experience this error because I never let the script run for more than a few minutes, so please try to move the “Get-PBIAuthToken” to inside the while statement and it should solve your issue.
      Thanks, Rui Romano

      • eric vogelpohl says:

        Yes, the more research I’ve done suggests the token, without being Renewed (a formal process from what I can tell) or gotten-new, will expire in 3600 seconds – which is exactly what’s happening. I did add a Get-PBIAuthToken inside the continuous loop. I echoes the Token itself (500~ characters) to the screen. However, it doesn’t seem to refresh or renew it. The script still stops in 1 hour, same error.

        I think (and believe you me, I’m no programmer), the PBI Module needs a parameter added to the Get-PBIAuthToken function to call a renew or get-new method of the AD DLL.

        My plan is to modify your script slightly and call the SQL Server Counters on our production BI SQL Servers – 1) to monitor use and 2) to display the power of real-time data on a TV in our office area.

  4. eric vogelpohl says:

    Added. So far, so good. It’s been running past the 1 hour point. Thanks!

    Seems I watched a Channel 9 episode in which someone noted that the dataset creation request can contain a header telling PowerBI to create an initial size or record-count DB, at which point it performs a record level FIFO, rolling out an old record for each new one added. That would remove the problem that this script, which I plan to run in perpetuity, will create a database that’s too large. I’ll play around and see if I can hack that to work. If so, I’ll share.

    • Great! The basicFIFO is already supported by PowerBIPS, you just need to set the -retentionPolicy on the new-pbidataset (checkout the github page for more details). Thanks

  5. abhishek says:

    it just lets me create a report on power BI but not a dashboard because it says that data source auto refresh is not supported. any pointers?

  6. Francois de Beer says:

    Hi Rui,
    Thanks for this, it works GREAT!
    I am trying to automate the process, but I’m stuck at authenticating…
    When I use the Get-PBIAuthToken, I get the following error:

    Get-PBIAuthToken : A parameter cannot be found that matches parameter name ‘username’.
    At D:\xxxxx\xxxxxx\Samples\PowerBIPS.Sample.CSVUpload.ps1:19 char:69
    + Get-PBIAuthToken -clientId “xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx” -username “N …
    + ~~~~~~~~~
    + CategoryInfo : InvalidArgument: (:) [Get-PBIAuthToken], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : NamedParameterNotFound,Get-PBIAuthToken

    Any help would be greatly appreciated!
    Keep up the good work!

    Kind regards,
    Francois

  7. Wayne Wang says:

    Hi Rui,
    Thank you for the excellent work. I am trying to modify the sample script “PowerBIPS.Sample.CSVUpload.ps1” and there are two problems:
    First, I can use function “Clear-PBITableRows” to assign and clear data rows of any existing table/dataset, however, how can I add rows into it using CSV file?
    Second, I am trying to use fixed username and password instead of manually entering username and password, but I have no idea what clientID I should enter here?

    Any help would be appreciated.

    Regards,
    Wayne Wang

      • Wayne Wang says:

        Hi Rui,
        Thank you so much, it works. However, sometimes I encounter errors like below:
        ———————————–
        Invoke-RestMethod : 遠端伺服器傳回一個錯誤: (429)。
        位於 C:\Users\Administrator\Desktop\powerbi-powershell-modules-master\Modules\PowerBIPS\PowerBIPS.psm1:767 字元:12
        + … $result = Invoke-RestMethod -Uri $url -Headers $headers -Method Pos …
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod],WebException
        + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
        ———————
        I have tried searching answers to fix the error but didn’t get enough information to do so. I think the error emerges probably because of attempting adding too many data rows at a time, am I right? And would you please offer suggestions to resolve this issue?

        Thank you

        Regards,
        Wenkai Wang

  8. Hi Wayne, about the error you could be reaching a API limit. try putting a powershel try-catch around the code to see the exact error.

    The error shown by the console sometimes dont give all the detail, you can also use fiddler to inspect the response of the http request to PowerBI.

    • Wayne Wang says:

      Hi Rui,
      I tried using try-catch around the “Out-PowerBI” command, the Write-Host command showed “System.Net.WebException” in exception type. After searching for answers using “System.Net.WebException” “power bi” as keywords, I made a preliminary conclusion that this error is caused by adding too many data rows to Power BI (which exceeds the limit of Power BI can receive at a time). I am now trying to reduce the table size as splitting it into several small tables according to different management purposes. My first divided table(consisted of 5,663 data rows) has been successfully uploaded to Power BI with batchsize of 5,000. Thank you so much for all your helps, and if you don’t mind, I’ll keep sharing anything I could have learned on this.

      Regards,
      Wenkai Wang

      • Hi Wayne, glad that you solved it.

        Just one tip, out-powerbi has a parameter “-batchSize” (not sure about the name, confirm in the docs please) that allow you to control the qty of rows sent.

        Example you execute out-powerbi with 5000 rows an a batchsize of 1000 out powerbi will make 5 requests instead of one.

        Please test that and let me know if ot worked.

        Thanks

  9. Hi,
    When I use this approach
    $authToken = Get-PBIAuthToken -clientId “”

    it works. But when I tried this approach
    $authTokenWithUsernameAndPassword = Get-PBIAuthToken -ClientId “” -Credential (new-object System.Management.Automation.PSCredential(“”,(ConvertTo-SecureString -String “” -AsPlainText -Force)))

    i am getting this error:

    {“error”:”invalid_grant”,”error_description”:”AADSTS70002: Error validating credentials. AADSTS50126: Invalid username or password\r\nTrace ID: 2b2d4166-d5b5-494f-af2b-c747d6e90e8b\r\nCorrelation ID: 8a9199b0-af50-44b0-9164-2190c9f123fb\r\nTimestamp: 2016-02-15 20:20:36Z”,”error_codes”:[70002,50126],”timestamp”:”2016-02-15 20:20:36Z”,”trace_id”:”2b2d4166-d5b5-494f-af2b-c747d6e90e8b”,”correlation_id”:”8a9199b0-af50-44b0-9164-2190c9f123fb”}

  10. Hi Chris, strange… Just tried with my account using username+password and worked fine…

    Also noticed that I may have a bug in the module because I’m caching the AAD AuthenticationContext and if you login with the default clientId and then change it to yours it will keep using the first one (I will look into that soon).

    Try this please:

    – Make sure your App (clientId) is in the same Azure AD of the user you are logging in (I never get this working if the user is not in the same Azure AD, but I’m not sure if it is a config issue or by design)
    – Open a fresh PowerShell window
    – Run this “Get-PBIAuthToken -ClientId “” -Credential (new-object System.Management.Automation.PSCredential(“”,(ConvertTo-SecureString -String “” -AsPlainText -Force)))”

    Thanks

  11. Doing this works. It asks me to login and grant permission.

    $authToken = Get-PBIAuthToken -clientId “”
    $dataSets = Get-PBIDataSet -authToken $authToken
    Write-Host $dataSets

    Doing this, after restarting Powershell, using same credential gives error

    $authToken = Get-PBIAuthToken -ClientId “” -Credential (new-object System.Management.Automation.PSCredential(“”,(ConvertTo-SecureString -String “” -AsPlainText -Force)))

    AADSTS70002: Error validating credentials. AADSTS50126: Invalid username or password

    Is there some setting on my AD app that I’m missing?

  12. Just ensure that you have the “SignIn and read user profile” permission selected on the Azure Active Directory Application.

    Your Azure App is a Native App right?

    It could be something with the powershell PSCredentialObject, try to change the PowerBIPS cmdlet “Get-PBIAuthToken” and replace “$Credential.UserName, $Credential.Password” with yout hardcoded credentials and let me know if it worked.

    Regards

  13. Running out of ideas here, sorry… 😦

    But username&password works with native apps, every time I used username&password auth was with native apps.

    Please try my sugestion of changing the Get-PBIAuthToken, I suspect it could be an issue with PSCredential object.

    Regards

  14. Hi Rui,

    I tested out the PowerBIPS with a small portion of incident request (SCSM) i wanted to create a dataset from and i was able to import it easily. Now i decided to take it one step further with by gathering more data and it seems to fail when comes time to populate the dataset with Data.

    Working on Checking DataSet
    Working on Creating DataSet
    Writing DataSet Values [0]
    VERBOSE: Getting DataSets
    VERBOSE: GET https://api.powerbi.com/v1.0/myorg/datasets with 0-byte payload
    VERBOSE: received -1-byte response of content type application/json; odata.metadata=minimal; odata.streaming=true
    VERBOSE: Found 1 datasets.
    VERBOSE: Searching for the dataset ‘SCSMData_ALL’
    VERBOSE: Found dataset with name: ‘SCSMData_ALL’
    VERBOSE: Adding a batch of ‘223’ rows into ‘RequestData’ table of dataset ‘1610e7d1-8edc-42a2-8941-ae2db38806c5’
    VERBOSE: POST https://api.powerbi.com/v1.0/myorg/datasets/1610e7d1-8edc-42a2-8941-ae2db38806c5/tables/RequestData/rows with -1-byte payload
    Invoke-RestMethod : The remote server returned an error: (404) Not Found.
    At C:\Program Files\WindowsPowerShell\Modules\PowerBIPS\PowerBIPS.psm1:815 char:12
    + $result = Invoke-RestMethod -Uri $url -Headers $headers -Method Post -Body $bod …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

    Let me know if i should open a issue on Github

    Not every Colomn in the dataset has data (export-CSV omits alot of my columns)

  15. Thanks for the reply Rui,

    how would can your link help troubleshoot? or are you just saying that this is a alternative method to get my data into PBI web? I plan on trying to automate this script to run everyday (either via Task scheduler or orchestrator)

    How do i use fiddler to looks like a paying program

  16. Ah ic, but the problem that im having with CSV is that columns are missing …i read that it is because PowerShell bases the output of the first entry in the array. Because my first Object does not contain the property X, it will not be displayed by default. It is however stored in the array. Im still trying to figure out a way around that issue in order to try the second method

  17. Biggus Dickus says:

    I am looking for an automated mechanism to refresh a Power BI Desktop PBIX file from the command-line. I want to refresh all the data connections inside any pbix file I point to and save the result. This way I would not need to manually run this process. There seem to be some issues with different types of connections in PBI that seem to work fine on PBID … Don’t understand, don;t care why but this would be a great thing to find out.

    Thanx
    Dick Moffat

  18. Gianni Bergamo says:

    Hi Rui
    when I try to execute my app, It creates a dataset (or finds it if exists) but can’t add rows because of a 404 error. Has anything changed recently in REST API?

    Invoke-RestMethod : Errore del server remoto: (404) Non trovato.
    In C:\Users\Gianni\Documents\WindowsPowerShell\Modules\PowerBIPS\1.2.0.9\PowerBIPS.psm1:815 car:12
    + … $result = Invoke-RestMethod -Uri $url -Headers $headers -Method Pos …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebExc
    eption
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

      • Gianni Bergamo says:

        The code is similar to yours. The only difference is that I select data from SQL server. I think that the problem is that I passed a wrong object to Add-PBITableRows. My data are on a System.Data.DataSet but Add-PBITableRows needs rows, so I have to manage this.

  19. Rui Romano says:

    Hi, DataSet should work ok if I remember right you only need to call Add-PBITableRows with ds.Tables[]. As an alternative check the cmdlet “Out-PowerBI” than one supports DataSet directly and does all the job for you.

    • Gianni Bergamo says:

      Thank you Rui. It works with ds.Tables[0] (I have only one table). I tried Out-PowerBI and it worked but I had some problem passing an existing dataset name. But Add-PBITableRows works as I expected!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s