The Clearly Podcast

So You Want To Be A Power BI Consultant

Summary

In this episode we offer practical advice to anyone thinking of starting a career as a Power BI consultant. We talk about:

  • What a Power BI consultant does

  • If an average day in the life of a Power BI consultant exists, what it looks like

  • What experience and skills are useful to build on

  • Skills v character and what we value the most

  • Qualifications

  • What we look for when hiring consultants

  • CV/Resumes

  • Interview preparation

  • How to approach the market and the companies you may like to work for

  • How to work with recruiters

In this episode we're joined by Jon Styles who leads specialist Microsoft recruitment agency Northcote Dynamics. Jon can be found at:

www.northcote-dynamics.com

https://www.linkedin.com/in/jon-styles-91858b35/

You can download Power BI Desktop from here.

If you already use Power BI, or are considering it, we strongly recommend you join your local Power BI user group here.

Transcript

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!