Power Bi with SQL server (on premise) real time connection in Power App

[Jasperca]

Hi Everyone

We currently have a Power App (canvas) where we can add new records to an SQL server database. Next to this we have a Power Bi tile that is linked to this database to show some visualizations. The thing is, whenever we add a new record to our database we need to manually refresh (or schedule refresh) the linked PowerBi dataset in order to see our new record in the visual.

Is it by any chance possible to have the SQL server and the linked PowerBi dataset always synced so we can see the data show up in real time inside the powerapp?  (or even in our Power Bi report?). What are our possibilities here?

Thank you so much!

[janesbenth1]

@fnaf Using Power BI Establish a Direct Query connection to the SQL server data source before configuring APR on the relevant report page. 

[harnesspaintbal]

@the backrooms As of now, the data set can be found in a Power Bi service workspace. I've been where you are, and it wasn't easy finding a solution to your predicament.

[lbendlin]

From Power BI connect to the SQL server data source in Direct Query mode, and then set up APR (automatic page refresh) on the appropriate report page.

[Jasperca]

Thanks for the response.

The dataset is already present in a workspace inside of Power Bi service.

If I open up Power Bi Desktop and choose that dataset (Power Bi datasets) it shows ‘Connected live to the Power Bi dataset …’. If I want to make some changes to it I can click ‘Make changes to this model’.

Then it automatically switches it to DirectQuery after it asks me to add a local model.

But no matter what, whenever I want to see the latest data inside of my visuals, I still need to refresh the dataset by clicking the Refresh now icon.

Does this mean I have to connect to my SQL server directly from Power Bi Desktop in order to avoid to refresh my dataset manually?

Thank you

[lbendlin]

As I said you also need to set up APR.