Question

What processes occur internally in Power BI when a relationship is created between two tables?
I’m interested in the internal mechanisms of Power BI when a relationship is established between two tables. What specific actions does Power BI perform, and how do these impact data modeling and performance?

Answer 1

When a relationship is created between two tables, the following happens internally in Power BI:

Metadata is Created: Power BI defines the cardinality of the relation, which can be one-to-one or one-to-many, and also determines the direction, which can be unidirectional or bidirectional.

Optimization: The optimization of query plans and data propagation is done. If Filter values are implemented in one table, they also affect the table related to this, which is useful in data retrieval.

DAX Adjustments: Power BI uses the relationship to optimize DAX calculation and queries, enhancing functionality. This means a reduction in execution time.

Performance Impact: A significant disadvantage of directional and high-distant relationships is the degrading of performance. Many-to-many relationships, on the other hand, may require the creation of bridge tables, which is complex due to their creation.

Storage and Compression: Assisting in data storage and compression. An important aspect is that it influences system memory and retrieval rate.

Highly efficient relationship design can affect improvement performance as well as accurate data modeling.