JDBC as Authoritative Source with Delta Aggregation Mode Enabled in Sailpoint

This document explains how to onboard JDBC as an Authoritative with Delta Aggregation

In this example I am using Oracle MySQL as database server

1. Create HR Source database, source table, delta table, triggers

1. Create new database

create database hrdatabase;

use hrdatabase;

2. Create hrsource table

create table hrsource (

   employeeNumber varchar(255),

firstName varchar(255),

   lastName varchar(255),

userType varchar(255),

   personalEmail varchar(255),

department varchar(255),

city varchar(255),

manager varchar(255),

status varchar(255),

PRIMARY KEY (employeeNumber)

);

Insert few records( for testing full aggregation )

insert into hrsource values('1001','Ravi','Kumar','E','ravikumar@gmail.com','Development','Bangalore','','A');

insert into hrsource values('1002','Jayanth','G','E','jayanth.g@gmail.com','Quality','Mysore','1001','A');

3. Create delta table

CREATE TABLE HRSOURCE_DELTA (employeeNumber VARCHAR(255), ACTION VARCHAR(20));

4. Create triggers for insert and update

For Insert Operation

DELIMITER #

create trigger hrsource_delta_insert_trigger after insert on hrdatabase.hrsource for each row BEGIN INSERT INTO hrdatabase.HRSOURCE_DELTA SET action = 'Insert',employeeNumber = NEW.employeeNumber;

END #

For Update Operation

DELIMITER #

create trigger hrsource_delta_update_trigger after update on hrdatabase.hrsource for each row BEGIN INSERT INTO hrdatabase.HRSOURCE_DELTA SET action = 'Update',employeeNumber = NEW.employeeNumber;

END #

2. Onboard JDBC Connector App
1. Onboard new Application with name  ‘HR Source’ type as ‘JDBC’ and check ‘Authoritative Application’

2. Update Connection configuration settings

Configuration -> Settings -> Object Type: Account

Update username, password and database URL, SQL Statement

3. Configure Delta Aggregation

4. Update Schema

Use discover schema to populate schema and update identity and display attributes

5. Set Account and Manager Correlation

I already created Identity attributes in Identity Mappings

Save the Application Configuration and reopen the application to update correlation

Account correlation

Manager correlation

6. Update Identity Attribute mapping

Global Settings -> Identity Mappings

3. Create Aggregation Task

Create new Aggregation Task

Setup -> Tasks -> Account Aggregation

With default options

Save and Execute the task

2 Users created

Run Refresh Identity Cube for Manager and other attributes to update in Identity Cube

4. Update/Insert new records into hrsource table and Run reaggregation with delta aggregation mode

Insert 2 new records

insert into hrsource values('1003','Raman','Rao','C','raman.rao@gmail.com','Management','Bijapur','1001','T');

insert into hrsource values('1004','Shiva Kumar','Bellubbi','C','shivakumar.bellubbi@gmail.com','Development','Bijapur','1001','A');

Update existing record

update hrsource set city = 'Doddaballapur' where employeeNumber = '1001';

5. Enable Delta aggregation in the Aggregation task(Account Aggregation - HR Source)

Save and Execute the Aggregation task

Data in hrsource_delta will be truncated after running aggregation task