Power BI API empty JSON result set

[SteWi]

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

[SteWi · Accepted 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.

[lbendlin]

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.

[SteWi]

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