Planning

Note

1. Coolr Group platforms offers multiple integration options.
2. The preferred option is to use HTTPS API endpoints.
3. Coolr Group endpoints are also called "controllers".

For integration process, there are a few things to consider.

Keys/ Surrogate Keys/ Unique Identifiers

Almost all the data entities should have a unique identifiers for different purposes. Let us consider "Location" entity for example:

Property	Purpose	Notes
LocationCode	Code for the location	Publicly visible unique identifier for a location. Example: 7-11-1001
LocationName	Name for the location	Publicly visible name for a location. Example: 7-11 Chantilly (1001)
LocationId	Coolr's internal Unique Id for location	Auto generated unique identifier for the location within Coolr system
ExternalLocationCode	3rd party unique surrogate key for location

In this example, LocationCode and LocationName are very well known attributes commonly used in the retail execution. However, LocationId and ExternalLocationCode are unique for integration purposes.

Whenever a record is added to the Coolr's platform a unique Id is generated for the entity. This allows us to make modifications to fields by referring to the record using this unique identifier.

However, an integration with systems such as Sales Force may not allow you to refer to a record to be modified using Coolr's LocationId as you may not be able to store the Coolr's surrogate id due to system limitations or complications.

In such cases, you can use LocationCode as the only primary key to update the records. However, this can also pose challenges when this "seemingly" unique identifier such as LocationCode also needs to be modified.

Hence, it is generally recommended to use the integration system's surrogate key or unique identifier as ExternalLocationCode. This way, you can refer to your system's key itself instead of relying on either Coolr's surrogate key "LocationId" or a key such as "LocationCode" that may get changed due to errors/ other reasons.

With this background, let us consider multiple integration options for these keys using some psuedo code:

Using Publicly Visible Unique Identifier

In this case, you do not need to know about Coolr's surrogate key "LocationId" and Coolr doesn't need to know about your surrgoate key

Limitation: In this case, if the location code itself needs to be changed, it cannot be done using the API calls

1. Adding a new location

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=save" -d "LocationCode=Demo&LocationName=Demo"

2. Updating the location

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=save" -d "LocationCode=Demo&LocationName=New%20Name"

3. Deleting the location

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=delete" -d "LocationCode=Demo"

Using Coolr's Surrogate Key

In this case, you must know/ store Coolr's surrogate key "LocationId" and Coolr doesn't need to know about your surrgoate key

Limitation: In this case, you must either store the Coolr's surrogate key in your system or you must make an additional call to retrieve Coolr's surrogate key.

Note: You could also retrieve Coolr's surrogate id for a record using the list APIs. However, if key identifiers have already changed in your system, it can become complicated.

1. Adding a new location

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=save" -d "LocationCode=Demo&LocationName=Demo"

In the return value, you should return the details of the full object include "LocationId", that you can save/ use for subsequent updates. Let us assume the LocationId returns is 5.

2. Updating the location

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=save&id=5" -d "LocationName=New%20Name"

Now, you can also update the location code:

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=save&id=5" -d "LocationCode=New%20Name"

3. Deleting the location

curl -X POST "https://portal.coolrgroup.com/controllers/location.ashx?action=delete&id=5"

Using Integration System's Surrogate Key

In this case, you must pass your surrogate key to Coolr for each call.

Advantage: In this case, you do not need to know Coolr's surrogate key and you can also update publicly visible unique identifier.

Let us assume that sample data in your system has a unique identifier as "DemoLoc1"

1. Adding a new location

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=save" -d "ExternalLocationCode=DemoLoc1&LocationCode=Demo&LocationName=Demo"

2. Updating the location

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=save" -d "ExternalLocationCode=DemoLoc1&LocationName=New%20Name"

Now, you can also update the location code:

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=save" -d "ExternalLocationCode=DemoLoc1&LocationCode=New%20Name"

3. Deleting the location

curl "https://portal.coolrgroup.com/controllers/location.ashx?action=delete" -d "ExternalLocationCode=DemoLoc1"

Recommendation

Most customers choose to use their integration system's surrogate key

Integration strategy

It is important to think about the integration strategies to align with the goals and technical limitations.

