Designing a Star Schema

What Is a Star Schema?

A star schema is a data modeling design in which a central fact table connects to multiple dimension tables.

The fact table stores measurable business events, while dimension tables provide descriptive attributes for filtering and grouping those events.

The structure resembles a star because several dimension tables connect to the central fact table.

Example of a Sales Star Schema

Imagine a retail company with the following model:

• Sales: Contains order identifiers, product identifiers, customer identifiers, order dates, quantities, and sales amounts.
• Products: Contains product names, categories, and brands.
• Customers: Contains customer names, locations, and customer segments.
• Calendar: Contains dates, months, quarters, and years.

The Sales table connects to the Products, Customers, and Calendar tables through their respective identifiers.

This structure allows a report to answer questions such as:

• What is the total revenue for each product category?
• Which customer segment generates the most sales?
• How does revenue change from month to month?
• Which regions have the highest sales volume?

Why Use a Star Schema?

A star schema often makes Power BI models easier to understand and maintain.

Its advantages include:

Clear structure: Fact and dimension tables have distinct responsibilities.

Predictable filtering: Dimension tables can filter the central fact table through well-defined relationships.

Reusable calculations: Measures can be used across different report visuals without creating separate calculations for every category.

Improved analytical design: It supports common business analysis patterns and can help avoid unnecessary model complexity.

Star Schema vs. Flat Table

A flat table stores descriptive and transactional information together in a single table.

For a small dataset, this may be convenient. However, larger analytical datasets can contain repeated product names, customer details, and category descriptions across many transaction rows.

A star schema separates these attributes into related tables. This can reduce duplication and provide a more maintainable structure.

The best design depends on the data source, report requirements, dataset size, and complexity.