The data harvested from your external system must be transferred into the special staging tables within the InterAction database. These tables are created when you create the data sets.
This section covers the following topics:
- Viewing the Table Structure
- View the Table Names and Columns for a Data Set Table
- Naming Conventions for Data Set Tables and Columns
- Documenting the External System
Viewing the Table Structure
When harvesting data, you must respect the properties of the data set tables that contain the data. This includes:
- Field length
- Field type
- Precision
- Nullable
For example, if the Nullable property for a column is set to No, you cannot add a row missing that field value to the table.
Some utilities for populating the tables (such as BCP) do not respect the table properties and lets you insert incorrect data. This generates an error during transformation. Be sure your data conforms to the table properties before populating the data set tables!
You can view the table name and table structure for a data set table in the main Application Collaboration view. The table structure includes the following for each column in the table:
- Name
- Data type
- Length
- Precision
- Nullable indicator
Understanding the table structure is important when designing your data harvesting routine.
View the Table Names and Columns for a Data Set Table
When you select a data source from the console list, the corresponding table names for all the related data sets display in the right pane. When you select a data set from the console list, the corresponding table structure for the related data set table displays in the right pane.
In the example shown in the figures below, companies (clients) should be harvested into the table IDCAPP.INT_DTS_COMPANY$1. This table has four columns - SYS_ID, COMPANY_NM, COMPANY_KNOWN_AS, and MAP_PRIMARY_CLIENT_NUMBER.
The number of rows of data harvested into a data set table displays next to the data set name in the console list.
Naming Conventions for Data Set Tables and Columns
All data set table names follow the same convention:
IDCAPP.INT_DTS_DataSetSpecificName$DataSourceID
The DataSetSpecificName varies depending on the type of data set the table represents.
For Company, Person, and Group Member data sets, this is always Company, Person, or Group Member respectively. For the other data sets, this depends on a name you define when creating the data set. You cannot use numbers in the names of data sets for this reason.
The DataSourceID is always a number representing the data source. The first data source ID is 1; Application Collaboration increments this number with each new data source.
Therefore, the data set table for companies for the first data source you create is named:
IDCAPP.INT_DTS_COMPANY$1
The table columns normally match the field names in InterAction, but with underscore characters instead of spaces. The columns always display in all upper case.
For example, if you select to populate the InterAction additional field Billings 2000, the column is named BILLINGS_2000.
Note that the column names must be valid SQL names. Therefore, some additional field names may be modified. For example, if a name begins with a number, Application Collaboration replaces the number with an underscore. Therefore, 2000 Billings becomes _000_BILLINGS.
Documenting the External System
The first step in designing your harvesting routine is to locate and document the data to retrieve in the external system.
This documentation should show how the columns in the data set tables should map to the fields in the external system. Include the specific tables and columns that contain it.
For example, you might fill in a table similar to this:
A reference describing all the columns in the harvesting tables starts Company Data Set Table.