data modeling for 2 fact tables having same key fields with many dimension tables

[#1]

Hi All,

I working on a datamodel where I have two fact tables (e.g 1.Corporate Sales Table 2. Customer Sales Table).The problem is that both these tables contain same key fields and I have 6-7 different dimension   tables. As I am trying to create a datamodel, any dimension table is getting joined with both fact tables. In any type of  schema building (star/snow flake) I am getting many loops. PFA example diagram.

Anyone know how to proceed in such kind of scenarios?

Thanks & regards,

Amey pantoji

[isaiah82]

Classic question ~ your best bet will be to use a link table.  Do a search for this on the forum and you'll get lots of good hits.  -Isaiah

[#3]

Thanks.

I will try to search this on forum.

[Anonymous]

Hi

try to use the Qualify and UnQualify keywords

Regards

Ashok

[#5]

Hi Ashok,

Even if I use qualify and unqualify keywords in a dimension/fact table, ultimately a dimension table is going to join with both  fact tables. So same issue will persist. Please correct me if i am wrong.

Regards,

Amey

[#6]

I think the link table Isaiah mentioned is the best idea.

However, if the table schemas are similar, you can concatenate them together and create a new field (called 'RecordType' or something like that) denoting whether a record is for 'Corporate Sales' or 'Customer Sales', and then use that in your chart expressions (either with an IF statment or using set analysis).