This documentation guides the process of extracting, modeling, and visualizing data from the AdventureWorks2019 OLTP database using Direct Query mode. The aim is to create a robust Power BI data model with a Star Schema for efficient analysis and then visualize the insights through an interactive Power BI report.
Data Source: AdventureWorks OLTP Database.
Tables to Extract:
a. Sales.SalesOrderHeader
b. Sales.SalesOrderDetail
c. Sales.vSalesPerson (view)
d. Sales.SalesTerritory
e. Purchasing.ShipMethod
f. Production.Product
g. Production.ProductSubcategory
h. Production.ProductCategory
i. Status (Add based on ufnGetSalesOrderStatusText function)
j. Dates (Power Query)
- Star Schema:
Designing a Star Schema with a central fact table (Fact|Order) connected to dimension tables (DIM|Salespersony, DIM|ShipMethod, DIM|Territory, DIM|Product) through primary and foreign key relationships.
- Table and Column Renaming:
- Renaming tables and columns for clarity and consistency.
- Removing any unused columns to streamline the data model.
Date Dimension Creation:
- Using Power Query to create a Date dimension table (DIM|Date) based on the existing Dates table.
- Extracting relevant date-related information (Year, Month, Quarter, etc.) and adding calculated columns.
- Ensuring the Date dimension is properly linked to the central fact table using appropriate relationships.
Number.ToText(Date.Year([Date])) &
(
if Date.Month([Date]) < 10 then
"0" & Number.ToText(Date.Month([Date]))
else
Number.ToText(Date.Month([Date]))
) &
(
if Date.Day([Date]) < 10 then
"0" & Number.ToText(Date.Day([Date]))
else
Number.ToText(Date.Day([Date]))
)
-
No. of Orders: Calculation: Count of unique Sales Order IDs.
-
Total SubTotal Measure: Calculation: Sum of the SubTotal for all sales orders.
-
Total Tax Measure: Calculation: Sum of the Tax for all sales orders.
-
Total Freight Measure: Calculation: Sum of the Freight for all sales orders.
-
Total Due Measure: Calculation: Sum of the TotalDue for all sales orders.
-
Qty: Calculation: Sum of the quantity of products in sales orders.
-
No. of Orders by OrderDate vs. ShipDate vs. DueDate: Visualization Type: Line Chart
Description: Visualizes the number of orders over time based on Order Date, Ship Date, and Due Date. -
No. of Orders by Status: Visualization Type: Pie Chart
Description: Represents the distribution of orders based on their status. -
No. Orders by Shipmethod: Visualization Type: Bar Chart
Description: Illustrates the number of orders using different ship methods. -
No. of Orders by Category, Subcategory, Product: Visualization Type: Treemap or Nested Bar Chart
Description: Hierarchically displays the breakdown of orders by product category, subcategory, and specific products. -
No. of Orders by FlagOnlineOffline: Visualization Type: Stacked Bar Chart
Description: Compares the number of online and offline orders. -
No.of Orders and TotalDue by Territory: Visualization Type: Dual-Axis Bar Chart or Map
Description: Provides a comparison of the number of orders and total due amount across different territories. -
Top 10 Sales Persons (No. of Orders or Total Amount): Visualization Type: Bar Chart
Description: Highlights the top 10 salespersons based on either the number of orders or total sales amount.
After modeling the data, proceed to create an interactive Power BI report incorporating the designed measures and visualizations. I used the Power BI platform to develop engaging dashboards and insightful reports that allow users to explore and understand the AdventureWorks OLTP data. Ensured proper use of colors, chart types, layout, and descriptive titles for a compelling and user-friendly experience.