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.
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?
Hi @JosephJC,
>>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?
This performance issue often appears when you operation on large amount of records, current it not contains the effective solution to solve it. I'd like to suggest you submit the requirement of refresh performance to ideas.
Updated:
I receive some suggestions from power bi team for the performance issue, perhaps you can try them.
i. Create in memory indexes in Azure SQL which can potentially improve performance.
ii. Upgrading the Azure to Premium Tier , so that if the Azure SQL DB is at Premium Level, he can use Clustered Column Store indexes.
Note:-Power BI Direct Query has a limitation of returning 1 Million rows to Power BI.
Regards,
Xiaoxin Sheng
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |