Forum Discussion
Push Data Set - Data Looks Wierd
I have created a push data set and have got it most of the way working and formatted. The issue I have is that the data posted into it looks like it's holding the header in front of the value, which we don't want.
Any and all help to get this data represented correctly would be extremely helpful!
Ideally your data source has things like LIMIT and OFFSET that you can use to paginate. If not then do the pagination in Powershell.
7 Replies
Ideally your data source has things like LIMIT and OFFSET that you can use to paginate. If not then do the pagination in Powershell.
Show the JSON that you are using to push a row into the dataset. Should look like this:
[ { "host" :"AAAAA555555", "temperature" :98.6, "humidity" :98.6, "timestamp" :"AAAAA555555", "utc" :"2021-05-30T22:43:38.298Z", "pressure" :98.6 } ]Currently, the data is being called by Invoke-Restmethod Get and then goes through the ConvertTo-Json process before being passed back as Invoke-Restmethod Post. The data looks like this currently and is missing the first and last bracket that yours has:
{
"JobOpDtl_Company01": "x",
"ResourceGroup_JCDept": x,
"ResourceTimeUsed1_ResourceGrpID": x,
"ResourceTimeUsed1_ResourceID": x,
"Calculated_RegionCode": "x",
"JobOpDtl_JobNum01": "x",
"JobHead_PartNum": "x",
"JobHead_RevisionNum": "x",
"Calculated_Op": x,
"JobOper_OpCode": "x",
"JobHead_ProdQty": "x",
"JobOper_QtyCompleted": "x",
"Calculated_piecesRemaining": "x",
"JobOper_EstSetHours": "x",
"JobOper_ActSetupHours": "x",
"JobOper_EstProdHours": "x",
"JobOper_ActProdHours": "x",
"JobHead_ReqDueDate": "x",
"JobOper_DueDate": "x",
"JobOper_StartDate": "x",
"JobHead_SchedCode": "x",
"Calculated_PriorOp": x,
"ResourceTimeUsed4_ResourceGrpID": x,
"Calculated_NextOp": x,
"ResourceTimeUsed5_ResourceGrpID": x,
"Customer_Name": "x",
"Vendor_Name": x,
"Calculated_DaysInDepartment": "x"
}How would we go about getting the open and close bracket into the data?
I wouldn't worry about the record identifier, it should work without that. What you seem to be missing is a UTC timestamp field. That is mandatory for push datasets.
Here is an example using Powershell (I use that to monitor gateway cluster member health)
#capture stats $mem = (Get-Counter '\Memory\Available MBytes').CounterSamples.CookedValue $proc = (Get-Counter '\Processor(_Total)\% Processor Time').CounterSamples.CookedValue $disk = (Get-Counter '\LogicalDisk(C:)\% free space').CounterSamples.CookedValue $date = (Get-Date).ToUniversalTime().ToString('yyyy-MM-ddTHH:mm:00.000Z') $payload = @{ "Host" = $env:computername "Timestamp" = $date "Available Memory in MB" = $mem "Processor Load %" = $proc "% Free on C:" = $disk } #enforce TLS1.2 [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 #push URL $endpoint = "https://api.powerbi.com/beta/<tenant id>/datasets/<dataset id>/rows?key=<push key>" Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))I'm not entirely sure a UTC is required in this case. I've got the following code working well with a single entry and data now seems to be coming in the way it should:
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Authorization", "Your Authorization")$response = Invoke-RestMethod 'Where the data is' -Method 'GET' -Headers $headers
Write-Output ""
Write-Output ""$headers2 = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers2.Add("Content-Type", "application/json")write-output $body
$response2 = Invoke-RestMethod 'Where the data needs to go' -Method 'POST' -Headers $headers2 -Body (ConvertTo-Json @($response.value))
$response2 | ConvertTo-JsonThe issue now is the dataset that I'm going to be pushing is roughly 24k lines worth. Power bi has a limitation of 10k lines per post and only 5 queued posts at a time. What do you think is the best way to divvy up my response from the Invoke get method would be?
Do your run of the mill pagination - for example 5K rows at a time. Ideally you could do that at the data source as part of the GET call.
But I get a feeling that you are abusing push datasets for something they were not designed to do.
