AdventureWorks OLTP Data Analysis

Overview:

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.

1. Data Extraction:

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)

2. Data Modeling:

• 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.

3. Data Refinement:

• Table and Column Renaming:
  
• Renaming tables and columns for clarity and consistency.
  
• Removing any unused columns to streamline the data model.
  

4. Create DIM|Date using Power Query:

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]))
)

4. Measures:

• 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.
  

5. Data Visualization and Power BI Report:

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.