The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a data model that in a simplified way has 5 tables:
>>> TAB1: the granularity of this table is the ID_OPE and there is a metric associated with this table (fact)
>>> TAB2: the granularity of this table is the ID_SUB and there is a metric associated with this table (fact). The same ID_OPE can be associated with more than one ID_SUB.
>>> TAB3: the granularity of this table is the ID_LIB and there is a metric associated with this table (fact). The same ID_SUB can be associated with more than one ID_LIB
>>> TAB4: the granularity of this table is the ID_OPE_EVENT. The same ID_OPE can have several ID_OPE_EVENT
>>> TAB5: the granularity of this table is the ID_SUB_EVENT. The same ID_SUB can have several ID_SUB_EVENT
I want to be able to relate information from all tables in the same view and filter bidirectionally as needed. However, I can only make the model as a whole work if I put a many-to-many relationship, as shown below:
Doing the above model looks like everything is working. However, I would like to know what the best practice would be (I don't think it's possible to use a star model and I can’t use a single association table, with all ids, as it results in tens of millions of rows in my model, impacting performance ).
I'd like to know if there are any issues I should be concerned about this model above, and if so, which other model should I use.
Hi @maxbbbfff ,
You may consider to remove table1 and create many to many relationship between table2 and table4.
Best Regards,
Jay
Hello, Thanks for the feedback. I need the existing information in the 5 tables. I summarized the tables by just putting the IDs, but they all have important data. In particular, tables 1, 2 and 3 are fact tables.
User | Count |
---|---|
77 | |
77 | |
36 | |
30 | |
28 |
User | Count |
---|---|
107 | |
100 | |
55 | |
49 | |
45 |