# Aggregation

For more information about aggregation, refer to [Loading Account Data](<https://documentation.sailpoint.com/saas/help/accounts/loading_data.html>).

The following topics provide additional information for configuring aggregation:

## Aggregating Accounts and Entitlements

After you create and test the connection, you can load accounts or entitlements.

You must first [discover the schema](<https://documentation.sailpoint.com/saas/help/accounts/schema.html#discovering-an-account-schema>) and identify attributes to use for required identity data before loading accounts or entitlements.

> [!NOTE]
>
> **Best Practice**
>
> Test the SQL queries outside of SailPoint to make sure you get the results you expect. If you receive a general error, check that your query does not contain a semicolon (;) at the end.

## Account Aggregation

After you have discovered the schema and identified required attributes, you can load accounts.

1. Enter the query into the **Account SQL Query** field in the [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/account_query_settings.html#account-query-settings>).
2. Go to **Account Management \> Account Aggregation** and select **Start Aggregation**.
3. Check the results in the Aggregation Activity log.

   If the results do not seem right, try running the query as a database user and compare the number of rows returned by the query.

## Entitlement Aggregation

Load entitlements to SailPoint from the JDBC server.

1. Enter the query into the **Group SQL Query** field in the [Group Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/group_query_settings.html#group-query-settings>).
2. Go to **Entitlement Management \> Entitlement Aggregation** and select **Start Aggregation**.
3. Check the results in the Aggregation Activity log.

   If the results do not seem right, try running the query as a database user and compare the number of rows returned by the query.

## Loading a Single Account or Group

Loading a single account or group is helpful when you want to check the status of an account or entitlement.

The normal SQL syntax is:

```sql
Select * from <TableName> where NativeIdentity = '$(identity)'
```

For example:

```sql
Select * from Users where UserID = '$(identity)'
```

- You configure the SQL query for accounts in the **Single Account SQL Query** field in [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/account_query_settings.html#account-query-settings>).
- You configure the SQL query for groups in the **Single Group SQL Query** field in [Group Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/group_query_settings.html#group-query-settings>).

### Account Status

To set an account as enabled or disabled based on the values inside a specific column, you can add the `IIQDisabled` (string) attribute in the source account schema. For more information, refer to [Adding Attributes to a Source Schema](<https://documentation.sailpoint.com/saas/help/accounts/schema.html#editing-an-account-schema>).

The following SQL query can be used to set up the state for the account:

```sql
select <Column1>, <Column2>, <Column...n>, case when <conditional Column for enable disable> then 'true' else 'false' end as "IIQDisabled" from <table_name>;
```

For example:

```sql
select EmployeeId, FirstName, LastName, IsActive, Groups, case when IsActive=0 then 'true' else 'false' end as "IIQDisabled" from Employee;
```

If you are using a stored procedure, the stored procedure needs to be updated to set a status.

For example:

```sql
CREATE PROCEDURE empstatus AS select empid, FirstName, LastName, isactive, Groups, case when isactive=0 then 'true' else 'false' end as "IIQDisabled" from master.dbo.emp GO;
```

> [!NOTE]
>
> In cases where the stored procedure can not be modified, use the JDBC BuildMap Rule to set the account status. For more information, refer to [Identity Security Cloud Rule Guide - BuildMap Rule](<https://community.sailpoint.com/knowledge/IdentityNow-Rule-Guide-JDBC-BuildMap-Rule>).

## Data Merging Support

For the JDBC SaaS source, if the data for a single account spans multiple rows of database table, you can merge the data for successful aggregation.

For the usage of the REST APIs for the following attributes and keys, refer to:

- API to get source details:

  [https://developer.sailpoint.com/docs/api/v2024/list-sources](<https://developer.sailpoint.com/docs/api/v2024/list-sources>)
- API to get specific source's current schema details:

  [https://developer.sailpoint.com/docs/api/v2024/get-source-schemas/](<https://developer.sailpoint.com/docs/api/v2024/get-source-schemas/#request>)
- API to add new schema attribute to source:

  [https://developer.sailpoint.com/docs/api/patch-requests/\#add](<https://developer.sailpoint.com/docs/api/patch-requests/#add>)

For example, if you want to merge data of the attributes such as email and phone, you can use the REST API to set the `mergeRows` attribute and use the value as `true`.

If you want to merge the columns, set form-data values as follows:

- **key**: use the `indexColumns` attribute key
- **value**: the value of this key must be the name of the column or list of columns that you want as index column for merging. For example, you can you use value in a format such as:

  ```text
  [<IndexKey1>, <IndexKey2>,...<IndexKeyn>]
  ```
- **key**: use the `mergeColumns` attribute key
- **value**: the value of this key must be the name of the column or list of columns that you want to merge with the index column. For example, you can you use value in a format such as:

  ```text
  [<MergeKey1>, <MergeKey2>,...<MergeKeyn>]
  ```

> [!NOTE]
>
> The Account SQL query should have order by clause when merging is enabled. The query should be ordered by the index columns defined in the source.
>
> ```sql
> eg: select * from <Table> order by IndexKey1, IndexKey2
> ```

## 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](<https://documentation.sailpoint.com/saas/help/accounts/loading_data.html>).
2. Create the delta change table (as described below).
3. Enable delta aggregation in your source as described in [Account Delta Aggregation](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/account_query_settings.html#account-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](<https://community.sailpoint.com/t5/Community-Tutorials/How-To-Use-the-SailPoint-Support-Portal/ta-p/157222>) 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:

   ```sql
   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`

   ```sql
   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.

## Aggregation Retry Configuration

Use the following parameters to specify the number of aggregation retries, the delay between each attempt, and create custom retry exceptions.

You can update these parameters using the SailPoint REST API.

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

### Enable Retry

> [!NOTE]
>
> Once Retry is enabled, it's applicable for all aggregation types.

This enables you to configure the number of times the system attempts to retry aggregation and the delay between each attempt. Use this feature to retry the whole aggregation in case of failure.

To enable this feature, set the `connRetryEnable` attribute to `true`.

### Retry Count

Specify the number of times you want the system to retry aggregation. The maximum attempts is 10.

To specify the number of retries, set the `connMaxRetry` attribute and enter the value as a `<string number>`.

### Delay Between Retry Attempts (in Seconds)

Specify the delay time between attempts in seconds. The maximum is 10 seconds.

To specify the delay time, set the `connWaitTimeForRetry` attribute and enter the value as a `<string number>`.

### `connRetryExceptionList`

This parameter must be set to retry only specific exceptions.

Use the following format:

- key: `connRetryExceptionList`
- value: a comma separated list of the exception strings

For example, you can use the value: `[<Communications link failure >, <Connection reset >]`

In this case, the system only retries exceptions that contain the strings "Communications link failure" or "Connection reset".