DRAFT: This module has unpublished changes.
DRAFT: This module has unpublished changes.

DRAFT: This module has unpublished changes.
  • User Dimension Table (derived from "T_USERS" table from C2C Schema)
  1. user_key(PK) : This indicates the primary key of user dimension table
  2. salutation : This indicates the salutation for each user. This is added as an attribute because BI system should have each data stored separately to facilitate the analyzing process
  3. user_name (NK): This indicates the natural key of the user dimension table
  4. email : This indicates the email of the user
  5. phone_number : This indicates the phone number of the user
  6. address : This indicates the address of the user
  7. city : This indicates the city of the user
  8. state : This indicates the state of the user
  9. zipcode : This indicates the zipcode of the user
  10. is_active : This flag indicates whether the user is active or not
  • Date Dimension Table (Common for C2C and Red Runner) (designed to analyze "Data Range" KPI )
  1.  date_key (PK): This indicates Primary Key for Date Dimension
  2.  date (NK): This indicates Natural Key
  3.  day_of_week: This indicates which day of week it is to support data range query by day name
  4.  calendar_month_name: This indicates the month name that can be used to support month range queries
  5.  calendar_year : This indicates the year and can be used to support yearly query
  6.  fiscal_year_month(YYYY-MM) : This indicates the fiscal year which can be used while calculating the income and profits in the BI system
  7.  holiday_indicator : This flag indicates whether a national holiday falls on this date. This can used to make a decision on whether the frequency of the buses or courier service should be increased or not 
  • Payment Dimension Table (Common for C2C and Red Runner) (designed to analyze "use of gift card" KPI)
  1. payment type_key(PK): primary key for “Payment Type Dimension Table”
  2. payment_type_description: this indicates whether credit card or gift card or department charge is used in this payment.

 

  • Pickup Location Dimension View (C2C) (derived from "LOCATION" table from C2C Schema)
  1. dim_t_pickup_location_key: primary key
  2. location_key(PK): business key
  3. pickup_location_name: this describes the name of the location or the point of interest (POI)
  4. location_city : this column shows the city of the location
  • Dropoff Location Dimension View (C2C) (derived from "LOCATION" table from C2C Schema)
  1. dim_t_dropoff_location_key: primary key
  2. location_key(PK): business key
  3. dropoff_location_name: this describes the name of the location or the point of interest (POI)
  4. location_city : this column shows the city of the location
  • Pickup Location Dimension Table (Red Runner) (applied k-means clustering method on location data from Courier, and derived center point from each cluster)
  1. dim_courier_derived_p_key(PK): primary key
  2. id: business key for pickup location
  3. order_id: order_id associated with this location
  4. p_location: this describes the name of the location or the point of interest (POI)
  5. derived_pickup_location: the derived value of location category of this location record, based on k-means
  • Dropoff Location Dimension Table (Red Runner) (applied k-means clustering method on location data from Courier, and derived center point from each cluster)
  1. dim_courier_derived_d_key(PK): primary key
  2. id: business key for dropoff location
  3. order_id: order_id associated with this location
  4. d_location: this describes the name of the location or the point of interest (POI)
  5. derived_dropoff_location: the derived value of location category of this location record, based on k-means
  • Loyalty Program Dimension Table (C2C) (derived from "T_LOYALTY_PROGRAMS" table from C2C Schema)
  1. loyalty_program_key(PK): primary key for loyalty Program Dimension Table
  2. user_id: the user id of this loyalty program owner
  3. start_date: aggregated loyalty program start date
  4. end_date: aggregated loyalty program end date

 

  • Service Dimension table (Red Runner) (derived from "SERVICE_TYPE " table from Courier Schema)
  1. service_type_key(PK): primary key
  2. service_type_name: name of service type offered by Red Runner
  3. service_type_description: description of service type offered by Red Runner
  • C2C Fact Table
    1. ride_record_key: primary key for this fact table.
    2. user_key: foreign key for “User Dimension Table”, referring to more details about the user for this booking event.
    3. reservation_id: a unique id to identify the reservation (the modification and cancelling of the same reservation would have the same reservation_id).
    4. pickup_date_key: foreign key for “Date Dimension Table”, referring to which date the customer would be pickup.
    5. dropoff_date_key: foreign key for “Date Dimension Table”, referring to which date the customer would be drop-off.
    6. pickup_time: the time of the pickup.
    7. dropoff_time: the time of drop-off.
    8. pickup_location: foreign key for “Location Dimension Table”, referring to where the customer would be pickup.
    9. price_for_ride: foreign key for “Location Dimension Table”, referring to where the customer would be drop-off.
    10. payment_type_key: foreign key for “Payment Type Dimension Table”, indicates whether the customer paid with credit card or gift card.
    11. loyalty_key: foreign key for “Loyalty Program Dimension Table”, indicates the possible type of loyalty program for this ride event.
    12. order_status: the flag to indicate whether this record is a booking record “B”, or a record to modify the previous record “M”, or a record to cancel the previous record “C”, or a record that notes the ride has been taken “T”, or a record that notes the ride hasn’t been taken “N”.
  • When a customer books a ride, a new reservation_id will be generated, the reservation status of this new reservation will be “B”.  
  • When a customer changed the time/location for a booked ride, a record with the same reservation_id but has the new time/location will be append to this table. The reservation status will be “M”.
  • When a customer cancelled a booked ride, a record with the same reservation_id but has the reservation status will be “C” will be added.
  • When a customer has taken the booked ride, a record with the same reservation_id but has the reservation status will be “T” will be added.
  • When a booked ride has passed but the customer doesn’t take it, a record with the same reservation_id but has the reservation status will be “N” will be added.
  • The way to get these records is using “C2C” Schema -> “T_RESERVATION_DETAILS” Table -> “F_STATUS” Column, which has indicators for whether the ride has been cancelled (“C”) or completed (“T”).
  • Also comparing “C2C” Schema -> “T_RESERVATION_DETAILS” Table -> “F_CREATE” Column and “C2C” Schema -> “T_RESERVATION_DETAILS” Table -> “F_CANCELED_DATE” Column, if we see a creation of new record immediately after a cancellation, we would know these two record shall be combined and form a “M” record.
  • Red Runner Fact Table
  1. delivery_record_key: primary key for this fact table record.
  2. user_key: foreign key for “User Dimension Table”, referring to more details about the user for this booking event.
  3. reservation_id: a unique id to identify the reservation (the modification and cancelling of the same reservation would have the same reservation_id).
  4. pickup_date_key: foreign key for “Date Dimension Table”, referring to where the customer would be pickup.
  5. dropoff_date_key: foreign key for “Date Dimension Table”, referring to where the customer would be drop-off.
  6. pickup_time: the time of the pickup.
  7. dropoff_time: the time of drop-off.
  8. pickup_location: foreign key for “Location Dimension Table”, referring to where the customer would be pickup.
  9. price_for_ride: foreign key for “Location Dimension Table”, referring to where the customer would be drop-off.
  10. price_for_ride: the price for the delivery event.  
  11. payment_type_key: foreign key for “Payment Type Dimension Table”, indicates whether the customer paid with credit card or gift card.
  12. service_type_key: foreign key for “Service Type Table”, indicates the type of service for this booking event.
  13. order_status: the flag to indicate whether this record is a booking record “B”, or a record to modify the previous record “M”, or a record to cancel the previous record “C”, or a record that notes the delivery has started “S”, or a record that notes delivery has finished “F”.
  • When a customer books a delivery, a new reservation_id will be generated, the order status of this new reservation will be “B”.  
  • When a customer changed the time/location for a booked delivery, a record with the same reservation_id but has the new time/location will be append to this table. The reservation status will be “M”.
  • When a customer cancelled a booked delivery, a record with the same reservation_id but has the reservation status will be “C” will be added.
  • When the delivery get started, a record with the same reservation_id but has the reservation status will be “S” will be added. This record will has a start_date.
  • When a booked delivery has been finished, a record with the same reservation_id but has the reservation status will be “F” will be added. 
DRAFT: This module has unpublished changes.
Show Comments (0) and Tags