Bird
Raised Fist0
Power BIbi_tool~20 mins

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

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Challenge - 5 Problems
🎖️
Star Schema Master
Get all challenges correct to earn this badge!
Test your skills under time pressure!
🧠 Conceptual
intermediate
1:30remaining
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?

ADimension table
BBridge table
CLookup table
DFact table
Attempts:
2 left
💡 Hint

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

❓ data_modeling
intermediate
1:30remaining
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?

AOne-to-many from dimension to fact
BOne-to-many from fact to dimension
CMany-to-many between fact and dimension
DOne-to-one between fact and dimension
Attempts:
2 left
💡 Hint

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

❓ dax_lod_result
advanced
2:30remaining
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]))
ACALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Product, Product[Category]))
BSUMX(Product, Sales[Amount])
CSUM(Sales[Amount])
DCALCULATE(SUM(Sales[Amount]), ALL(Product))
Attempts:
2 left
💡 Hint

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

❓ visualization
advanced
2:00remaining
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?

APie chart showing sales amount by Store
BStacked area chart with Date on X-axis and sales amount stacked by Product Category
CTable listing all sales transactions
DScatter plot with Product price vs sales quantity
Attempts:
2 left
💡 Hint

Think about showing changes over time and comparing categories.

🔧 Formula Fix
expert
3:00remaining
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?

AThe relationship between Sales and Product is set as many-to-many instead of one-to-many
BThe relationship between Sales and Date is inactive
CThe Product dimension has duplicate ProductID values
DThe Sales table has missing ProductID values
Attempts:
2 left
💡 Hint

Check uniqueness of keys in dimension tables.

Practice

(1/5)
1. What is the main characteristic of a star schema in Power BI?
easy
A. A single table with all data combined
B. Multiple fact tables connected in a chain
C. One central fact table connected to multiple dimension tables
D. Only dimension tables without any fact table

Solution

  1. Step 1: Understand star schema structure

    A star schema has one main fact table that stores measurable data.

  2. Step 2: Identify dimension tables role

    Dimension tables provide descriptive attributes and connect to the fact table.

  3. Final Answer:

    One central fact table connected to multiple dimension tables -> Option C

  4. Quick Check:

    Star schema = One fact + many dimensions [OK]
Hint: Remember star schema looks like a star with one center [OK]
Common Mistakes:
  • Thinking multiple fact tables form a star schema
  • Confusing dimension tables as fact tables
  • Assuming star schema has no fact table
2. Which of the following is the correct way to describe the relationship in a star schema?
easy
A. One-to-many from dimension tables to fact table
B. Many-to-many between fact and dimension tables
C. One-to-many from fact table to dimension tables
D. One-to-one between fact and dimension tables

Solution

  1. Step 1: Identify relationship direction

    Dimension tables filter many rows in the fact table, so one dimension row relates to many fact rows.

  2. Step 2: Confirm relationship type

    This means a one-to-many relationship from dimension to fact table.

  3. Final Answer:

    One-to-many from dimension tables to fact table -> Option A

  4. Quick Check:

    Dimension to fact = one-to-many [OK]
Hint: Dimension filters many facts, so one-to-many from dimension [OK]
Common Mistakes:
  • Reversing the relationship direction
  • Choosing many-to-many which is not typical in star schema
  • Assuming one-to-one relationships
3. Given a star schema with a Sales fact table and a Product dimension table, what happens if you slice sales by product category?
medium
A. The report shows total sales without any filtering
B. Sales values are grouped by product category from the dimension table
C. Sales values are ignored because categories are not in the fact table
D. An error occurs because dimension tables cannot filter facts

Solution

  1. Step 1: Understand slicing in star schema

    Slicing by product category uses the Product dimension to filter the Sales fact table.

  2. Step 2: Effect on sales values

    Sales are grouped and summed by the selected product categories.

  3. Final Answer:

    Sales values are grouped by product category from the dimension table -> Option B

  4. Quick Check:

    Slicing by dimension groups facts [OK]
Hint: Dimensions slice facts to group data in reports [OK]
Common Mistakes:
  • Thinking dimension filters do not affect fact data
  • Assuming error occurs when slicing by dimension
  • Believing sales values are ignored
4. You created a star schema but your report shows incorrect totals when filtering by a dimension. What is the most likely cause?
medium
A. The report uses a pie chart instead of a bar chart
B. The fact table has duplicate rows
C. The dimension table has too many columns
D. The relationship between fact and dimension tables is inactive or missing

Solution

  1. Step 1: Check relationships in star schema

    Inactive or missing relationships prevent filtering from dimension to fact table.

  2. Step 2: Impact on report totals

    Without active relationships, filters do not apply correctly, causing wrong totals.

  3. Final Answer:

    The relationship between fact and dimension tables is inactive or missing -> Option D

  4. Quick Check:

    Active relationships needed for correct filtering [OK]
Hint: Verify relationships are active to fix filtering issues [OK]
Common Mistakes:
  • Blaming duplicate rows without checking relationships
  • Thinking chart type affects data filtering
  • Ignoring relationship status
5. You want to optimize a Power BI model with sales data. Which star schema design improves query speed and report clarity?
hard
A. One large fact table with keys to small, focused dimension tables
B. Multiple fact tables with duplicated dimension data inside each
C. One fact table with all dimension attributes merged inside it
D. Only dimension tables with no fact table

Solution

  1. Step 1: Understand star schema optimization

    A large fact table with keys to small dimension tables reduces data duplication and improves performance.

  2. Step 2: Avoid poor designs

    Duplicating dimension data or merging all attributes in fact table causes slow queries and confusion.

  3. Final Answer:

    One large fact table with keys to small, focused dimension tables -> Option A

  4. Quick Check:

    Star schema = big fact + small dimensions [OK]
Hint: Keep fact big and dimensions small for best speed [OK]
Common Mistakes:
  • Duplicating dimension data in fact tables
  • Merging all data into one table
  • Removing the fact table entirely
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard