Forum Discussion

ryanbrandl's avatar
ryanbrandl
New Member
5 years ago
Solved

Power BI - Cardinality Swapping (Xpost from Reddit)

I'm very familiar with database relationship structure. I may get some of the terminology wrong, since I'm mostly self taught, but this is how I generally understand it:

When you're describing the cardinality between two tables (1:1, 1:*, *:1, *:*) the left hand value is the primary table and the right hand value is the secondary table.

For example, you have two tables:

  1. A dimension table of employees with a primary key of employee ID

    1. There is only one entry per employee

    2. This should be your "Primary table"

  2. A fact table of employee logins/logouts to a particular system with the employee ID as the foreign key

    1. This should be your "Secondary table"

  3. Their relationship would be denoted as one-to-many [1:*]

    1. One entry on the employee (primary) table links to many entries in the login/logout (secondary) table

This all makes sense in my head. However, when I establish relationships in Power BI, it seems to reverse the cardinality to read [secondary:primary]. In this case instead of the commonly agreed upon notation of [1:*] Power BI will change it to [*:1]. Is this a bug or a feature? If the latter, why would they do this? Or am I misunderstanding how to denote database/table relationships? Any insight is appreciated!

I've set up this Cardinality in every way imaginable, but it always displays [secondary:primary]. It doesn't just happen to the relationship between these two tables. This doesn't appear to affect the actual behavior of PBI, but I am writing an SOP for my company and need to be able to address the behavior.

 

Update: It appears Power BI uses Star Schema

  • Thanks, but it actually looks like the answer is that Power BI uses Star Schema

     

    I updated my original post as well.

3 Replies

  • yeah, it's mildly confusing.  Power BI doesn't care which table you specify first.  

     

    If you specify the dimension table first in the relationship dialog then it is 1:*, if you specify the fact table first, it will be *:1

     

    Power BI will validate if your cardinality choice is supported.

     

    For your SOP tell the devs to always select the dimension table first.