Forum Discussion
Recommended Approach for Large Project
I had a similar project...about 50 tables, 800 measures, 50M records. I developed the data model in Azure Analysis Services and used that as the source for dozens of individual reports. Simplified maintenance since measures were all centrally located, performance was adequate (used S0 AAS so it was kind of a weakling, but later upgraded to S1). That may/may not be an option for you, but if possible it's worth looking at.
I would also recommend you making a idea post: https://community.powerbi.com/t5/Custom-Visuals-Ideas/idb-p/CustomVisualsIdeas about Dremio not being able in direct query, see if Power BI team can work on a conector for it if good amount of people need this, this can help you optimize your model further and send statement to your SQL in it for the source to process part of the data before getting it to BI,
and yes migrating to a cloud wherehouse does have their pros and could be a good idea for your needs,
if any of this post helped you out, please let us know marking them as solution, and best of lucks with your proyect.