Glad it helped!

Glad it helped!

Glad it was helpful!

I understand. However, please watch the video again, and understand the context where I am talking. If you still think it's not correct, let me know. I'll definitely try my best to resolve your query.

For anyone that may have had similar questions, I think I can answer these: 1) I think his example data may have been unclear, misleading, or poorly constructed, but I'm not sure which. If you had class_id in the Student table, each student should have multiple rows; same thing for if you have student_id in the Class table, but his example data doesn't clearly show that (particularly in the Student table, where each student only has one row). The reason the class ID's look weird is because the ID column of that table is the primary key, which MUST be unique. Because student_id is included in the class table, each class must have multiple rows. As such, in order for the ID column to be the primary key, it isn't unique to each class, but rather each class-student pair. 2) Which brings me to the problems with having many-to-many relationships between tables. It is precisely BECAUSE there is a many-to-many relationship between students and classes that the primary key/ID in the class table *cannot* correspond to unique classes (the ID column in the Student table can't correspond to a unique student, either, but his example data doesn't really show that). There are numerous issues with many-to-many table relationships, but the core problem is that it *forces* the tables to duplicate data. Extending his example a bit, imagine if the class table had a dozen or so other columns about who teaches the class, what grading scale they use, what time it takes place, what day(s) it takes place, etc., etc. Because you have the student_id in there, because it's a many-to-many relationship, all of the data specific to each class is duplicated a number of times equal to the number of students it has. Which means you're wasting a ton of memory on storing redundant info, any queries to that table are much slower and typically require a lot of extra complexity, changing something like the time a class is held at requires updating numerous rows (and if you miss any rows, you now have conflicting data, which is the absolute LAST thing you want), etc. Duplicate data is basically the enemy of any relational database; it causes all sorts of problems down the line that you REALLY want to avoid. It might not seem to have much effect when you're dealing with very small, simple data sets, but it can have a MASSIVE effect once your data size and/or complexity starts to grow. Eliminating as much data duplication as possible is basically the central goal of relational databases (transactional ones, at least; sometimes some duplication can be practically useful, but it's generally something you want to avoid). 3) In order to avoid many-to-have relationships, the Enrollment table *needs* at least 3 columns: a primary key (call it enrollment_id), and 2 foreign keys (class_id and student_id) AND class_id needs to be removed from the student table, as well as student_id from the class table. The purpose of the "bridge table", i.e. the enrollment table is to essentially remove the foreign keys from the main tables (student_id from Class, and class_id from Student) and store them in a table that specifies the relationship between each student and each class, such that you no longer need duplicate rows in the Class table for each student and vice versa. Done correctly, you will have 1 ID in the Student table per student, and 1 ID in the Class table per class; you'll also have many enrollment_id's per student_id, but only 1 student_id per enrollment_id, as well as 1 class_id per enrollment_id and many enrollment_id's per class_id. Then if, for example, you want to change the time a class occurs, you only need to make a single change in the Class table, rather than make the change for every student in the class. You can now query, update, add, and remove student data and class data *independently from one another*! If a student drops a class, you now only need to remove a single row, from the enrollment table; if you didn't make change, and kept the direct many-to-many relationship between the Student and Class tables, every time a student dropped a class, you'd have to remove a row or value in BOTH the Student table AND the Class table (and, again, if you forget to do so in one of the tables, you'll then have conflicting data and you won't be able to tell whether or not the student was enrolled in that class anymore). All in all, he basically just kinda did it wrong, using what appears to be poorly constructed example data. I have no doubt he understands how to fix many-to-many relationships, though; the confusion and errors are more likely just the result of the fact that it can be difficult to come up with good, illustrative example data and teach based on it in a clear and comprehensive way. ESPECIALLY when you're used to doing things like this in an applied setting where things are always 1000x messier; "ideal" data that's useful for demonstrating core concepts can almost seem foreign and incomprehensible after awhile. It's why "teaching" and "doing" are two very different skill sets; not all who can do something can teach it, and not all who can teach something can do it.

Thank you. Definitely, will do a demo on it. Thank your for your feedback.

Yes, this would certainly do. However, you can try just to keep ID columns from both (many) sides of the table in a separate table (only unique values) and that may solve your problem. Or you may create two different dimensional tables.

If there is no keys are there, you may combine one or more column, and create it. However, in relational database there is always a relationship between the tables, otherwise you cannot do it. You must create a relationship.

Of course, that would work. There is already another video, where I have explained about it. Watch this video: kzfaq.info/get/bejne/qpd4krV_2bewlJ8.html

