Importing Data into PeopleSoft Enterprise CRM

This chapter provides an overview of the Data Import Application Engine (AE) process (RBIMPORT) and discusses how to:

• Set up data import global settings.

• Define data import templates.

• Edit matching SQL.

• Run the Data Import process.

• View the Data Import process status.

• Run the Basics Data AE process.

Understanding the Data Import Application Engine Process (RBIMPORT)

This section lists prerequisites and discusses:

• Data Import process steps.

• Matching SQL.

• When to use the Basics Data AE process to load basic data.

Prerequisites

Before you import data into PeopleSoft Enterprise Customer Relationship Management (PeopleSoft Enterprise CRM), ensure that:

• The input file is sorted in key field order. For example, when you import multiple contacts to the same company, you must sort the input file on the Company field. This prevents duplicate BO_IDs from being assigned to the same company when all the matching criteria you specified are not met.

• The PeopleSoft super user ID has CREATE TABLE privileges for dynamic table creates.

• You define the FTP server so that it can store import file attachments.

  To do this, modify the RB_IMP_ATTACH URL identifier by using the URLs - URL Maintenance page in PeopleTools.

  See Enterprise PeopleTools 8.50 PeopleBook: System and Server Administration

• You define data import global settings.

Data Import Process Steps

The Data Import process enables you to:

• Batch-load external data from other systems (in flat file format) into the PeopleSoft Enterprise CRM database.

  You do this mainly to load companies, contacts, and consumers. You also can generate marketing lists from the data that you load.

• Update existing individual and organization records with profile data for better targeting and personalization of marketing efforts.

  Note. You cannot import profile fields of the Text Block data type, although you can import other fields in records that contain text block fields.

This diagram shows the steps of the data import process.

Data Import process flowchart

To import data into PeopleSoft Enterprise CRM:

1. Create a flat file with an accepted delimiter (tab, comma, or semicolon) for the import.

   Data import accepts flat files with rows that have a blank address, phone number, and email address.

2. Define an import template that uses the flat file.

   The import template must specify:

   • Identifying information, such as the template name, role of the imported data, import file fields, import file name, and optionally, audience.

   • Specifications for mapping between input fields and database fields.

   • Matching criteria, or the criteria for determining whether the imported data matches existing database information.

3. (Optional) Validate the import template.

   You can validate the template setup against the data file contents by running the Data Import process with the File Validation Only check box selected.

   The file validation step checks the file contents and structure and writes errors to an error file. You can use this file to correct any problems prior to importing data. Examples of validations are length of fields in the import file compared to field lengths that are in the database, compatibility of data types, and number of delimiters in each row.

4. Run the import process.

   You use the Data Import page to identify the import file, schedule the run, and specify import parameters.

5. View the status of the import process.

   On the View Import Status page, you can view import dates, times, and statuses.

6. View import results including statistics on the number of inserted, matched, updated, and in-error import rows.

7. Correct data errors that prevent records from successfully loading and run the Data Import process again.

Matching SQL

When the Data Import process runs, it dynamically builds SQL statements that match input files with database fields, based on the matching criteria that you enter. These statements are stored in the stage and matching tables. When you schedule the Data Import process, you can choose to keep these tables and later fine-tune the generated SQL to improve data import performance.

Important! You should perform this function only with the assistance of qualified consultants.

One bad change may render the whole Data Import process nonfunctional.

See Also

Business Object Relationship Modelling

When to Use the Basics AE Process to Load Basic Data

The Data Import process automatically updates the basic data tables if you have selected the Process Basic Data Summary option on the CDM System Options page. However, situations might arise when you cannot use the Data Import process, for example:

• During initial data load.

• When you enable a new role or relationship.

• If you are updating the BORM from legacy systems and choose not to use the PeopleSoft-provided APIs.

In these cases, you must use the Basic Data Load Application Engine process (BO_BASICS) to update basic data.

See Also

Understanding Business Object Relationship Model Components

Setting Up Data Import Global Settings

To set up data import global settings, use the Data Import Global Settings (RB_IMP_GLB_SETUP) component.

This section discusses how to define data import global settings.

Page Used to Set Up Data Import Global Settings

Defining Data Import Global Settings

Access the Data Import Global Setup page (Customers CRM, Data Import, Data Import Global Settings, Data Import Global Setup).

