cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adetogni Member
Member

near Real time data refresh & composite models

Hi

my database is Mysql and therefore does not support DirectQuery. I'm currently working in Import mode and the main table is made of +3M rows so it takes a while to refresh it. This table is containing transactions that updates every minute (~3000 per day).

 

The previous requirement was to have in a dashboard data up to the midnight of the previous day so I wrote an ETL that gets data up to the midnight at 5:00 to a new database and Powerbi at 6:00 gets this dataset and shows  it.

 

Now the requirement has changed and they want, in the report, also data that occurs today, so therefore real time or almost real time update. I can easily change the ETL so it loads the data more frequently but then I face the situation that PowerBI service accepts only max 8 updates per day. So that might be

- 9:00

- 11:00

- 12:00

- 14:00

- 15:00

- 16:00

- 17:00

- 18:00

This might work but it's far from perfect.

 

So I was thinking about using a composite model. The idea is to have a main table made with import with historic data and an additional table in DirectQuery (on a directquery supported database...) with only today's data. So each time a person clicks on "refresh" it will get today's updated data. So I read this
https://docs.microsoft.com/en-gb/power-bi/desktop-composite-models

Where there is this note

-----------

Beginning with the October 2018 release of Power BI Desktop, you can publish composite models to the Power BI service. For scheduled refresh and dashboard tile refresh, composite models in the Power BI service behave in the same way as Import models.

-----------

So that means that even if I create a direct query "part" of my report, it still won't refresh when the person hits refresh.

 

I've investigated over the Streaming Dataset but those are tiles in the dashboard, while i need rows for a table where then some complex DAX queries needs to run.

 

Is any other solution I'm not considering?

Thanks

3 REPLIES 3
Community Support Team
Community Support Team

Re: near Real time data refresh & composite models

Hi @adetogni

How about purchasing a power bi premium which refresh rate is up to 48 per day?

 

Best Regards

Maggie

 

adetogni Member
Member

Re: near Real time data refresh & composite models

Well, that's a fairly expensive option. Cheapest pricing is 5k/month, at the moment we have roughly 30 licenses, and even in worst scenario they shouldn't go over 100. That is 8.4€*100/month = 800. 

adetogni Member
Member

Re: near Real time data refresh & composite models

any suggestion?