OnRamp Operational Specification

Topics:

This section describes how various integration scenarios will be supported through the relational OnRamp in Omni-HealthData™. Specifically, the purpose of this section is to make the rules governing how the OnRamp changes Omni Source tables to explicit, and to describe how these changes relate to and reflect different integration tasks. It assumes an understanding of the structure of a ramp table, for example, its keys, the mapping of Omni types to columns, and the hierarchical organization of the different ramp tables belonging to a subject.

The concept is that of a ramp batch, which is a set of records in relational ramp tables aggregated by means of a batch_id and then submitted as a data update by an integrator.

Each record in the OnRamp table must correspond to a unique record in the source system. This is enforced by the OnRamp table keys.

Omni-HealthData maintains source tables (identified by a _s suffix), which contain the latest version of a given source instance. Source and ramp instances are compared with each ramp batch in order to prevent instances, which have not materially changed from being reprocessed. For any ramp operation, the resulting state of the Omni Source tables is dependent on:

Integration Modes

Two integration modes can be usefully distinguished:

  • Full Data. The integrator provides a complete set of replacement data for a subject from one or more source systems. Full data loads can be used for initial loads or subsequent loads. This mode is suitable when source data is provided cumulatively. New records are added to the repository, existing records are replaced, and records no longer present are deleted.
  • Incremental Data. The integrator provides a set of data consisting of only the records that are to be changed, for example, data to be added, modified, or deleted for a subject from one or more source systems. Previously loaded Omni data not referenced by an Incremental Data batch (directly or indirectly) are left unchanged.

Batch Types

A set of operations are available to support integration needs. These operations are specified by batch type and are given as an execution parameter on a particular batch. The following table provides a high-level summary of the supported batch types and the scenarios in which they are used.

Batch_type

Integration Mode

Scenario

UPSERT

Incremental

The integrator presents a set of records for a given subject to be added or replaced.

INSERT_ONLY

Incremental

The integrator presents a set of NEW instances or instance hierarchies (no implied hierarchies) to add to a given subject.

REPLACE_SELECTED

Incremental

The integrator presents a set of records for a given subject to be added or replaced.

REPLACE_ALL

Full

The integrator presents a complete replacement set for a given subject.

DELETE

Incremental

The integrator presents a subset of records for a given subject to be (logically) deleted from the repository.

Detailed Batch Types

Topics:

This section describes the various batch types in detail.

UPSERT

Use cases:

  • The integrator can access or compute incremental data from a source system.
  • The integrator wants to replace a set of instances or instance children (no implied hierarchies).
  • Migration path for OID policy="merge".

When an UPSERT batch is processed:

  • New instances from the batch are added.
  • Pre-existing instances (top level or subcollection) changed in any way by the batch are replaced. NULLS are handled according to the upsertNullHandling option with values PRESERVE and IGNORE. PRESERVE is the default.
  • Pre-existing instances not in the batch are unchanged.

The following table shows the status of a source instance after an UPSERT operation.

Instance in Ramp?

Instance in Source?

Source Status Before Operation

Source Status After Operation

Yes

Yes

ACTIVE

ACTIVE

Yes

Yes

INACTIVE

ACTIVE

Yes

No

N/A

ACTIVE

No

Yes

ACTIVE

ACTIVE

No

Yes

INACTIVE

INACTIVE

Guidelines for a batch composition

The set of instances in an UPSERT batch can update any existing instances. New instances must be introduced as complete hierarchies, either from the subject root of the child or an already existing hierarchy. This condition will be satisfied when one of the following holds for each instance in the batch:

  • The instance is a top-level instance.
  • The instance is a child instance and all its ancestors are in the batch OR all its ancestors are in Omni Source.

INSERT_ONLY

Use cases:

  • The integrator wants to perform an initial load of subjects into the Omni database.
  • The integrator wants to add a set of instance records not yet known to be in the Omni database.

Note: In some cases, INSERT_ONLY with truncateBeforeInsert = TRUE may outperform REPLACE/UPSERT operations (this is only appropriate for transactional subjects for which capturing changes between loads in unnecessary).

The following table shows the status of a source instance after an INSERT_ONLY with truncateBeforeInsert = FALSE operation.

Instance in Ramp?

Instance in Source?

Source Status Before Operation

Source Status After Operation

Yes

Yes

ACTIVE

*INSERT_ONLY operation fails with error

Yes

Yes

INACTIVE

*INSERT_ONLY operation fails with error

Yes

No

N/A

ACTIVE

No

Yes

ACTIVE

ACTIVE

No

Yes

INACTIVE

INACTIVE

The following table shows the status of a source instance after an INSERT_ONLY with truncateBeforeInsert = TRUE operation.

Instance in Ramp?

Instance in Source?

Source Status Before Operation

Source Status After Operation

Yes

Yes

ACTIVE

ACTIVE

Yes

Yes

INACTIVE

ACTIVE

Yes

No

N/A

ACTIVE

No

Yes

ACTIVE

N/A

No

Yes

INACTIVE

N/A

Guidelines for a batch composition

The set of instances in an INSERT_ONLY batch must be introduced as complete hierarchies, either as the subject root or the child of an existing hierarchy.

REPLACE_SELECTED

Use cases:

  • The integrator can access or compute incremental data from a source system.
  • The integrator wants to replace a full hierarchy of an instance or instance child.
  • Migration path for OID policy="replace".

When a REPLACE_SELECTED batch is processed:

  • New instances from the batch are added.
  • Pre-existing instances (top level or subcollection) changed in any way by the batch are fully replaced. NULLS override existing values.
  • Pre-existing instances not in the batch, such as:
    • Top level: No action
    • Child level: If an ancestor is in the batch, it is deleted. Otherwise, no action is taken.

