Building a Power BI Data Model for ShopKe: Understanding Relationships, Schemas and Joins
Introduction Say your working as a data analyst at ShopKe , a fictional Kenyan e-commerce company selling electronics, fashion and home products. The management team wants to understand sales performance, customer purchasing behavior and product profitability. The company has three datasets namely Customers, Products and Orders. Even though these datasets contain valuable information, they need…
In this article, the author delves into the topic of building a Power BI data model for ShopKe, a Kenyan e-commerce company. The goal is to enable the management team to gain insights into sales performance, customer purchasing behavior, and product profitability. The key datasets involved are Customers, Products, and Orders. Proper organization of these datasets is crucial before creating a Power BI dashboard, and this is where data modelling comes into play.
Data modelling is the process of organizing data into tables and defining relationships between them to facilitate proper analysis. A well-designed data model supports accurate reporting and DAX calculations, enhanced query performance, scalability, easier report development, and improved readability and maintainability. In the context of ShopKe, a flat table, star schema, and snowflake schema are discussed as different data modelling approaches.
A flat table stores all information in a single table, which is simple and easy to understand. However, it suffers from redundancy and may become difficult to maintain as the business grows. A star schema consists of a central fact table connected to multiple dimension tables, which is simple to understand, supports readable DAX, and reduces unnecessary duplication.
This schema is well-suited for analytical reporting and can handle sales, finance, inventory, and customer intelligence projects. On the other hand, a snowflake schema, a variation of the star schema, is a type of star schema where dimension tables are further divided into related tables. Although it reduces redundant descriptive information and can support complex hierarchies, it comes with increased model complexity and may make report development less intuitive.
Fact tables and dimension tables are the two primary components of a data model. Fact tables store measurable business events, such as sales transactions in ShopKe's FactSales table. Dimension tables store descriptive attributes used to analyze facts, like customer names, product names, and dates in DimCustomer, DimProduct, and DimDate.
The grain of a fact table refers to what one row represents. For example, one row in FactSales signifies one product line in one customer order, which is essential because an order may contain several product lines. Consequently, counting rows may not equal counting unique orders.
In a star schema like ShopKe's, the FactSales table connects to the dimensions through keys like DimCustomer [CustomerID], DimProduct [ProductID], DimDate [DateKey], and DimLocation [LocationID]. These relationships allow ShopKe to analyze total sales by product, customer, or date. In Power BI, relationships are established using matching columns.
A primary key uniquely identifies a row in a table, while a foreign key references a key in another table. For instance, CustomerID is unique in DimCustomer but may repeat in FactSales, creating a one-to-many relationship. Relationships are categorized as one-to-many (1:*) when one row in a dimension relates to many rows in a fact table.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
