Data Model for Video Streaming Platform
Creating a data model for video streaming platforms
Let’s build out another data model for a video streaming platform. I have written another article that explores the topics for a Ride/Cab Platform in this article. We will follow Kimball’s Dimensional Designing steps to create it.
Summarizing the Dimensional Modelling Technique
- Select the Business Process
- Declare the grain
- Identify the dimensions
- Identify the facts
Let’s go through all these steps in detail
1. Select the Business Process
What am I tracking
In this case, we are tracking the content that users are consuming on the platform. The data model that we are creating can be modified slightly for audio streaming platforms as well.
2. Declare the Grain
Declaring the grain means specifying exactly what an individual fact table row represents
The above definition is from Kimball’s DataWarehousing Toolkit.
It helps decide the level of detail you want to go within the fact table.
Think like this: If you add user-related detail to rides, you can view and slice and dice the data set by users/customers. For our use case, we are keeping individual user sessions, for all the watch events
3. Identify the Dimensions
Who, what , where, when , why, and how
The dimensions help you map out the business event with these values which help answer the above question. Dimensions are also said to ‘define’ the business process.
For eg, What is the user’s subscription type, the device they use, the show they watch, etc
4. Identify Facts
| What is the process measuring?
In our case, we are measuring the rides-related KPIs(Key Performance Indicators) and metrics are fact is rides booked. Based on the grain we decided earlier we have to ensure our fact records follow the same, or if required review and revise the grain.
We can also have multiple flags based on our analytical use cases. For eg , did the user select the video to watch from the trending/top 10 pages, or did they search for the content? These additional flags can help answer critical business questions.
Finally, if you are creating a lot of sparse fields(including flags), you might want to reconsider the grain/business process and reflect on whether a separate fact table might make more business sense.
Putting it all together, this is our rough data model(created in excel)
I am using DBDesigner to create the same which makes it easier to see the relations
A simple business operation can have multiple business processes, each requiring a business data model, we will explore others in the next set of articles
