I am creating a dashboard with 4 Pages with a total of approximately 70 visualizations and filters. I have a mostly flattened structure with 1 Fact Table and 3 Dimension Tables. The Fact Table contains over 6 million records, and one of the dimension table contains over 2 million records.
I am using Direct Query agains a SQL Azure Database (as the data soure options in Power BI Embedded are quite limited). I noticed a substantial performance issue. For example, clicking on a filter takes about 3 minutes for all the charts in the page to refresh. The issue is in the execution of the SQL Queries. It seems that Power BI will send a separate SQL query for each visualization in the dashboard. These are taking over a minute each to complete. My Azure SQL Database is set as 100 DTUs on the Standard Tier.
My concern is also that I am testing on a limited data-set, the actual production data would contain 10 times as much data and be accessed by multiple users concurrently.
I can of course try to optimize the query and add indexes as required but my opinion is that usign a relational database (ROLAP) for such large amounts of data is not the correct approach. I believe it has to be an SSAS (Tabular to Multi-dimensional). However, this is not possibly (currently) with Power BI Embedded.
Using the Import functionality is also not an option due to the size of the data and also the fact that refresh is not available in embedded.
Has anyone experienced similar performance issues previously? What should be the recommended approach? Should I try to optimize the Queries or is it just a bad design to use a ROLAP for such a scenario? Is Power BI embedded currently an option for these data sizes?
