Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Storing, Extracting, and Loading Additional Data > About Integrating Data from Source Systems Without Prepackaged Business Adapters >

Universal Source Flat File Template

Given the vast differences in source data, Siebel Customer-Centric Enterprise Warehouse does not prepackage transformation logic other than date transformations. Therefore, there are a few requirements on how to present source data when using the universal ETL components. Siebel Customer-Centric Enterprise Warehouse provides a flat file template that delineates what data to provide, as well as the format in which the data must be presented. Each flat file template corresponds to exactly one data warehouse table. The following are the specifications for the source flat file:

• Supply all the universal source data in a comma delimited flat file (*.csv). Because flat files have only number and string data types, all dates must be provided in the String (14) data type. For example, you can use 200112310000 for December 31, 2001.
• Specify the values for the Key ID and Source ID in the flat file. Unlike prepackaged sources, these values are not formed in the Source Adapter for universal sources.
• Preset the record deletion flags. The delete flag can have the values Y or N.
• If applicable, supply the source Effective To and Effective From dates in the flat file. If the effective dates are not supplied, Siebel Customer-Centric Enterprise Warehouse inserts default values—January 01, 1899 for the Effective From date and January 01, 3714 for the Effective To date.
• Each flat file template has 10 system columns that are not used. These columns are named RESERVED_1, RESERVED_2, and so on. These are not customization columns for your use; Siebel Business Analytics reserves these columns for future development.

  NOTE:  The IA_COPYRIGHT column is populated during an initial load (M_Z_INITIAL_LOAD) from one of the prepackaged CSV files.

Each of the extract mapping components performs the following functions:

• Source Qualifier. The Source Qualifier provides the means for extracting the data from the source.

  NOTE:  You can use the Source Qualifier to join multiple source flat files from the same database platform. However, if you are sourcing from multiple sources that belong to different database platforms you cannot use a Source Qualifier; you must use a Joiner Transformation to join the sources.

• Business Component. The Business Component is omitted from the extract mapping for universal sources as there are no transformations to perform other than the date data type change. Therefore, you can either transform the data prior to it being extracted by the universal adapter or incorporate transformation logic within the universal adapter.
• Expression Transformation. As previously mentioned, the Expression transformation only converts dates with the string(14) data type into a timestamp data type, which is the standard data type used in the data warehouse tables.
• Staging Table. The staging tables are the target tables for the extract mappings. Each staging table mirrors its corresponding load control table structure. However, it does not have control columns, such as CURR_KEY, IA_INSERT_DT, IA_UPDATE_DT, and other CURR_* columns. If a load control table is not available, the staging table mirrors the IA table, except that instead of *_KEY and *_DT columns, it uses *_ID and *_DT columns.

After data is extracted, the data then goes through a load mapping, described in the following section.