Default SetID	Enter the default setID. The Data Import process enters the default setID on any target table that requires a set ID while it processes import data. Examples of tables that require setIDs are RD_PERSON and RD_COMPANY.
Currency Code	Enter the default currency code, which the Data Import process uses for target tables that require a currency code.
Last Object Id Sequence	Enter a starting integer. Use this field when you create object IDs that are not system-generated. The format of generated IDs is sequence number:counter. For example, if the starting sequence number is 1, and you import a file with 100 companies, the format of the generated IDs is 1:1 through 1:100. A subsequent load of 50 company objects generates IDs 2:1 through 2:50.
Run Security AE (Run Security Application Engine)	Select this check box to run the security AE process against imported data. If this is not selected, imported data will not be available for applications that use application security. Note. You can run the security AE against the entire database at a later time to make this data available.

Stage Table Generation

Defining Data Import Templates

To define data import templates, use the Data Import (RB_IMP_DATA_CONFSR_GBL) component.

This section discusses how to:

• Access data import templates.

• Define data import templates.

• Define import files.

• Map source fields to target fields.

• Specify concatenation.

• Define matching criteria.

• Verify template creation.

Pages Used to Define Data Import Templates

Accessing Data Import Templates

Access the Data Import page (Customers CRM, Data Import, Data Import Templates, Data Import).

The appearance, behavior, and personalization options for this page are controlled by the search definition for the Data Import Template component.

Defining Data Import Templates

Access the Data Import - Step 1: Define Import Template page (click the Create a New Import Template button on the Data Import Template Search page).

Import Role	Select the role to associate with the import template. All records that are in the file are imported into the CRM database with the selected role. Values are: Company: Select for company or partner company records including sites for the company. You must import sites in the context of a company by using the Company template. Company/Contact: Select for individual records for persons who function as contacts for companies. Consumers: Select for persons who do not function as contacts for companies. Note. The role that you select here determines the objects to which you can map. For example, if you select Consumer, the only fields that are available to map to are those that are defined for the Consumer role.
Source	Enter the source of the information that is contained in the flat file. This field is associated with the template and not with individual records that are in the file. To associate a source with each record, create a text profile field that is called Source and use the Data Import - Step 3: Mapping page to map a constant value into the field.
Import Status	Displays the import status that the system assigns when you begin to create an import template. Initially, the status is In Process. It stays as such until you complete the steps to define an import template, at which time it changes to Active.
Next	Click to access the Data Import - Step 2: Define Import File page and proceed to the next page in the import definition process.

Import as Audience

You can create audiences only for import data with the Consumer or Company/Contact role types.

The Data Import process imports only contact methods with the type of Home when creating audiences with the Consumer role. Other contact method types, such as Business, are ignored. This conforms to the requirements of the Marketing product.

Defining Import Files

Access the Data Import - Step 2: Define Import File page (click the Next button on the Data Import - Step 1: Define Import Template page).

On initial access to this page when you are defining a new template, the Attach File button is available; the rest of the fields are empty. The Delete File and View File buttons are not available until you attach a file.

Mapping Source Fields to Target Fields

Access the Data Import - Step 3: Mapping page (click the Next button on the Data Import - Step 2: Define Import File page).

When you access this page, the source field labels appear in the Step 3: Mapping scroll area, and the Advanced scroll area is collapsed.

You must map at least one source field from the flat file to a target field in the CRM database and you must map all required fields (required fields have an asterisk at the end of the field name in the folder tree view).

If you can perform one-to-one mapping of the source fields to the target fields, use the Mapping scroll area only.

To use a default constant in a target field, concatenate source fields into one target field, or enter a source field into multiple target fields, use both the Mapping and Advanced scroll areas.

No Grouping

To give you more control over what fields Data Import considers unique when two or more continuous rows have the same data, PeopleSoft CRM added the No Grouping check box.

For example, assume that you are importing the following data:

Data Import considers First Name and Last Name unique, and therefore inserts only one row and updates the EMPLID to 124. If you, however, select the No Grouping check box, the system inserts or matches on every row.

Selecting Target Fields

When you click this link, a hierarchical tree structure appears with required fields marked by an asterisk (*). The hierarchy differs depending on the role that you selected for the import template. Required fields are at each folder level. If any field in the folder is mapped, then you must map the required fields in that folder and in the folders above it. The required fields provide the concatenated key to the target fields.

In the following example, the import role is Company and the source file field is Work Email. The Business Object Name field in the Organization - Company folder is required.

