Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @Anonymous
How about purchasing a power bi premium which refresh rate is up to 48 per day?
Best Regards
Maggie
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.
any suggestion?
I am having the same issue.
I will update if I get a solution.
Hi all
I finish my POC with the CData connector to MySQL. https://www.cdata.com/drivers/mysql/powerbi/
We had some problems and bugs at the beginning, but the last version does work.
I successfully connected to MySQL DB via desktop and gateway in direct-query mode.
It required buying a license, of course, but it is a working solution.
I hope this helped.
Nadav
I'd love to know it, too, could someone from Microsoft support help answer this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |