fact table filtering dimension table (one to many), Star Schema modeling error?

[luizzed]

Hi!
I'm having trouble modeling my data. The way I'm doing it results in the fact table filtering the dimension table with the cardinality one (for the fact table) to many (for the dimension table). I believe that conceptually this is incorrect. How could I model the following scenario:
I have a fact table that records information about projects. factPROJECTS:

And a dimension table with the team for each project. dimTEAM:

The relationship between the tables is thus, cardinality from one to many, starting from the fact table.

How could I better model this using Star or SnowFlakes Schema?

[Icey · Accepted solution]

Hi @luizzed ,

A dimension table contains a key column (or columns) that acts as a unique identifier, and descriptive columns. Your table "factPROJECTES" meets this definition, doesn't it?

And, there's no table property that modelers set to configure the table type as dimension or fact. It's in fact determined by the model relationships. A model relationship establishes a filter propagation path between two tables, and it's the Cardinality property of the relationship that determines the table type. A common relationship cardinality is one-to-many or its inverse many-to-one. The "one" side is always a dimension-type table while the "many" side is always a fact-type table.

Therefore, your table "factPROJECTES" is a dimension-type table and "dimTEAM" is a fact-type table.

If your model is relatively simple, you can consider changing the direction to Both. Or, just merge the two tables.

Reference: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.