Forum Discussion
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:
A dimension table of employees with a primary key of employee ID
There is only one entry per employee
This should be your "Primary table"
A fact table of employee logins/logouts to a particular system with the employee ID as the foreign key
This should be your "Secondary table"
Their relationship would be denoted as one-to-many [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
- lbendlin
Super User
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.
- ryanbrandlNew Member
Thanks, but it actually looks like the answer is that Power BI uses Star Schema
I updated my original post as well.
- lbendlin
Super User