Forum Discussion
How to build proper model
Hello experts,
I wanted to practise building the model correctly a little.
I have a sample data set of music reviews.
I have tables such as:
reviews, artist, content, genres, labels and in addition I have created a Calendar.
The problem with this is that it seems to me that the Reviews table will be a fact table and the rest of the tables will be dimension tables.
Unfortunately the dimension tables contain multiple rows for one review. When defining relationships, they start to create one to many bi-directional relationships from the facts table to the dimensions table.
I don't really know if I should rework the dimension tables or the fact tables somehow.
I am attaching the uploaded data already in the PowerBI file. So far I have only linked the tables by example.
If anyone would be able to suggest how to approach this I would be very grateful:)
Link to PBI report
PijushRoy what you say about this model?
I've created a "bridge table" between dimensionals tables and fact table review.Hi Konrad909090
Bridge table or lookup table is also fine but when you are using a both way relationship and want to create visual with multiple table, there are chance to filter out other table data.
Please validate, if works fine then stay with this approach
5 Replies
Hi Konrad909090
Please find the proper data model, find the attached PBIX file.
Please let me know if you face any issues.Hi PijushRoy
Thank you for taking up the subject.
The question is whether this approach is correct. Here we have the very relations of many to many. And as far as I remember, the creation of such relationships should be avoided. So shouldn't we be doing some transformations on the tables to get to a one-to-many relationship?
Or possibly create a single table for analysis, or skip the genres, label and artists tables and focus only on the reviews table?Yes you can Konrad909090
As you have star schema table structure, if you have many to many relationship but filter one way, it does not create any issue
PijushRoy what you say about this model?
I've created a "bridge table" between dimensionals tables and fact table review.Hi Konrad909090
Bridge table or lookup table is also fine but when you are using a both way relationship and want to create visual with multiple table, there are chance to filter out other table data.
Please validate, if works fine then stay with this approach