Bookshelf Home | Contents | Index | PDF     

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

Fact Table Format

A fact table that is linked to other tables requires a surrogate key. If the fact table is used in other mappings the surrogate key becomes the primary key. If the table is not used in other mappings, the primary key is a composite of the KEY_ID and SOURCE_ID. The naming convention for fact tables is IA_[SUBJECT], for example, IA_AP_XACTS.

The following is the order of the columns, data type, and precision for fact tables:

  1. Surrogate key. The data type is decimal (10,0) or (15,0) (for example, in the fact table for Accounts Payable transactions, IA_AP_XACTS, the surrogate key is AP_XACTS_KEY).

    NOTE:  This column does not apply to all fact tables.

  2. Dimensions keys. The data type is decimal (10,0) or (15,0) (for example, the GL_ACCOUNT_KEY in IA_AP_XACTS).
  3. Date Key columns. The data type is decimal (15,0) in Julian format with a DK suffix (for example, CREATED_ON_DK is a date key in the IA_AP_XACTS fact table).
  4. Amount columns. The data type is decimal (28,10) ordered by currency type and with an AMT suffix (for example, AP_GRP_AMT in IA_AP_XACTS).
  5. Quantity columns. The data type is decimal (18,3) with a QTY suffix (for example, XACT_QTY).
  6. Code columns. The data type of all codes is varchar (30) with a CODE suffix (for example, UOM_CODE). There are two kinds of codes:
    • The first code type contains units of measure and currency.
    • The second code type is code-name pairs where the code and the code name are both stored, such as states where there is both CA and California.
  7. Other fact attributes. The data type of the attribute is subjective to the kind of attribute. For example, for ACCT_DOC_NUM, the data type is varchar (30), while for ACCT_DOC_ITEM the data type is decimal (15,0).
  8. Description columns. The data type for the description columns is varchar (254) or varchar (255) with a DESC suffix (for example, GL_ACCOUNT_DESC).
  9. Name columns. The data type of the name columns is varchar (254) or varchar (255) with a NAME suffix (for example, GL_ACCOUNT_NAME).
  10. Extension columns. Extension columns have the same data type and precision as their column type (for example, amount, quantity, or code). The order for the extension columns is as follows:
    • The dimensional key naming convention is [FACT TABLE ABBREVIATION]_DIM[SEQUENTIAL NUMBER]_KEY (for example, APXT_DIM1_KEY in IA_AP_XACTS).
    • The format for data keys is [FACT TABLE NAME]_DATE[SEQUENTIAL NUMBER]_DK (for example, APXT_DATE1_DK in IA_AP_XACTS).
    • The quantity naming convention is [FACT TABLE NAME]_[SEQUENTIAL NUMBER]_QTY (for example, APXT_1_QTY in IA_AP_XACTS).
    • The amount naming convention is [FACT TABLE NAME]_DOC[SEQUENTIAL NUMBER]_AMT (for example, APXT_DOC1_AMT in IA_AP_XACTS).
    • The code and name pair naming convention is [FACT TABLE NAME]_ATTR[SEQUENTIAL NUMBER]_CODE and [FACT TABLE NAME]_ATTR[SEQUENTIAL NUMBER]_NAME (for example, APXT_ATTR1_CODE and APXT_ATTR1_NAME in IA_AP_XACTS).
    • The text column naming convention is [FACT TABLE NAME]_ATTR[SEQUENTIAL NUMBER]_TEXT (for example, APXT_ATTR1_TEXT in IA_AP_XACTS). (The data type for text columns is varchar (254).)
  11. Control columns. The data type and precision of the control columns varies. KEY_ID is data type 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.
    
Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide