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.

  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:

    • One table to use for account aggregation, where all identity data resides.

    • Another table to capture only those identities who are undergoing modifications. This table must contain two columns:

      • 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 accounts in SAP HANA Cloud Database is as follows:

    CREATE COLUMN TABLE EMPLOYEES_DELTA (EMPLOYEE _ID NVARCHAR (20), ACTION NVARCHAR(10));

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

  3. Create triggers on the master table (the account aggregation table) which activate during aggregation processes. The triggers should cause the connector to store modifications in the delta table.

    For example, in SAP HANA Cloud databases, the example trigger below writes the EMPLOYEE_ID in the account aggregation table, which has undergone modifications, along with the respective action.

    Key

    • Table for account - EMPLOYEES

    • Delta table for account - EMPLOYEES_DELTA

    Copy 
    CREATE TRIGGER TRIEMPDELTA
    AFTER INSERT OR UPDATE OR DELETE
    ON MYSCHEMA.EMPLOYEES
    FOR EACH ROW
    BEGIN
        -- For INSERT operations
        IF :NEW.EMPLOYEE_ID IS NOT NULL AND :OLD.EMPLOYEE_ID IS NULL THEN
            INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
            VALUES (:NEW.EMPLOYEE_ID, 'Insert');
        END IF;
        
    -- For UPDATE operations
        IF :NEW.EMPLOYEE_ID IS NOT NULL AND :OLD.EMPLOYEE_ID IS NOT NULL THEN
            INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
            VALUES (:NEW.EMPLOYEE_ID, 'Update');
        END IF;

    -- For DELETE operations
        IF :NEW.EMPLOYEE_ID IS NULL AND :OLD.EMPLOYEE_ID IS NOT NULL THEN
            INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
            VALUES (:OLD.EMPLOYEE_ID, 'Delete');
        END IF;
    END;

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