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
