Data Modeling with Microsoft Power BI

Part V. Data Modeling for Power BI with the Help of SQL

The part about SQL is especially aimed toward data engineers (usually part of the IT department) and the dedicated domain expert. SQL stands for “Standard Query Language,” a deceiving name, as every database management system comes with its own dialect. As this book is about Microsoft Power BI, I will concentrate on Transact-SQL (T-SQL), which is available for all SQL interfaces in Microsoft’s data platform. This dialect comes with a procedural extension, which allows to create variables, implement conditional executions of code or loops. Such a code can be stored in the form of procedures and functions in a database.

Chapter 17 starts with an introduction to the parts of a data model in a SQL-based database:

• Tables

• Primary and Foreign Keys

• Relationships

• Combining the content of tables and possible traps

Chapter 18 shows you all steps typically used when building a data warehouse (layer) from any data source:

• Normalizing and Denormalizing

• Adding calculations

• Transforming flags and indicators into meaningful text

• Creating your own date and time tables

• Duplicating tables in case they play more than one role in a data model

• Implementing slowly changing dimensions of different types

• Flattening parent-child hierarchies

The challenges of the real-world can be manifold. Also in this part, I will show ...