Siebel Marketing Guide > External Data Mapping > Working With Fields >

Maintaining Fields After Data Mapping

You can add and edit fields using the Fields list in the Tables view. You cannot delete a table (or its fields) once saved because other Siebel Marketing components may have established dependencies on the table and fields.

If a field's data type is inappropriate for its intended use, you can change its type. (Examples include Social Security numbers that appear as numeric fields instead of characters and dollar values that appear as characters when they should be numeric.) To maintain fields after initial data mapping, perform the following tasks:

For information specific to Marketing fields and Visual Segmentation, see Mapping Siebel Analytics Subject Areas.

Adding New Fields After Data Mapping

When you add a new field to a table in the data warehouse, it does not automatically appear as a field in the corresponding Siebel Marketing table. You use the Fields view to add the field (or edit existing fields). The field that you add will be included in the table definition in the Marketing Repository.

To add a new field after data mapping

  1. From the application-level menu, choose View > Site Map > Marketing Administration > External Data Mapping.
  2. Click the Tables view tab.
  3. In the Tables list, select the table to which fields will be added.

    4. The Fields list displays the fields associated with the selected table.

  4. In the Fields list, create a new record.
  5. Complete the necessary fields, using the following table as a guide.

    6. Field
    Comment
    Data Retrieval Enabled
    This system field is blank as the default. A check mark appears in this field when Enable Data Retrieval is selected, and the field appears in the list in the Data Retrieval view with a label similar to the following:
    Accounts.LINE_OF_BUSINESS
    For more information, see Enabling Data Retrieval for Field Values.
    Data Type
    Choose the type of data this field contains from the list. The options are Char (alphabetic), and Double, Float, Long, and Short (numeric).
    The choice should reflect how the data will be used, not how it is designated in the data warehouse. For example, if the field holds ZIP Code or postal code information, it may be stored as numerical data. However, because you do not perform mathematical operations (add, subtract) with ZIP Codes, its type should be set to Char.
    Data from this field is automatically converted (if necessary) from its original type to the type specified.
    Description
    Type a description for the field. The description might include the range of values if the data type selected is different than the data type in the data warehouse.
    Expression
    Optional. Type an expression or use the default reference name.
    The maximum size for the expression is 512 bytes (characters). Date fields use a yyyymmdd format and are bound to a character (8). For details, see Creating Virtual Fields Using Expressions.
    Reference Name
    Type a name for the new field. Maximum of 50 characters.
    Width
    This field is automatically populated when you select the field's type. You can only edit the Width value if the Data Type is Char.

  6. Save the record.
  7. In the Fields list, select the field and click Enable Data Retrieval.

    8. Clicking this button retrieves a list of values associated with the field in your database. These values are available for use in segment and filter definitions. For more information, see Enabling Data Retrieval for Field Values.

Enabling Data Retrieval for Field Values

The Enable Data Retrieval function allows you to generate the list of distinct values associated with the field in a particular table. For example, a bank might want to create a hierarchy using a Region field.

When a table's field is selected (indicated by a check mark in the check box) and the Enable Data Retrieval button clicked, a one-level attribute hierarchy is created.

NOTE:  Fields which contain values that have a width of more than 50 characters will be ignored when you click Enable Data Retrieval. You must shorten the invalid field name before attempting to retrieve data for these fields. During the validation process, a message notifies you that the field is invalid and asks if data retrieval should proceed with other valid fields. In addition, the combined total length of tablename.fieldname should not exceed 40 characters or data retrieval will fail for that field.

The one-level hierarchy is visible in the Data Retrieval list of records awaiting data retrieval (Marketing Administration > External Data Mapping >Data Retrieval view tab). When you select the one-level hierarchy in the Data Retrieval list, and click Retrieve Data, the results are available for selection in the Edit Segment view, and the Filters Detail view (Fields option). For more information, see Enabling Data Retrieval for Field Values and Defining Filter and Segment Criteria.

You can set up data retrieval to occur automatically when you refresh your data warehouse. Using this feature, you can make sure that fields and attributes with volatile datasets are regularly synchronized with the Marketing Repository. For more information, see Automatic Data Retrieval.

Creating Virtual Fields Using Expressions

If you need a field that does not exist in the data warehouse but can be derived from an existing field, you can use the expression function to create virtual fields to provide data that is not explicitly available in your database. You can also use an expression to convert data, for example, converting nulls to strings.

Frequently, expressions are used to convert date information to character information. When you map or connect to a table that has a date field, the date field is automatically converted to a Long field using the following expression:

( { fn YEAR (DATE_FIELD) } * 10000) +

( { fn MONTH (DATE_FIELD) } * 100) +

( { fn DAYOFMONTH (DATE_FIELD) })

NOTE:  This particular expression returns a date formatted as YYYYMMDD.

You might also use this method to create a new virtual field that provides only the month or only the year.

You can create virtual field expressions by typing the expression in the Expression field on the Fields list tab in the Tables view. Using virtual fields you can build many kinds of new fields. The functions available to you are those provided by your ODBC driver, SQL, and your RDBMS.

When creating virtual fields, follow a consistent naming convention. For example, if you add a field based on an expression, make the first letter of each word uppercase (for example, Month, SSN, Year). Use uppercase letters for fields that exist in the data warehouse (with or without expressions). This method allows you to recognize which fields are from the data warehouse and which are virtual fields that you created using fields in the data warehouse.


 Siebel Marketing Guide, Version 7.5, Rev. A 
 Published: 18 April 2003