An intermediate level in the hierarchy, Contact, is not shown. The Lname field in the Contact folder is required and selected. The target field, Email Address, is defined in the lowest level folder,Business Email-Contact.

Mapping Source Fields to Target Fields

To map the source flat file fields to target fields, use the Step 3: Mapping region. Select a target for each field on the import file.

Advanced Mapping Actions

The advanced mapping actions enable you to update database records when a one-to-one correspondence does not exist between database fields and fields in the import file.

Specifying Concatenation

Access the Specify Concatenation page (select a Mapping Action of Concatenate in the Advanced region of the Data Import - Step 3: Mapping page and click the Specify Concatenation link).

Concatenation Type

Select Value to specify a constant in the Value column and Field to select from the import field labels in the Field Label list.

Defining Matching Criteria

Access the Data Import - Step 4: Matching page (click the Next button on the Data Import - Step 3: Mapping page).

This page enables you to specify the database fields that must match fields in the import file to update the database from the import file. If no match is found, the system inserts the incoming row as a new record in the CRM database. If a match is found, the system updates the existing record with new information from the file. You can override the update by deselecting the Update Matched Records check box when running the import process. In that case only, the system writes matched records to the error file.

New data never overwrites or updates an existing record's contact method fields (phone, email, and address); the new fields are appended as additional contact methods. All other types of mapped fields are updated or overwritten by the new data for matched records.

Warning! All fields, including email address, are case-sensitive for matching. To avoid instances in which a match is not found due to case-sensitivity, you should convert fields such as email address to lowercase prior to import. For example, convert Joe_Smith@ABC.com to joe_smith@abc.com before importing the file.

Matching Logic

Each template's matching criteria consists of one or more OR conditions, each of which contains an AND condition or a single field that must match. For example, in the condition shown, either all three of the fields, Busn_Email.Email Address, Contact.Last Name, and Company.Business_Object_Name must match their corresponding database fields or both of the fields Company.Business_Object_Name and Busn_Email.Email Address must match the database fields.

Note. You cannot nest conditions.

Note. You might see matching statements with conditions that are separated or nested using parentheses. These statements were developed in a previous version of PeopleSoft CRM. For upward compatibility, these statements act the same as they did in previous versions. However, if you make any changes to the statement, you must enter it in the new format

Verifying Template Creation

Access the Save Confirmation page (click the Save Import Template button on the Data Import - Step 4: Matching page).

This page displays:

• A message that indicates whether the template is saved successfully.

  The Import status is set to Active.

• The Run Data Import link.

  Click this link to access the Run Data Import page and immediately run the import process.

• The Go Back to Import Definition link.

  Click this link to access the Data Import - Step 1: Define Import Template page, view the existing template, and make any necessary changes.

Editing Matching SQL

To edit matching SQL, use the Edit Matching SQL (RB_IMP_MATCH_EDIT) component.

This section discusses how to:

• Select a statement for editing.

• Edit a statement.

Pages Used to Edit Matching SQL

Selecting a Statement for Editing

Access the Edit Matching SQL page (Customers CRM, Data Import, Edit Matching SQL).

This page enables you to view SQL statements and their processing time from the last run of the data import template. You can select a statement and edit it.

Editing a Statement

Access the Edit SQL page for Data Import page (click the Edit button for any SQL statement on the Edit Matching SQL page).

Use this page to cut and paste the SQL statement to Query Analyzer, tune it, and replace the SQL statement with the tuned statement.

For example, an administrator runs a Data Import job with the Keep Tables option selected. After the job finishes or terminates due to performance issues, the administrator can access this page to review the SQL statements to see how long each one took and start tuning those ones that took a long time.

When finished editing, the administrator saves the matching SQL and can rerun the same Data Import template.

Running the Data Import Application Engine Process (RBIMPORT)

This section discusses how to run the Data Import process.

Pages Used to Run the Data Import Application Engine Process (RBIMPORT)

Running the Data Import Application Engine Process (RBIMPORT)

Access the Data Import page (Customers CRM, Data Import, Run Data Import, Data Import).

File Information

File Field List

The File Field List region displays the fields that are associated with the import template. This enables you to verify whether the import works in the case where you access the Data Import process directly from the menu and use an existing template.

Import As Audience

The fields in this section function similarly to the identical fields on the Define Import Template page. Values that you enter override the value on the import template.

Import Status

The Import Status group box appears only after you click the Import Data button.

Viewing the Data Import Process Status

