Power BI star schema: define facts and dimensions first

A star schema separates measurable business events from the descriptive tables used to group and filter them. Define the grain of the fact table first, then create dimensions with unique keys and deliberate filter paths. A diagram that resembles a star is not sufficient if the tables contain inconsistent grains or ambiguous identities.

The Power BI retail lab supplies four original synthetic CSVs and independently checked control totals. It does not supply a tested PBIX; build and validate the model in your Power BI environment.

Declare what one fact row means

FactSales has one order line per LineID. Order O1 has two lines, so OrderID is not the table's unique key. Quantity and UnitPricePaise calculate gross line value; DiscountPaise and CostPaise are already line totals.

Net line value is Quantity × UnitPricePaise − DiscountPaise. Do not multiply the line discount or cost by quantity again. Status determines whether the line belongs in the Paid measure population.

Microsoft's star-schema guidance describes the roles of dimensions, facts and consistent fact grain. The fixture makes those roles testable through small, inspectable records.

Create three dimension relationships

Import DimCustomer, DimProduct, DimDate and FactSales. Preserve CustomerID as text; set numeric keys and paise amounts to whole numbers; parse date columns as dates.

Create active one-to-many, single-direction relationships from DimCustomer[CustomerKey] to FactSales[CustomerKey], DimProduct[ProductKey] to FactSales[ProductKey], and DimDate[Date] to FactSales[OrderDate]. The dimension is the one side and filters the fact side.

DimDate covers complete 2025 and 2026 calendar years, although the fixture provides transactions only in January and February 2026. Calendar coverage does not imply that an absent year's business activity was zero.

An optional inactive relationship to DeliveryDate supports a later lesson. Do not make order and delivery dates interchangeable in a measure without naming the intended time role.

Add a measure with explicit eligibility

Paid Net Paise =
CALCULATE(
    SUMX(
        FactSales,
        FactSales[Quantity] * FactSales[UnitPricePaise]
            - FactSales[DiscountPaise]
    ),
    FactSales[Status] = "Paid"
)

The expected unfiltered result is 69,500 paise. The status condition intentionally defines a Paid-only measure; it can replace a filter on the same status column. Do not label it an arbitrary “selected-status sales” measure. Use a separate definition if a status slicer should control eligibility differently.

This DAX is an application-review example. SQLite independently verifies the expected arithmetic, but does not evaluate DAX or prove that your relationships are configured correctly.

Reconcile dimension slices

Place DimProduct[Category] and Paid Net Paise in a table visual. Expect Software 39,000, Training 23,500 and Support 7,000. Their sum is 69,500.

By customer region, expect North 44,500, West 20,000 and Unknown 5,000. South has a pending line but no paid activity. Display behaviour for a no-activity region depends on the visual and measure; do not infer a source defect merely because an empty category is hidden.

The explicit Unknown customer key 0 preserves an unresolved identity while keeping its 5,000 paise in the total. Silently discarding that member would understate the full paid population.

Avoid mixing unrelated grains

A monthly budget is one row per month and budget category, not one order line. Repeating that monthly amount on every sales line would multiply it when summed. Model it as a separate fact with dimensions and measures appropriate to its grain.

Likewise, do not join sales lines directly to several refund events and then sum the original line value without controlling multiplication. A model needs a clear policy for each fact's aggregation and shared dimensions.

Test the model before styling visuals

Verify eight fact rows and 77,500 paise across all statuses, then seven paid lines and 69,500 paid paise. Test January 47,500 and February 22,000 through the date dimension. A correct overall measure with incorrect monthly slices often points to a date relationship or filter issue.

Exercise: add a duplicate CustomerKey to a copy of DimCustomer. Explain why the proposed one-side relationship is no longer valid and why changing cardinality merely to accept the data would not resolve the customer identity problem.

NeuraPath's Data Analytics with Generative AI course connects Power BI modelling with source reconciliation. A strong dashboard starts with a model whose grain, relationships and measure population are independently understandable.

Continue learning

This article is part of the Power BI data models and reporting sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Continue with Power BI relationship cardinality with a duplicate-key example.
• Then apply it in Single versus bidirectional filtering in Power BI.