Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dgreen23
Helper III
Helper III

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.

Dgreen23_0-1622060407347.png

Any and all help to get this data represented correctly would be extremely helpful!

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Ideally your data source has things like LIMIT and OFFSET that you can use to paginate.  If not then do the pagination in Powershell.

View solution in original post

7 REPLIES 7
lbendlin
Super User
Super User

Ideally your data source has things like LIMIT and OFFSET that you can use to paginate.  If not then do the pagination in Powershell.

lbendlin
Super User
Super User

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.

@lbendlin ,

 

I'm new enough at this I'm not sure how a run-of-the-mill one would work. Would I just do multiple get statements and somehow select rows from the top down ignoring a portion of the returns?

lbendlin
Super User
Super User

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))

@lbendlin,

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-Json

 

Dgreen23_0-1622567675245.png

 

The 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?

 

lbendlin
Super User
Super User

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
}
]

@lbendlin,

 

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?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.