Logical Model --> Physical table

[ubanerjee]

I have the following objects in my Model in ERWIN.

When I import this model in Compose I get the following

In the mapping AGREEMENT_MASTER shows up as follows.

Why are these additional columns (BUSINESS_UNIT, COMPANY_ID, ADDR_ID) showing up as part of the key? Compose throws an error if I do not have a value for these columns, so I am populating them with 0. I have a BUSINESS UNIT column in this table, but I cannot use that to map here as that value may be null in some case. What is the expected behavior here?

I believe this is due to the dummy column DATA_SOURCE that is part of the PK for every table. This has been added to ensure I am able to load entities from multiple sources. How should I be changing the model to fix this?

[ubanerjee]

Here is another example, but the behaviour is different in Compose.

upon import of the Erwin file the model generates as below.

UOM table shows up like this(Notice the missing DATA_SOURCE Column).

I am unable to understand how Compose differentiates between identifying vs Non-identifying relationships of Erwin in the Compose model. 

[TimGarrod]

Hi @ubanerjee  - 

Compose automated ETL will handle associating the physical DW tables together based on the surrogate keys (ID column).   In the Compose model you have relationships between your entities and those relationships are based on the LOGICAL KEY (typically considered the natural or business key in DW parlance). 

When processing the data, Compose needs to understand which source columns map to the parent(foreign key) tables NATURAL KEYs.   To do this, Compose adds the KEY columns for the related table into the mapping.  

So for your example - Agreement_Master has an FK to Business_Unit_Master <BUM>   Business_Unit is a KEY column for BUM - so it has been pulled down into the Agreement_Master mapping.    

What looks like an issue here (and something I would not do in Compose personally) - is making a relationship part of the KEY of the table.   Any attribute that is defined as a KEY requires a value to be populated (as you can't really have NULL's for KEYs ).   So if you have FK's defined - I would remove them from being part of the key and denorm the model slightly so the columns exist in both places.     This is also why you are seeing "cascading" of the natural key columns - eg. Company_ID.   Its been a while since I've worked in ErWin (17 years or so!) - so can you remind me - do you have Company FK defined as part of the KEY of Business_Unit_Master ?   (if so - thats why you are seeing cascading of the column Company_ID - if not it appears that the import somehow detected it as part of the key and that needs correcting). 

Hope this makes sense - happy to chat offline if not as sometimes its easier to discuss in person / with examples :). 

As an FYI - when you map source columns to the related key columns (eg Business_Unit in your case)  -

if the value in the source column is NULL and its defined as part of the KEY of the entity you are loading - you'll get the error.

if the value in the source column is NULL and its NOT defined as part of the KEY of the entity you are loading - the record will be tied to the auto-created "NULL" record (ID = 0).

if the value in the source column is NOT NULL and its NOT defined as part of the KEY of the entity you are loading - the record will be tied to the correct "parent" record if it exists or it will be inferred if it does not exist.