This section explains Data Warehouse's requirements for custom fields.

The Include in Data Warehouse option adds a custom field to a Data Warehouse table for use in reports. All the values of custom fields of a single category that are specified to be included in Data Warehouse are stored in one Data Warehouse table. When you design categories and their reportable fields, you must not exceed Data Warehouse's limits.

Data Warehouse requirements vary depending on whether your system uses an MS SQL Server or an Oracle database. Unless stated otherwise, the following requirements apply to both MS SQL Server and Oracle:

•A category name must be unique within its object if it has any custom fields included in Data Warehouse.

If a category name is NOT unique within its object, all the corresponding Data Warehouse tables will be created. However, Business Objects create only one class for all the Data Warehouse tables that have the same category name. If the objects within that class mix data from the Data Warehouse tables that have the same category name, the report may include incorrect data.

•Field labels must be unique within a category.

If a field label is NOT unique within its category, the corresponding Data Warehouse table will be created. However, Business Objects will add only one column to the corresponding class in the universe for all the non-unique field labels, and the report may include incorrect information because Business Objects use the data from only one of the fields that has a non-unique field label within one category.

•If a custom field is to be included in Data Warehouse, its field label must NOT exceed 35 characters.

If a field label exceeds 35 characters, its Data Warehouse table can be created, but Business Objects may not create the necessary object, so reports cannot include the custom field.

The TeamConnect limit for field labels is 50 characters, regardless of whether the field is included in Data Warehouse.

•For objects of the type Involved, the length of custom field names must NOT exceed 26 characters.

TeamConnect limits the length of custom field names to 30 characters. However, Data Warehouse scripts add a four-character suffix to the custom field names of objects of the type Involved, so the additional four characters could cause field names to exceed the 30-character limit. If the limit is exceeded, creation of the Data Warehouse table fails and an error is logged in the Data Warehouse log file.

•Custom field names must NOT equal any of the words that are reserved for use by your database server. Refer to your database documentation regarding column names for a list of those words.

Oracle Limitations

Data Warehouse tables in Oracle support a maximum of 1000 columns. They do not have a per-row data length limit. That is, in a single category, you can include up to 1000 custom fields in Data Warehouse, regardless of the field type. You must design your categories and reportable fields so that the 1000 column limit is not exceeded.

MS SQL Server Limitations

In MS SQL Server, Data Warehouse tables support a maximum of 8060 bytes per row. You should design your categories and reportable fields so that the 8060 byte limit cannot be exceeded. The following table lists the bytes allocated per column for each field type. These amounts are subtracted from MS SQL Server's maximum allocation of 8060 bytes per row.

For example, if there are 30 custom fields of the type Text within a single category, the Data Warehouse table has 30 columns that use 250 bytes each. Altogether, 30 custom fields of the type Text use 7500 bytes of the 8060-byte allocation, which only leaves 560 bytes for other field types in that category.

Bytes Allocated for each Field Type (MS SQL Only)

Important: When run with MS SQL Server, Data Warehouse has a 250-byte limit for custom fields of the type Text. If a user enters data in excess of this 250-byte limitation, the Data Warehouse scripts truncate the text when populating the table.

TeamConnect limits data entry in custom fields of the type Text to 2000 characters. However, when the Data Warehouse scripts are run with MS SQL Server, any text that exceeds the 250-byte limitation is truncated.

In addition, some Non-ASCII characters use two bytes per character of the 250-byte data length limitation. To make sure that users do not exceed the 250-byte limit, which results in truncated data in reports, choose one or more of the following options:

•Use memo text fields instead of custom fields of the type Text if users might exceed the 250-byte limit.

•Develop rules in TeamConnect to enforce the 250-byte limit for each custom field of the type Text.

Data Warehouse tables in MS SQL Server support a maximum of 1024 columns as long as the number of bytes for all fields in the category does not exceed the 8060 byte limit. For example, if a category has 1000 check-boxes and 24 memo text fields included in Data Warehouse, MS SQL Server supports all of the fields because the total allocation is less than 8060:

(1000 * 4 bytes) + (24 * 16 bytes) = 4384 bytes

MS SQL Server does not limit the creation of Data Warehouse tables with a large number of columns, although it displays a warning in the Data Warehouse log file about the 8060 byte limit. If a user attempts to insert data into a table in excess of the 8060 byte limit, MS SQL Server displays an error, the system cannot access the Data Warehouse table, and the data cannot be transferred to the Data Warehouse table.

For more information on Data Warehouse, see the Data Warehouse Help.