Choosing the Right Approach
1. Choosing the Right Approach
Hello, and welcome to the video concluding the data model techniques. We'll learn to select the best data modeling technique for our needs. Each approach has strengths, and we'll explore how to match them with business requirements.2. Use cases for each modeling technique
To choose the suitable model, we assess business needs, including reporting, analysis, and data history requirements, and match them with each model's strengths. For instance, Entity-relationship models are best for managing daily routine business data, as seen in customer management systems. They handle transactional data, the regular data produced by a business.3. Use cases for each modeling technique (1)
Dimensional models are the go-to choice for quickly analyzing large amounts of data. Dimensions are commonly used in business intelligence platforms, which help companies understand their data for better decision-making.4. Use cases for each modeling technique (2)
Lastly, Data Vault modeling is ideal for data warehouses emphasizing long-term storage and historical tracking. It's indispensable for regulatory reporting and auditing, that needs a comprehensive history of data.5. Technical considerations
The technical architecture of the data warehouse, the expected volume of data, performance requirements, and maintenance scalability will all influence the decision. Entity–relationship models tend to be simpler but can become difficult when data grows significantly.6. Technical considerations (1)
Dimensional models offer fast query performance but can require more maintenance as data dimensions change.7. Technical considerations (2)
Data Vault models are highly scalable and can quickly handle complex, changing data scenarios. Yet, they require managing additional elements that may present a steep learning curve, especially for beginners or smaller organizations who have yet to become familiarized with its complexities.8. Data models in action
For each use case, the recommendation from data modelers is to weigh business and technical requirements against model capabilities to decide on the best model to apply. Sometimes, a hybrid approach may be the best solution.9. Data models in action (1)
In our example, the Entity-relationship model suits the students, classes, and schools entities in university data for transactional tasks and maintaining enrollment integrity. However, a Data Vault model is advantageous for analyzing enrollment and performance trends over time. It's effective at monitoring changes and auditing historical data.10. Retrieving data from the models
To understand student engagement with classes, we'll begin by locating students in the student's hub and pulling each student's details from the satellite student table. We'll join these tables on the student_key field. For simplicity, we'll use 'hs' and 'ss' AS aliases for hub_students and sat_student, respectively, giving each entity a short alias and making our query straightforward to read and write.11. Retrieving data from the models (1)
To grasp the extent of student enrollment, we'll use a LEFT JOIN. This join type combines every row from the left entity with corresponding rows from the right entity. If there's no match, it still shows left entity rows, leaving right entity values empty. We get each student's enrollment details by LEFT JOINing to the link_enrollment entity. This way, we ensure we include all students, even those not enrolled in any class.12. Retrieving data from the models (2)
We then aggregate the enrollment data to count the enrolled classes per student. We use the COUNT function ON the class_key from the link_enrollment entity. GROUP BY ensures that we get a distinct count for each student rather than a count for each row.13. Retrieving data from the models (3)
Finally, we're interested in when each student last enrolled in a class. We add another LEFT JOIN to bring in the class data from the class satellite and use the MAX function to find the most recent date.14. Functions overview
Let's summarize functions we have used in this cheatsheet.15. Functions overview
Take note of the implementations of the functions as well.16. Let's practice!
We'll apply what we've learned by identifying the proper data modeling technique and querying the data from it. Let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.