Data Modelling in Power BI

Data modelling helps to define the relationship between sources of data. Organizations work with large amounts of data which require modelling, before building reports. This article is a practical guide for building performance optimized data models in Power BI.

Data models in Power BI are referred to as semantic models. The data model is where you describe and organize your data.

** How to build relationships

Modelling Approaches

There is a sequence of steps followed when creating a data model. These include:

1. Identifying all the object entities and attributes in your data model.
2. Identifying the relationships between entities in your data model.
3. Deriving the best modelling approach from the entities and relationships.
4. Optimizing your model for performance

Star Schema

This type of modelling approach is widely adopted for relational data. It consists of one central table known as a fact table connected to another table or tables known as dimension tables. The star schema ensures data is denormalized to improve performance by reducing the number of joins needed to retrieve data. Fact tables mainly contain quantitative data while dimension tables describe entities.

Types of Relationships

Every relationship is represented by its cardinality type:

1. One-to-one
2. One-to-many
3. Many-to-one
4. Many-to-many

The star schema is best represented by the one-to-many cardinality because the fact table connects to multiple dimension tables.

Data Denormalization

This technique is used to improve query performance in star schemas. All redundant data are added to the fact table to maintain one single source of truth.

Snowflake Schema

In this type of schema data is normalized into multiple related sub-dimension tables. This avoids data duplication and is more suitable for complex data. This schema is more suitable for hierarchical data and data that changes often. Snowflake schemas also save storage space since data is normalized.

Conclusion

Star schemas are preferred when building data warehouses since they are optimized for better performance by reducing the number of queries and need for joins. For