Data modeling is at the core of any analytics solution in Power BI. It defines how raw data is
connected and structured so that reports and dashboards deliver meaningful, accurate
insights. A well-built model not only ensures correct calculations but also simplifies the process
of analysis for business users.
This blog uses a simple dataset with one fact table and three dimension tables to demonstrate
how relationships are created in Power BI’s Model View. The resulting star schema makes it
possible to analyze orders across customers, products, and time.

Dataset Overview

The dataset is organized into the following tables:
1. Fact Table
• fact_orders
Contains transactional data for orders, including:
o order_id – unique identifier for each order
o order_date – date the order was placed
o customer_id – reference to the customer placing the order
o product_id – reference to the product ordered
o order_qty – quantity ordered
o delivery_qty – quantity delivered

This table represents measurable business activity and forms the center of the data model.

2. Dimension Tables

• dim_customers
o customer_id, customer_name, city
o Provides descriptive attributes about customers to segment orders and sales.
• dim_products
o product_id, product_name, category
o Contains details about products for category-wise and product-level analysis.
• dim_dates
o date, week_number, name_of_day
o Provides a calendar structure to analyze orders by day, week, or other date
attributes.
These dimension tables enrich the fact table by providing context and categorization.

Building the Data Model in Power BI

Once the tables are imported into Power BI Desktop, the next step is to create relationships in
Model View. The following relationships define the structure of the model:

• dim_customers → fact_orders
o Relationship: customer_id
o Each order is linked to the customer who placed it.
• dim_products → fact_orders
o Relationship: product_id
o Each order is connected to the product purchased.
• dim_dates → fact_orders
o Relationship: date to order_date
o Each order is associated with a specific date for time-based analysis.

The result is a star schema, where the fact table (fact_orders) sits at the center and connects
to three surrounding dimension tables.

Why This Model Works?

This star schema design offers several advantages:
• Efficiency – Fact data is separated from descriptive attributes, keeping the model lean.
• Flexibility – Reports can filter and group data by customer, product, or date.
• Accuracy – Relationships ensure that aggregations like total orders or quantities are
calculated correctly.
• Scalability – Additional dimensions (such as regions or suppliers) can be added later without disrupting the structure.

Example Use Cases

With this model, analysis becomes straightforward. Some examples include:
• Tracking total orders by product category.
• Analyzing which cities generate the highest order volume.
• Understanding order trends across days of the week.
• Comparing ordered quantity versus delivered quantity.

A strong model is the foundation for effective data visualization, and even with just four
tables, it is possible to unlock valuable insights into customer behavior, product performance,
and order trends.