Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there,
The result of my API call returns empty JSON sets means I receive the expected amount of sets (rows) but none contains data.
This issue only appears for the ActivityEvent cmdlet when I issue the following commands/script (sorry, indenting does not work here):
## ########################################################################
## Power BI Logon
## ########################################################################
$CredentialUser = "MASKED"
$CredentialPassword = ConvertTo-SecureString "MASKED" -asplaintext -force
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $CredentialUser, $CredentialPassword
## Connect-PowerBIServiceAccount
Connect-PowerBIServiceAccount -Credential $Credential
## ########################################################################
## Set Constants
## ########################################################################
$SqlServer = "MASKED"
$SqlDatabase = "MASKED"
$Days = 1 ## Last X days for delta load
$DaysOffset = -1
$DateNow = Get-Date
## ########################################################################
## Empty SQL Server Staging Area Table
## ########################################################################
Invoke-Sqlcmd -ServerInstance $SqlServer -Database $SqlDatabase -Query "TRUNCATE TABLE [SA].[tb_powerbi_service_event_all]"
## ########################################################################
## Load Activity Events
## ########################################################################
$Date = $DateNow.Date.AddDays($DaysOffset)
For($i = 1; $i -le $Days; $i++)
{
$ActivityEvents = Get-PowerBIActivityEvent -StartDateTime $Date.ToString("yyyy-MM-ddT00:00:00.000") -EndDateTime $Date.ToString("yyyy-MM-ddT23:59:59.999") | ConvertFrom-Json
if ($ActivityEvents.Count -gt 0)
{
$ActivityEvents | foreach {
$SqlQuery = `
"
SET QUOTED_IDENTIFIER OFF
INSERT INTO [SA].[tb_powerbi_service_event_all]
(
[event_id]
, [event_name]
, [activity_id]
, [activity_name]
, [user_key]
, [user_id]
, [workspace_id]
, [workspace_name]
, [capacity_id]
, [capacity_name]
, [tenant_id]
, [flag_success]
, [created_datetime]
, [request_id]
, [load_datetime]
)
VALUES
(
""" + $_.Id + """
, """ + $_.Operation + """
, """ + $_.ActivityId + """
, """ + $_.Activity + """
, """ + $_.UserKey + """
, """ + $_.UserId + """
, """ + $_.WorkspaceId + """
, """ + $_.WorkspaceName + """
, """ + $_.CapacityId + """
, """ + $_.CapacityName + """
, """ + $_.OrganizationId + """
, """ + $_.IsSuccess + """
, """ + $_.CreationTime + """
, """ + $_.RequestId + """
, GETDATE()
)
SET QUOTED_IDENTIFIER ON
"
Invoke-Sqlcmd -ServerInstance $SqlServer -Database $SqlDatabase -Query $SqlQuery -ErrorAction SilentlyContinue
}
}
$Date = $Date.AddDays(-1)
}
## ########################################################################
## Logoff from Power BI
## ########################################################################
Disconnect-PowerBIServiceAccount
For others like Capacity, Workspace or specifically filtered ActivityEvents like ViewReport it works fine.
The account I use is Power BI Service Admin and of type windows service account in AD and AAD.
With my personal account it also works fine when I execute the script.
All my scripts are executed with Windows Task Scheduler while all scripts execute fine and use all the same structure only the above script returns empty result sets.
It's also interesting when I execute the above script manually in PowerShell (not via Run Job in Task Scheduler) I receive data as expected.
Someone has an idea how to move on with troubleshooting this?
For me it does not make sense that all other scripts using the same settings/template just using different CMDlets (they also need Power BI Service Admin account) work but only this one not and the manual execution works as well. Mmmh.
Regards,
SteWi
Solved! Go to Solution.
Oh my, I got it. This is the only script where I did not remove the following at the start of the script (and I did not paste it into my post). It turns out when the script contains import-module command the json sets are empty.
Import-Module MicrosoftPowerBIMgmt
Import-Module SqlServer
After removing the 2 lines it works now.
Oh my, I got it. This is the only script where I did not remove the following at the start of the script (and I did not paste it into my post). It turns out when the script contains import-module command the json sets are empty.
Import-Module MicrosoftPowerBIMgmt
Import-Module SqlServer
After removing the 2 lines it works now.
There are a couple of API calls that behave like this. Read up on Value.MetaData and manual exception handling.
As to the "it does not make sense" part. You are absolutely right.
Hey,
thanks for your answer. Do you have a code snippet based on my script what you mean with Value.MetaData or a helping link?
Something like this $ActivityEvents[1].Id.Metadata does not work. There is no MetaData function I could find during my search.
Thanks
User | Count |
---|---|
5 | |
4 | |
3 | |
2 | |
2 |
User | Count |
---|---|
8 | |
6 | |
4 | |
4 | |
4 |