Forum

TCS: How Do You Opt...
 
Share:
Notifications
Clear all

TCS: How Do You Optimize a Slow Power BI Report Connected to 20M+ Rows?

1 Posts
1 Users
0 Reactions
91 Views
Posts: 5
Topic starter
(@Kalyan)
Joined: 4 months ago

This question is frequently asked in TCS Power BI interviews, especially for Data Analyst and BI Developer roles. The interviewer wants to test your understanding of data modeling, DAX performance, and optimization techniques in real-world enterprise scenarios.

✅ Step-by-Step Answer

1. Check the Data Model Structure

  • Use Star Schema instead of Snowflake.
  • Keep fact tables large and dimension tables small.
  • Remove unnecessary relationships and avoid many-to-many joins.

2. Reduce Columns and Rows Loaded into Power BI

  • Load only required columns using custom SQL views.
  • Apply filters at the source (e.g., last 3 years data only).
  • Avoid loading high-cardinality text columns.

3. Optimize DAX Measures

  • Replace CALCULATE + FILTER combos with optimized versions.
  • Avoid row-by-row functions such as EARLIER and iterators unless necessary.
  • Use SUMX only on small tables.

4. Improve Query Performance

  • Enable Query Folding in Power Query.
  • Check the View Native Query option is active.
  • Push transformations to SQL instead of Power Query.

5. Manage Visual-Level Performance

  • Reduce number of visuals per page (ideal: under 12).
  • Use Aggregations for large fact tables.
  • Disable auto date/time in Power BI Desktop.

💡 Good Sample Short Answer for Interview

“To optimize a slow Power BI report connected to a 20M+ row table, I first review the data model to ensure it follows a proper star schema. I reduce unnecessary columns and push filters to the source database to minimize rows loaded. I also optimize DAX by replacing expensive iterators with efficient functions. Next, I ensure query folding is maintained in Power Query so heavy transformations happen at the SQL side. Finally, I optimize visuals by limiting complex charts and enabling aggregations for large datasets.”

📌 Why TCS Asks This Question

TCS handles large enterprise datasets, and they expect candidates to understand data modeling, performance tuning, and scalable BI design.


Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: