Integration With the Relational OnRamp

Topics:

The Omni-Gen Relational OnRamp is a set of relational tables and protocols used to load data into an Omni system. This section describes how data can be integrated with the Relational OnRamp.

Table Mappings

Excel workbooks, generated on deployment, provide documentation for each IDS subject. The top-level subject and each child collection are described on separate worksheets of the workbook. The worksheet describes each attribute, its data type, and the layout of the corresponding ramp and model tables of a subject. Table mappings can be obtained from the Deployment area of the Omni Console, where a Download Documentation option is available.

Table Keys

Each OnRamp table has a group of identifying columns, which taken together, map to the source system record. These columns will be part of the primary key.

For top-level subjects, the identifying columns are:

  • source_name (Source Name). Name of the source system (for example, source_name = TestEpic).
  • subject_sid (Source Instance ID). Unique Business identifier of the Subject within the named source system (for example, facility_sid = 123).

For child collections, the identifying columns are:

  • source_name. Same as the source name of the parent (for example, source_name = TestEpic).
  • top_level_subject_sid. Unique identifier of top-level parent subject (for example, facility_sid = 123).
  • intermediate_level_collection_sid [0-n]. Identifiers of each intermediate collection separating the child from the root subject. There is one sid per level. (for example, facility_child_sid = CHILDTYPE1, facility_grandchild_sid = GRANDCHILDTYPE1, and so on).
  • child_sid. Unique Business identifier of the child collection object within the hierarchy defined by the key (for example, a discriminator with respect to the parent or facility_address_sid = PrimaryLocation).

Equivalently, the identifying columns of a child collection may be defined recursively as the combination of the following:

  • Identifying columns of the direct parent.
  • sid of the child.

Finally, the full primary key for any of these tables, whether top-level, or child collection, is comprised of the identifying columns described above and the batch_id column, which is present in each ROR table.

Note: The identifying column values are restricted to the following characters: A-Z, a-z, 0-9, and underscores (_), hyphens (-) and carets (^).

Non-Key Data Columns

The following are conventions for forming OnRamp fields from their IDS names, based on their type.

  • String, Double, Integer fields. <CamelCase> becomes <lower_case>, as before.
  • Date/Time fields (idsIdsDateTimeType). It is recommended that date and time elements terminate with tokens Date, Time, or DateTime to indicate the desired precision. If these tokens are found, they are converted to lowercase in the OnRamp field name using the usual transformation.
  • Codes. Three columns are used to comprise the code reference:
    <lower_case> + _src
    <lower_case> + _set
    <lower_case> + _val
  • Links. The element name changes from <CamelCase> to <lower_case> and takes the following suffixes and links:
    • Sample 1. Link is defined to refer to a single subject.
      <lower_case> + _subjectName + _snm
      <lower_case> + _subjectName + _sid
    • Sample 2. Link is defined to refer to one of several possible subjects.
      <lower_case> + _subjectName + _sbj
      <lower_case> + _subjectName + _snm
      <lower_case> + _subjectName + _sid
  • Group Names. Elements within groups work unchanged.
    <CamelCase>

    becomes

    <grp_id> + <lower_case>

Additional Columns

  • batch_id. This is part of the key for each OnRamp table. Every OnRamp row is associated with an input batch.
  • onr_created_datetime. Not currently implemented. If the integrator sequences entries in a batch by this field, the OnRamp may in the future use it for recovery.

Source Codes

Source Codes and Source Code Sets are loaded through the OnRamp tables like other subjects. The top-level subject to use when submitting a batch with either or both is SourceCodeSet. However, the IDS structure for SourceCode and SourceCodeSet is slightly different from standard subjects, so the following considerations apply.

  • Duplicate naming of fields
    • source_code_set_r. Source_code_set_sid and code_set_name must be populated with duplicate values of the code set name.
    • source_code_r. Source_code_sid and source_code must be populated with duplicate values of the source code.
  • Source Codes reference their Sets as part of the key. The records for these sets (from source_code_set_r) must either be included in the OnRamp batch with the source codes or have already been loaded.
  • Omni-Gen uses standard codes (SourceCodeStandard). SourceCodeSet and data that reference those codes, including SourceCodeStandard, can be loaded in any order.