BenchMark Quizzes by MDFT Pro

Design a Star Schema with Minimal Development

You work for MDFT Pro, a well-known training agency that offers certification courses across multiple product categories. Mark, a Data Warehouse Developer at MDFT Pro, manages a Fabric warehouse called CourseAnalytics that contains staging tables with course and sales data. The warehouse has four staging tables: ProductCategory (containing categories like “IT Certifications” and “Business Skills”), ProductSubcategory (containing subcategories like “Azure” and “Microsoft 365”), Product (containing individual course details), and SalesOrder (containing transaction records). Business analysts frequently run queries that join these product-related tables together to analyze sales by category, subcategory, and individual course. Mark needs to implement a star schema design that will make these analytical queries easier to write and faster to execute while minimizing the development effort required to transform the staging tables into the final dimensional model.

What should Mark do with the ProductCategory, ProductSubcategory, and Product tables when implementing the star schema?

Choose the correct answer from the options below.

Explanations for each answer:

Add to the model as individual tables is incorrect. Keeping ProductCategory, ProductSubcategory, and Product as separate tables creates a snowflake schema rather than a star schema. This approach requires multiple joins during queries, increasing query complexity and reducing performance. It also requires more development effort to maintain relationships between tables.
Denormalize into a single product dimension table is correct. Denormalizing ProductCategory, ProductSubcategory, and Product into a single product dimension table is the correct approach for a star schema. This minimizes development effort by creating a simple one-to-many relationship between the dimension and fact table, improves query performance by eliminating multiple joins, and simplifies analytical queries by providing all product attributes in one table.
Denormalize by adding to the Sales table is incorrect. Adding product attributes directly to the Sales fact table violates star schema design principles and creates significant data redundancy. This approach bloats the fact table with repeated dimension data, makes updates difficult when product information changes, and increases storage costs without providing the benefits of a proper dimensional model.

Learn more about modeling dimension tables:

Dimension Table Modeling

Practice Exam

Design a Star Schema with Minimal Development

Discuss this question on social media: