Star schema concept in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Identify the central table in a star schema

In a star schema, which table acts as the main table that connects to all other tables?

A Dimension table

B Bridge table

C Lookup table

D Fact table

💡 Hint

Think about which table holds the measurable data like sales or quantities.

The fact table is the central table in a star schema. It contains the numeric data and keys that link to dimension tables.

❓ data_modeling

intermediate

Choose the correct relationship type in a star schema

What type of relationship typically exists between the fact table and each dimension table in a star schema?

A One-to-many from dimension to fact

B One-to-many from fact to dimension

C Many-to-many between fact and dimension

D One-to-one between fact and dimension

💡 Hint

Consider which table has unique keys and which has repeated keys.

Dimension tables have unique keys and connect to many rows in the fact table, so the relationship is one-to-many from dimension to fact.

❓ dax_lod_result

advanced

Calculate total sales by product category using DAX

Given a star schema with a fact table 'Sales' and a dimension table 'Product' with a 'Category' column, which DAX measure correctly calculates total sales amount by product category?

Power BI

Total Sales by Category = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Product, Product[Category]))

A CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Product, Product[Category]))

B SUMX(Product, Sales[Amount])

C SUM(Sales[Amount])

D CALCULATE(SUM(Sales[Amount]), ALL(Product))

💡 Hint

Think about keeping the filter on category while removing other filters.

Option A uses ALLEXCEPT to keep the filter on Product[Category] while summing sales amount, correctly calculating total sales by category.

❓ visualization

advanced

Best visualization for star schema sales data

You have sales data in a star schema with dimensions for Product, Date, and Store. Which visualization best shows total sales trends over time by product category?

A Pie chart showing sales amount by Store

B Stacked area chart with Date on X-axis and sales amount stacked by Product Category

C Table listing all sales transactions

D Scatter plot with Product price vs sales quantity

💡 Hint

Think about showing changes over time and comparing categories.

A stacked area chart with Date on the X-axis and sales stacked by product category clearly shows trends over time and category comparisons.

🔧 Formula Fix

expert

Identify the error in star schema relationship setup

You created relationships in Power BI star schema: fact table 'Sales' linked to 'Product' dimension on ProductID, and to 'Date' dimension on DateKey. But your sales by product report shows incorrect totals. What is the most likely cause?

A The relationship between Sales and Product is set as many-to-many instead of one-to-many

B The relationship between Sales and Date is inactive

C The Product dimension has duplicate ProductID values

D The Sales table has missing ProductID values

💡 Hint

Check uniqueness of keys in dimension tables.

Duplicate keys in the Product dimension break the one-to-many relationship, causing incorrect aggregation in reports.