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 consume memory and are computed at refresh, whereas the sales and per-order shipping calculations belong in measures evaluated at query time over the star schema. Adding columns is appropriate when a value must be sliced or filtered directly, not for aggregations.
- ✗
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
Flattening CustomerRegion into the fact table duplicates region text on every order row, inflating the model and preventing a proper relationship to the region dimension. Denormalisation suits scenarios needing a single flat export, not a star schema supporting region-level aggregation and year-over-year slicing.
- ✗
Use DirectQuery on the CSV file to avoid storing data in Power BI.
Why it's wrong here
DirectQuery cannot be used against a CSV file; it requires a supported relational source, and it would also push every visual query to that source, harming performance. DirectQuery is the right choice when data must remain in a large source and freshness outweighs query speed.
- ✓
Import both tables into Power BI, create a date table, and build measures using SUMX and time intelligence.
Why this is correct
Importing both tables gives in-memory performance, and a dedicated date table enables time intelligence for year-over-year comparisons. SUMX iterates at row level to compute Quantity * UnitPrice - Discount correctly, satisfying the sales, shipping and YoY requirements.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 524 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: Option B is correct because creating a one-to-many relationship from Customers[CustomerID] to Sales[CustomerID] establishes the Customers table as the lookup (dimension) table and Sales as the related (fact) table, so filter context applied to Customers[CustomerSegment] flows down to the Sales table and affects the SalesAmount measure. This is the standard star-schema filter propagation behavior in Power BI. Option A does not propagate filters; LOOKUPVALUE merely retrieves a value row-by-row in a calculated column and cannot drive cross-table filtering. Option C would work functionally but destroys the dimensional model and is not the recommended design. Option D adds a Segment table but does not by itself create the needed relationship from Customers to Sales, so segment filters would not reach SalesAmount.
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.