If an instance or instance child is included in a REPLACE_SELECTED batch, all descendants which are still required must also be included as part of the batch. Any omitted descendants are deleted.

The following table shows the status of a source instance after a REPLACE_SELECTED operation.

Instance in Ramp?

Instance in Source?

Source Status Before Operation

Source Status After Operation

Yes

Yes

ACTIVE

ACTIVE

Yes

Yes

INACTIVE

ACTIVE

Yes

No

N/A

ACTIVE

No

Yes

ACTIVE

ACTIVE, if no direct ancestor instance is in ramp. Otherwise, INACTIVE.

No

Yes

INACTIVE

INACTIVE

Guidelines for a batch composition

The set of instances in a REPLACE_SELECTED batch must form fully connected hierarchies, but not necessarily from the subject root. No two hierarchies within the batch, however, can share the same subject root. This condition will be satisfied when one of the following holds for each instance in the batch:

  • The instance is a top-level instance.
  • If the parent of the instance is not in the batch, then both:
    • All the ancestors of the instance are not in the batch.
    • All the ancestors of the instance are in Omni Source.

REPLACE_ALL

Use Cases:

The data from a source system is only available in its entirety. The integrator supplies an initial load or a complete replacement load for the source system.

When a REPLACE_ALL batch is processed:

  • New instances from the batch are added.
  • Pre-existing instances (top level or subcollection) changed in any way by the batch are replaced.
  • Pre-existing instances (top level or subcollection) not in the batch are deleted.

The following table shows the status of a source instance after a REPLACE_ALL operation.

Instance in Ramp?

Instance in Source?

Source Status Before Operation

Source Status After Operation

Yes

Yes

ACTIVE

ACTIVE

Yes

Yes

INACTIVE

ACTIVE

Yes

No

N/A

ACTIVE

No

Yes

ACTIVE

INACTIVE

No

Yes

INACTIVE

INACTIVE

Source system isolation: A source system name or comma-delimited list of source systems names, is given in a column on ramp_control. These system names become a filter to the RampToSource function. This supports loading from different source systems on separate schedules. No activity will occur for source systems not on the list.

Hazard: Mass deletion will result if a batch is launched in error with partial data. The missing section will be made inactive. A subsequent full REPLACE_ALL will restore the integrity of the repository. As a precaution against a misspelled batch_id, no records will be changed if an empty REPLACE_ALL batch is received.

Guidelines for batch composition

The set of instances in a REPLACE_ALL batch must form fully connected hierarchies from the subject root. This will be the case when all instances in the batch satisfy one of the following:

  • The instance is a top-level instance.
  • All the ancestors of the instance are in the batch.

DELETE

Use cases:

  • The integrator selects instances of hierarchies to delete, either from the root level of a subject or a subcollection level.

When a DELETE batch is processed:

  • New instances from the batch are ignored.
  • Pre-existing instances (top level or subcollection) are marked as inactive. All descendants of the instances are marked inactive.
  • Pre-existing instances are not in the batch:
    • Top level: no action
    • Child level: If the ancestor is in the batch, it will be marked as inactive. Otherwise, no action is taken.

The following table shows the status of a source instance after a DELETE operation.

Instance in Ramp?

Instance in Source?

Source Status Before Operation

Source Status After Operation

Yes

Yes

ACTIVE

ACTIVE

Yes

Yes

INACTIVE

ACTIVE

Yes

No

N/A

ACTIVE

No

Yes

ACTIVE

ACTIVE, if no direct ancestor instance is in ramp. Otherwise, INACTIVE.

No

Yes

INACTIVE

INACTIVE

Guidelines for a batch composition

There are no restrictions on a DELETE batch.

Deleting Omni Data

A delete instruction for a particular instance (either top level or subcollection) is an indication that it should no longer be considered a logical member of the subject set. This instance is then given inactive status. The consequences of an instance becoming inactive this way are outside the scope of this document but include suppression of cleansing, possible mastering reconciliation and removal from the visibility of consumption views, and the Management Console. The history of the inactive instance should be maintained and the contents of the instance, aside from being marked inactive, are otherwise left intact.

An instance (top level or subcollection) will be deleted by the OnRamp as the result of the following operations:

  • The key for the instance is referenced in a DELETE batch.
  • The key for the parent of the instance is referenced in a DELETE batch.
  • The key for the instance is omitted in a REPLACE_ALL batch.
  • The key for the instance is omitted in a REPLACE_SELECTED batch, and the key for an ancestor of the instance is referenced by the batch.

An inactive instance can be returned to active status if it appears in a subsequent REPLACE_ALL, REPLACE_SELECTED, or UPSERT batch.

Inactive instances may be scheduled for physical removal by using the Purge Inactive Time and Purge Inactive Age settings.

For more information on purging inactive data, see the Omni-Gen™ Operation and Management Guide.

Orphan Omni Data

Orphan Omni data are active child instances that have at least one ancestor that is either inactive or does not exist. Using the different ramp operations, you can introduce orphan records into Omni-HealthData. These orphans are either inaccessible or have undefined behavior with respect to Omni-HealthData processing. If the usage guidelines given below for each of the ramp operations are followed, orphan records will not be introduced.

Nulls

The upsertNullHandling option controls the handling of a NULL column input for the batch_type UPSERT.

  • PRESERVE. Preserves the existing value.
  • OVERRIDE. Replaces the existing value with NULL.

If neither option is specified, PRESERVE is used as the default.

Note: Non-null columns always replace existing values.