Star Schema Versus Snowflake in the Repository
A team is modeling a retail subject area with a SALES fact table and dimensions for PRODUCT, STORE, and TIME. PRODUCT has a normalized hierarchy: PRODUCT to CATEGORY to DEPARTMENT, each in its own physical table. STORE also references REGION in a separate table. Analysts frequently filter by department name and region name, and they drill from department to category to product. Query performance is acceptable but joins are multiplying: a typical report joins six physical tables. The repository currently exposes every physical table as a separate logical table, so users see CATEGORY and DEPARTMENT as standalone dimensions. The team wants simpler queries and a cleaner presentation layer while keeping the normalized source unchanged.
1. Explain how to present this snowflake as a star in the logical layer, and what happens to the physical joins.
Read the worked answer
Keep the physical layer normalized. In the business model and mapping layer, define PRODUCT as a single logical dimension whose logical table sources include the PRODUCT, CATEGORY, and DEPARTMENT physical tables, joined by their keys. Expose DEPARTMENT_NAME and CATEGORY_NAME as logical columns on that dimension, mapped through the appropriate source. Do the same for STORE with REGION.
The BI Server then generates the necessary joins at query time; the snowflake is hidden from users, who see one PRODUCT dimension and one STORE dimension. Drilling is configured on the logical dimension levels, not on separate logical tables.
Remove CATEGORY and DEPARTMENT from the presentation layer as standalone dimensions, since exposing them invites incorrect cross joins and confuses users. The physical schema remains unchanged, so ETL and source systems are unaffected. Verify that the logical keys and levels are consistent so aggregation and drill behave correctly.
2. What risks arise if the team instead denormalizes the physical tables into a flat PRODUCT table?
Read the worked answer
Physical denormalization moves work into ETL and storage. It can improve join performance and simplify the physical model, but it duplicates category and department attributes on every product row and increases the chance of inconsistent updates. It also requires ETL changes and a reload strategy, which the logical approach avoids entirely.
Because the stated performance is already acceptable, denormalizing adds risk without a clear benefit. The logical star presentation solves the user-facing problem, and the BI Server can still push down efficient joins. Reserve physical denormalization for cases where measured join cost is the bottleneck, and validate with the query log before committing.