Delta Aggregation Tables

When delta aggregation is enabled, only accounts that have changed since the last aggregation are loaded. A delta aggregation typically takes less time and fewer system resources than a full aggregation.

1. Perform a full aggregation as described in Account Aggregation.

2. Create the delta change tables (as described below).

3. Enable delta aggregation in your source as described in Delta Aggregation.

The next time you load accounts, only accounts that have changed in the source database tables are loaded.

Creating the Delta Change Tables

The database table containing the delta changes attribute must be provided with the value of table name (this is what you enter in the Database Table Name Containing the Delta Changes field). This table must have read and write permissions.

To create the Delta Change tables, complete the following:

1. Create two tables to capture the identities whose data is modified in the master tables:

   • One table for capturing the account whose attributes, entitlements, or direct permissions are modified in the master table for account, master table for its entitlements, or master table for its direct permissions, respectively.

   • Another table for capturing the account-group whose attributes are modified in the master table for account-group.

   Each of the two tables must contain two columns such that:

   • The first column stores the identity attribute defined in SailPoint.

   • The second column stores the action. The values of the action can be Insert, Update, or Delete.

   For example, the SQL command to create such a table for account or entitlement in Oracle Database is as follows:

   CREATE TABLE USER_DELTA (USER_ID VARCHAR2(20), ACTION VARCHAR2(10));

2. Assign the privileges to read and delete the records from the tables created in Step 1 to the connection user whose credentials you entered in the source configuration page within SailPoint.

3. Create the triggers on the master table.

   When there is a change in the attributes, entitlements, or permissions on the master table (or the tables that are linked to it, like the Entitlements table and the Permissions table for the Account), this triggers the system to write the Account Identity attribute in the first table created in Step 1.

   Similarly, the trigger on the Account-Group table writes the Account-Group Identity attribute, in the second table created in step 1, whose attributes have changed.

   For example, in Oracle databases the following trigger writes the user IDs in the first table (Account table), which has undergone some modifications, along with the respective action:

   Copy

   CREATE or REPLACE TRIGGER T1
   
   AFTER DELETE OR INSERT OR UPDATE ON USER_MASTER
   
   FOR EACH ROW
   
   BEGIN
   
   IF INSERTING THEN
   
         INSERT INTO USER_DELTA (USER_ID, ACTION)
   
             VALUES (:NEW.USER_ID, 'Insert');
   
       END IF;
   
       IF UPDATING THEN
   
         INSERT INTO USER_DELTA (USER_ID, ACTION)
   
             VALUES (:NEW.USER_ID, 'Update');
   
       END IF;
   
       IF DELETING THEN
   
         INSERT INTO USER_DELTA (USER_ID, ACTION)
   
             VALUES (:OLD.USER_ID, 'Delete');
   
       END IF;
   
   END;
   
   /

   With the above example, triggers can be created for the following tables also by replacing USER_MASTER with the following respective table names:

   • Entitlements table for account (ACCOUNTENTITLEMENT_MASTER)

   • Permissions table for account (PERMISSION_MASTER)

4. Once the above-mentioned steps are performed, the tables start capturing the user or group IDs that have undergone changes in their master tables.

5. After you finish the delta aggregation the tables are reset/re-initialized and start capturing changes again.

6. Similarly, you can create a delta table for any schema defined in the JDBC application by creating the master table for that schema and creating a delta table and trigger as mentioned in steps 1 through 3.