Creating SQL and Oracle Database Integration Projects Using EDI Studio

Data projects define the source database connection and schedule information for an integration task. Once created, the project can be imported into the ICPAM EDI module to begin data synchronization.

This section provides an example to import personnel records into ICPAM from one of the following databases:

·         MySQL version 5.0.4

·         Oracle versions 10g and 11g

·         SqlServer 2005 and SqlServer 2000

This example does not cover every possible scenario, and the specific records, fields and other data may not match the details for your site. Contact your database administrator for assistance when performing this process.

Because SQL and Oracle projects are created for organization, personnel, and credential data, you must create separate projects for each data type, and run the projects separately. Each project must be monitored to ensure the data integration is complete and successful before the next project is started.

Step 1   Select EDI Studio on your Windows PC. The ICPAM Enterprise Data Integration window opens.

Step 2         Create a new workspace.

a. Right-click Root and select New Workspace (or highlight Root and select New Workspace from the File menu).

b.   Enter the workspace name and click OK.

The new workspace is created along with a Projects folder.

Tip           Root and Workspace help organize your projects. They do not serve any other purpose.

Step 3        To create a new EDI project, right-click a Projects folder and select New (or highlight the folder and select New from the Project menu). The Choose Project Template window opens.

Step 4          In the resulting Choose Project Template page of the New Project wizard, select one of the available project templates.

choose_project_template_2.png

a. Project name: type a name for the project.

b. Project template: select a template that defines the data type (such as SQL credential data)

c. Source DB: select the database source (such as Oracle or MySQL).

d. Destination DB: select the destination database (SQL or MySQL).

Note     Oracle databases do not support Boolean data types so you can define numeric data types and use them as Boolean.

e.  Click Next.

Step 5            Enter the source parameters.

source_parameters.png

a.  Type the Database name.

b.     Type the User name required to log in to the database.

c.  Type the Password for the database password.

d.  Type the Server IP address of the database server.

e. Type the TCP Port number for the database server. Use a number between 1000 and 65536.

f.  Click Next or Test Connection to validate the server settings.

o      If the settings are valid, a message dialog stating Test connection successful appears.

o      If the settings are not valid, an error dialog stating Test connection failed appears. One or more of the parameters is incorrect. Work with your system administrator to obtain the correct settings then test the connection again.

Step 6         Map the database fields for the Destination [ICPAM] database with the database fields for the Source database.

a.  Enter the Source table name of the source database.

b. Enter a Source field for all required Destination [ICPAM] fields (which are marked with an asterisk*). The Destination fields vary depending on the data type as shown in the following field.

 

Data Type

Required Fields

Organization

Organization Data

·         name: (primary key) Name of the organization.

Department Data

·          name: (primary key) Name of the department.

·         orgName: (primary key) Organization name

Personnel

·         site: Site of the personnel record.

·         firs_name: User’s first name.

·         last_name: User’s last name

·        govt_id: (primary key) Government ID number. If the govt_id is a Social Security number, the length must be exactly nine digits (without dashes). The valid values are: I, II, III, Jr., and Sr.

·         govt_id_spec: a unique id that can identify a personnel record. Valid values are SSN, FIN, and ID#.

·         emp_status: Employment status. The valid values are: active, inactive, on_leave, retired, and terminated. If your organization has additional employee status codes, such as Active Password Expired, you can manually add those codes to the bottom of the list: enter the ICPAM Value in the left column (it must be one of the 5 supported emp_status values), and then enter your organization’s code in the right column.

·         The emp_type is not required, but has the following valid values: contractor, employee, employee_full_time, employee_part_time, intern, other, vendor, and visitor. emp_type is a type of employee.

1        The Region and Nationality fields be values already defined in system.

Credential (Badge Records)

2        The primary keys are badgeId and facilityCode.

·         badgeId: (primary key) The badge ID.

·         badgeTemplate: Use this field to assign the parameters from a badge template in ICPAM to imported badges. This option is used when importing badges into ICPAM for the first time.

For example, create or edit a badge template in ICPAM as described in Configuring Badge Templates . This template can contain settings for fields such as access policy, facility code, badge type, watch level, and effective date. Enter the name of the badge template in the Source Attribute Value column for badgeTemplate. For example: KeyPad_BCD4, 26BitWiegandCT, 26BitWiegandKeyPadCT, etc.

·         facilityCode: (primary key) The facility code

·         activationDate: Activation date for the badge.

·         expirationDate: Date the badge expires. This date must be greater than the activation date.

·         validity: The valid values are: active, inactive, destroyed, lost, and stolen.

·         role: The user’s role in the organization. The valid values are: employee, contractor, vendor, and temporary.

c.  Source: Enter the corresponding field name for the source database. Enter a name for all required Destination fields, and any additional fields, if necessary.

d. Click Next.

e.  Organization data only: Enter the additional Department Data settings and click Next again.

Step 7            Define the default database values and click Next to continue.

Step 8   (Optional) If necessary, select an EDI Extension file and click Next.

edi_extension_2.png

Step 9            Choose a schedule to specify how often data will be synchronized.

Note       EDI actions are conducted according to the ICPAM appliance time and time zone settings (not the source database server settings).

·         Every hh:mm: the data synchronization begins once every hour/minute specified.

·         Every day: the data synchronization is conducted once a day.

·         Every week: the data synchronization is conducted once a week.

create_new_project_schedule_2.png

Note      The minimum time frame to schedule a EDI job for synchronization is one hour.

Step 10       Click Finish to create the new database project and return to the ICPAM Data Enterprise application window.

The project is shown in the main window and the project file is saved to the default EDI project directory on your PC:

C:\Program Files\ICPAM Systems\EDI Studio\workspaces\Project_Folder\projects\.

Tip          An error message appears if any fields are incorrect or missing. Use the Back button to navigate to the screen and correct the entry. When you are done, click Finish from the window the correction was made. You do not need to return to the last window. The entries in all windows are preserved.

Step 11         Import and start the EDI project in ICPAM. 

See Importing, Starting, and Monitoring EDI Projects in ICPAM.

 

See also:

Creating Active Directory Database Integration Projects Using EDI Studio

Synchronizing Data Using Enterprise Data Integration (EDI)