Practice: Video Streaming Service

Here's a high-level dimensional model for the video streaming service:

Fact Tables:

• Viewing_Fact
• Recommendation_Fact
• Streaming_Quality_Fact

Dimension Tables:

• Date_Dim
• Time_Dim
• User_Dim
• Content_Dim
• Device_Dim

Fact Table Schemas:

Viewing_Fact (
    date_key (FK),
    time_key (FK),
    user_key (FK),
    content_key (FK),
    device_key (FK),
    viewing_duration,
    completion_percentage,
    is_recommended (0/1)
)
Recommendation_Fact (
    date_key (FK),
    user_key (FK),
    content_key (FK),
    was_clicked (0/1),
    was_watched (0/1),
    recommendation_algorithm
)
Streaming_Quality_Fact (
    date_key (FK),
    time_key (FK),
    user_key (FK),
    content_key (FK),
    device_key (FK),
    buffering_instances,
    average_bitrate,
    stream_start_time
)

Explanation

Core Business Process:

1. The central event is a viewing session, which forms the basis of our main fact table, Viewing_Fact.
2. Granularity:
   • Viewing_Fact: Individual viewing session grain. This allows for detailed analysis of viewing patterns.
   • Recommendation_Fact: Individual recommendation grain. This supports analysis of recommendation effectiveness.
   • Streaming_Quality_Fact: Individual streaming session grain. This allows for detailed quality analysis.
3. Dimensions:
   • Date_Dim and Time_Dim: Support time-based analysis.
   • User_Dim: Contains user attributes for segmentation and preference analysis.
   • Content_Dim: Stores information about content, including genre, creator, and release date.
   • Device_Dim: Captures device information for cross-device analysis.
4. Facts:
   • Viewing_Fact: viewing_duration and completion_percentage for content performance analysis.
   • Recommendation_Fact: was_clicked and was_watched for recommendation effectiveness.
   • Streaming_Quality_Fact: buffering_instances, average_bitrate, and stream_start_time for quality analysis.
5. Slowly Changing Dimensions:
   • User_Dim could be a Type 2 SCD to track changes in user preferences over time.
   • Content_Dim might be Type 2 to track changes in content metadata.
6. Additional Considerations:
   • The is_recommended flag in Viewing_Fact allows for easy filtering of views that came from recommendations.
   • The recommendation_algorithm in Recommendation_Fact allows for comparison of different recommendation strategies.
   • Streaming_Quality_Fact allows for detailed analysis of user experience metrics.

This model supports all the required analyses:

• Viewing time and completion rates can be analyzed using Viewing_Fact.
• User preferences and viewing patterns can be derived from Viewing_Fact and User_Dim.
• Recommendation effectiveness can be measured using Recommendation_Fact and Viewing_Fact.
• Streaming quality is captured in Streaming_Quality_Fact.
• Content performance by various attributes can be analyzed using Viewing_Fact joined with Content_Dim.

The model balances the need for detailed data with performance considerations. It's also flexible enough to accommodate future requirements, such as new content types or recommendation algorithms.