Star schema concept in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a business intelligence analyst at a retail company.

📋 Request: Your manager wants you to organize sales data using a star schema to improve reporting and analysis.

📊 Data: You have raw sales transactions data including sales date, product details, store location, and sales amount.

🎯 Deliverable: Create a star schema model in Power BI with one fact table and related dimension tables, then build a simple sales report using this model.

Sample Data

SalesID	SalesDate	ProductID	ProductName	Category	StoreID	StoreName	Region	SalesAmount
1	2024-01-05	101	Notebook	Stationery	10	Central Store	North	15.00
2	2024-01-06	102	Pen	Stationery	11	East Store	East	5.00
3	2024-01-07	103	Stapler	Office Supplies	10	Central Store	North	8.00
4	2024-01-08	101	Notebook	Stationery	12	West Store	West	15.00
5	2024-01-09	104	Marker	Stationery	11	East Store	East	7.00
6	2024-01-10	105	Paper Clips	Office Supplies	12	West Store	West	3.00
7	2024-01-11	102	Pen	Stationery	10	Central Store	North	5.00
8	2024-01-12	103	Stapler	Office Supplies	11	East Store	East	8.00

1

Step 1: Create a fact table named 'FactSales' with columns: SalesID, SalesDate, ProductID, StoreID, SalesAmount.

In Power BI, import the sales data and select only these columns for the fact table.

Expected Result

FactSales table with 8 rows and columns: SalesID, SalesDate, ProductID, StoreID, SalesAmount.

2

Step 2: Create a dimension table named 'DimProduct' with unique ProductID, ProductName, and Category.

Use 'Remove Duplicates' on ProductID from sales data to create DimProduct.

Expected Result

DimProduct table with 5 unique products and their categories.

3

Step 3: Create a dimension table named 'DimStore' with unique StoreID, StoreName, and Region.

Use 'Remove Duplicates' on StoreID from sales data to create DimStore.

Expected Result

DimStore table with 3 unique stores and their regions.

4

Step 4: Create a dimension table named 'DimDate' with unique SalesDate and additional columns for Year, Month, and Day.

Extract unique SalesDate from FactSales and add calculated columns: Year = YEAR(SalesDate), Month = FORMAT(SalesDate, "MMMM"), Day = DAY(SalesDate).

Expected Result

DimDate table with 8 unique dates and Year, Month, Day columns.

5

Step 5: Create relationships in Power BI model: FactSales.ProductID to DimProduct.ProductID, FactSales.StoreID to DimStore.StoreID, FactSales.SalesDate to DimDate.SalesDate.

Use Power BI 'Manage Relationships' to create one-to-many relationships from dimensions to fact table.

Expected Result

Star schema model with FactSales at center connected to DimProduct, DimStore, and DimDate.

6

Step 6: Create a measure 'Total Sales' in FactSales to sum SalesAmount.

Total Sales = SUM(FactSales[SalesAmount])

Expected Result

Measure 'Total Sales' calculates total sales amount correctly.

7

Step 7: Build a report page with a table visual showing Total Sales by Region and Category.

Add DimStore[Region] and DimProduct[Category] as rows, and 'Total Sales' as values in the table visual.

Expected Result

Table shows sales totals grouped by Region and Category.

Final Result

Star Schema Model:

       DimProduct
          |
          |
DimStore--FactSales--DimDate


Report Table:
+---------+----------------+------------+
| Region  | Category       | Total Sales|
+---------+----------------+------------+
| East    | Stationery     | 12.00      |
| East    | Office Supplies| 8.00       |
| North   | Stationery     | 20.00      |
| North   | Office Supplies| 8.00       |
| West    | Stationery     | 15.00      |
| West    | Office Supplies| 3.00       |
+---------+----------------+------------+

✓The East and North regions have the highest total sales.

✓Stationery products generate more sales than Office Supplies.

✓The star schema model simplifies analysis by separating facts and dimensions.

Bonus Challenge

Add a new dimension table 'DimCategory' with unique product categories and link it to DimProduct. Then update the report to show sales by Category and Month.

Show Hint

Create DimCategory with Category column, relate DimProduct[Category] to DimCategory[Category], and use DimDate[Month] in the report.