Data Modeling Project

Overview

This data modeling project, executed within Power BI, involved the creation of a comprehensive sales data model using the STAR Schema.

The project encompassed the import and cleansing of sales data, the development of dimension tables, and the creation of relationships between these tables.

Using the new data model, It will be easier to analyze the data and provide actionable insights. 


Starting dataset

Conceptual Model 

I took the following steps to transform the starting dataset to the conceptual model:

Step 1. Import Sales Data into Power BI: 

The first step I took is importing the sales data into Power BI. During this crucial step. I did the first part of data cleaning by changing & correcting data types. Having the correct data types is essential for ensuring accurate and efficient data processing, as they determine how data is stored, manipulated, and interpreted by Power BI

Step 2. Use Power Query to Create Dim Tables: 

I created four distinct tables from the FactSales table. Using the relevant columns from the FactSales table I created these four dim tables: DimGeography, DimProduct, DimCustomer, and DimCatSeg. Since these came from FactSales there is some data cleaning I needed to conduct such as removing duplicates and separating text within columns.

Step 3. Create Data Table Through Power Query  

I created the DimDate table through the Power Query Editor. I used this resource to create this table. A date dimension table is crucial in Power BI because it facilitates data filtering, hierarchical analysis, time-based calculations, and the centralized tracking of various types of dates like holidays.

Step 4: Create Relationships

Using keys carried over from the FactSales table to the Dimensional tables I created relationships linking to these tables. There was no key for the DimCatSeg so I had to create a surrogate key in DimProduct using a merge query within Power Query. 

Step 5: Create DAX Measures

I created DAX measures using the newly created model to ensure it is working properly. These DAX measures include:

Data Model 

DAX Measures