Recommended Free Tools
Start a dimensional warehouse by declaring what one row in each fact table represents. Then choose dimensions that make those facts easy to filter and group, and decide—attribute by attribute—whether changes should overwrite old values or preserve history. A star schema is usually the clearest starting point; snowflaking and slowly changing dimension (SCD) strategies address specific modeling needs rather than serving as automatic upgrades.
What is a star schema?
A star schema organizes analytical data around fact tables and dimension tables. A fact table records measurements at a declared grain—the precise business event or state represented by one row. Dimension tables describe the entities and attributes used to filter, group, sort, and summarize those facts.
As an Amazon Associate I earn from qualifying purchases.
For example, a sales fact might represent one product line on one order. Its measures could include quantity and sales amount; its dimensions might describe the product, customer, date, and store. The grain determines which keys and measures belong in that fact table. Microsoft Learn describes star schemas as suited to analytical query workloads involving filtering, grouping, sorting, and summarizing. A warehouse can contain several fact tables and therefore several related stars. Microsoft’s dimensional-modeling overview explains the roles of facts and dimensions.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsWhy declare grain first?
Grain is the contract that makes aggregation meaningful. If rows in a fact table represent different levels of detail, the same measure may be counted inconsistently or joined to dimensions at incompatible levels. State the grain in business terms before choosing keys or loading data, and keep it consistent within that fact table.
#1 Best Overall
- Used Book in Good Condition
What is a snowflake schema, and how does it differ?
A snowflake dimension divides one business dimension into multiple normalized, related tables. A product dimension, for instance, might be split into product, subcategory, and category tables. In a star, those hierarchy attributes would commonly be stored together in a denormalized product dimension.
| Design | Where hierarchy attributes live | Practical trade-off |
|---|---|---|
| Star / denormalized dimension | Alongside other attributes in one dimension table | Fewer joins and a more direct model for report authors; hierarchy values may be repeated. |
| Snowflake / normalized dimension | Across related tables in the hierarchy | Reduces repeated hierarchy data, but adds joins and can make the model less straightforward to use. |
Microsoft Learn generally favors denormalized dimensions for usability and query performance, while identifying cases where a snowflake may be appropriate. A well-designed star schema can offer high-performance relational queries because it uses fewer joins and is more likely to have useful indexes; this is Microsoft’s qualitative guidance, not a measured benchmark. See Dimensional Modeling – Microsoft Fabric and Modeling Dimension Tables in Warehouse – Microsoft Fabric.
When might snowflaking make sense?
- A dimension is extremely large, making duplicated hierarchy attributes a material design concern.
- Facts exist at different hierarchy grains and need keys at higher levels.
- You need to track historical changes at a higher hierarchy level, independently of lower-level entities.
Weigh those needs against the extra joins and the experience of the people building reports. In Power BI semantic models, Microsoft notes that a view joining snowflake tables may be needed to expose a denormalized result for hierarchy use. A normalized layout is not inherently better; the right choice depends on the workload and model.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
How do SCD Type 1, Type 2, and Type 3 differ?
An SCD policy determines what happens when descriptive dimension attributes change. Choose the behavior per attribute: a dimension can use different types for different attributes.
Rank #3
| Type | What happens on change | What historical reports show | Useful when |
|---|---|---|---|
| Type 1 | Overwrite the existing attribute value. | Older facts are viewed using the latest value, so historical groupings can be restated. | The prior value is not needed, or the existing value was erroneous and should be corrected. |
| Type 2 | Insert a new dimension row for the changed tracked value and retain the old version. | A fact can remain associated with the dimension version valid when that fact occurred. | Historical context must be preserved and queried. |
| Type 3 | Keep limited prior-value information in dimension attributes rather than creating a full sequence of rows. | Only the limited history represented by those attributes is available; this is not a full audit trail. | A narrowly limited previous-value comparison is sufficient; Microsoft describes this type as less commonly used. |
Microsoft’s guidance on dimension tables and star schemas in Power BI covers these SCD patterns.
Business keys and surrogate keys are different
A business key identifies the real-world entity in the source, such as a customer number. A surrogate key is a warehouse-assigned identifier for a dimension row. With Type 2, each version needs its own surrogate key, while the business key connects all versions of the same entity. Facts use the version-specific dimension key when historical attributes must remain applicable to the event.
Rank #4
How to load a Type 2 dimension
- Match incoming staged records to dimension entities using their business keys.
- Identify new entities and compare tracked attributes on matched entities to find changes.
- For a changed entity, expire the current dimension version using the model’s validity dates or current-version indicator.
- Insert a new dimension row with a new surrogate key and validity information, retaining the business key that links it to the same entity.
- Load or resolve fact rows against the dimension version applicable to their event time, according to the warehouse’s effective-date rules.
Microsoft’s dimensional-model loading guidance describes matching dimension rows and Type 1/Type 2 load behavior. Type 2 history is not automatic: if the source does not retain versions, the warehouse’s load process must detect and store changes. Exact SQL, late-arriving-data handling, time-zone rules, and effective-date conventions depend on the implementation and are not prescribed by that overview.
How should you choose a design?
Make two related decisions: how to lay out the dimension and what history each attribute requires. A star with Type 2 dimensions, for example, is entirely possible; snowflaking and SCD types solve different problems.
- For the schema: consider join complexity, query performance, duplicated hierarchy storage, report-author usability, facts at different hierarchy grains, and the need to preserve history at a higher hierarchy level.
- For each changing attribute: ask whether previous values must remain queryable, whether corrections should rewrite history, how much prior history is needed, and whether the version keys and validity logic of Type 2 are justified.
- For rapidly changing attributes: do not put them into an SCD by reflex. Microsoft suggests considering a fact-table measure or a separate dimension instead.
For further reading, Microsoft’s dimensional-modeling overview points to The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd edition (2013), by Ralph Kimball and others.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
