Bookshelf Home | Contents | Index | Search | PDF

Siebel Data Warehouse Installation and Administration Guide > Customizing the Siebel Data Warehouse > Siebel Data Warehouse ETL Process Concepts >

About Siebel Data Warehouse Extension Tables

---

The extension tables are provided with the Siebel Data Warehouse exclusively to help customize the data warehouse tables.

CAUTION:  Do not customize existing mappings or the database schema. If you need to customize the standard mappings, create a new mapping that loads the extension table instead.

For every extension table, there is an extension staging table that is populated by the custom SDE process. The custom SIL process sources data from this staging table and populates the final extension table. For information about how to use extension tables, see Customization Steps for Siebel Data and Siebel Data Warehouse Customization Steps for Non-Siebel Data.

The extension tables have a 1:1 relationship with their corresponding dimension and fact tables.

The data warehouse extension tables are different from the OLTP database extension tables in the following ways:

• Data warehouse extension tables are not maintained by Siebel Tools.
• There is no relationship between data warehouse extension columns and the corresponding OLTP extension columns. For example, attr01 in S_ORG_EXT_X is not the same as attr01 in W_ORG_DX.
• Mappings have to be implemented explicitly to populate the extension columns.

Using the Extension Tables

The extension tables have a 1:1 mapping between the dimension and fact records to the extension records. To insert a record into a dimension or fact table, choose the appropriate column and write a new mapping that uses the ETL server's incremental logic to load the staging table. When populating data from the staging table to the final dimension or fact tables using the INTEGRATION_ID and the DATASRC_NUM_ID, find the primary key of the dimension table and use that as the primary key of the extension table.

Naming New Extension Tables

When you create a new extension table, use the same naming convention as is used in the Siebel Data Warehouse by adding the prefix WC to the table name, for example, WC_TABLENAME_DS. This will simply future upgrades to the Siebel Data Warehouse.

NOTE:  If a new table is created, you need to register the table and its indices in the DAC repository. Also, if you modify indices on existing tables, you need to register the indices.

Creating New Dimension or Fact Tables

You can create new tables using the same logic as is used for extension tables.

CAUTION:  The data in any customized table will face upgrade issues.

If you are creating a new dimension or fact table, use the required system columns that are part of each of the data warehouse tables to maintain consistency and the ability to reference existing table structures. When you create a new table, you need to register the tables and its indices in the DAC repository.

Required Columns

For main and extension staging tables the following columns are required:

• INTEGRATION_ID. Stores the primary key or the unique identifier of a record as in the source table.
• DATASRC_NUM_ID. Stores the Data source from which the data is extracted. For example, Siebel OLTP is 1.

For data from any external data source assign a number greater than 1.

For dimension and fact tables, the required columns are the INTEGRATION_ID and DATASRC_NUM_ID columns as well as the following:

• ROW_WID. A sequence number generated during the ETL process, which is used as a unique identifier for the data warehouse.
• ETL_PROC_WID. Stores the ID of the ETL process information. The details of the ETL process are stored in the S_ETL_RUN table on the OLTP side and W_ETL_RUN_S table on the OLAP side. This is also the Process ID on Current Run/Run History screen in the DAC.

The required columns for dimension and fact extension tables are ROW_WID and ETL_PROC_WID.

Bookshelf Home | Contents | Index | Search | PDF