Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Storing, Extracting, and Loading Additional Data > About Adding More Attributes to the Data Model >

Determining the Type of Table to Use for Attributes

Before you can store domained or free text attributes in the data warehouse, you must first decide what table the data goes in. The following sections describe the options available to you; the options are listed in the order that they are recommended.

First Recommendation—Store Additional Attributes in an Existing Dimension or Fact Table

The preferred option is to store additional attributes is an existing dimension table that primarily stores this type of data. There are a few requirements that must be met before you can do this:

• The attribute stored in the dimension table must be at the same base grain as the table. Changing to a lower base grain may negatively affect joins to other tables, and it is therefore recommended that you do not change the base grain of the table.
• The relationship between the dimension table base grain to the attribute can be one-to-one, or many-to-one, but not one-to-many. For example, assume that the IA_PRODUCTS table's grain is the product number. So, for example, you cannot incorporate a store location code column, which takes the base grain of product, and matches it with several store attributes, changing the grain from products code to store code. However, you can add a color column to track that attribute of the product without changing the grain of the table.
• Given the limited number of extension columns, you must be selective when choosing data that you want to incorporate into the data warehouse. If you require more extension columns than are provided, keep attributes that are the most closely associated with the dimension table in that table, and place all other attributes in the other tables. For example, if you had a dimension table that covered the attributes of storage capacity for your warehouse, and you had both additional storage and location attributes to incorporate, you would choose to create a new location table, rather than split the storage capacity information. For information on creating new tables, see Table Formats.

Second Recommendation—Store Additional Attributes in a New Dimension Table and Extension Tables to Existing Dimensions

If you have a group of related attributes to load, but they cannot be incorporated into an existing dimension table or class table, then your final option is to create a new dimension table. For example, if you have a group of attributes that all relate to Profit Centers, then you can create a Profit Center dimension table. It is recommended that you create dimension tables for attributes that are grouped by a business area. For information on creating new tables see Table Formats.

Do not create a dimension table to store a disparate set of attributes. If you decide to create a new dimension table, use the same structure and naming conventions as the prepackaged dimension tables. Structurally, the new dimension table must contain columns such as primary key, Source ID, Key ID, fact keys, dimension keys, and so on. In addition, with each of these columns, there are naming conventions. For information on naming conventions, see the Siebel Customer-Centric Enterprise Warehouse Data Model Reference.

If you need to add more columns to a dimension table and if all the extension fields of the existing dimension are used, create an extension table to the dimension table with the same Surrogate Key, Key ID, source if and effective from, and two dates to populate these extra fields.