Introduction Andy: Welcome to the Clearly Podcast episode 12, recorded on December 17, 2020. We're discussing Power BI pre-aggregation. We'll cover its definition, when to use it, how to implement it, and its pitfalls. How are you guys doing?
Defining Pre-Aggregation Tom: We often talk about pre-aggregation. Microsoft simply calls it "aggregation." Whenever you use an aggregate function, you're aggregating data. Pre-aggregation involves aggregating data as it's loaded into the data model, balancing direct query and import.
Benefits of Pre-Aggregation Shailan: Pre-aggregation helps with system responsiveness, especially for large datasets. Direct queries retrieve and aggregate data in real time, but pre-aggregations can be pre-calculated at the query level.
Tom: Pre-aggregation can improve performance and manage resources better by handling common queries in Power BI and less frequent ones via direct query.
Implementing Pre-Aggregation Andy: In large projects with many transactions, is pre-aggregation commonly used?
Shailan: Yes, it's often used. Other BI tools have similar techniques. For example, creating a duplicate table for high-level pre-aggregations while keeping detailed transactions.
Tom: Focus on common use cases when deciding what to pre-aggregate. Avoid creating too many pre-aggregated scenarios as it becomes unmanageable.
Pitfalls and Considerations Andy: What are the pitfalls of pre-aggregation?
Tom: If pre-aggregations are too granular, they lose their advantage. They should simplify data management, not complicate it. Balance between responsiveness and data maintenance.
Shailan: Use performance tools to identify where pre-aggregation is needed. Pre-aggregate historical data since it's less likely to change, focusing on current data.
When to Avoid Pre-Aggregation Tom: Avoid pre-aggregation if the data fits comfortably in memory or if the use case involves many one-off ad hoc reports. Pre-aggregation is not beneficial for scenarios with varying exploratory analysis needs.
Top Tips for Pre-Aggregation Shailan: Import data if possible. Use pre-aggregation for common queries like year-to-date figures. Filter high-level data on dashboards.
Tom: Don't build every possible pre-aggregation combination. It leads to management issues.
Andy: After pre-aggregation, verify your figures against known values. Ensure accuracy.
Long-Term Maintenance Andy: What about long-term maintenance of pre-aggregated data?
Tom: Maintain the complexity added by pre-aggregations. As the business evolves, so should your data model. Keep it as simple as needed to perform the task.
Conclusion Andy: Thanks, everyone. For questions or ideas, visit clearlycloudy.co.uk (UK & Europe) or clearlysolutions.net (North America). Email us at podcast@clearlysolutions.net. We'll be back on January 25, 2021, with a discussion on project management of Power BI projects. Stay safe and have a great week!
