This Power BI training session focused on building a structured data model using the Northwind Traders dataset. After preparing the source data in Power Query, participants moved into Power BI's Model View to identify fact and dimension tables, establish relationships, and create a dedicated date table for time-based analysis.
The session provided practical experience in moving from cleaned datasets to a properly connected data model — an important step before creating DAX calculations and interactive reports.
Preparing the Northwind Dataset
The session began with importing the Northwind Traders CSV files into Power BI Desktop.
The datasets included tables such as:
-
Categories
-
Customers
-
Employees
-
Orders
-
Order Details
-
Products
-
Shippers
Participants reviewed each table for basic data quality issues before moving into the modeling stage.
For example, the Customers table required the first row to be promoted as headers. The Employees table also contained a null value related to an employee's reporting structure. After reviewing the context, the team determined that the null value was acceptable and did not require modification.
This demonstrated an important data preparation principle: not every null value represents an error. Data should be reviewed based on its business meaning before deciding whether it needs to be changed.
Creating Gross Sales and Net Sales
The session also revisited the calculations required for sales analysis.
Gross Sales
Gross sales were calculated using:
Gross Sales = Quantity × Unit Price
This calculation provides the sales value before applying discounts.
Net Sales
Net sales were calculated using:
Net Sales = Gross Sales × (1 − Discount)
The instructor explained why the discount is included within the (1 − Discount) calculation. Since the discount is represented as a percentage, the formula calculates the portion of the gross sales amount that remains after the discount is applied.
These calculations provide the foundation for later DAX-based analysis and dashboard metrics.
Calculating Shipping SLA
The class also worked on refining shipping-related information by calculating the Shipping SLA using the required date and ship date.
The objective was to understand the difference between the expected shipping date and the actual shipping date.
During the exercise, the team encountered a software version issue that affected access to certain date subtraction functionality. They also addressed null values in the delivery-related data by replacing them with "Not Delivered" and changing the resulting field to the appropriate text data type.
This exercise demonstrated how data preparation decisions can directly affect the quality of later analysis.
Moving from Power Query to Model View
Once the data preparation was complete, participants used Close & Apply to load the transformed data into Power BI.
The class then moved into Model View, where the focus shifted from transforming individual tables to understanding how those tables work together.
Participants reviewed:
-
Table relationships
-
Cardinality
-
Cross-filter direction
-
Primary and foreign keys
-
Fact and dimension tables
-
Relationship structure
The team removed the automatically generated relationships and started the modeling process again manually.
This was emphasized as an important modeling practice because automatically suggested relationships should always be reviewed rather than accepted without validation.
Understanding Fact and Dimension Tables
A major focus of the session was distinguishing between fact tables and dimension tables.
Fact Tables
Fact tables record business events or transactions.
In the Northwind dataset, examples include:
-
Orders
-
Order Details
These tables contain multiple records representing transactions and activities that occur over time.
Dimension Tables
Dimension tables provide descriptive information used to analyze those transactions.
Examples include:
-
Customers
-
Employees
-
Products
-
Categories
-
Shippers
Dimension tables generally contain reference information that changes less frequently than transactional data.
Together, these tables create the structure required for effective reporting and analysis.
Related Offerings
Creating Relationships Between Tables
The team then manually established relationships between the tables.
The primary focus was on many-to-one relationships, where multiple records in a fact table connect to a single corresponding record in a dimension table.
For example:
Customers → Orders
One customer can have multiple orders, while each order belongs to a specific customer.
Similarly:
Products → Order Details
A product can appear across many order-detail records.
The relationship between Orders and Order Details was also established using the Order ID.
This allows Power BI to connect order-level information with individual products, quantities, prices, and discounts associated with each order.
Why Keys Matter in Power BI
The session reinforced the importance of using appropriate keys when creating relationships.
A primary key uniquely identifies a record within a table, while a foreign key references that identifier from another table.
For example:
-
Customer ID identifies a customer in the Customers table.
-
Customer ID in the Orders table connects each order to its customer.
-
Order ID identifies an order and connects it to the corresponding Order Details records.
Using consistent key fields creates reliable connections between tables and allows Power BI to filter and aggregate information correctly.
Building the Date Table
The team also created a dedicated Date Table using DAX.
A dedicated date table is important for time-based reporting because it provides a consistent calendar structure that can be used across different calculations and visualizations.
The date table was then marked as a Date Table within Power BI.
This prepares the model for future analysis such as:
-
Year-over-year comparisons
-
Monthly sales trends
-
Quarterly analysis
-
Year-to-date calculations
-
Month-to-date calculations
-
Time-based DAX measures
The date table will become particularly important as participants move into the next stage of the course.
The Completed Data Model
By the end of the session, participants had established a structured model containing the major Northwind tables:
Orders and Order Details
→ Transactional / Fact Tables
Customers, Employees, Shippers, Products, and Categories
→ Dimension Tables
Date Table
→ Time Intelligence Dimension
The resulting structure provides a strong foundation for building analytical reports and applying DAX calculations.
Key Power BI Data Modeling Lessons
The session reinforced several important modeling principles:
1. Clean Before You Model
Data quality issues should be addressed before relationships and calculations are built.
2. Understand the Business Meaning of Data
A null value is not automatically an error. Its meaning should be evaluated before changing it.
3. Separate Facts from Dimensions
Understanding which tables contain transactions and which contain descriptive information is fundamental to building a reliable model.
4. Use Keys to Build Relationships
Relationships should be based on appropriate identifiers rather than descriptive fields such as names.
5. Review Automatic Relationships
Power BI can suggest relationships, but they should be reviewed and validated against the actual business structure.
6. Use a Dedicated Date Table
A proper date table provides a consistent foundation for time-based analysis and DAX time-intelligence calculations.
Preparing for DAX
With the data model now established, participants are ready to move into the next stage of their Power BI learning journey: DAX.
The upcoming class will build on the model created during this session and introduce calculations and measures that can be used to analyze the Northwind data.
After completing the DAX module, participants will be able to apply these calculations to Power BI visualizations and begin developing more advanced analytical reports.
What Participants Learned
By the end of this session, participants practiced:
-
Importing and reviewing multiple CSV datasets
-
Cleaning and preparing Power BI tables
-
Handling null values
-
Creating gross and net sales calculations
-
Calculating shipping-related metrics
-
Identifying fact and dimension tables
-
Understanding primary and foreign keys
-
Creating many-to-one relationships
-
Managing cardinality and filter direction
-
Manually building a Power BI data model
-
Creating a DAX-based date table
-
Marking a table as a Date Table
-
Preparing a model for DAX and visualization
Looking Ahead
The data modeling stage establishes the foundation for everything that follows in Power BI.
With the Northwind Traders dataset cleaned, structured, and properly connected, participants are now ready to explore DAX calculations and measures in the next class.
From there, the learning journey will progress toward data visualization and interactive dashboard development, where the completed model will be transformed into meaningful business insights.
