Live Stream Power BI

[AliSafa]

Hi Folks,

is it possible to create a Live Streaming Dataset reading Data from a SQL table? 
I could not find any source which explains it well. 

I tried to run a PowerShell code to force it to use an SQL server. but the problem is the power shell code does not  execute and it gives a bunch of errors complaining KeyBlocker:

+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId: WebCmdletWebResponseException, Microsoft.PowerShell.Commands.InvokeRestMethodCommand
Invoke-RestMethod: {"error":{"message": "The request was blocked by KeyBlocker "}}
At line:57 char:1
+ Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Jason ...

[AliSafa]

thanks Ibendlin 
so i should convert my sql code which is (Select * from My table) to Json and Run the result inside the windows powershell ISE.
i will search a tool to convert it to json. 
thanks a lot

[lbendlin]

One thing to keep in mind:  One of the fields in your table should probably be a timestamp, and it should be in UTC and in ISO8601 format.

[lbendlin]

You can certainly do that - run a query against the SQL table and push the result into the streaming dataset.  It sounds a little weird though - how real time is your SQL data source?

[AliSafa]

its each 15 minutes. you said run a query against the SQL table and push the result into the streaming dataset.

the question is how to push the rsult to streaming Dataset?

thx

[lbendlin]

Real-time streaming in Power BI - Power BI | Microsoft Learn

Here is an example using Powershell

#capture stats
$date = (Get-Date).ToUniversalTime().ToString('yyyy-MM-ddTHH:mm:00.000Z')
$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
$mashups = 0+(get-process "Microsoft.Mashup.Container.NetFX45" | Measure-Object ).Count
$mashmem = 0+(get-process "Microsoft.Mashup.Container.NetFX45" | Measure-Object WS -Sum ).Sum
$gwmem = 0+(get-process "Microsoft.PowerBI.EnterpriseGateway" | Measure-Object WS -Sum ).Sum
#enforce TLS1.2
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$payload = @{
"Host" = $env:computername
"Timestamp" = $date
"Available Memory in MB" = $mem
"Processor Load %" = $proc
"% Free on C:" = $disk
"Mashups" = $mashups
"Mashup Memory" = $mashmem
"Gateway Memory" = $gwmem
}
#push URL
$endpoint = "https://api.powerbi.com/beta/<tenant ID>/datasets/<dataset id>/rows?key=<API key>"
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

[AliSafa]

thank you for your answer 
it seems that this code is reading data from memory and Processor etc and push it into the streamin dataset. 
but i need to read data from a sql table and push it to a Streaming dataset.

[lbendlin · Accepted solution]

Yes, it was an example. Run your SQL query, convert the result to JSON, then use that as the payload.