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.
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.
