Building Fact and Dimension Tables
A sales report shows revenue by product category, customer region, and order date. At first, the totals look reasonable. Then someone filters to one region and notices that a few categories no longer agree with the source data. The model refresh completed without an error, the tables loaded, and the report still responds to selections.
Is that enough evidence that the data was modeled correctly? No. A configuration can function technically while representing the business incorrectly. To judge the model, you need to know what each table represents, what one row means, how filters should reach measurable events, and what observable result would prove that the design meets the requirement.
For the PL-300 course blueprint, the published scope includes creating fact tables and dimension tables. That wording points to an applied modeling decision. You’re not merely importing whatever tables happen to arrive from a source.
You’re deciding how those tables should represent business activity inside a Power BI semantic model. The source might provide one wide export, several operational tables, or data whose original organization served a transaction-processing system. None of those structures automatically becomes the best structure for analysis simply because Power BI can load it.
Start with the requested outcome. Suppose a sales organization wants report consumers to analyze sales amounts and quantities by customer, product, date, and region. Sales amounts and quantities describe measurable business activity. Customer names, product categories, dates, and regions provide ways to describe and group that activity.
A star schema separates those roles. Fact tables hold measurable events, while dimension tables hold descriptive attributes. This separation makes the model’s business meaning and filter propagation easier to understand. A report filter such as product category should identify the relevant products and then restrict the sales events associated with them.
That distinction isn’t the same as separating large tables from small ones. A table’s row count doesn’t establish its business role. Imagine a small file containing only twenty unusual sales adjustments. It may be tiny, but each row could still represent a measurable event.
Now imagine a product table containing hundreds of thousands of products and many descriptive attributes. It may be much larger, but its role can still be dimensional because its rows describe products used to categorize events. Size can matter for other design considerations, but it doesn’t answer the fact-or-dimension question. Business role and grain do.
Grain means what one row represents. In a raw sales export, one row might represent an entire order, an individual line on an order, a daily product total, or an adjustment to an earlier sale. Those are different meanings. Before building relationships, state the row meaning in ordinary business language.
If one row represents one order line, then quantity and sales amount belong to that order-line grain. Customer, product, and order date values on the row describe that event. If you can’t say what one row means, you can’t reliably decide which measurements belong there or how another table should relate to it.
The first important layer decision is between transformation and modeling. Power Query is Power BI’s data acquisition and transformation environment, and M is its formula language. If a raw export mixes sales events with repeated customer and product descriptions, Power Query can shape query output into tables suitable for the model. The semantic model then gives those tables analytical roles and relationships.
A report visual doesn’t perform that restructuring merely by displaying category and sales amount together. A service permission doesn’t do it either. Those layers can affect presentation or access, but they don’t replace the work of shaping and modeling facts and dimensions.
Consider a raw table called Sales Export. Each row represents an order line. It contains an order number, order date, customer code, and customer name. Customer region, product code, product name, and product category add descriptive detail.
Quantity and sales amount describe the event. The repeated names and categories may make the export convenient to read, but repetition doesn’t clarify the analytical roles. The event is the order line.
Quantity and sales amount measure that event. The customer and product fields describe entities associated with it. The analyst can therefore work toward a sales fact table, a customer dimension, a product dimension, and an appropriate way to represent date descriptions.
This isn’t a rule that every text column must leave the fact table or that every numeric column must be a fact. A customer code may be numeric in the source, yet its business purpose is identification, not measurement. Adding customer codes together wouldn’t produce a meaningful sales result. A product category is descriptive even if the source encodes it with a number.
Conversely, a measurable amount belongs with the event it measures, even when it arrives as text that must be corrected during transformation. Data type, business role, and grain answer different questions. Treating them as interchangeable leads to fragile models.
The sales fact table should preserve the event grain needed by the requirement. If the business needs order-line analysis, collapsing the data to one row per order would remove product-line detail. If the business only has daily summarized sales, pretending that each row is an individual order line would invent detail that the source doesn’t contain.
Creating facts and dimensions is therefore not just splitting columns into several named tables. The resulting tables must continue to represent the available business events honestly. The requested analysis and the source grain have to agree.
Now follow a category filter through the intended design. The consumer selects a product category. That category describes products, so it belongs with the product dimension’s descriptive attributes. The model uses the relevant relationship to propagate the filter from product descriptions toward the sales events.
The resulting sales amount should reflect only events associated with products in that category. The fact table supplies the measurements, and the dimension supplies the grouping or filtering context. That understandable path is one of the practical benefits of separating measurable events from descriptive entities.
Compare that with an improvised model in which several raw tables are connected before anyone determines their grain. One table might have one row per product. Another might contain repeated product rows by warehouse. A third might hold order lines.
If an analyst sees a shared product code and immediately creates relationships, the model may load and a normal total may even appear correct. Under a warehouse or category filter, however, the result may no longer match the intended business question. The shared column name didn’t establish compatible roles or grains. Relationships should follow classification, not substitute for it.
Suppose the initial regional report is wrong only when a particular product category is selected. Changing the visual type won’t correct a modeling problem. Renaming a field won’t change how filters reach events. Reformatting the sales measure may make the number look different without changing its value.
Those actions operate near the symptom, not necessarily at its cause. Reproduce the failing condition first. Record the expected regional and category combination, then compare it with the actual result. That gives you a specific behavior to investigate instead of a vague complaint that the report seems inconsistent.
Next, inspect the layer that owns the behavior. Does the query output produce one row per intended event? Do the customer and product outputs contain the descriptive entities the model expects?
Do the model relationships connect the intended roles? Does the DAX result respond to the filter context as required? Does the report interaction actually apply the selected filter?
Is a refresh or access state causing someone to consume different data than expected? These checks aren’t interchangeable. A good query output can still feed an unsuitable model, and a sound model can still appear wrong if the report interaction being tested doesn’t represent the expected state.
For the sales export, begin with evidence from the transformed query output. Inspect whether the proposed fact output still has the expected order-line grain. Compare representative order numbers and product codes with the raw input. Check that the event’s quantity and sales amount remain associated with the right row.
Then inspect the proposed dimensions. A product dimension should represent products and their descriptive attributes, while a customer dimension should represent customers and theirs. The purpose isn’t to make every output table look tidy. It’s to confirm that each output has a coherent business role before it enters the relationship design.
A successful refresh proves that Power BI completed the refresh process. It doesn’t, by itself, prove that the tables express the right grain or that report filters produce the right business result. Likewise, creating a relationship without an interface error only proves that the configuration was accepted.
It doesn’t prove that the relationship matches the meaning of the rows. Validation has to reach the behavior you care about. If the requirement is sales by category and region, then category and region filters, together and separately, need to produce explainable sales results.
Use known cases for that validation. Choose a product whose category is known and a customer whose region is known in this hypothetical dataset. Confirm that their sales events are present in the fact output. Then test the category alone, the region alone, and the combination.
Compare the report or DAX result with an expected value derived from the relevant event rows. You’re not merely asking whether a number appears. You’re asking whether the correct descriptive filter reached the correct set of measurable events. That is testable evidence of the model’s intended semantics.
An edge case is especially useful because ordinary cases can conceal a mistake. Suppose most customers have sales in only one region, but one source record has a missing region. Or suppose one product appears in a rarely used category. The goal isn’t to invent a convenient answer for missing data.
It’s to observe whether the transformed outputs and model behavior match the stated requirement under that condition. If the expected treatment hasn’t been defined, that’s a business requirement gap. Silently forcing the row into a familiar category would make the model look complete while weakening its meaning.
If the failure appears only after refresh, return to the responsible layer rather than assuming the report itself changed. Compare the refreshed query output with the prior expectation. Did the incoming rows still represent the same kind of event? Did the descriptive outputs still identify the entities required by the model?
Then retest the relationships, calculations, and report filters that depend on those outputs. Correctness is a chain. Source and query output support the semantic model, and the semantic model supports calculations and report behavior. A failure later in the chain can originate earlier, but each possibility should be tested rather than guessed.
This also explains why there isn’t one universally correct button or setting for creating facts and dimensions. One source may already deliver a clear event table and separate descriptive tables. Another may deliver one repeated export that needs transformation.
A third may provide summarized events at a grain different from what a stakeholder initially assumed. The required business analysis, the real grain, and the roles of the tables determine the design. Familiarity with a feature can help you implement a decision, but it can’t make the decision for you.
Keep DAX in its proper role as well. A DAX calculation can evaluate measurements in the model’s filter context, but a calculation isn’t a substitute for deciding what the underlying rows mean. If the fact table mixes incompatible grains, a polished calculation may still produce a misleading result.
If the dimension used by a report doesn’t describe the intended business entity, changing calculation syntax doesn’t repair that classification. Check the rows, roles, and relationships first. Then validate that the calculation responds to the resulting context as the requirement expects.
The same boundary applies to report design. A report can present a category slicer, a regional chart, and a total sales card, but those objects consume model behavior. They don’t prove that the behavior is correct. A visually plausible chart can conceal duplicated, omitted, or incorrectly filtered events.
Test the values behind the presentation with controlled filters. If the values disagree with known event rows, trace the filter path and the table roles. Don’t keep adjusting colors, labels, or interactions in the hope that a modeling discrepancy will disappear.
Service state belongs in the investigation only when it can explain the observed condition. If one consumer sees different results after a refresh or under a particular access state, verify that condition directly. But don’t begin by changing service governance when the query output itself contains the wrong rows. In the other direction, don’t rebuild fact and dimension queries when the semantic model behaves correctly and the discrepancy only occurs for a particular consumer state. Identify where expected behavior first diverges from actual behavior, then work at that layer.
After a correction, rerun both the failing case and the original normal case. Suppose the analyst discovers that product descriptions were drawn from an output whose rows represented product-by-warehouse combinations, not products alone. The correction must be judged by behavior, not by the fact that the revised model saved successfully. Does the unusual category now return the expected event rows?
Do ordinary categories still return their prior correct totals? Does a region filter still narrow the same sales events? A fix that repairs one selection while disturbing another hasn’t fully satisfied the requirement.
Documenting the reasoning can be simple and precise. State that one sales fact row represents one order line. State that product and customer dimensions supply the descriptive attributes used to group and filter those events. State which transformed outputs and relationships support that behavior.
Then record the known filters and expected results used for validation. This explanation is more valuable than saying the model uses a star schema without describing what the star represents. It connects the architecture to business meaning and gives another analyst a way to check the work.
That habit matters when a task spans several parts of Power BI. A raw source problem may need correction in Power Query. A table-role or relationship problem belongs to the semantic model. A calculation problem must be evaluated in DAX context.
A presentation problem may live in report interactions, while refresh and consumer access can require service-level checks. These layers are connected, but they aren’t interchangeable. Restating the required outcome keeps you from changing a nearby feature simply because it’s familiar.
Return to the original regional sales discrepancy. The model isn’t correct merely because it refreshes, contains relationships, and displays numbers. It’s correct when the tables have explicit business roles, each table’s grain is understood, descriptive filters reach the intended measurable events, and controlled tests produce expected results. If the analyst can show those things for ordinary and edge conditions, the evidence is much stronger than configuration success. That same reasoning also exposes what still needs clarification when the source grain or business treatment isn’t defined.
Creating facts and dimensions starts with meaning, not table size or a convenient relationship. Identify what one row represents, separate measurable events from the descriptive entities used to analyze them, and let that classification guide the semantic model. Then prove the design where its behavior can actually be observed.
Check the transformed outputs, follow controlled filters to the intended event rows, and compare the resulting values with known expectations. A completed refresh or accepted configuration is only an intermediate signal. When the grain, table roles, filter behavior, and test results all support the same business requirement, you have a defensible model rather than one that merely appears to work.
Identify which fields belong in a fact table and which belong in dimensions for a sales model. What filter path and aggregation would you use to test the design? Consult the matching episode reference when you are ready to inspect the terminology and reproduce the verification process. This lesson was produced by Bare Metal Cyber dot com.