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

Aggregate Table Format

Aggregate tables aggregate facts across a dimension or set of dimensions. You can have several aggregate tables linking to the same tables, but aggregating data for different time periods. for example, there are three contact center aggregate tables (IA_CC_REP_A1, IA_CC_REP_A2, and IA_CC_REP_A3) that aggregate various contact statistics by hour, day, and month respectively.

The first keys in an aggregate table are the Dimension keys that link the table to the dimension tables for which it is aggregating data. As illustrated in the IA_CC_REP_A1 example, the naming convention for aggregate tables is IA_[SUBJECT]_A[SEQUENTIAL NUMBER].

The following is the order of the columns, data type, and precision for IA aggregate tables.

1. Dimension keys. The data type is decimal (10, 0) or decimal (15, 0). Dimension keys link the aggregate table to the dimension tables that could be used to analyze the aggregate table. For example, SUPERVISOR_KEY links IA_CC_REP_A1 with the Employee dimension table. There can be several dimension keys drawing information from several different dimension tables.
2. Date columns. The data type is decimal (10,0) or decimal (15,0), with a DK suffix (for example, PERIOD_START_DK).
3. Amount columns. The data type is decimal (28,10), with an AMT suffix (for example, TRUNK_COST_AMT).
4. Quantity columns. The data type of (18,3), with QTY suffix (for example, PRA1_1_QTY).
5. Currency Code columns. The data type is varchar (30), with a code suffix (for example, LOC_CURR_CODE).
6. Extension columns. Format for Extension columns varies and is as follows:
   • Dimensional keys with a format of [FACT TABLE NAME]_DIM[SEQUENTIAL NUMBER]_KEY, such as APXT_DIM1_KEY.
   • Date keys with a format of [FACT TABLE NAME]_DATE[SEQUENTIAL NUMBER]_DK, such as APXT_DATE1_DK.
   • 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.

     NOTE:  The data type for text columns is varchar (254).

7. Control columns. Control keys include CREATED_BY_KEY, CHANGED_BY_KEY, CREATED_ON_DK, CHANGED_ON_DK, DELETE_FLAG, IA_COPYRIGHT, and a composite primary key containing the KEY_ID and SOURCE_ID. 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.