Circular refererence - concat or map

[Tee_dubs]

I have an issue.  Ive built a quite complicated app running on a big fact table.  3mil rows and 100+ columns. 

My customer has come to me with a requirement to explore the realationships between 2 more large fact tables. 

Ive given it a first try and ended up with a circular reference.

Im trying to figure out how to best deal with it.  

All tables have date fields, machine_id and a hierarchy_id.

Could I concatenate all three adding a master_date column, master_machine_id and master_heirarchy_id and then prefix the remaining collums with a fact table id.

Or Could I create a Mapping load for all 3 

Either way im not entirely sure how to accomplish this and what effect it will have on performance. 

Help please 🙂

[felipedl]

You could replace the combination of date fields, machine_id and a hierarchy_id fields by an Id (say, an incremental id) and create another table that translates that into the other tables you have.

So, your original tables will remove the 3 fields and replace it with the Id and you'll create a third table with lets you explore the data.

Would be the concept of linktable that's widely spread in the community.