Hello All,
I've read articles and seen examples of joins, link tables, concatenates, applymaps and dimension tables, but I haven't found an example that matches my situation. From reading about each of these it seems I may need to do a combination of them in order to correctly link my data. I have many fact tables with either 2 or 3 key fields in them. These fact tables are very large (most have tens of millions of rows). Here is a simplified version of each table.
Table1: | Table2: | Table3: | Table4: | Table5: | Table6: | Table7:
Key1, | Key1, | Key1, | Key1, | Key2, | Key1, | Key1,
Key2, | Metric11, | Metric13,| Key3, | Key3, | Key3, | Key2,
Key3, | Metric12 | Metric14,| Metric16, | Metric16, | Metric19,| Key3,
Metric1,| Metric15 | | Metric17, | Metric17, | Metric20 | Metric21,
Metric2,| | | Metric18 | Metric18 | | Metric22,
Metric3,| | | | | | Metric23
Table1 details the parts that make up a product, so these Keys are not distinct. The other tables hold details about the parts (Key2) and products (Key1), sometimes depending on their location (Key3).
Each of these tables have many more fields (Metrics) in them but this is a simplified version to show where the keys are in each table.
I've considered a link table but I'm not sure how to create the key field when each table has a different number of keys in them.
Can anyone help me connect this table correctly as to keep metrics from duplicating/summing incorrectly and achieve optimal performance? (keep in mind, some of these tables have millions of rows in them) With this much data, synthetic keys will give me incorrect results.
I appreciate any help! Thank you!
I've attached a sample with some made up data using inline tables, however my data will be coming from QVDs. I've also tried to decrease the space between tables in the explanation in hopes that it formats correctly when published.
EDIT 1/29/16 - STILL LOOKING FOR HELP!
