Having Issue with M:M Joins

[Anonymous]

Hi Experts,

file attached :sen1

i try to remove duplicate from item table and link my item merch -->item_table -->item_distinct table and from item distinct i joined my fact 1 and fact 2.  i want to values from item_merch cat_code and cat_id and againt sales data.

file attached :sen2

dim_item tables has duplicate item_id . when i trying to join with my fact 1 and fact 2 . i cannot make active relationship for both , either one is active. and active fact got correct data.

please help. cannt understand whats going on

[Anonymous · Accepted solution]

@lbendlin 

I have item table and item_merchant table which will be join based on merch code. my item table join with 2 facts that i using item_id.

my requirement is to create a report which show Merch_Code,Merch_Cat,Sales2020_Amt,Sales2021_Amt merch_code .

1) In my data model if i Join ITEM table with Fact tables it only joining with M:M relationship

2) one ofmy facts relationship is diabled as metioned in screenshot above.

sample data

Hope I clarify the issue, please let me know if any further info

[lbendlin · Accepted solution]

Your data model should look like this

ITEM_MERCHANT 1:M ITEM 1:M SALES_FACTS

Combine your sales_facts table, no need to keep them separate.

[lbendlin]

If a dimension table has duplicate values in the key column then it is not a dimension table. Are you clear about what is causing the duplicates, and do you have a way to mitigate it that does not involve a bridge table?

What is the actual reporting question that your data model needs to answer?

[Anonymous · Accepted solution]

@lbendlin 

I have item table and item_merchant table which will be join based on merch code. my item table join with 2 facts that i using item_id.

my requirement is to create a report which show Merch_Code,Merch_Cat,Sales2020_Amt,Sales2021_Amt merch_code .

1) In my data model if i Join ITEM table with Fact tables it only joining with M:M relationship

2) one ofmy facts relationship is diabled as metioned in screenshot above.

sample data

Hope I clarify the issue, please let me know if any further info