Relationship test tips – Designing a Data Model

Relationship test tips

Here are some tips for managing table relationships:

  • Relationships can only connect two tables together.
  • Relationships do not have to be between columns of the same data type, though it is advised.
  • Relationships have direction, either Single or Both.
  • Cardinality defines which table in the relationship the filter is applied to.
  • Relationships can help reduce the model size, but too many relationships can make models confusing and hard to work with.
  • You can never create a circular relationship.
  • Two tables can have more than one relationship between them, but only one can be active.

Define role-playing dimensions

If you are used to using star schemas, one of the features is the idea of a role-playing dimension. This does not mean that your dimension will be out LARPing somewhere; it means we can use a dimension in multiple ways.

Power BI doesn’t directly support role-playing dimensions; instead, it mimics the capability by utilizing active and inactive relationships. A table can only have one active relationship with another table, but it can have multiple inactive relationships. We just tell Power BI when we want to use the inactive relationship instead of the active one.

Date table as a role-playing dimension

In this diagram, our Date table has two relationships between it and the Sales table: one on the order date and one on the shipping date. This allows this Date table to filter the Sales table by order date or shipping date, or both! It allows the Date table to pretend to be the “order date dimension” or the “shipping date dimension.”

Figure 5.6 – Date table playing multiple roles Date dimensions are some of the most common types of role-playing dimensions you will use. We will explore this more in the Create a common date table section later in this chapter. For now, we’re moving on to table configuration.

Author: Noah Walker

Leave a Reply

Your email address will not be published. Required fields are marked *