Extension Tables

Description of and how to configure extension tables.

Caution: Allow only qualified personnel who have training in the Boomi™ application to configure or create integrations.

(Optional) Extension Tables provide read and write access to custom database tables for custom data integrations, reports, and other product extensions. These tables can be critical tools for processes like payroll and work tracking.

Examples:

  • Maintain daily payroll records in the extensions database so that payroll export integrations compare records to those in the database and process only changed records.
  • Maintain billing-rate tables in the extensions database for generation of Billing reports with the correct amounts billed to each customer customer.

Each tenant can have multiple extension tables:

  1. Start with an empty database schema for extensions.
  2. Define tables in the empty schema from Setup.
  3. Design integration templates that add, update and delete data in the extension tables by way of a Java Database Connectivity (JDBC) Connector

Types of extension tables

  • Data Import— Import all person data from multiple systems by way of custom database tables.
    Caution: Do not use special characters such as angle brackets (< or >) in data in cross-reference tables, data maps, and input files. These characters can make the integration fail.
  • Data Export— The destination system requires that changes to previously exported records be accompanied by an external record ID that is stored in a custom database table by the original export.
  • Data Lookup— Reference data that is not supported by the Integration Designer (the Boomi™ application), such as dynamic loads of data by APIs. These can go by way of custom database tables.

Connector overview

Integrations use a Connector shape to exchange data with the extensions database.


Connector shape
Connector: Get data into the integration or send data out from the integration.

Connectors enable data communication with and the movement of data between the applications or data sources that are the "end points" of an integration process. The applications and data sources can be an on-premise or web-based application, a data repository such as an SFTP directory, a database, or an email server.

Unlike an API which acts like a socket that waits until something is plugged in, a connector acts like a plug into an API to get the technical details and communication protocols.

A connector combines 2 components:

  • Connection is the "where", that is, the type of data source.
  • Operation is the "how", that is, how to physically connect to the source and the type of data records to exchange.

Every process begins with a Start shape which gets data, may have one or more Connector shapes in the middle to get data or values from a reference table, and typically ends with one or more Connector shapes that send data to multiple destinations or as notifications.

Edit the FAP

    1. Click Tap Main Menu > Administration > Application Setup Access Profiles > Function Access Profiles.
    2. Select the profile. Click Tap Edit.
    3. Select Manager— System Configuration.
    4. Select Integrations Setup:
      1. In Connections > Access Scope, select Allowed to configure extension tables.

      2. In Extension Database:

        In Add, select Allowed to enter database commands.

        In View, select Allowed to see the detailed view of database commands.

    5. Click Tap Save & Return.
  2. Associate access profiles to people

    In People Information, associate the access profiles to managers or administrators. If you don't have access to People Information, contact the administrator who does have access.

    1. Click Tap Main Maintenance > People Information. Select a person.
    2. In Employee, select Access Profiles.
    3. Select the Function Access Profile.
    4. Click Tap Save .
    5. Repeat for other people.

Run DDL commands to extension tables

You can create Data Definition Language (DDL) commands only for the schema of your tenant.

  1. Click Tap Main Menu > Administration > Application Setup System Configuration > Extension Tables.
  2. Click Tap Create .
  3. Enter a unique DDL Command Name.
  4. (Optional) Enter a Description.
  5. In Commands for Execution, enter DDL commands, each separated by a semicolon (;).

    Only the following commands are supported:

    • CREATE, ALTER, or DROP table
    • CREATE, ALTER, or DROP index
    • TRUNCATE table
    • REINDEX table or index
    • ANALYZE

    Example DDL commands

    CREATE TABLE PayrollExtractTest (payrollExtractID BIGINT NOT NULL, payrollExtractName TEXT NOT NULL, runDate DATE, CONSTRAINT pk_PayrollExtractTest PRIMARY KEY (payrollExtractID)); CREATE INDEX i1_payrollExtractName on PayrollExtractTest(payrollExtractName);
  6. Click Tap Execute.
  7. If the execution remains in-progress, click tap Refresh .

Show the results of DDL command runs

  1. Click Tap Main Menu > Administration > Application Setup System Configuration > Extension Tables.
  2. Select a set of DDL commands.
  3. Click Tap View .
  4. If the run completed, the Commands Result List shows the result of each statement.
  5. Configure the Extension DB Connection in the Integration Designer.
    1. Select Design Integrations > Design Integration Templates.
    2. Configure the JDBC Connector with the DML credentials and connection details.

Get DML credentials

An integration can run only Data Manipulation Language (DML) commands.

Note: The following steps are automated for refreshed or cloned tenants, and are required only for new tenants.
  1. Click Tap Main Menu > Administration > Application Setup System Configuration > Extension Tables.
  2. Select a set of DDL commands.
  3. Click Tap View DML Credentials .
  4. Copy the DML credentials and connection details.
  5. Configure the Extension DB Connection in the Integration Designer:
    1. Select Design Integrations > Design Integration Templates.
    2. Configure the JDBC Connector with the DML credentials and connection details.

Show extension table details

You can show detailed information for current extension tables, such as columns, primary and foreign keys, and indexes.

  1. Click Tap Main Menu > Administration > Application Setup System Configuration > Extension Tables.
  2. Select an extension table.
  3. Click Tap View Extension Tables .

Increase the number of connections

You can change the number of connections for the extension database.

  1. Log into the tenant management system (TMS).
  2. Click Tap Main Menu Administration > Application Setup > System Configuration > System Settings.
  3. Select the Global Values tab.
  4. In global.ihub.integrationdb.connection.limit, enter the maximum number of connections for the extension database.
    • Default = 10 connections.
      Caution: Change this value only for large organizations. The default of 10 connections is recommended for most organizations.
    • Minimum = 10.
    • Maximum for DDL = 10.
    • Maximum for DML = 500.
  5. Click Tap Save.

Create Extension Tables for SuccessFactors Integrations

Create extension tables in the database so that the SuccessFactors Delta Employee Data Import integration uses request filters.

(Optional) Extension Tables provide read and write access to custom database tables for custom data integrations, reports, and other product extensions.

Note: Only for the SuccessFactors Delta Employee Data Import integrations, and if you will use request filters such as Company, Business Unit, or Department. For detailed information about all integrations, see Configure Extension Tables.

The extension tables in the database allow Delta Employee Data Import integrations to use request filters such as Company, Business Unit, or Department.