Star Schema Design for E-commerce in Power BI
You are developing a Power BI semantic model for an e-commerce company. The source data comes from a CSV file containing order details: OrderID, OrderDate, CustomerID, ProductID, Quantity, UnitPrice, Discount, and ShippingCost. The file is updated daily. You need to model the data to support the following analyses: 1) Total sales amount (Quantity * UnitPrice - Discount) by product and month. 2) Average shipping cost per order by customer region (CustomerRegion is in a separate table). 3) Year-over-year comparison of sales. You need to create the measures and ensure optimal performance. What should you do?
Quick Answer
The correct approach is to import both tables into Power BI, create a date table, and build measures using SUMX and time intelligence functions. This establishes a star schema design for e-commerce analytics, where the OrderDetails fact table links to a CustomerRegion dimension table, enabling efficient filtering and aggregation. By using measures rather than calculated columns, you avoid bloating the model storage and ensure optimal performance for dynamic calculations like total sales amount (Quantity * UnitPrice - Discount) and average shipping cost per order by customer region. On the PL-300 exam, this scenario tests your understanding of star schema fundamentals and measure creation—a common trap is reaching for calculated columns or DirectQuery, but CSV files require import mode, and flattening tables duplicates data. Remember the memory tip: “Import and relate, don’t calculate and duplicate” to keep your model lean and fast for year-over-year comparisons using time intelligence.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Import both tables into Power BI, create a date table, and build measures using SUMX and time intelligence.
Importing both tables into Power BI and creating a star schema with a central fact table (Orders) and dimension tables (CustomerRegion, Date) enables efficient measure calculation. The measures can use SUMX to compute sales amount (SUMX(Orders, Orders[Quantity] * Orders[UnitPrice] - Orders[Discount])) and average shipping cost per order filtered by region via relationships. A separate date table is required for time intelligence functions like SAMEPERIODLASTYEAR for year-over-year comparison. Option A is suboptimal because adding calculated columns in Power Query increases storage and processing time; measures are preferable. Option B creates a flat denormalized table which duplicates customer region data, leading to larger model and slower performance. Option C is incorrect because DirectQuery is not supported on CSV files; Power BI requires data import or a connection to a database. Therefore, Option D is the best approach.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use Power Query to add a calculated column for sales amount and shipping cost per order, then import.
Why it's wrong here
Calculated columns increase model size and refresh time.
- ✗
Create a single table by appending the customer region to each row in the CSV using Power Query, then import.
Why it's wrong here
This creates a denormalized table with data duplication.
- ✗
Use DirectQuery on the CSV file to avoid storing data in Power BI.
Why it's wrong here
DirectQuery is not supported for CSV files.
- ✓
Import both tables into Power BI, create a date table, and build measures using SUMX and time intelligence.
Why this is correct
This leverages in-memory engine and efficient DAX.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 217 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are designing a Power BI semantic model for an e-commerce company. You have a fact table with OrderID, CustomerID, OrderDate, and SalesAmount. You also have a Customers table with CustomerID, CustomerName, and CustomerSegment. You need to ensure that filters on CustomerSegment propagate to the SalesAmount measure. What should you do?
easy- A.Use the LOOKUPVALUE function in a calculated column.
- ✓ B.Create a one-to-many relationship from Customers[CustomerID] to Sales[CustomerID].
- C.Merge the Customers and Sales tables into a single table in Power Query.
- D.Create a snowflake schema by adding a separate Segment table.
Why B: Creating a one-to-many relationship between Customers[CustomerID] and Sales[CustomerID] (the fact table) enables filters on CustomerSegment (a column in the Customers table) to propagate to the SalesAmount measure. This is the standard star schema design in Power BI. Option A is incorrect because LOOKUPVALUE is used to retrieve a single value and does not create a relationship for filter propagation; it would not allow filters on CustomerSegment to affect the SalesAmount measure. Option C is incorrect: merging tables in Power Query would create a denormalized flat table, which may cause data duplication and is not necessary when a relationship suffices. Option D is incorrect because adding a separate Segment table introduces a snowflake schema, which is unnecessary here; the current design with Customers containing CustomerSegment is sufficient.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.