When RPI loads a file into the data warehouse, a number of steps are undertaken:
•When loading the data project’s first file:
o A database table is created. The table is named “UD_ + [table name]” ([table name] being supplied by the user in the Definition tab’s File Analysis panel). All fields within the table are created as nullable. Fields are created in accordance with the data type and size stipulated at definition. If a table of the same name already exists, it is dropped and replaced.
o Data is loaded into the table.
o To support future Redpoint Data Management functionality, the following fields are always added to the custom table into which data is loaded:
§ BRAND_FILE (Nvarchar (128))
§ SEQ_NUM (Bigint)
§ BRAND_DT (DateTime)
§ JOBID (Nvarchar (64))
o A SQL Server primary key is created in accordance with any specified Key settings.
•If loading a second or subsequent file within a data project:
o The actions undertaken are dependent on the value selected for Post-initial load behavior in the Definition tab’s File Analysis panel:
§ Insert new, update existing: new records are inserted into the table. Existing records (as defined by the data project’s key) are updated.
§ Insert only: new records are inserted. Any existing records are treated as errors.
§ Update only: existing records are updated. Any new records are treated as errors.
§ Complete refresh: the existing contents of the table are erased and replaced by the records in the file.
Note that, in all cases, only valid records are loaded. Any trailing and preceding spaces within the data are trimmed.
o The data file is archived, being copied to the folder defined by system configuration setting DataIntakeArchiveDirectory. Each data file is stored in a uniquely-name subfolder.