Star schema with multiple fact tables

Question

How would you structure your data if we have two fact tables. As an example, let's say I have an Accounts table as a dimension table, so only a single row for each account. Then I have an Orders table which could have multiple orders per Account. And a Survey Table that has multiple Satisfaction Experience Surveys per Account.

This question is related to the following Learning Module

Answer 1

Hi Carnabuci, Michael P,

Thank you for reaching out to us on the Microsoft Q&A forum.

To structure a star schema with multiple fact tables, set up Orders and Surveys as separate fact tables, each linked to a central Accounts dimension by Account ID.

Establish one-to-many relationships from Accounts to each fact table, and link any shared dimensions (e.g., Date) to both facts for cross-analysis. This design allows flexible reporting and combined metrics across orders and surveys without duplicating data.

If you found the information helpful, we would greatly appreciate it if you could acknowledge it by selecting the Accept Answer & Upvote options.

Thank you.

Answer 2

Typically the orders table would be the “fact” table, the rest are dimensions. The account table should generate demographic dimensions.