This section discusses how to view the status of the Data Import process. You can:

• View the status of one data import process instance.

• View import statistics.

• View the data import process error file..

Pages Used to View the Data Import Process Status

Viewing the Status of One Data Import Process Instance

Access the Data Import page (Customers CRM, Data Import, Run Data Import, Data Import).

Viewing Import Statistics

Access the View Import Statistics page (click the View Import Statistics link on the Run Data Import page or the View Import Status page).

This page displays the import statistics from the Data Import process.

Viewing the Data Import Process Error File

Access the error file.

The error file looks just like the input flat file except that after every row messages appear about what is wrong with the input row. Correct the problems with data on this error file and then rerun the Data Import process by using this file.

Running the Basics AE Process

This section provides an overview of the Basics AE process (BO_BASICS) and discusses how to:

• Disable unnecessary roles, relationships, and contact methods.

• Optimize the database for performance.

• Submit the Basics AE process.

Understanding the Basics AE Process

The Basics AE process reduces the complex tables and relationships contained in the BORM to two flat tables, BO_BASIC_IND (for data that is related to individuals) and BO_BASIC_ORG (for data that is related to organizations). For every customer business object that is enabled for Basics by role or relationship, the Basics AE finds all related business objects and writes the information to the relevant tables for use by the PeopleSoft Enterprise Online Marketing and Marketing products.

For example, a customer company has four sites, each site has four contacts, and each contact has four contact methods. The process locates 64 (4 times 4 times 4) business objects to get contact information for the customer and organizes this information into a single BO_BASIC_IND record per role and relationship type keyed by the customer's business object identifier (BO_ID), role, and relationship ID. Rows for both the company and the site are populated in the BO_BASIC_ORG table.

On a large database, for example seven million rows, this process might run for several hours. You can significantly improve the performance of this process by enlisting the services of qualified implementation consultants or a database administrator (DBA). Working with these persons, you can tailor the Basics AE process to meet specific enterprise requirements and improve process performance.

The contact methods displayed in the Audience result grid are taken directly from the Basic Data tables. To show values for all the contact methods in the Audience result grid, Contact/Consumer contact methods must be set up to follow the corresponding mapping logic for Basic Data.

Step 1: When adding a person, you should ignore the description field, and concentrate on the Type fields. After doing that, remember that the first data entry you make by default becomes the primary contact information for all roles.

Step 2: Go to the Advanced Options section of the Address Book, and manually define the primary address, phone, and email info for each role.

Page Used to Run the Basics AE Process

Disabling Unnecessary Roles, Relationships, and Contact Methods

The Basics AE process dynamically reads all enabled roles, relationships, and mappings. To tailor the process for your particular requirements and improve process performance, you can disable or remove the following information:

• Basic data for roles.

  You are not required to implement all delivered roles for an installation, or copy all implemented roles to basic data. To disable a role for basic data, deselect the Enabled for Basic Data option for the role on the Role Type page.

  The roles that are enabled for basic data as delivered are:

  • Company

  • Site

  • Worker

  • Contact

  • Individual Consumer

  • Partner

• Basic data for relationships.

  You can also disable relationships for basic data if they are not used, for example, if an enterprise does not sell through the partner channel. Disable a relationship by deselecting the Enabled for Basic Data check box on the Relationship Type page. The relationships that are enabled for basic data as delivered are:

  • Primary Contact / Company

  • Contact / Company

  • Primary Contact / Partner

  • Contact / Partner

• Remove basic data mappings for contact method purposes that are not needed. You can add or clone these mappings if they are needed in the future.

  The basic data mappings are delivered with multiple contact method purpose types associated with each role and relationship. These are the contact method purpose types that are mapped as delivered, and they vary by role:

  • Phone - Business

  • Phone - FAX

  • Phone - Home

  • Email - Business

  • Email - Home

  • Address - Business

  • Address - Home

  To remove a contact method purpose from a role or relationship, display the role or relationship on the Basic Data Mappings page, scroll to the contact method purpose row, and use the Delete Row button.

Optimizing the Database for Performance

The DBA might perform some or all of the following actions:

Stripe the Disks on Which Data Resides

Striping is the process of dividing data into blocks and locating the blocks on different physical disks, which enables parallel database processing and speeds up the run.

Run SQL to Detect Problematic Data

The following data conditions can cause the Basics AE program to run inefficiently and can be detected and addressed prior to running the process:

