Hi Viewers, if you have any queries on this topic or you want any topic to learn from us, drop them in our comments section. Please make sure you subscribe to our RUclips channel 👉Please ping on WhatsApp wa.me/9916961234
Highly appreciate your video, easily understood regarding fact and dimension tables as well as star and snowflake schemas. You are one of the highest performing teacher. Many thanks.
Hello! Great question regarding database relationships and schema design in Power BI (or any other data modeling environment). 1. Unique Values in Fact vs. Dimension Tables: Fact Table: This table contains transactional data (e.g., sales, orders) and typically has non-unique values. Each row in the fact table represents an event (like a sale), so multiple rows might have the same product, customer, or date. Dimension Tables: These are lookup tables that describe the data in the fact table, and they usually contain unique values. For example, if you have a "Products" dimension, each product in this table should appear only once, uniquely identified by a key (e.g., ProductID). In summary: Fact tables often have non-unique values (e.g., multiple transactions involving the same product), while dimension tables typically have unique values (one row per product, customer, etc.). 2. Many-to-Many Relationships: In a star schema or snowflake schema, relationships are typically one-to-many, where a fact table connects to dimension tables. However, sometimes you may need a many-to-many relationship between tables. Here's how you can handle that: How to Handle Many-to-Many Relationships in Power BI (or Database Design): Option 1: Using a Bridge Table (Junction Table): In many-to-many relationships, a bridge table (also called an intermediary or junction table) is often used. This table breaks the many-to-many relationship into two one-to-many relationships. Example: Imagine you have a Products table and a SalesOrders table where one product can appear in multiple orders, and one order can have multiple products. You can create a bridge table that has the unique combination of OrderID and ProductID: SalesOrderDetails (bridge table): OrderID | ProductID | Quantity | Price Now, you can link SalesOrders to SalesOrderDetails and Products to SalesOrderDetails through one-to-many relationships. Option 2: Power BI's Built-in Many-to-Many Relationship: Power BI offers a feature for many-to-many relationships directly. To use this, Power BI allows you to relate two tables without the need for a bridge table, but you'll need to carefully manage how the filters propagate through these relationships to avoid incorrect aggregations. Steps: When defining relationships in Power BI, you can set the relationship to "Many-to-Many" in the model. Go to Model view. Click on the relationship line between the two tables. In the "Cardinality" section, select Many-to-Many () relationship**. Option 3: Merge or Flatten Data: In some cases, you may want to flatten the tables into one (combining both dimensions into a larger table) if the complexity is manageable. This can sometimes avoid the need for many-to-many relationships altogether, though it may not be ideal for large datasets. Example Schema with Many-to-Many: Consider a scenario where: You have a Products dimension. You have an Orders fact table. There is no direct way to link these two tables without a many-to-many relationship because each product can be in multiple orders, and each order can have multiple products. You would introduce a bridge table like OrderDetails, which would look like this: Fact Table: Orders OrderID, OrderDate, CustomerID, etc. Bridge Table: OrderDetails OrderID, ProductID, Quantity, Price Dimension Table: Products ProductID, ProductName, Category, etc. With this, you’ve effectively broken the many-to-many relationship into two one-to-many relationships: Orders → OrderDetails (one order can have many details) Products → OrderDetails (one product can appear in many details) Final Thoughts: Unique values are typically found in dimension tables, while the fact table often contains repeated keys. Many-to-many relationships can be resolved either by using a bridge table (best practice in most cases) or utilizing Power BI's built-in many-to-many feature for direct relationships. Let me know if this clarifies the concept or if you'd like to dive into a specific part of the process!
I have attended power bi interview this morning and interviewer asked the below question. We need to calculate the average attrition% .. Based on the user selection ... Example : 1. fiscal year - Apr - Mar.. 2. We need to calculate the average attrition % .... 3. If you click / Select December month then attrition count and average should dynamically calculate from sum( April - November ) ...and we need to add those value in numerator and by denominator should be December month values
Somewhere I have seen that star,snowflake can have multi fact tables If star has multi fact tables then what's the difference between galaxy and star schemas
I have a question sir... If we created a report with default color coding and client didn't like report colouring ... What would be our next approach..... Note : client didn't provide their own colour Please help me regarding this???
if clients don't give colors.. we can choose the best combinations of colors. Default colors is usually avoided. Also please check our end to end project playlist. we have explained this
For 2 years exp . SQL+ Power BI should be enough.. Seems like you are not applying correctly. Please follow this video. This will be very useful ruclips.net/video/TMlKSVhj1m8/видео.html
Hi Viewers, if you have any queries on this topic or you want any topic to learn from us, drop them in our comments section. Please make sure you subscribe to our RUclips channel 👉Please ping on WhatsApp wa.me/9916961234
Number is not working sir🙏
Highly appreciate your video, easily understood regarding fact and dimension tables as well as star and snowflake schemas. You are one of the highest performing teacher. Many thanks.
Glad it was helpful!
This is the best explanation of the topic I've seen so far. Highly appreciated.
Glad it was helpful! , Please subscribe our channel for regular updates , all the best
You explaination is on the another level... All your videos are very helpful... Thank you so much KSR...
You are most welcome
It is helpful for new SQL learners thank you
Thank you so much, Please subscribe our channel, it motivates us a lot
Very informative vedio. Thanks for an amazing content.
Glad you liked it!
Your team cleared my long doubt
Thats great, Please subscribe our channel, it motivates us.
Teaching is very easy to understand concepts sir tq
Keep watching, Thank you, Please subscribe our channel for regular updates , all the best
Highly appreciated,,,, thank you... 😊
Thank you for your kind words, they truly mean a lot to me.
Thank you for the breakdown between the different schemas, it was very helpful. Great job!
Glad it was helpful!
Thanks, I never considered different data models untilI got into Microsoft Power BI!
very nice explanation sir jii
Thanks and welcome, Please subscribe our channel, it will motives to do more videos
Thanks for a clear explanation!
You're welcome! we are glad that we could provide a clear explanation. If you have any more questions or need further assistance, feel free to ask!
Your explanation is next level sir
Glad to hear that. Thanks
Another useful video🥳
Glad you think so!
Watched all ur videos . All are very useful .. And the explanation is too good.. ..
Thanks a lot 😊
Please upload full videos on this two schemas..
Many videos are there in this shemas but no one is explaining like you sir.
Thanks. We will upload more videos
Thank you so much for wonderful explanation
Glad it was helpful!
Hi.. Sir, In above case the fact table should have unique values or dimension tables ? How do we create many to many relationships with the schema?
Hello! Great question regarding database relationships and schema design in Power BI (or any other data modeling environment).
1. Unique Values in Fact vs. Dimension Tables:
Fact Table: This table contains transactional data (e.g., sales, orders) and typically has non-unique values. Each row in the fact table represents an event (like a sale), so multiple rows might have the same product, customer, or date.
Dimension Tables: These are lookup tables that describe the data in the fact table, and they usually contain unique values. For example, if you have a "Products" dimension, each product in this table should appear only once, uniquely identified by a key (e.g., ProductID).
In summary: Fact tables often have non-unique values (e.g., multiple transactions involving the same product), while dimension tables typically have unique values (one row per product, customer, etc.).
2. Many-to-Many Relationships:
In a star schema or snowflake schema, relationships are typically one-to-many, where a fact table connects to dimension tables. However, sometimes you may need a many-to-many relationship between tables. Here's how you can handle that:
How to Handle Many-to-Many Relationships in Power BI (or Database Design):
Option 1: Using a Bridge Table (Junction Table): In many-to-many relationships, a bridge table (also called an intermediary or junction table) is often used. This table breaks the many-to-many relationship into two one-to-many relationships.
Example: Imagine you have a Products table and a SalesOrders table where one product can appear in multiple orders, and one order can have multiple products. You can create a bridge table that has the unique combination of OrderID and ProductID:
SalesOrderDetails (bridge table):
OrderID | ProductID | Quantity | Price
Now, you can link SalesOrders to SalesOrderDetails and Products to SalesOrderDetails through one-to-many relationships.
Option 2: Power BI's Built-in Many-to-Many Relationship: Power BI offers a feature for many-to-many relationships directly. To use this, Power BI allows you to relate two tables without the need for a bridge table, but you'll need to carefully manage how the filters propagate through these relationships to avoid incorrect aggregations.
Steps:
When defining relationships in Power BI, you can set the relationship to "Many-to-Many" in the model.
Go to Model view.
Click on the relationship line between the two tables.
In the "Cardinality" section, select Many-to-Many () relationship**.
Option 3: Merge or Flatten Data: In some cases, you may want to flatten the tables into one (combining both dimensions into a larger table) if the complexity is manageable. This can sometimes avoid the need for many-to-many relationships altogether, though it may not be ideal for large datasets.
Example Schema with Many-to-Many:
Consider a scenario where:
You have a Products dimension.
You have an Orders fact table.
There is no direct way to link these two tables without a many-to-many relationship because each product can be in multiple orders, and each order can have multiple products.
You would introduce a bridge table like OrderDetails, which would look like this:
Fact Table: Orders
OrderID, OrderDate, CustomerID, etc.
Bridge Table: OrderDetails
OrderID, ProductID, Quantity, Price
Dimension Table: Products
ProductID, ProductName, Category, etc.
With this, you’ve effectively broken the many-to-many relationship into two one-to-many relationships:
Orders → OrderDetails (one order can have many details)
Products → OrderDetails (one product can appear in many details)
Final Thoughts:
Unique values are typically found in dimension tables, while the fact table often contains repeated keys.
Many-to-many relationships can be resolved either by using a bridge table (best practice in most cases) or utilizing Power BI's built-in many-to-many feature for direct relationships.
Let me know if this clarifies the concept or if you'd like to dive into a specific part of the process!
I have attended power bi interview this morning and interviewer asked the below question.
We need to calculate the average attrition% ..
Based on the user selection ...
Example : 1. fiscal year - Apr - Mar..
2. We need to calculate the average attrition % ....
3. If you click / Select December month then attrition count and average should dynamically calculate from sum( April - November ) ...and we need to add those value in numerator and by denominator should be December month values
We have solved similar usecase in session. we will upload same video shortly
Yes, but did you get the job?
Are you providing complete training on Multi-Dim Data Modelling in Snowflake?
Yes sir
Somewhere I have seen that star,snowflake can have multi fact tables
If star has multi fact tables then what's the difference between galaxy and star schemas
In galaxy. U will have common dimensions tables.. between facts
Hi sir your videos are very Helpful ,I had watched your End To End Project Webinar, Can you Please Conduct a Webinar Specially On DAX
Sure we will have it asap
Hi sir thanku you very much sir.
I want SCD types class. Can you please provide link.
will try to explain in different video
In real time which schema more used
Star Schema mostly used
Thanks!
you are Welcome!, please subscribe our channel for regular updates.
Can we have more than 1 fact tables in a snowflake schema in realtime?
Yes possible
I have a question sir... If we created a report with default color coding and client didn't like report colouring ... What would be our next approach.....
Note : client didn't provide their own colour
Please help me regarding this???
if clients don't give colors.. we can choose the best combinations of colors. Default colors is usually avoided. Also please check our end to end project playlist. we have explained this
@@KSRDatavizon thank u sir
I am 2020 passed out I had learn power bi with SQL but now I struggle to get the place in field. Cannot receive the call from requirements
Learn python
For 2 years exp . SQL+ Power BI should be enough..
Seems like you are not applying correctly. Please follow this video. This will be very useful
ruclips.net/video/TMlKSVhj1m8/видео.html
Make a video on many to many relationship
Sure Karan, in pipeline.
Cardinality
star schema always have only one fact table?
It can have more than 1.. But it should be connected to all dimension tables
@@KSRDatavizon Thank you 👍
P
Thank you