Master Dimensional Modeling Lesson 01 - Why Use a Dimensional Model?
Vložit
- čas přidán 5. 08. 2024
- Dimensional Modeling is a popular and effective way to organize your data to maximize business value. In this video, you will learn what a Dimensional Model, aka
a Star Schema is and why you should use them to organize your data warehouse.
Support me on Patreon
www.patreon.com/bePatron?u=63...
Slides
github.com/bcafferky/shared/b...
Understanding Dimensional Modeling
• Dimensional Modeling
Should You Use a Data Vault for a Data Lake? by Advancing Analytics
• Behind the Hype: Shoul...
Databricks Blog: Dimensional Modeling on Databricks
www.databricks.com/glossary/s... - Věda a technologie
As a 21 Year Old struggeling with these stuff, because we usually dont really get told what to exactly learn, I really appreciate your videos !
Thank You!
I watched and took notes from your older video on dimensional modeling. Best explanation I’ve watched! Looking forward to watching this series too
Thanks!
Very excited about this series
Thanks a lot Bryan. This is a great info and hard to find it so please continue the series..
Thanks Bryan, I'm looking forward to your series. Take care.
Thanks. You too.
This is exactly we need.
loved it and enjoy ur voice modulation :)
Great Material !
Thanks a lot.
You're Welcome!
Yes!
Thanks for the video! Would you always choose dimensional modeling when the goal is to support reporting and analytics? I ran into a situation where the analysts would rather use Salesforce's source data model with some minimal denormalization than its star schema version. They believe it would actually introduce MORE joins for them as one table - e.g. Opportunity - is now split into a fact and a dimension table so they don't see the benefit. How would you go about demonstrating the benefits for the end users in such a case? Thank you!
It is not always necessary to use a Star Schema but usually a better option than an OLTP model. If the purpose is to use the data by Power BI then a Star Schema is highly recommended by Microsoft. I'm not clear on Tableau. However, without a star schema, you can't easily support dimension history, i.e., SCD 2. So if a store changes from one region to another, all data related to the store is updated at that point in time which means reports a year ago don't match new reports year over year and regional sales comparisons will not longer be valid.
If the business does not care about that and are Ok with a changing snapshot of the data, then the next thing is to look at the complexity of the model. If it's just a few tables, it may not matter but if you need to join many tables and nest joins like Opportunity to Order to Product to Product Category, then it will likely not perform well.
Best bet is to test both models against what you expect the primary queries to be like and see how they hold up. It comes down to what delivers the most value to the business.
I made a mistake in an interview today and confused the star schema with the 3 Normal Forms. I also stated star schema was normalization when it was denormalized...oh well.
The important part of the star schema, the fact table, IS in third normal form. Only the dimensions are 2NF for simplicity and speed. Pure 3NF is not as important in a reporting database as it is an a transactional database. I like to say that a star schema is a reorganization, not a denormalization. The more important “denormalization“ is header detail tables into one table.
@@Milhouse77BS Thank you for clearing this up for me!
What position did you apply for?
@@mindyourown6320 Sr Healthcare Data Analyst. Just did a 2nd interview with them today.
Hello, thank you for the video, very informative. Im just wondering isnt there a risk of table deadlock since the table FactResellerSaler is always the entrypoint for most of the queries ? Thank you for your response :).
Good question. Only possible during table loads but that should be done one table at a time to avoid the issue. Main thing is the Fact table must be updated after the dimension tables. Reading the tables will not cause deadlocks.
When you are describing what's on the screen, can you please use the mouse to point to what you are referring to please.
It can be tricky using mouse pointers in the recordings. I've experimented with some. I usually animate slide bullet points so you focus on the current bullet point. Which specific parts of the presentation were you confused by?
Snowflake doesn't enforce Keys, they are only informative.
That forces devs and architects to handle data quality on their end thus dim model might be daunting to implement, still doable :)