Forum Discussion
Create streaming dataset from powershell script
Hi All,
I am trying to build a streaming dataset using power shell script.
On powershell doc there is not this option:
Add-PowerBIDataset (MicrosoftPowerBIMgmt.Data) | Microsoft Docs
On the other hand looks like that it is possible from rest api:
Push Datasets - Datasets PostDataset - REST API (Power BI Power BI REST APIs) | Microsoft Docs
Not sure if a solution could be to call the rest api from powershell.
Is there a way to do it using the powershell cmdlet authentication?
Are there Any other sdk that allows it?
For instance:
# User credential
$User = "[email protected]"
$Pword = ConvertTo-SecureString –String 'bL3JZOyTDU' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword
# Connect to service
Connect-PowerBIServiceAccount -Credential $Credential
Hi Anonymous,
Your code looks fine except for the way you pass the JSON value. You can use:# User credential $User = "[email protected]" $Pword = ConvertTo-SecureString –String 'bL3JZOyTDU' –AsPlainText -Force $Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword # Connect to AAD $AzureProfile = Connect-AzAccount -Credential $Credential # Get an AccessToken to the Power BI service $AccessTokenDetails = Get-AzAccessToken -ResourceUrl 'https://analysis.windows.net/powerbi/api' -DefaultProfile $AzureProfile $Headers = @{ 'Authorization' = "Bearer $($AccessTokenDetails.Token)" } $Url = "https://api.powerbi.com/v1.0/myorg/datasets?defaultRetentionPolicy=basicFIFO" $Body = '{ "name": "SalesMarketing", "defaultMode": "Push", "tables": [ { "name": "Product", "columns": [ { "name": "ProductID", "dataType": "Int64" } ] } ] }' Invoke-RestMethod -Method 'Post' -Headers $Headers -Uri $Url -Body $body -ContentType 'application/json'
This worked for me.
5 Replies
- SpartaBI
Community Champion
Hi Anonymous,
You can call the Post Dataset API like any other API using PowerShell. It definitely works.
If you want to authenticate using a PowerShell SDK, you can use the module Az.Accounts
Building on your example:# User credential $User = "[email protected]" $Pword = ConvertTo-SecureString –String 'bL3JZOyTDU' –AsPlainText -Force $Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword # Connect to AAD $AzureProfile = Connect-AzAccount -Credential $Credential # Get an AccessToken to the Power BI service $AccessTokenDetails = Get-AzAccessToken -ResourceUrl 'https://analysis.windows.net/powerbi/api' -DefaultProfile $AzureProfile $Headers = @{ 'Authorization' = "Bearer $($AccessTokenDetails.Token)" } # Call an API Invoke-RestMethod -Headers $Headers # Complete the call...Hi SpartaBi,
using your suggested procedure and appending the rest request, as below, I am getting 403:
# User credential $User = "[email protected]" $Pword = ConvertTo-SecureString –String 'bL3JZOyTDU' –AsPlainText -Force $Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword # Connect to AAD $AzureProfile = Connect-AzAccount -Credential $Credential # Get an AccessToken to the Power BI service $AccessTokenDetails = Get-AzAccessToken -ResourceUrl 'https://analysis.windows.net/powerbi/api' -DefaultProfile $AzureProfile $Headers = @{ 'Authorization' = "Bearer $($AccessTokenDetails.Token)" } $Url = "https://api.powerbi.com/v1.0/myorg/datasets?defaultRetentionPolicy=basicFIFO" $Body = '{ "name": "SalesMarketing", "defaultMode": "Push", "tables": [ { "name": "Product", "columns": [ { "name": "ProductID", "dataType": "Int64" } ] } ] }' $Body = $Body | ConvertFrom-Json Invoke-RestMethod -Method 'Post' -Headers $Headers -Uri $Url -Body $body Invoke-RestMethod : The remote server returned an error: (403) Forbidden. At line:1 char:1 + Invoke-RestMethod -Method 'Post' -Headers $Headers -Uri $Url -Body $b ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebExc eption + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand- SpartaBI
Hi Anonymous,
Your code looks fine except for the way you pass the JSON value. You can use:# User credential $User = "[email protected]" $Pword = ConvertTo-SecureString –String 'bL3JZOyTDU' –AsPlainText -Force $Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword # Connect to AAD $AzureProfile = Connect-AzAccount -Credential $Credential # Get an AccessToken to the Power BI service $AccessTokenDetails = Get-AzAccessToken -ResourceUrl 'https://analysis.windows.net/powerbi/api' -DefaultProfile $AzureProfile $Headers = @{ 'Authorization' = "Bearer $($AccessTokenDetails.Token)" } $Url = "https://api.powerbi.com/v1.0/myorg/datasets?defaultRetentionPolicy=basicFIFO" $Body = '{ "name": "SalesMarketing", "defaultMode": "Push", "tables": [ { "name": "Product", "columns": [ { "name": "ProductID", "dataType": "Int64" } ] } ] }' Invoke-RestMethod -Method 'Post' -Headers $Headers -Uri $Url -Body $body -ContentType 'application/json'
This worked for me.