As some of the datasets such as Location and Asset can be huge. Let us consider a scenario:

1. 50,000 locations
2. Average 2 assets per location
3. This means, there can be up-to 100,000 asset records to sync
4. Considering fetching record by record from integration system, making call to Coolr, latency and logging etc., it can take up-to 1 second per record
5. 100,000 record will take 100,000 seconds or 27 hours which is impractical. Even if the average time drops to half, it'll be 13.5 hours - still challenging

To handle this concern, there are a few strategies that are followed in general:

1. Initial sync: It can be provided in Coolr supported Excel template, CSV or another format as one time import
2. Updates: Only delta records are updated. As only a small number of updates are expected, a one-by-one update is quite common/ feasible

Alternative Stragey

• Coolr's data import APIs can also be used for both initial sync as well as updates. However, the response times for import APIs are longer and callback strategies may increase complexity.
• Coolr's data import APIs allow CSV, Excel and JSON formats. CSV/ Excel are generally more performant as column names are not repeated for each record. JSON is quite verbose resulting in longer processing times.

Based on these facts, you may need to plan for a delta/ changes identification strategy so that you can push only the modified records either on schedule or as changes happen.

Coolr inernally uses the Timestamp based methodology to allow you to pull the added/ modified records from the last successful sync.

Warning

Coolr does not suggest doing full data syncs regularly.

Delta identification strategies

As mentioned above, there can be various Delta identification strategies. Based on limitations of various strategies, you can choose which strategy works for you. Coolr

Timestamp based

In this strategy, the source system must maintain a timestamp whenever a record is modified. Assuming the same, the sample strategy would be:

• Identify a cut off time at every sync (initial or subsequent)
• Pick only the records modified since last sync

Pseduo example:

const lastSync = await db.get('AssetLastSync') || '2001-01-01';    // assume last sync as start of century if no sync was done
const currentTimestamp = new Date();    // current time upto which we are pulling the records
const dataToSync = await db.get('SELECT * FROM dbo.Asset', {
    where: {
        "modifiedOn": { operator: ">", value: lastSync }
    }
});
await syncToCooler(dataToSync);         // sync to Coolr
await db.save({ AssetLastSync: currentTimestamp });  // save the timestamp until which records were updated

Note

Timestamp based strategy is the most commonly used strategy by our customers.

Delta based

If the timestamp based strategy does not work for you, an intermediate strategy may be used within the integration process to identify the modified records.

This involves:

• Calculating a checksum of each record when synchronized
• Comparing the checksum before next update is pushed

Considering this, even while generating the initial load, the checksum must be calculated. Using the psuedo code above, let us assume:

• You are synchronizing Asset data
• Primary key in your system is AssetUniqueId
• Sample record AssetUniqueId are "ab-01" and "ab-02"

Checksums can be stored in a database or JSON. We suggest using SHA256 or higher. If storing in JSON, it could look like:

Asset-Checksums.JSON

{
    "ab-01": "9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08",
    "ab-02": "60303ae22b998861bce3b28f33eec1be758a213c86c93c076dbe9f558c11c752"
}

const fileName = './Asset-Checksum.JSON';
const lastSync = JSON.parse(await fs.readFile(fileName));
const allData = await db.get('SELECT * FROM dbo.Asset');
const toSync = allData.filter(record => util.SHA256(record) !== lasySync[record.AssetUniqueId]);
await syncToCooler(toSync);         // sync to Coolr
const newChecksums = {};
const checkSums = allData.forEach(record => newChecksums[record.AssetUniqueId] = util.SHA256(record));
await fs.writeFile(fileName, JSON.stringify(checkSums));  // save the checksum for each record

Other straetgies

Some of our customers have used additional strategies to identify modified records:

• Triggers: Some database systems allow to use triggers on CRUD operations to record modifications
• CDC: Some databases support CDC (Change Data Capture) to identify the record modifications

Process

The typical process is:

1. Identify the delta/ modification strategy
2. Prepare initial dataset
3. Import initial dataset based on one one the strategies suggested earlier:
   1. Share with Coolr via SFTP or similar mechanism and let Coolr technical team import
   2. Use import API
   3. Sync record by record using standard APIs