Data Modeling with Microsoft Power BI

Chapter 19. Real-World Examples Using SQL

Chapter 3 introduces real-world use cases I’ve encountered while working on projects for customers and how I solved them in Power BI. Being able to “attack” a problem with different “weapons” is of help when it comes to those challenges. You always need to be flexible and sometimes think out-of-the box to find the right approach. In this chapter, I demonstrate how to generate and shape tables in SQL:

• You’ll learn how to generate both the bin table and the bin range table in SQL.

• To resolve a many-to-many relationship (like for a budget that is on a different level of granularity than the actual values), you need to create a bridge table. This can be done easily in SQL.

• For the demonstrated solution for multi-language reports, you need a table containing the texts for headlines, buttons, etc. I will show you how you can pivot the table, so it perfectly fits the solution.

• Key-value pair tables are hard to query in the shape they are in natively. Therefore, you will learn how you can pivot the table to bring it in an analytics-friendly shape.

• I’ll show you what role a data warehouse plays in the concept of self-service BI versus enterprise BI.

Binning

In “Binning”, I describe three solutions. All of the necessary tables can be created in SQL. Remember that I wouldn’t recommend adding the bin information into the fact table, as a change in the bin ranges would mean updating the whole fact table. That’s why you should look at the ...