Share via

Star schema with multiple fact tables

Carnabuci, Michael P 40 Reputation points
2024-10-26T16:55:16.31+00:00

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

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
0 comments
Answer accepted by question author
  1. Syed Saleem Peera 13,100 Reputation points Microsoft External Staff Volunteer Moderator
    2024-10-28T07:15:52.1733333+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 81,971 Reputation points Volunteer Moderator
    2024-10-26T17:04:46.4766667+00:00

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

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.