Dimension in multiple tables - avoiding synthetic key
Looking for some advice on a load script. The tables look something like I've outlined below. If I were to just load each table as per below, then obviously there'd be a synthetic table created based on the BUSINESS_ID. What's the best way to avoid this? I initially did a join (so joining manufacturer to business, for example), but this doesn't seem to be very efficient. What are my other options for avoiding the synthetic key? Any help would be greatly appreciated.
Thanks for the response. I dunno why I thought that would create synthetic keys. What's happening in my load script is that associations are being created when those three are loaded (as you mentioned, no synth keys). Following that, I have another table that contains items from all three tables:
(biff, embedded labels, table is Sheet1$);
This then creates the synthetic keys. So I'm wondering if there is way to avoid the synth key in this case without joining the first three tables first and creating a mapping key (sorry for explaining so poorly initially)
You need to choose by which key field (of BUSINESS_DESCRIPTION, MANUFACTURER_NAME, ADDRESS) you want to link the transactions to the main table.
Are the other possible key fields in this spreadsheet redundant (ie the same as what has already been loaded in the main table), or do they differ (ie BUSINESS_DESCRIPTION is associated with a different MANUFACTURER_NAME and/or ADDRESS than in the main table)?
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
my guess is it makes sense to join the first two tables and build a combined key in the new table from BUSINESS_DESCRIPTION and MANUFACTURER_NAME (BUSINESS_DESCRIPTION &’_’ & MANUFACTURER_NAME as %BDMAN) and rename the fields to something else in both table. Then load the Transaction Table also with the new Key like shown above and rename the two fields here as well.
Then load the Area table like you do right now ….
If you could send me a sample qvw with some fake data I could show you better what I mean …