The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 = "admin@CRM738334.onmicrosoft.com"
$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
Solved! Go to Solution.
Hi @Anonymous,
Your code looks fine except for the way you pass the JSON value. You can use:
# User credential
$User = "admin@CRM738334.onmicrosoft.com"
$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.
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 = "admin@CRM738334.onmicrosoft.com"
$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 = "admin@CRM738334.onmicrosoft.com"
$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
Hi @Anonymous,
Your code looks fine except for the way you pass the JSON value. You can use:
# User credential
$User = "admin@CRM738334.onmicrosoft.com"
$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.
@Anonymous my pleasure
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
75 | |
36 | |
33 | |
23 | |
14 |