topic Re: Live Stream Power BI in Developer

Live Stream Power BI

AliSafa — Fri, 25 Nov 2022 18:57:09 GMT

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

Re: Live Stream Power BI

lbendlin — Sat, 26 Nov 2022 16:49:34 GMT

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?

Re: Live Stream Power BI

AliSafa — Mon, 28 Nov 2022 16:39:12 GMT

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

Re: Live Stream Power BI

lbendlin — Mon, 28 Nov 2022 17:39:36 GMT

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

Re: Live Stream Power BI

AliSafa — Mon, 28 Nov 2022 18:59:42 GMT

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.

Re: Live Stream Power BI

lbendlin — Mon, 28 Nov 2022 19:07:05 GMT

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

Re: Live Stream Power BI

AliSafa — Mon, 28 Nov 2022 19:22:19 GMT

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

Re: Live Stream Power BI

lbendlin — Mon, 28 Nov 2022 19:43:39 GMT

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.