Clarification on Fact and Dimension Tables in Star Schema Design

Question

Dear Data Analyst Course Support Team,

I hope this message finds you well. I have a question regarding some ambiguity I encountered while studying Unit Two on star schema design in the data analyst course.

The course material states that the role of a fact table is to store an accumulation of rows representing observations or events that record specific business activities. For example, it mentions that a sales fact table could store:

• Sales orders
• Order lines

However, on the same page, the provided example considers a "Sales Order" table, which has sales order columns and a one-to-one relationship with the fact table ("sales") table and a "Sales Order Line" table as part of the "Sales Order" table and this table is consederd as dimension table. seems to contradict the earlier explanation where it describes order lines as part of the fact table.

Could you please clarify the distinction between fact and dimension tables in this context, specifically regarding the classification of sales orders and order lines, and whether this data belongs to the fact table or dimension table? I want to ensure I correctly understand the star schema design principles as they apply to these examples.

Thank you for your assistance.

Best regards,

This question is related to the following Learning Module

Answer 1

Hi Mengesha, Epheson,

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

In a star schema:

• The fact table stores quantitative data about events, such as sales transactions or order lines, including metrics like TotalSalesAmount and QuantitySold.
• The dimension tables provide descriptive attributes, such as customer details or product information, that add context to these facts.

"Sales Order Line" details, which represent specific items sold, belong in the fact table. The "Sales Order" can serve as a dimension table, describing overall order attributes. The fact table captures the actual transactions, while dimension tables describe them.

This distinction should clarify any confusion: "Sales Order" provides descriptive context, and "Sales Order Line" represents detailed transactions.

If you are still facing any issue, please let us know in the comments. We are glad to help you.

If the information is helpful, please Accept Answer so that it would be helpful to community members.

Thank you.