Data Model

[maxbbbfff]

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.

[v-jayw-msft]

Hi @maxbbbfff ,

You may consider to remove table1 and create many to many relationship between table2 and table4.

Best Regards,

Jay

[maxbbbfff]

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.