Excel Data Model + Databricks (ODBC) - Slicers Causing Major Performance Slowdowns

Question

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!

Answer 1

Hi @MT29,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your sharing and my understanding about Power Pivot, this behavior is quite expected in an Excel sheet having Excel slicers added on top of very large PivotTables/Charts which are built on the Data Model (Power Pivot). In this situation, each slicer interaction can trigger recalculation across relationships and across all connected PivotTables, which can become noticeably slower as model size, field cardinality, and number of connected pivots can increase drastically.

For this situation, some suggested optimization steps can be:

• Reduce what’s loaded into the Data Model, bring in only the columns/rows you actually need and also try to reduce the number of unique values in columns used for filtering.
• Use slicers on dimension attributes with fewer values for example Category, Brand, Month, Region, etc. Large-value slicers can create high memory load and extra work.
• Minimize how many PivotTables each slicer control which can improve performance slicers are connected to less pivots/visuals.

For more in-depth knowledge and insights regarding this situation, I recommend you sharing your concern in the Excel | Microsoft Community Hub. This forum is dedicated to Excel and is actively monitored by power users, Microsoft partners, and engineers who can provide step-by-step guidance and share best practices.

I apologize for redirecting you to another community, as I may not have the necessary skills and experience to solve your Excel issue.

Thank you for your patience and understanding during this process. For any questions or further assistance in the future, please feel free to reach out again at any time. Not only will I be happy to assist you, but also all the moderators of the Q&A forum will do their best to provide support and guidance.

I hope everything will be resolved soon. Have a great day.