I’m building a dashboard in Power BI’s Pro Workspace, connecting data via Direct Query from Databricks (around 60 million rows from 15 combined tables), using a SQL Serverless (small size and 4 clusters).
The problem is that the dashboard is taking too long to load.
Considering that I can’t change my workspace to Premium, work with import mode, or use aggregation tables, is there anything else I can do besides what I’ve already done (listed below)?
- Model tables in a “Star schema” in Power BI;
- Materialize all tables in Databricks, avoiding as much as possible calculations and joins inside Power BI;
- Check the “Referential Integrity” box when joining tables in Power BI;
- Avoid using bi-directional filters;
- Avoid using calculated columns or any field with “line-to-line interaction”;
- Avoid nesting one calculation inside another;
- Set up dimension tables in Dual Storage Mode;
- Add an “Apply” button to apply all changes at once.
- Increase the “Maximum connections per data source” in Power BI's configuration;
- Increase the “Maximum number of simultaneous evaluations” in Power BI's configuration;
- Increase the “Maximum memory used per simultaneous evaluation” in Power BI's configuration;
- Uncheck “Auto date/time” in Power BI's configuration;
- Uncheck “Allow data preview to download in the background” in Power BI's configuration;
- Uncheck “Enable parallel loading of tables” in Power BI's configuration;
- Uncheck “Turn on Q&A to ask natural language questions about your data” in Power BI's configuration;
- Uncheck “Cross highlighting/filtering by default” in Power BI's configuration;
P.S.: I also tried to scale up the SQL Serverless (both memory and the number of nodes), but despite the dashboard taking 3-4 minutes to load, only 20%-30% of the compute power is being used. So, it does not seem to be a matter of compute power.
Due to that, I suspect that connection latency might have something to do with the slow performance.
