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 Loading Account Data.

2. Create the delta change table (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.

Note
Currently SailPoint does not support delta aggregation for groups. Open a support ticket for assistance.

Creating the Delta Change Table

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 that Contains 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.

   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. After you finish the delta aggregation the tables are reset/re-initialized and start capturing changes again.