I am looking for some ways (and possibly the best way) to assemble DI Studio job(s) to load a 4NF (fourth normal form) table structure in a database. For example, let's say there is an ID table called CUSTOMER - within it is a sequential numeric ID as the primary key and the Current_Name. As its 'children' tables, there are tables called INDIVIDUAL and ORGANIZATION - within each there is the CUSTOMER_ID (FK ) and SSN or TAX_ID respectively (with a few other columns). An finally, there are tables below ORGANIZATION that categorize the ID's into groups: BUSINESS, GOVERNMENT_AGENCY, NON-PROFIT - each of these only contains the CUSTOMER_ID (FK). [JPEG attached for clarity.] Without really questioning the 4NF structure and assuming that the data needed to populate these tables was available in a SAS dataset, what job or series of DI Studio jobs - and the transformations within - would be needed? Hopefully, you can see the challenge of where to even begin. Let's say, for even a simple example, you have a input SAS dataset of Government Agencies you wish to load. You may first need to check the ORGANIZATION table to see if it has already been loaded (sort of a SCD2 loader). If there are rows that are not already within the table, then you must load the Current_Name into the CUSTOMER table to get the latest Customer_ID's; then, you can load the ORGANIZATION table with the other content and finally load the Customer_ID's to the GOVERNMENT_AGENCY table. If this sounds right, which exact transformations would best accomplish this? DI Studio - 4.903 Thanks in advance!
... View more
