Practice: E-commerce Platform

Here's a high-level dimensional model for the e-commerce platform:

Fact Table:

• Sales_Fact

Dimension Tables:

• Date_Dim
• Product_Dim
• Customer_Dim
• Store_Dim (if applicable for omnichannel sales)
• Campaign_Dim

Fact Table Schema:

Sales_Fact (
   date_key (FK),
   product_key (FK),
   customer_key (FK),
   store_key (FK),
   campaign_key (FK),
   order_id,
   quantity_sold,
   sales_amount,
   discount_amount,
   cost_amount,
   profit_amount
)

Additional tables:

• Inventory_Snapshot_Fact (for daily inventory levels)

Explanation

Core Business Process:

1. The central event here is a sale transaction. This forms the basis of our main fact table, Sales_Fact.

Granularity:

1. The grain of the Sales_Fact table is at the individual product level within each order. This allows for maximum flexibility in analysis, supporting both detailed and aggregated views.
2. Dimensions:
   • Date_Dim: Supports time-based analysis and includes attributes like day, month, quarter, year, holiday flags, etc.
   • Product_Dim: Contains product attributes including category, which is crucial for category-level analysis.
   • Customer_Dim: Stores customer information for segmentation and customer-centric analysis.
   • Store_Dim: If applicable, for analyzing sales across different stores or channels.
   • Campaign_Dim: To link sales with marketing campaigns for effectiveness evaluation.
3. Facts:
   • quantity_sold: Supports volume analysis.
   • sales_amount: For revenue analysis.
   • discount_amount: To analyze the impact of discounts.
   • cost_amount and profit_amount: For profitability analysis.
4. Slowly Changing Dimensions:
   • Product_Dim might be treated as a Type 2 SCD to track changes in product categories or prices over time.
   • Customer_Dim could be a Type 2 SCD to track changes in customer attributes for accurate historical analysis.
5. Additional Considerations:
   • Inventory_Snapshot_Fact: A periodic snapshot fact table to track daily inventory levels. This supports the requirement to monitor product inventory levels.
   • The Sales_Fact table at the order line level allows for easy calculation of AOV (by aggregating to the order level).
   • CLV can be calculated by aggregating customer purchases over time.
   • The Campaign_Dim allows for linking sales to specific marketing campaigns, supporting campaign effectiveness analysis.

This model supports all the required analyses:

• Daily sales totals by category can be calculated by aggregating Sales_Fact and joining with Product_Dim.
• Customer purchase history and segmentation can be analyzed using Sales_Fact and Customer_Dim.
• AOV and CLV can be calculated from Sales_Fact.
• Inventory levels are tracked in Inventory_Snapshot_Fact.
• Marketing campaign effectiveness can be evaluated by analyzing Sales_Fact in conjunction with Campaign_Dim.

The model balances the need for detailed data (supporting complex analyses) with performance considerations (using aggregate tables for common queries). It's also flexible enough to accommodate future requirements, such as adding new product attributes or campaign types.