Star schema concept in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define the central table in a star schema.

Power BI

FactTable = [1]

Drag options to blanks, or click blank then click option'

Aa table that stores only dates

Ba table that stores only descriptive attributes

Ca table that stores unrelated data

Da table that stores measures and keys

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing fact tables with dimension tables

Thinking dimension tables hold measures

The fact table is the central table in a star schema that stores numeric measures and foreign keys to dimension tables.

2fill in blank

medium

Complete the code to identify a dimension table in a star schema.

Power BI

DimensionTable = [1]

Drag options to blanks, or click blank then click option'

Aa table that stores numeric measures

Ba table that stores only keys

Ca table that stores descriptive attributes

Da table that stores aggregated data

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up dimension tables with fact tables

Assuming dimension tables hold numeric measures

Dimension tables store descriptive information that helps explain the facts, such as product names or customer details.

3fill in blank

hard

Fix the error in the DAX expression to create a relationship between fact and dimension tables.

Power BI

Relationship = CREATE_RELATIONSHIP(FactTable[[1]], DimensionTable[DimensionKey])

Drag options to blanks, or click blank then click option'

AFactKey

BDimensionKey

CMeasure

DDate

Attempts:

3 left

💡 Hint

Common Mistakes

Using the dimension key column from the fact table instead of the foreign key

Using measure columns instead of keys

The fact table uses a foreign key (FactKey) to link to the dimension table's primary key (DimensionKey).

4fill in blank

hard

Fill both blanks to complete the DAX formula that calculates total sales from the fact table.

Power BI

TotalSales = SUM(FactTable[[1]]) + SUM(FactTable[[2]])

Drag options to blanks, or click blank then click option'

ASalesAmount

BDiscountAmount

CQuantity

DCost

Attempts:

3 left

💡 Hint

Common Mistakes

Using quantity or cost instead of sales and discount amounts

Mixing measure and key columns

The total sales can be calculated by summing the sales amount and discount amount columns from the fact table.

5fill in blank

hard

Fill all three blanks to create a calculated column that categorizes sales as High, Medium, or Low based on Quantity.

Power BI

SalesCategory = IF(FactTable[[1]] > 100, "High", IF(FactTable[[2]] > 50, "Medium", "[3]"))

Drag options to blanks, or click blank then click option'

AQuantity

CLow

DSalesAmount

Attempts:

3 left

💡 Hint

Common Mistakes

Using different columns for the two conditions

Putting a numeric value instead of a string label in the last blank

This formula checks the Quantity column twice to assign categories: above 100 is High, above 50 is Medium, else Low.

Practice

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