@@BIConsultingPro yeah I already saw that when it was released few weeks ago by you and there it was clear.

I'll request you to watch our latest video on "Many to Many relationships" and read the following: Yes, it's possible and often recommended to create two new single-column dimension tables (e.g., "Student Table" with unique Student ID and "Class Table" with unique Class ID). You can then connect these dimension tables separately to your fact table(s) using a one-to-many relationship. This approach helps in simplifying many-to-many relationships, improves performance, and provides better control over data relationships and filtering in Power BI.

I have tried the same, and it's working for me. You can try to remove the duplicate. Please do share your feedback too.

There is no need for a cartesian product. It will inflate table size. Since only those record combinations which exist in Student and Class are sufficient. Then from Class to Enrollment its 1-many and from Student to Enrollment 1-many.

Hi Renuka, Star Schema won't be a snowflake schema with multiple fact tables. this would still be a star schema only. support.sisense.com/kb/en/article/design-a-schema-with-more-than-one-fact-multi-fact-schema

Well, ideally, it should be done like this only as you have mentioned. However, instead of Append you should merge tables. Merging would act like a join. However, appending only works when both tables have the same columns

In the context of using a bridge table to eliminate many-to-many relationships in a data model, the primary key and foreign key relationships are indeed essential, but they may not always follow the traditional primary key-foreign key setup. Let's break down the concept: Many-to-Many Relationships: In a many-to-many relationship, one or more records from one table can be related to multiple records in another table, and vice versa. This creates ambiguity in the data model, as it cannot be directly represented in a relational database. Bridge Table: A bridge table is used to resolve many-to-many relationships. It acts as an intermediary table between the two related tables, breaking down the many-to-many relationship into two separate one-to-many relationships. Primary Key and Foreign Key: In the context of the bridge table, both the primary key and the foreign key play crucial roles. The primary key of the bridge table is usually a combination of the primary keys from the two related tables. This combined key acts as a unique identifier for each relationship in the bridge table. On the other hand, the foreign keys in the bridge table reference the primary keys of the related tables. These foreign keys establish the one-to-many relationship between the bridge table and the related tables. Cardinality in Power BI: In Power BI, the relationship between tables is defined based on the cardinality. Cardinality describes how the rows in one table are related to the rows in another table. In the context of the bridge table, the cardinality is typically set to "One" on the side of the bridge table and "Many" on the sides of the related tables. This one-to-many cardinality relationship between the bridge table and the related tables ensures that each record in the bridge table is linked to one record in each of the related tables. As a result, the many-to-many relationship is effectively resolved. In the real world, bridge tables are commonly used in scenarios where there are complex relationships between entities, such as in sales transactions with multiple products and customers, or in data models involving many-to-many relationships between dimensions and fact tables. Using bridge tables allows for a more flexible and efficient data model, making it easier to perform analytics and reporting in Power BI.

I didn't get. . What do you mean by "In this relationship" here? Could you please explain it so that I can help you. Also, you can email me with the reference book snapshot along with your query at connect@biconsultingpro.com

@@BIConsultingPro ok sir..i will send you the photo....in your mail id..ok sir..

Thanks for your feedback. You can help us to improve it if you have any idea.

Which one is not executed? You can write me an email at connect@biconsultingpro.com

Both returns different values. Distinct returns a column while UNION returns a table. So, I don't think so it's possible. However, I'll encourage you to try it.

Yes, only corresponding data would come.

Here's how you can implement this in Power BI: 1. Power Query Editor: Use Group By and Merge Queries operations to create bridge tables. 2. Data Model: Create relationships between the tables and bridge tables. 3. DAX Measures: Adjust measures to ensure they correctly use the bridge tables for relationships. Note: You need to create bridge tables such as AB, BC and AC

You can try and share the feedback here

It should and it will

I am taking my word back, it's working perfectly. Thanks

Thank you. I am glad it's helping you.

Apologies! this file is not available now.

Hi Frank, thanks for your feedback! Could you please explain a bit more and help me out to correct this?

@@BIConsultingProFirst your example , you connect student is to class id ( see scheme), wrong. Also in your movie, wrong @@BIConsultingPro which are needed. So extract the numbers from both tables , create a list, convert list into quert, id and then append them, so only the numbers which are needed in the connecting table. Then tou have to reverse the filters, use crissfilter. Your totals will not be ok, use sumx and i hasone value

Better remove movie

@@frankgovers8420 Sir I have a doubt why we should not have many to relationships .....what problem do we face if we have many to many relationship?

@@pravallikam9777 , your totals can be wrong!!

You can just create a bridge table with a column with the Ids from both tables and then connect.