# 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.

**Prerequisite**: Ensure you entered an Account SQL Query into the source connection. For more information, refer to [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-query-settings>).

1. Go to the **Import Data** tab and select **Start** beside **Manual Aggregation**. For more information on running a manual aggregation, refer to [Starting a Manual Aggregation](<https://documentation.sailpoint.com/saas/help/setup/load_entitlements.html#starting-a-manual-aggregation>).
2. 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/va/help/group_query_settings.html#group-query-settings>).
2. Manually start an **Entitlement Aggregation** in the **Import Data** tab.
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/va/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/va/help/group_query_settings.html#group-query-settings>).

### Account Status

For the JDBC source, 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 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/va/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 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:

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

   ```sql
   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 the delta aggregation completes, all processed rows are deleted from the delta tables. Rows that are added during or after the aggregation are processed in the next delta aggregation.

## Aggregation Out of Memory Error and Resolution

If your JDBC source has to aggregate a large amount of data, it may encounter an `out of memory` error. To mitigate this issue and aggregate the data, you can perform one of the following solutions:

- Use the `TYPE_FORWARD_ONLY` ResultSet type for JDBC drivers that support it.
- Increase RAM of the Java heap size (memory) in the Java virtual machine for JDBC drivers that do not support the `TYPE_FORWARD_ONLY` ResultSet type.

JDBC drivers that support the `TYPE_FORWARD_ONLY` ResultSet type read the data in the forward direction only which reduces data caching and results in lower memory consumption. Use the SailPoint REST API to update JDBC drivers that support the `TYPE_FORWARD_ONLY` ResultSet type.

> [!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.

Use the following attribute keys:

| Attribute Keys | Suggested Values |
| --- | --- |
| resultSetFetchSize | 1000 |
| statementFetchSize | 1000 |
| resultSetType | TYPE\_FORWARD\_ONLY |

For JDBC drivers that don't support the `TYPE_FORWARD_ONLY` ResultSet type, increase the RAM allocated for the Java heap size (memory) in the Java virtual machine. To increase the allocated RAM:

1. Create a text file and name it `java_overwrites.yaml`. Copy the following parameters into the file.

   ```yaml
   #Xmx: 912m
   #DbsfPoolSize: 5
   ```

   Currently, the virtual appliance only supports the following CCG JAVA HEAP options:

   - `Xmx`
   - `DbsfPoolSize`
2. Uncomment the two lines `#Xmx:` &amp; `#DbsfPoolSize` by removing the `#`. Then configure your required values and save the file.

   > **Note**
   >
   > The default value for `Xmx`, `912m`, represents 912 MB. This parameter can only be configured in megabytes. For example, if you want to configure 24 GB, set `Xmx` to `24000m`.

   For example:

   ```yaml
   Xmx: 24000m
   DbsfPoolSize: 5
   ```

   > **Caution**
   >
   > Always leave at least 3GB of RAM on the virtual appliance to run non-CCG processes.
3. Copy the `java_overwrites.yaml` file to the virtual appliance in the following directory:

   ```text
   /home/sailpoint/ccg/
   ```

   > **Note**
   >
   > On newer CCG images, CCG is owned by root. To add the `java_overwrites.yaml` file, you may need to run the following command:
   >
   > ```text
   > sudo /usr/bin/chown sailpoint /home/sailpoint/ccg
   > ```
4. Restart CCG by running the following command:

   ```bash
   sudo systemctl restart ccg
   ```
5. Confirm the ccg.log, found at `/home/sailpoint/log/ccg.log` does not contain errors related to the new configuration.

## Aggregation Retry Configuration

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

> [!NOTE]
>
> Use the REST API to set the values for the parameters in this section.
>
> For more information on the 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".

## Account Schemas

Manage the account schema from the JDBC source. The **Account Schema** option in the source's **Import Data** tab enables you to discover your JDBC account schema, edit the schema, and add attributes to the schema.

> [!NOTE]
>
> You must have discovered the account schema first before managing it or its attributes. For more information on discovering the account schema, refer to [Discover the Schema](<#discover-the-schema>).

### Discover the Schema

You must discover the schema and identify the attributes to use for the required identity data before doing anything else.

**Prerequisite:** Ensure you entered an Account SQL query into the source connection. For more information, refer to [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-query-settings>).

1. From your connector page, go to **Import Data \> Account Schema**.
2. Select **Options \> Discover Schema**.

   The Account SQL Query automatically executes and the discovered attribute names are displayed.
3. Define the attributes to be used for `Name` and `Account ID`. Mark them in the **Account ID** column.

> [!CAUTION]
>
> If this source is going to be used as an authoritative source for an identity profile, the Account Name and Account ID both need to be unique. If not, source accounts could correlate to the first Identity that includes that ID/Name instead of creating an additional Identity.

### Add a New Account Schema Attribute

Add additional attributes to your JDBC account schema from SailPoint.

1. In the JDBC source page, go to **Import Data \> Account Schema**.
2. Select **Add New Attribute** to create a new attribute.

   A form to configure the new attribute displays.
3. Add a **Name** and unique **Description** to the attribute, then select a **Type**. These are required fields.
4. (Optional) As required, check the boxes for **Entitlement** and **Multi-Valued**.
5. (Optional) If you want to create another new attribute after saving the current one, check the **Add Another** box. When you save the current one, another form displays for the next attribute you want to create.
6. Select **Save**.

### Edit the Account Schema

Give the account schema a unique description, and define the attributes for the schema's **Account ID** and **Account Name**.

1. In the JDBC source page, go to **Import Data \> Account Schema**.
2. Select **Options \> Edit Schema**.

   A form displays which allows you to update the schema.
3. Enter a unique description into the **Description** field.
4. Select the attribute to use as the schema's Account ID from the **Account ID** dropdown.
5. Select the attribute to use as the schema's Account Name from the **Account Name** dropdown.
6. Select **Update**.

## Entitlement Schemas

You need to manually add and configure entitlement schemas to the JDBC source.

### Add Entitlement Types

This process describes how you can add entitlement types (entitlement schemas) and configure their attributes in SailPoint.

> [!NOTE]
>
> Any new entitlement schemas you create appear in **Query Settings**. In Query Settings, you can configure the connection and SQL queries as needed. For more information, refer to [Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/group_query_settings.html#group-schema-query-setup>).

1. In the JDBC source page, go to **Import Data \> Entitlement Types**.
2. Select **Create Entitlement Type**.

   A form to configure the new entitlement type displays.
3. Give the entitlement a **Name** and a unique **Description**.

   > **Note**
   >
   > Once you enter the entitlement type's name you can't change it later.
4. (Optional) If the new entitlement should include permissions during aggregations, check the **Include permissions in aggregations** checkbox.
5. Select **Save**.

### Add Attributes to an Entitlement Type

Edit the attributes associated with each of your entitlement types.

1. In the JDBC source page, go to **Import Data \> Entitlement Types**.
2. Select an entitlement type from the list.

   The entitlement type entry expands to display the associated attributes.
3. Select **Add New Attribute** to create a new attribute.

   A form to configure the new attribute displays.
4. Add a **Name** and unique **Description** to the attribute, then select a **Type**. These are required fields.
5. (Optional) As required, select the checkboxes for **Entitlement** and **Multi-Valued**.
6. (Optional) If you want to create another new attribute after saving the current one, select the **Add Another** checkbox. When you save the current one, another form displays for the next attribute you want to create.
7. Select **Save**.

## Configuring Multiple Group Objects

The JDBC connector supports multiple group objects for Entitlement Aggregation. Depending on the managed system that you are connecting with the JDBC source, you can set up attributes such as entitlements and roles. You can add one or more entitlement schemas for aggregation.

> [!NOTE]
>
> To aggregate entitlement schemas, regardless of if you need one or multiple, you must to manually add and configure them in the JDBC source. For more information, refer to [Entitlement Schemas](<#entitlement-schemas>).

### Adding or Updating Multiple Group Objects from SailPoint

Once you establish a connection to the JDBC database as described in [Account Connection Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-connection-settings>), you can access the **Account Schema** and **Entitlement Types** options in the **Import Data** tab.

### Adding an Entitlement Schema Using the REST API

In the JDBC source for entitlement aggregation, you can add the entitlement schema using the REST API. This topic provides information on adding the entitlement schema using the REST API. Alternatively, you can add the entitlement schema in the SailPoint user interface. For more information, refer to [Entitlement Schemas](<#entitlement-schemas>).

> [!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.

Example request body:

```json
{
  "id": "string",
  "name": "string",
  "nativeObjectType": "string",
  "identityAttribute": "string",
  "displayAttribute": "string",
  "hierarchyAttribute": "string",
  "includePermissions": true,
  "features": [
    "string"
  ],
  "configuration": {},
  "attributes": [
    {
      "name": "string",
      "type": "STRING",
      "schema": {
        "type": "ACCOUNT_CORRELATION_CONFIG",
        "id": "string",
        "name": "string"
      },
      "description": "string",
      "isMultiValued": true,
      "isEntitlement": true,
      "isGroup": true
    }
  ],
  "created": "string (date-time)",
  "modified": "string (date-time)"
}
```

### Configuring Multiple Group Objects with the REST API

Use the following request body for the 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.

```json
{
  "id": "string",
  "name": "string",
  "nativeObjectType": "string",
  "identityAttribute": "string",
  "displayAttribute": "string",
  "hierarchyAttribute": "string",
  "includePermissions": true,
  "features": [
    "string"
  ],
  "configuration": {},
  "attributes": [
    {
      "name": "string",
      "type": "STRING",
      "schema": {
        "type": "ACCOUNT_CORRELATION_CONFIG",
        "id": "string",
        "name": "string"
      },
      "description": "string",
      "isMultiValued": true,
      "isEntitlement": true,
      "isGroup": true
    }
  ],
  "created": "string (date-time)",
  "modified": "string (date-time)"
}
```

## Partitioning Aggregation

The JDBC connector supports the manual partitioning through configured SQL statements.

For example, if there is an employee dataset that has 100,000 rows with a sequential `employeeId` field, the partitioning statements that can be used are as follows:

```sql
select x,y,z from a where employeeId <= 10000;
select x,y,z from a where employeeId > 10000 AND employeeId <= 20000;
select x,y,z from a where employeeId > 20000 AND employeeId < =30000;
…
select x,y,z from a where employeeId > 90000;
```

The above example has 10 partitions, handling approximately 10,000 accounts. The last row (with `employeeId > 90000`) handles a larger number of accounts depending on the total number of employees in the system.

> [!NOTE]
>
> An additional aggregation option, `noAttributePromotion`, has been added. If this attribute is set to `true`, the attribute promotion is skipped during aggregation.

To enable partitioning, add the following attribute key-value pairs into the source XML using the REST API.

- **Attribute Key** - `partitionAggregationEnabled`; **Value** - `true` to trigger a specific operation rule.
- **Attribute Key** - `partitionMode`; **Value** - `true` to assign the rule to the source.
- **Attribute Key** - `partitionStatements`; **Value** - enter either of the following to use SQL statements or stored procedures for aggregation:

  SQL query, for example:

  ```sql
  select x,y,z from a where employeeId <= 10000;
  ```

  Stored procedure, for example:

  ```sql
  EXEC emp ;
  ```

  > **Important**
  >
  > To use a stored procedure with the `partitioningStatements` instead of a SQL query, you must also add the `useStoredProcedurePartition` attribute key with the value `true`.

> [!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.