Real-time Dashboard showing a table (grid) - Is it possible?

[dparenteau]

Hello! I have to provide to my user a dashboard (or report) on which they could see as they would look into a SQL table directly. The requirement is that user shoudn't have to click "refresh" button to see if new data has been inserted or updated in the SQL table: changes from SQL table should be pushed directly to Power BI. 

I've experienced push data and it works well except two things: 

• I'm not able to create a dashboard (or report tile) containing a table visualization (grid) in real time. Is it normal? How I could achieve this?
• When data changes on SQL table, I would push the entire table data to the streaming data set. In order to do that, does the historical dataset is the only option? 'cause I was not able to "clear" the dataset using powershell, in order to prevent to have rows and rows growing on every time a row change in the SQL table...

I'm looking at this since last week only. Any confirmation that my requirements is or is not possible would help me a lot.

Thanks in advance!

[v-jayw-msft]

Hi @dparenteau ,

Check the document about Real-time-streaming and Automatic page refresh.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-real-time-streaming

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-automatic-page-refresh

Using Direct Query mode and configure on-premised data gateway will be a choice if your "real-time" requirements is not such strict.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You don't really need a streaming dataset for that.  You could equally have a direct query connection to your data source (SQL server table)  feed a table visual, and then pin that table visual to the dashboard.  Then use API refresh (via Powershell scheduler) to refresh your dataset every five minutes (or however frequent you need), which will then automatically update the dashboard.

Since the dashboard is not interactive this means that you will only be able to show a limited number of rows of your original data, but I guess you knew that already.

[dparenteau]

lbendlin, so I should create a report in Power BI Desktop and publish it to Power BI Service Workspace. From there I pin taht report to a dashboard, right? This way, when dataset is refreshed, the "report" pineed to the dashboard will also refresh automatically, without requiring the user to click "Refresh?"

PS: No, I was not aware about the dashboard limitation. I'm a fresh newbie, but thanks a lot for the info!  🙂

David

[lbendlin]

Correct. Dashboard contents will refresh whenever the dataset is updated.

[dparenteau]

OK. I'm trying it. I'm block when I call the "refreshes" API REST Method.

When I invoke the "Get", I can get me dataset details. Maybe it's a access permission missing. 

+ Invoke-PowerBIRestMethod -Url $RefreshDSURL -Method Post -Body $M ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

[lbendlin]

You can use this page to test run your API call

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refreshdatasetingroup#code-try-0

Note: this is for a Premium capacity, you may need to use the other (without the group)

[dparenteau]

Interesting... 

I get this, but again, not sure if it's me or because I'm using a standard capacity workspace. I got this by fill-in datasetId and GroupId from this try it page: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refreshdatasetingroup#code-try-0

Shouldn't have to put some credential info in the http POST?

I got my code from this site: https://insightsquest.com/2019/03/03/refresh-power-bi-datasets-with-powershell/#:~:text=Dataset%20Re...

{
  "error": {
    "code": "InvalidRequest",
    "message": "Invalid dataset. This API can only be called on a Model-based dataset"
  }
}

[lbendlin]

The credentials are generated when you login to the Azure service (basically the "Bearer"  token)

as I said RefreshDatasetInGroup is for premium workspaces. You should try the RefreshDataset call.

What actually is your dataset's datasource?

[dparenteau]

Ho you mean here: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refreshdataset

Ok.

My dataset datasource is a Gateway connection, which appears green "Running on MyCompanySQLServer"

datasource credentials appears fine "(admin has granted access, credenttials are not required)"

It's a SQL Server DirectQuery I used in my report on Power BI Desktop. I published the report from the desktop application to a ABC workspace.