Automatic report upload through API erratic behaviour - looking for guidelines

[h4tt3n]

I have been working professionally with measurement data vissulatisation in PBI for over a year, and one of my job tasks is to manage an ever-growing number of reports for our company. For this reason we have moved from manually managing reports through the PBI service GUI to automatic management via a number of PowerShell scripts. This includes automatic upload of tens of reports at a time.

In pseudocode, this  is what the script attempts to do:

for each customer account in our DB:

Check if report exists

If not:

upload empty report template

Set parameters that pair report with customer account

Set refresh schedule

Refresh dataset

Unfortunately, PBI service responds in an erratic and unpredictable way when attempting automatic report upload. It appears that often the parameters does not get set, making the report unable to retrieve customer data. I have added a 10 sec wait command between every requests to give the PBI service API and DB some breathing space, but this doesn't seem to improve behaviour.

I am looking for guidelines on how to manage multiple report upload through API calls without running into these challenges.

Cheers, Mike

[V-lianl-msft]

How do you set parameters and retrieve data?

Could you provide more information to let me know your scenario

[h4tt3n]

I retrieve data from Azure T-SQL DB using a SQL query. I set parameters with an API call, using the below function. It works most of the time, but fails sometimes for non-apparetn reasons. I have improved the success-rate by increasing the wait time after the report is uploaded, before updating parameters. 

Also, I have noticed a peculiar tencency; older accounts (4-5 years) tend to fail more often than accounts that have recently been created ( < one year).

function UpdateParameter
{
    # This operation is only supported for the dataset owner. Call TakeOverDataset()
    
    [CmdLetBinding()]
    param 
    ( 
        [Parameter(Mandatory=$true, Position=0)] [string] $datasetId, 
        [Parameter(Mandatory=$true, Position=1)] [string] $parameterName, 
        [Parameter(Mandatory=$true, Position=2)] [string] $parameterNewValue 
    )
    
    $url = "https://api.powerbi.com/v1.0/myorg/datasets/$datasetId/Default.UpdateParameters"

    $body = '{ "updateDetails": [ { "name": "'+$parameterName+'", "newValue": "'+$parameterNewValue+'" } ] }'

    Invoke-PowerBIRestMethod -Url $url -Body $body -Method POST

}

Cheers, Mike