Jim, I'm having a very similar problem.
I've tried 8 ways of Sunday to fix it - including a concatenate, which was not fruitful.
Here's one I got to work in a link table with 1 caveat. Here's what I used:
First of all, you have 2 tables with 0 unique fields - I'm going to assume there's at least 1 in each that's unique to just that table - otherwise, kind of pointless.
The downside of this method teaches that for each table you comprise a [$Key field] consisting of 'all' foreign keys.
(in your case, that would be:
Table 1 Key: A&B&C&D&E&F&G&H&I as [%Key field]
Table 2 Key: <all of them concatenated> as [%Key field]
Table 3 Key: <again, all of them concatenated> as [%Key field]
Now, this WORKS - as in, creates a link table, eliminates synthetic key and doesn't produce errors.
Obviously the downside is: they don't match.
If you'll read the comments, they address this, kind of. That's my caveat - I'm having trouble getting past that.
Hope this helps . . .
I made some headway:
Items in bold link 'somewhere else' - indicating they constitute those combinations
Table1: Table2: Table3:
A A D
B C E
C D F
G E H
value value value
Load A&'|'&C as Table1_Key, // could also use autonumberhash128 (A, C) to produce an integer
Load A&'|'&C&'|'&D&'|'&E as Table2_Key,
Load D&'|'&E as Table3 Key,
Load distinct //distinct very important
A&'|'&C as Table1_Key,
Resident Table 1:
Drop fields A, C from Table1;
//don't need them in the original now as they're loaded in the Link and associated with the key field
A&'|'&C&'|'&D&'|'&E as Table2_Key, // if you used autonumberhash128 above, use the same here
Drop fields A, C, D, E from Table2;
Load D&E as Table3 Key,
Drop fields D, E from Table3;
This will produce a link table with no synthetic keys, and load data accordingly creating a true star schema.
NOTE: In this scenario (similar to my own) you'll notice Table3 does not have a direct association with Table1. I'm noticing that in the UI, they're not associating at all for some reason (unless table B is involved). Not sure if that's design (bad) or a bug in my system.
Hope that helps!
I did that as well with my own scenario using joins.
It produced a massive table with 60M+ rows when the source data was 5k or less. The load time was forever and the application was extremely sluggish. It was ineffective, where the link table has very good performance (with the exception of the aforementioned association issue . . . ) loading only hundreds of rows.