6 Replies Latest reply: Nov 18, 2014 11:03 AM by Thomas Rieck RSS

    Dimension in multiple tables - avoiding synthetic key

      Hey There,

       

      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.

       

      BUSINESS:

      LOAD BUSINESS_ID,

           BUSINESS_DESCRIPTION,

           DIVISION

      FROM

      [BUSINESS.xls]

      (biff, embedded labels, table is Sheet1$);

       

      MANUFACTURER:

      LOAD MANUFACTURER_NAME,

           BUSINESS_ID

      FROM

      [MANUFACTURER.xls]

      (biff, embedded labels, table is Sheet1$);

       

      AREA:

      LOAD BUSINESS_ID,

           ADDRESS,

           SUBURB,

      FROM

      [AREA.xls]

      (biff, embedded labels, table is Sheet1$);

        • Re: Dimension in multiple tables - avoiding synthetic key
          Ali Hijazi

          this won't create a synthetic key

          all tables will be associated by the business_id field

          • Re: Dimension in multiple tables - avoiding synthetic key
            Jonathan Penuliar

            Looking at your listed load script,

            the only common field i can see is Business ID, so theoretically, you should not have synth keys.

            But if there are more fields you havent listed, it is possible to have synth keys.

            One way to avoid synth key is to QUALIFY the loaded tables.

              • Re: Dimension in multiple tables - avoiding synthetic key

                Hey There,

                 

                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:

                 

                TRANSACTION_TABLE:

                LOAD BUSINESS_DESCRIPTION,

                          MANUFACTURER_NAME,

                          ADDRESS,

                          SALES_AMOUNT,

                          SALES_DATE

                FROM

                [TRANSACTION_TABLE.xls]

                (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)

                  • Re: Dimension in multiple tables - avoiding synthetic key
                    Jonathan Penuliar

                    can you attach a screenshot of your Data Model (ctrl+T).

                    for Synth keys which you think are not really Important(key fields) you can just rename them,

                    another way is to Qualify all fields (*) or qualify only the fields you want, in this case, synth field.

                    There are plenty of ways to avoid Synth keys if you dig a bit more i the forums, might be useful to your current requirement.

                     

                    JP

                    • Re: Dimension in multiple tables - avoiding synthetic key
                      Jonathan Dienst

                      Hi

                       

                      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)?

                       

                      Jonathan

                      • Re: Dimension in multiple tables - avoiding synthetic key
                        Thomas Rieck

                        Hi Nathan,

                         

                        based on the info you provided …

                        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 …