Course: Microsoft Power BI Training
Topic: Building Star and Snowflake Schemas
Course Link: Microsoft Power BI Training at JustAcademy
Demo Link: Register for a Free Course Demo
JustAcademy Website: Visit JustAcademy
Star and snowflake schemas are two important data modeling techniques used in business intelligence, data warehousing, and Microsoft Power BI. They organize data into fact and dimension tables to make reporting, analysis, and dashboard development easier.
When building Power BI reports, a well-designed data model helps users analyze business performance across different dimensions, such as products, customers, locations, employees, and time periods.
A star schema generally uses a central fact table connected directly to dimension tables. A snowflake schema organizes dimensions into additional related tables through normalization.
A data schema defines how tables are structured and how they relate to one another. It specifies the columns, keys, relationships, and organization of data within a database or analytical model.
For example, a sales reporting model might contain the following tables:
These tables can be connected to create reports such as monthly revenue, sales by category, and customer performance.
Dimensional modeling is a data design approach that structures information for analytical queries and reporting. It separates measurable business events from the descriptive information used to analyze them.
Dimensional models commonly use two main types of tables:
A fact table stores measurable business events. In a sales model, it might record the quantity sold, sales amount, discount, cost, and identifiers for the product, customer, and date.
| SalesID | DateKey | ProductKey | CustomerKey | Quantity | SalesAmount |
|---|---|---|---|---|---|
| 1001 | 20260101 | 101 | 501 | 2 | 1500 |
| 1002 | 20260102 | 102 | 502 | 1 | 800 |
| 1003 | 20260103 | 101 | 503 | 3 | 2250 |
| 1004 | 20260104 | 103 | 501 | 2 | 1200 |
In this example, each row represents a sales transaction. The keys identify the corresponding date, product, and customer in dimension tables.
Grain defines exactly what one row in a fact table represents. It must be decided before designing the model.
Examples of grain include:
Best practice: Avoid combining records at different levels of detail in the same fact table unless the model is deliberately designed to handle the differences. Inconsistent grain can cause incorrect aggregations.
Dimension tables contain descriptive information about the entities involved in business events. They make fact table measurements meaningful to report users.
| ProductKey | ProductName | Category | Brand |
|---|---|---|---|
| 101 | Laptop | Electronics | Brand A |
| 102 | Keyboard | Accessories | Brand B |
| 103 | Monitor | Electronics | Brand C |
This dimension lets users analyze sales by product name, category, and brand without storing those descriptive values repeatedly in every sales transaction.
A primary key uniquely identifies each row in a dimension table. A surrogate key is a generated identifier used to represent a dimension record independently of the original business identifier.
For example, ProductKey 101 might identify a laptop in the Product dimension. The Sales table references this key to connect a transaction to the correct product.
Dimension keys should be unique on the dimension side of a one-to-many relationship. Duplicate keys can prevent the intended relationship from being created or cause ambiguous results.
A star schema is a dimensional data model in which one central fact table connects directly to multiple dimension tables. Its visual structure resembles a star, with the fact table at the center and dimension tables surrounding it.
Date Dimension
|
|
Customer Dimension -- Sales Fact -- Product Dimension
|
|
Store DimensionThe Sales Fact table contains sales measurements and foreign keys. Each dimension provides descriptive attributes for analyzing those measurements.
A retail company might create the following tables:
Each dimension connects directly to FactSales using its key. This structure enables users to analyze revenue by year, product category, customer segment, and store region.
A snowflake schema is a dimensional model in which one or more dimension tables are split into related sub-dimension tables. This process is commonly associated with normalization.
Instead of storing every descriptive attribute in one dimension table, a snowflake model separates related attributes into multiple tables and connects them through keys.
Category
|
|
Brand -- Product Subcategory -- Product
|
|
Sales Fact
|
|
Date DimensionIn this simplified example, product details are separated from product subcategories and categories. The Product dimension connects to the fact table, while the product hierarchy is represented through additional related tables.
A normalized product model might use these tables:
The relationship path from a product category to sales runs through the product hierarchy and then to the fact table.
| Feature | Star Schema | Snowflake Schema |
|---|---|---|
| Structure | Central fact table connected directly to dimensions | Fact table connected to dimensions that may have related sub-dimensions |
| Dimension design | Generally denormalized | Generally more normalized |
| Number of tables | Usually fewer | Usually more |
| Data duplication | May contain repeated descriptive attributes | Can reduce repetition by separating shared attributes |
| Ease of understanding | Generally easier for report creators | Requires understanding additional relationships |
| Relationship complexity | Usually lower | Can be higher |
| Power BI usability | Often preferred for semantic models | Useful when normalized dimensions offer a clear benefit |
| Maintenance | Often simpler for reporting teams | Can be more complex across multiple related tables |
| Typical use | Business dashboards and self-service analytics | Complex hierarchies and certain governed data structures |
Important: A snowflake schema is not automatically faster or more storage-efficient in every environment. The right choice depends on data volume, model design, refresh requirements, relationship behavior, and report workload.
Relationships connect tables using matching key columns. In a typical star schema, a dimension table has a one-to-many relationship with the fact table.
A one-to-many relationship means that a key occurs once on the dimension side and can occur multiple times on the fact side.
For example:
DimProduct[ProductKey] 1 ---- * FactSales[ProductKey]One product can appear in many sales transactions. The Product dimension normally contains one row for each product key, while FactSales can contain many rows with the same key.
In a standard star schema, dimension tables usually filter fact tables through single-direction relationships. Selecting a product category filters the relevant products and, through the relationship, the sales transactions used in calculations.
Single-direction filtering is often a good default because it reduces unexpected filter paths and makes the model easier to reason about.
A many-to-many relationship occurs when the joining key is not unique on either side. These relationships may be valid for some business scenarios, but they require careful design. A bridge table or an alternative dimensional model may provide more predictable behavior.
Follow these steps to create a star schema using Power BI Desktop.
Open Power Query Editor and prepare the tables before loading them into the model.
Create or prepare separate tables for products, customers, dates, and locations. Each dimension should provide descriptive attributes and a unique key for each entity at the intended grain.
For example, DimProduct may contain ProductKey, ProductName, Category, and Brand. ProductKey must uniquely identify each product row.
Prepare FactSales with the measures and foreign keys needed for reporting. Remove redundant descriptive fields if they are unnecessary in the fact table and already exist in dimensions.
Ensure that each transaction references valid dimension keys. Where appropriate, use a consistent unknown or not-applicable member to handle missing dimension references.
For each dimension-to-fact relationship, verify the following settings:
Use DAX measures to calculate business metrics from the fact table.
Total Sales =
SUM(FactSales[SalesAmount])
Total Quantity =
SUM(FactSales[Quantity])
Total Cost =
SUM(FactSales[CostAmount])
Total Profit =
[Total Sales] - [Total Cost]
Profit Margin =
DIVIDE([Total Profit], [Total Sales], 0)These measures respond to filters from related dimensions, assuming the relationships and filter paths are configured correctly.
A snowflake model can be built by splitting dimension attributes into separate related tables. This is useful when attributes belong to distinct hierarchical entities and separating them provides a clear modeling or data-governance benefit.
Review the source data for descriptive attributes that represent separate entities. A product hierarchy might contain Product, Subcategory, and Category. A geography hierarchy might contain City, State, and Country.
Prepare separate queries or source tables for each entity.
DimProduct
-----------
ProductKey
ProductName
SubcategoryKey
DimSubcategory
--------------
SubcategoryKey
SubcategoryName
CategoryKey
DimCategory
-----------
CategoryKey
CategoryNameConnect the hierarchy tables using their keys.
DimCategory[CategoryKey]
1
|
*
DimSubcategory[CategoryKey]
DimSubcategory[SubcategoryKey]
1
|
*
DimProduct[SubcategoryKey]
DimProduct[ProductKey]
1
|
*
FactSales[ProductKey]The exact direction and cardinality should match the actual key uniqueness and data structure. For this example, the category and subcategory keys are unique on their respective parent tables.
Build a visual with CategoryName and the Total Sales measure. Select a category and verify that only the corresponding products and sales transactions are included.
If the category does not filter sales as expected, check the relationship keys, cardinality, active status, filter direction, and data quality.
Evaluate whether the additional tables provide a meaningful advantage. If report authors frequently need category and subcategory attributes, a single DimProduct table containing ProductName, SubcategoryName, and CategoryName may be simpler for Power BI users.
A dedicated date dimension is an important part of many Power BI dimensional models. It supports consistent time-based filtering and analysis.
DimDate =
ADDCOLUMNS(
CALENDAR(
DATE(2024, 1, 1),
DATE(2027, 12, 31)
),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q"),
"Year Month", FORMAT([Date], "YYYY-MM")
)This example creates a calendar covering 2024 through 2027. Adjust the start and end dates to match the reporting requirements.
A single date dimension can support several date roles, such as Order Date, Ship Date, and Delivery Date. Typically, one relationship is active while other relationships can be inactive and activated in specific measures with USERELATIONSHIP when appropriate.
Power Query is commonly used to prepare source data for dimensional modeling. Good transformation practices help maintain a reliable model.
Keep only the fields required for analysis, relationships, calculations, and data validation. Reducing unnecessary columns can lower model size, particularly in Import mode.
Before using a column as the one-side key, verify that each key appears only once. Do not simply remove duplicates if doing so would discard legitimate business records. Investigate why duplicates exist and define the correct entity grain first.
Keys that participate in relationships should use compatible data types. For example, avoid joining a numeric ProductKey in one table to a text ProductKey in another without an intentional conversion.
Identify fact records that do not match a dimension. Depending on the business requirement, assign them to an explicit Unknown member or correct the source data. Unmatched records can lead to blank categories or unexpected totals in reports.
When working with supported data sources, transformations that can be translated into source queries may improve refresh efficiency. Check query folding in Power Query and consider source-side views or transformations for large datasets when appropriate.
Total Sales =
SUM(FactSales[SalesAmount])Calculates the sum of sales amounts under the current filter context.
Create a bar chart with DimProduct[Category] on the axis and Total Sales in Values. In a snowflake model, use the category attribute from the related category table instead.
Sales YTD =
TOTALYTD(
[Total Sales],
DimDate[Date]
)This measure calculates year-to-date sales using the date dimension. For a non-calendar financial year, configure the calculation to match the organization's fiscal calendar.
Sales Previous Year =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(DimDate[Date])
)This measure compares the current date context with the equivalent period in the previous year, assuming a suitable date table and supported date range.
YoY Growth % =
DIVIDE(
[Total Sales] - [Sales Previous Year],
[Sales Previous Year],
0
)Format this measure as a percentage. If the previous-year value is zero or missing, consider whether displaying zero or blank is more appropriate for the business scenario.
A well-designed schema can improve model usability and help Power BI execute analytical queries efficiently. Performance also depends on data volume, storage mode, column cardinality, DAX complexity, source queries, and visual design.
Use a star schema as the default starting point for many Power BI semantic models. Keep the fact table at a well-defined grain and organize descriptive attributes in dimensions.
Use measures for dynamic aggregations and keep expressions understandable. Test complex measures with Performance Analyzer and other appropriate diagnostic tools when reports are slow.
For large datasets, aggregation tables, incremental refresh, source optimization, and suitable storage modes may help. Choose these techniques based on actual workload and refresh requirements rather than assuming every model needs them.
Problem: Power BI cannot create the intended one-to-many relationship because the dimension key contains duplicates.
Solution: Inspect duplicate rows in Power Query or the source system. Confirm the dimension grain and correct the data model before creating the relationship.
Problem: Relationship columns use incompatible data types or values do not match as expected.
Solution: Standardize data types, remove unwanted spaces from text keys where applicable, and verify that key values match.
Problem: A visual displays a blank category even though the dimension appears complete.
Solution: Check for missing or unmatched foreign keys in the fact table. Review null values and relationship integrity.
Problem: A measure returns unexpected totals when multiple dimensions are used.
Solution: Check the fact table grain, relationship cardinality, filter direction, duplicate fact rows, and measure logic. Verify the result against a known source total.
Problem: Selecting a product category does not change the sales chart.
Solution: Verify the category-to-subcategory and subcategory-to-product relationships in a snowflake model. Then verify the product-to-sales relationship, active status, and filter propagation.
Problem: The model becomes difficult to understand after normalization.
Solution: Evaluate whether descriptive hierarchy tables should be combined into a denormalized dimension for reporting. Keep separate tables when they provide a meaningful benefit.
Problem: Month names appear alphabetically rather than chronologically.
Solution: Select Month Name and choose Sort by column, then select Month Number.
Build a retail sales model using sample transaction data. The project will help you understand how the choice between a star and snowflake schema affects report design.
A retail company wants to analyze revenue, quantity sold, profit, product category performance, customer segments, and monthly sales trends.
The finished report should allow users to filter sales by date, product, customer, and location. Measures should return consistent results across the star and snowflake versions when the underlying business data and filtering requirements are equivalent.
Answer: A star schema is a dimensional model with a central fact table connected directly to dimension tables. It is widely used in business intelligence because its structure is relatively simple to understand and query.
Answer: A snowflake schema is a dimensional model in which dimensions are normalized into multiple related tables, often to represent hierarchies or reduce repeated descriptive attributes.
Answer: In a star schema, dimensions are generally denormalized and connect directly to the fact table. In a snowflake schema, some dimensions are divided into additional related tables.
Answer: It provides clear relationships, simpler filtering paths, easier report authoring, and a logical separation between facts and descriptive dimensions.
Answer: A fact table stores business events or observations, numeric measurements, and foreign keys to dimensions. Examples include sales transactions, inventory snapshots, and financial transactions.
Answer: A dimension table contains descriptive attributes used to categorize, filter, and group facts, such as product category, customer segment, or date.
Answer: Grain defines what one row in a fact table represents. A clearly defined grain helps prevent double counting and inconsistent aggregations.
Answer: It connects a unique key on the dimension side to multiple matching foreign-key values on the fact side.
Answer: No. Its effect depends on the model, source, storage mode, query patterns, and data characteristics. Additional relationships can increase complexity, so performance should be tested.
Answer: Use it when separate hierarchy tables or shared reference attributes provide a meaningful benefit, such as clearer governance or reduced duplication. Avoid extra normalization that makes the semantic model unnecessarily complicated.
Answer: Uniqueness enables the intended one-to-many relationship and ensures that each dimension key identifies one dimension record.
Answer: Power Query imports and transforms source data, cleans columns, standardizes types, removes unnecessary data, and prepares fact and dimension tables for modeling.
Answer: A surrogate key is a generated identifier used to uniquely identify a dimension record independently of the original business key.
Answer: Verify the fact grain, check for duplicate records, validate relationships and filter directions, review DAX calculations, and compare the output against trusted source totals.
Answer: It provides consistent calendar attributes and supports time intelligence, date filtering, period comparisons, and year-to-date calculations.
Star and snowflake schemas are foundational concepts for building reliable Power BI data models. A star schema keeps dimensions relatively simple and connects them directly to a central fact table, making it a strong default for many reporting scenarios. A snowflake schema separates selected dimension attributes into additional tables, which can be useful for complex hierarchies, shared reference data, and governance requirements.
To build an effective model, define the fact table grain, prepare clean dimension keys, configure relationships carefully, create reusable DAX measures, and test the results against trusted business data. Choose between star and snowflake designs based on usability, data structure, maintainability, and measured performance.
Learn more: Explore the Microsoft Power BI Training course and register for a course demo at JustAcademy.
