The Clearly Podcast
When Hybrid Models Save the Day
Summary
This week, we discuss hybrid models. In short, this involves combining data from direct query or live connection with data that has been imported into the model.
There are several reasons why you might want to do this. As we discussed last week, there are many times that you will need to use a direct query. The problem is that not all sources (eg Excel) support direct query, meaning we need to import that data. Equally, if you have a data source using SSAS you don't want to import that as it will flatten the structure. Finally, we touch on using a hybrid model to provide a pre-aggregated version to access the speed of import and detail of direct query.
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
Andy: Hey everyone, welcome to the Clearly Podcast episode on "When Hybrid Models Save the Day," recorded on October 22, 2020. I'm your host, Andy Clark, joined by Shailan Tudor Sama and Tom Gough. In this episode, we'll discuss Power BI hybrid models, how to deploy them, and when they save the day. We also have a listener question for Tom. Enjoy!
Listener Question
Andy: We received an email from Renata Clarke in Houston, TX. She writes, "Dear Mr. Gough, in episode one, you used a metaphor about using the right tools for the job. Could you share this wisdom with my husband, who believes DIY means 'destructing yourself'? Best wishes, Renata."
Tom: That's funny, but I tend to use a hammer for everything too!
Hybrid Models Overview
Andy: Let's talk about why hybrid models save the day. Tom, can you define a hybrid model in Power BI?
Tom: Sure. A hybrid Power BI model uses both import and direct query. It allows for some data to be imported and pre-aggregated, while other data remains in direct query form, offering both speed and real-time updates.
Andy: Shailan, where do you use these?
Shailan: Hybrid models are useful for structured data from finance or ERP packages and for combining static data like department codes with dynamic data that changes frequently, like stock movements.
Benefits and Use Cases
Andy: What's the benefit for customers using hybrid models?
Tom: They offer speed and real-time data access. For example, budget data in Excel can be imported, while large datasets like a general ledger can use direct query for detailed analysis.
Shailan: Exactly. They’re also useful when data from different sources needs to be combined, like budget spreadsheets with live financial data.
Implementation and Pitfalls
Andy: Have you had to change an import or direct query model to a hybrid model?
Tom: Yes, it's common in large Power BI projects. When you have static lookup data and live data, a hybrid model is often necessary.
Andy: What are the pitfalls of hybrid models?
Shailan: Sometimes performance issues arise due to the direct query needing constant refreshing. However, it’s often the best solution available.
Tom: Another pitfall is a timing mismatch between static and direct query data, which can cause inconsistencies. Overcomplicating the model with too many aggregates can also create performance problems.
Learning More
Andy: If someone wants to learn more about hybrid models, where should they go?
Shailan: Visit our website, clearly.co.uk, and check out our blog posts and training videos. The Power BI community is also a great resource.
Andy: The community is indeed fantastic. There are many experts willing to help.
Final Thoughts
Andy: Final thoughts, Tom?
Tom: Don’t optimize too early. Let users interact with the model first to understand real use cases.
Andy: Shaylan?
Shailan: Experiment and extend your models. Prototyping is key.
Andy: Great points. Hybrid models should be part of your consideration when deploying Power BI solutions. Thanks for listening, everyone! Visit clearly.co.uk for more information. We'll be back on November 23 to discuss branding your Power BI reports. Have a great week!