Forum Discussion
Excel Data Model + Databricks (ODBC) - Slicers Causing Major Performance Slowdowns
Hi everyone,
I have an Excel file connected to Databricks via ODBC. The SQL statements are very simple, basically just SELECT * from individual tables. The tables are quite large, with the biggest one having around 5 million rows. They include sales data by daily transaction, sales data by weekly transaction, BTD data, and a dimension table with product information related to the other tables.
In Excel, I created a data model, connected the tables, and added measures. The data is not loaded into worksheets, it’s only added as connections to the data model. The report itself consists of several large PivotTables and some charts built from the model.
One of the user requirements is to have slicers controlling the PivotTables/charts. However, adding slicers slows the report down significantly. I’ve tried suggesting using PivotTable filters instead, which perform much better, but users strongly prefer slicers and still expect good performance.
Does anyone have suggestions on how to improve performance in this kind of setup? Any best practices for using slicers efficiently with large data models connected via ODBC/Databricks?
Thanks in advance!
1 Reply
Hi
Won't be able to help at all with this & you did not mention where you Databricks sits (Azure/AWS...) so not sure the following pointers are relevant…
- Performance issues with multiple slicers in "Analyze in Excel"
- Databricks ODBC Driver download
Looks like version >/= 2.6.15 is the min. in your context - Create an ODBC DSN-less connection string for the Databricks ODBC Driver
- Driver capability settings for the Databricks ODBC Driver
- Databricks ODBC Driver (MSFT)
- Create an ODBC DSN for the Databricks ODBC Driver (MSFT)
- Databricks Community
Depending on the ODBC Driver capabilities some little optimizations might be possible… - Excel 2508 PivotTable Data Model Slowdown: Power Pivot Regression, Slicer Lag... Workarounds
TRUE/FALSE? No idea
basically just SELECT * from individual tables. The tables are quite large, with the biggest one having around 5 million rows
In such a context wonder if SELECT * is the best option
adding slicers slows the report down significantly. I’ve tried suggesting using PivotTable filters instead
I would test perf. using the Pivot Charts filters (instead of the slicers). If by any chance perf. are better, would this be acceptable workaround would be the next question to the user
Forgive me if this is all irrelevant