• Duplicate primary contact methods.

  Use this sample SQL to detect duplicates in the Phone table. You must run the same SQL for the Address and Email tables. To do so, replace each occurrence of the table name PS_BO_SRCH_PHN with PS_BO_SRCH_ADD (for address) and PS_BO_SRCH_EML (for email) before running.

  SELECT BO_ID, ROLE_TYPE_ID, BO_REL_ID, COUNT(*) FROM PS_BO_SRCH_PHN BO_CM_VW  WHERE⇒
   BO_CM_VW.ROLE_TYPE_ID IN (2, 3, 4, 8, 9, 11) AND BO_CM_VW.BO_CM_START_DT <= { fn⇒
   CURDATE() }  AND BO_CM_VW.BO_CM_END_DT > { fn CURDATE() }  AND BO_CM_VW.CM_USE_⇒
  START_DT <= { fn CURDATE() }  AND BO_CM_VW.CM_USE_END_DT > { fn CURDATE() }  AND⇒
   BO_CM_VW.PRIMARY_IND = 'Y'  AND BO_CM_VW.PROFILE_CM_SEQ = (SELECT MAX(G2.PROFILE_⇒
  CM_SEQ)  FROM PS_BO_SRCH_PHN G2  WHERE G2.BO_ID = BO_CM_VW.BO_ID  AND G2.BO_CM_⇒
  START_DT = BO_CM_VW.BO_CM_START_DT  AND G2.CM_USE_START_DT = BO_CM_VW.CM_USE_START_⇒
  DT  AND G2.CM_USE_ID = BO_CM_VW.CM_USE_ID  AND G2.ROLE_TYPE_ID = BO_CM_VW.ROLE_⇒
  TYPE_ID  AND G2.BO_REL_ID = BO_CM_VW.BO_REL_ID  AND G2.BO_REL_POSITION = BO_CM_⇒
  VW.BO_REL_POSITION) GROUP BY BO_ID, ROLE_TYPE_ID, BO_REL_ID HAVING COUNT(*) > 1

• Duplicate BO_ID in the Person table.

  Run this SQL to detect duplicates in the Person table.

  SELECT BO_ID, COUNT(*) FROM PS_RD_PERSON GROUP BY BO_ID HAVING COUNT(*) > 1

Gather Runtime Statistics

The Basics AE process step CDMXSTAT gathers and updates runtime statistics that are used by the relational database engine to optimize queries and find free space. This provides information to the engine about the distribution of data and improves the chances of index usage. This step is recommended after large numbers of rows are inserted or tables are reorganized, or when indexes are added or removed.

The DBA can disable this step (by making it Inactive in Application Designer) prior to a run if the statistics are already updated.

Drop and Rebuild Indexes

Drop table indexes and re-create them manually after the Basics AE process runs successfully. This is especially helpful on DB2UNIX or DB2 platforms. You can drop the indexes manually or enable the Basics AE process step CDMXDROP to drop indexes

This is the list of indexes to drop and re-create:

• PS0BO_BASIC_IND

• PS1BO_BASIC_IND

• PSABO_BASIC_IND

• PSBBO_BASIC_IND

• PSCBO_BASIC_IND

• PSDBO_BASIC_IND

• PSEBO_BASIC_IND

• PS0BO_BASIC_TMP1

• PS1BO_BASIC_TMP1

• PS0BO_BASIC_TMP2

• PS1BO_BASIC_TMP2

• PSABO_BASIC_ORG_TMP2

• PSABO_BASIC_ORG

• PSBBO_BASIC_ORG

• PSCBO_BASIC_ORG

Inactivate Scheduled Jobs That Can Conflict with the Basics Process

The jobs CM_FLTM (for 8.9 and prior releases) and CM_MAINT (for release 9) are regularly scheduled to synchronize the normalized BORM tables to the flattened contact method tables. This improves access to contact methods for online processing, but can cause performance issues when run concurrently with the Basics AE.

To inactivate these jobs and any others that might cause data conflicts, use the PeopleTools Schedule JobSet Definitions page located under PeopleTools, Process Scheduler. Search for the job name and select Inactive in the Status field. Save the job definition.

When the Basics process ends, reactivate this job.

Submitting the Basics AE Process

To submit the Basics AE process, access the Application Engine Request page and add a new run control for the BO_BASICS program.

See Enterprise PeopleTools 8.50 PeopleBook: PeopleSoft Application Engine