Practice: Ride-sharing Platform
Scenario: You're designing a data model for Uber to analyze ride performance, driver efficiency, and market dynamics.
Requirements:
- Track ride metrics (distance, duration, fare) by city and time
- Analyze driver performance and earnings
- Monitor supply-demand balance in real-time
- Measure the effectiveness of surge pricing
- Analyze user retention and frequency of use
Task: Create a high-level dimensional model to support these requirements.
Identify the core business process. What is the central event that generates most of the required metrics? Think about what happens when a ride is completed.
Consider the granularity of your fact table. At what level of detail do you need to store ride data to support all required analyses?
Identify dimensions. What contextual information is needed to analyze rides? Consider the "who, what, where, when" of the ride process.
Think about slowly changing dimensions. Which entities might have attributes that change over time, and how should these changes be tracked?
Consider additional fact tables. Are there any metrics that might require a different grain or update frequency? Think about real-time monitoring requirements.
Here's a high-level dimensional model for the ride-sharing platform:
Fact Tables:
- Ride_Fact
- Driver_Shift_Fact
- Supply_Demand_Fact
Dimension Tables:
- Date_Dim
- Time_Dim
- Driver_Dim
- Rider_Dim
- Location_Dim
Fact Table Schemas:
Ride_Fact ( date_key (FK), time_key (FK), driver_key (FK), rider_key (FK), pickup_location_key (FK), dropoff_location_key (FK), distance, duration, base_fare, surge_multiplier, total_fare, driver_earnings ) Driver_Shift_Fact ( date_key (FK), time_key (FK), driver_key (FK), location_key (FK), shift_duration, rides_completed, total_earnings ) Supply_Demand_Fact ( date_key (FK), time_key (FK), location_key (FK), active_drivers, active_riders, surge_multiplier )
Explanation
Core Business Process:
- The central event is a completed ride, which forms the basis of our main fact table, Ride_Fact.
- Granularity:
- Ride_Fact: Individual ride grain. This allows for detailed analysis of ride metrics.
- Driver_Shift_Fact: Individual driver shift grain. This supports driver performance analysis.
- Supply_Demand_Fact: Periodic (e.g., hourly) snapshot by location. This allows for real-time supply-demand analysis.
- Dimensions:
- Date_Dim and Time_Dim: Support time-based analysis.
- Driver_Dim: Contains driver attributes for performance analysis.
- Rider_Dim: Stores rider information for retention and usage frequency analysis.
- Location_Dim: Captures geographic information for city-level analysis.
- Facts:
- Ride_Fact: distance, duration, base_fare, surge_multiplier, total_fare, driver_earnings for ride performance analysis.
- Driver_Shift_Fact: shift_duration, rides_completed, total_earnings for driver efficiency analysis.
- Supply_Demand_Fact: active_drivers, active_riders, surge_multiplier for market dynamics analysis.
- Slowly Changing Dimensions:
- Driver_Dim could be a Type 2 SCD to track changes in driver attributes over time.
- Location_Dim might be Type 2 to track changes in city or zone definitions.
- Additional Considerations:
- The Supply_Demand_Fact table allows for real-time monitoring of market conditions.
- Surge pricing effectiveness can be analyzed by comparing surge_multiplier to ride volume and total_fare in the Ride_Fact table.
- User retention and frequency can be derived from the Ride_Fact table by analyzing rider_key and date_key.
This model supports all the required analyses:
- Ride metrics can be analyzed using Ride_Fact joined with Location_Dim and Date_Dim.
- Driver performance and earnings can be analyzed using Driver_Shift_Fact and Ride_Fact.
- Supply-demand balance is captured in Supply_Demand_Fact.
- Surge pricing effectiveness can be analyzed using Ride_Fact and Supply_Demand_Fact.
- User retention and frequency can be derived from Ride_Fact.
The model balances the need for detailed ride data with the ability to perform real-time market analysis. It's also flexible enough to accommodate future requirements, such as new pricing models or driver incentive programs.