Data Modeling

[rmcgrath]

Hi, everyone - looking for a recommendation.  My original table is pretty wide (411 columns).  I'm creating my Fact table and realize I may have to create several Fact tables, as only one would still be way too wide.  What is the best way to get Fact and Dimension tables connected when you have to have more than one Fact table?  Surrogate keys?

[audreygerred]

Hi! Your dimension tables would have your primary key and have the unique value and you would join that to the foreign key in your fact table which would have the many side of the join.

[rmcgrath]

Yep - understand that...but what is the best way to make relationships when you have multiple Fact tables?  How do you get everything connected?  Example --> Fact Table to Fact Table

[audreygerred · Accepted solution]

Each fact table will connect to the dimension tables, then you will use the fields from your dimension tables with your measures based on your fact tables. Here is a blog I wrote about star-schema (my example only has one fact table, but it is applicable to having multiple fact tables as well). http://powerbiwithme.com/2023/07/30/the-star-schema-edition/