Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Integrating Additional Data > Table Formats >

Load Control Table Format for Fact Tables

There are different load control table formats for both fact tables and dimension tables. This section focuses specifically on load control tables for fact tables. The first column in a load control table is the Current Key (CURR_KEY). It is the Current Key that links the load control table with the surrogate key in the fact table. The naming convention for a load control table is OD_[SUBJECT], for example, OD_AP_XACTS.

The following is the order of the columns, data type, and precision for load control tables for facts:

1. Current key (CURR_KEY). The data type for the current key is decimal (15, 0).
2. ID columns. The data type for ID columns is varchar (80), with a suffix of ID, which corresponds to the *_KEY columns in the fact (IA) table (for example, SALES_ORDLN_ID in the OD_SALES_PCKLNS load control table corresponds to the SALES_ORDLN_KEY).

   NOTE:  These columns only exist in an OD_* load control table if the corresponding IA_* warehouse table contains a surrogate key. For example, the IA_SALES_ORDLNS warehouse table uses a surrogate key for SALES_ORDLNS_KEY column; therefore, OD_SALES_ORDLNS table has a SALES_ORDLN_ID column.

3. Date columns. The data type of date columns is datetime (26, 6) for SQL Server; date (26,6) for Oracle; and timestamp (26,6) for DB2, with a suffix of DT (for example, CREATED_ON_DT). This column corresponds to the *_DK columns in IA fact tables.
4. Amount columns. The data type for the amount columns is decimal (28, 10) with a suffix of AMT (for example, NET_GRP_AMT). As with the amount columns in the fact table, they are in order of currency—group, local, and document.
5. Codes. The data type of all codes is varchar (30), with a suffix of CODE (for example, UOM_CODE). The code columns correspond with the fact table, therefore if the fact table has both units of measure and currency as well as code-name pairs, the load control table has these values too.
6. Other attributes. The data type varies to suit the attribute, but corresponds to what is found in the fact table.
7. Extension columns. As found in the fact table and in the same order:
   • Dimensional IDs with a format of [FACT_TABLE_NAME]_DIM[SEQUENTIAL_NUMBER]_ID, such as APXT_DIM1_ID.
   • Date with a format of [FACT_TABLE_NAME]_DATE[SEQUENTIAL_NUMBER]_DT, such as APXT_DATE1_DT.
   • Quantity columns with a format of [FACT_TABLE_NAME]_[SEQUENTIAL_NUMBER]_QTY, such as APXT_1_QTY. Amount columns with a format of [FACT_TABLE_NAME]_DOC[SEQUENTIAL_NUMBER]_AMT, such as APXT_DOC1_AMT.
   • Code and Name pairs with a format of [FACT_TABLE_NAME]_ATTR[SEQUENTIAL_NUMBER]_CODE, or [FACT_TABLE_NAME]_ATTR[SEQUENTIAL_NUMBER]_NAME, such as APXT_ATTR1_CODE and APXT_ATTR1_NAME.
   • Text columns with a format of [FACT_TABLE_NAME]_ATTR[SEQUENTIAL_NUMBER]_TEXT, such as APXT_ATTR1_TEXT. The data type for text columns is varchar (254).
8. Control columns. The data type and precision of the control columns varies. KEY_ID is varchar (80), SOURCE_ID is data type varchar (30), and IA_COPYRIGHT is data type varchar (254). IA_INSERT_DT and IA_UPDATE_DT are dependent on the database; datetime (26, 6) for SQL Server; date (26,6) for Oracle; and timestamp (26,6) for DB2.