CSV to Dimension Models Example

Sample Data

https://github.com/gchandra10/filestorage/blob/main/sales_100.csv

Design using https://mermaid.live/

---
title: Sales 100 example
---

erDiagram
  dim_region {
    region_id int
    region_name string
  }
  
  dim_country {
    country_id int
    region_id int
    country_name string
  }
  
  dim_item_type {
    item_type_id int
    item_type_name string
  }
  
  dim_sales_channel {
    sales_channel_id int
    sales_channel_type string
  }

  dim_order_priority{
    order_priority_id int
    order_priority string
  }

  dim_date{
    date_id int
    shortdate date
    day_of_week string
    is_weekend boolean
    day int
    month int
    week_of_month int
    week_of_year int
    quarter string
    year int
    leap_year boolean
  }
    
  fact_order_transaction {
    id int
    order_id int
    country_id int
    item_type_id int
    sales_channel_id int
    order_priority_id int

    order_date_id int
    ship_date_id int
    
    units_sold int
    unit_price float
    unit_cost float
    total_revenue float
    total_cost float
    total_profit float
  }

  dim_month {
    month_id int pk
    month string
    year int
    quarter string
    is_end_of_year boolean
  }

  fact_quarterly_sales {
    id int
    month_id int
    total_units_sold int
    total_revenue float
    total_cost float
    total_profit float
  }

  fact_region_sales{
    id int
    region_id int
    sales_channel_id int
    total_units_sold int
    total_revenue float
    total_cost float
    total_profit float
  }
  
dim_region ||--o{ dim_country : "region_id"
dim_country ||--o{ fact_order_transaction : "country_id"
dim_item_type ||--o{ fact_order_transaction : "item_type_id"
dim_sales_channel ||--o{ fact_order_transaction : "sales_channel_id"
dim_order_priority  ||--o{ fact_order_transaction : "order_priority_id"
dim_date ||--o{ fact_order_transaction : "order_date"
dim_date ||--o{ fact_order_transaction : "ship_date"

dim_month ||--o{ fact_quarterly_sales : "month_id"

dim_region ||--o{ fact_region_sales : "region_id"
dim_sales_channel ||--o{ fact_region_sales : "sales_channel_id"

Design

---
title: Sales 100 example
---

erDiagram
  dim_region {
    region_id int
    region_name string
  }
  
  dim_country {
    country_id int
    region_id int
    country_name string
  }
  
  dim_item_type {
    item_type_id int
    item_type_name string
  }
  
  dim_sales_channel {
    sales_channel_id int
    sales_channel_type string
  }

  dim_order_priority{
    order_priority_id int
    order_priority string
  }

  dim_date{
    date_id int
    shortdate date
    day_of_week string
    is_weekend boolean
    day int
    month int
    week_of_month int
    week_of_year int
    quarter string
    year int
    leap_year boolean
  }
    
  fact_order_transaction {
    id int
    order_id int
    country_id int
    item_type_id int
    sales_channel_id int
    order_priority_id int

    order_date_id int
    ship_date_id int
    
    units_sold int
    unit_price float
    unit_cost float
    total_revenue float
    total_cost float
    total_profit float
  }

  dim_month {
    month_id int pk
    month string
    year int
    quarter string
    is_end_of_year boolean
  }

  fact_quarterly_sales {
    id int
    month_id int
    total_units_sold int
    total_revenue float
    total_cost float
    total_profit float
  }

  fact_region_sales{
    id int
    region_id int
    sales_channel_id int
    total_units_sold int
    total_revenue float
    total_cost float
    total_profit float
  }
  
dim_region ||--o{ dim_country : "region_id"
dim_country ||--o{ fact_order_transaction : "country_id"
dim_item_type ||--o{ fact_order_transaction : "item_type_id"
dim_sales_channel ||--o{ fact_order_transaction : "sales_channel_id"
dim_order_priority  ||--o{ fact_order_transaction : "order_priority_id"
dim_date ||--o{ fact_order_transaction : "order_date"
dim_date ||--o{ fact_order_transaction : "ship_date"

dim_month ||--o{ fact_quarterly_sales : "month_id"

dim_region ||--o{ fact_region_sales : "region_id"
dim_sales_channel ||--o{ fact_region_sales : "sales_channel_id"