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

Dimension Table Format

Dimension tables always start off with a surrogate key. The surrogate key is always the primary key for the table. The naming convention for dimension tables is the same as fact tables, IA_[SUBJECT], for example, IA_PRODUCTS.

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

1. Surrogate key. The data type is decimal (10, 0) or decimal (15, 0) (for example, PRODUCT_KEY).
2. Dimension keys. The data type for the dimension key is decimal (15,0) (for example, the VISITOR_KEY in the IA_CUSTOMERS dimension table).
3. Date keys. The data type is decimal (15, 0) in Julian format and the suffix DK (for example, CREATED_ON_DK).
4. Attribute columns. Attribute columns can be of several different types, including descriptive and code, and the data type varies depending on the type. If it is a descriptive type such as name, the data type is varchar (254); if it is a code, it is varchar (30); all others, including number types, are either varchar (80) or varchar (30).
5. Hierarchy columns. The data type for codes is varchar (30), and for names the data type is varchar (254). The number of the hierarchy columns is based on the number of nodes in the hierarchy and there is no specific limit. Hierarchy columns are entered in code or name pairs, with the naming convention of [DIMENSION TABLE ABBREVIATION]_HIER[SEQUENTIAL NUMBER]_CODE, or [DIMENSION TABLE ABBREVIATION]_HIER[SEQUENTIAL NUMBER]_NAME. Each pair uses the same number, corresponding to its level in the hierarchy, for example, PROD_HIER1_CODE and PROD_HIER1_NAME in the IA_PRODUCTS dimension table are at the first level.

   NOTE:  This column is only applicable to a few dimension tables.

6. Code and Description columns. The data type for codes is varchar (30), and the data type for descriptions is varchar (254), with respective CODE and DESC suffixes (for example, DIVISION_CODE and DIVISION_DESC).
7. Extension columns. There are principally two kinds of extension columns in the following order:
   • Extension columns for additional code or name pair attributes:
     • Naming conventions of [DIMENSION TABLE ABBREVIATION]_ATTR[SEQUENTIAL NUMBER]_CODE and [DIMENSION TABLE ABBREVIATION]_ATTR[SEQUENTIAL NUMBER]_NAME.
     • Data types varchar (30) and (254).
     • CODE and NAME suffixes (for example, PROD_ATTR1_CODE and PROD_ATTR1_NAME).
   • Extension columns for additional textual information have a naming convention of [DIMENSION TABLE ABBREVIATION]_ATTR[SEQUENTIAL NUMBER]_TEXT, with data type varchar (254), and TEXT suffix (for example, PROD_ATTR1_TEXT and PROD_ATTR1_NAME).
8. Control columns. Control columns include CURRENT_FLAG and DELETE_FLAG varchar (1), KEY_ID varchar (80), SOURCE_ID varchar (30), and IA_COPYRIGHT varchar (254), IA_INSERT_DT, IA_UPDATE_DT, EFFECTIVE_FROM_DT and EFFECTIVE_TO_DT.where EFFECTIVE_FROM_DT and EFFECTIVE_TO_DT are added to handle slowly changing dimensions and are dependent on the database; datetime (26, 6) for SQL Server; date (26,6) for Oracle; and timestamp (26,6) for DB2.