ODI Consumer Interface Guide

Introduction

The Operational Data Integration (ODI) product is designed to improve the performance and capabilities of moving data between “data providers” and “data consumers”. ODI is a middleware that serves as a flexible central bulk data-store delivered by data providers which in turn is made available to data consumers. What has historically become a time consuming and costly process of extracting and delivering bulk data to numerous data consumers is now offloaded to the ODI system which provides standardized data collection from data providers, significant reduction of extraction routines, and efficient data distribution and delivery to consumers. The diagram below illustrates this relationship.

As a middleware product, ODI is largely invisible to customers. Most of the processing of ODI is also invisible to consumers, except for those aspects that allow the consumers to access the data. These touch points of ODI with which consumers will interact are:

• Notification that provider data for which a consumer is authorized is ready for consumption
• A service for consumers to request credentials to access SQL View data
• Direct access to the operational data store (SQL Views) to fetch data (preferred)

-or-

• Legacy file extracts in standard CSV form

Process Overview

Once data is received by ODI from the Data Provider, it is processed for consumption. When the data is ready for consumption a message is made available to the Data Consumer via the Enterprise Event System (event 50040). There are two distinct methods of data retrieval, Direct SQL data access and File based data access. Direct SQL data access requires the Data Consumer to acquire secure user credentials via the ODISvcCred message through jXchange Service Gateway. The user credentials are active for a limited time and cannot be used after expiration. Once expired, new credentials must be requested using ODISvcCred.

Direct SQL Access

Direct SQL Data Access allows the consumer to access the data store in SQL. The consumer can fetch data directly from SQL views. Once the data is retrieved to the consumer’s own data store or staging location, the consumer is free to manipulate the data as needed. This is the preferred data retrieval method as there is no processing required by ODI and the consumer can be flexible in when the data is retrieved.

File Based Data Access (CSV)

File Based Data Access allows the consumer to pick up a .csv file from a secured FTP. ODI will automatically create the file and deliver to the FTP site for pick up by the consumer.

The process repeats when the new Data Provider file is received by ODI. All existing data is overwritten with the new data and it is again presented for consumption.

As an example, here are the basic steps for using direct SQL access:

• Provider delivers data to ODI
• ODI stores the data in SQL
• ODI notifies the interested Consumers via EES
• Consumer reads the notification
• Consumer uses ODISvcCred to obtain temporary SQL credentials
• Consumer uses the SQL credentials to access their private view, using the connection info provided with the credentials
• Consumer stores the Provider data in their own staging area, waiting for all data updates to complete before updating their working data store.

ODISvcCred

There is a change regarding the ODISvcCred message response that will be implemented with the new version, ODI vNext. The message contract has not changed but information previously found in the response may no longer be provided. The ServerIPAddr and SQLInstcName elements may no longer contain IP addresses or SQL instance names. The primary reason for the change is due to the use of SQL Availability Groups (AG), which will provide High Availability services for the ODI data.

If SQL AG is present for the environment in use, the SQLInstcName element will be returned in the response but will be empty. If SQL AG is not used, the instance name value will be returned.

<ServerName>OdiProdSqlAgLis.odi.edn,54278</ServerName>
<ServerIPAddr></ServerIPAddr>
<SQLInstcName></SQLInstcName>
<RsStat>Success</RsStat>

Comprehensive information about the ODISvcCred API can be found here.

OFI (Operational File Integration)

The process described above covers most uses of the ODI system and the data it brokers. One special feature of ODI that has been added in the 2015.0 release is OFI, or Operational File Integration. Like Operational Data Integration, it serves to provide access to other products’ data without having to contact that provider directly, but allows the provider and consumer to share data in a format not ingestible by ODI.

Using OFI, a provider can make data available in any file format it chooses and pass that data file to ODI with the intent of the ODI system then making it available to one or more consumers. In this manner, ODI becomes a pass-through for data it cannot easily handle via the SQL database. There are a few additional features of OFI that make it useful in a wide range of applications. These features are contained in the *.ofi control file that must accompany the actual provider data file. First, the provider has the option of naming the consumers that are to receive the data file. The consumers must already be configured to receive the file, but this allows the provider some ability to customize the data for specific consumers. Second, the control file can contain a ticket ID that can be used by the provider and consumer to correlate the data as a response to some external request process not made via ODI. The ticket ID is revealed to the consumer as part of the EES event sent as notification of the file’s availability. In this manner, ODI is operating as a bulk response mechanism.

ODI Consumer Access

The data contract defines the data elements that will be included in the direct access view or flat file provided to the consumer. The list of available data elements is defined by the provider data contract. Note that some providers will not have all possible elements.

The data provided by the core systems contains many data elements, some of which may not be needed by any given consumer. Therefore, the data transformation configuration can specify a subset of the available elements or can be configured to use all available elements. It is best practice for the transformation definition to contain only those elements needed.

If a data element or type of data does not exist already in ODI, a gap must be declared for that core system and provider contracts created or updated.

The EI&S Developer Relations team will work with each consumer product team to configure the transformation definitions specifically for the product’s needs. These transformations are then added to the ODI inventory and can be used wherever ODI is installed.

Receiving “Data-Ready” Notifications

ODI consumers can receive notifications that their consumer data is available. This notification will serve as a method for allowing the consumer to determine that provider data has been loaded and is available in the form the consumer transformation definition specifies. This data will typically be consumed through direct access to the database; some consumers may choose to access data through the legacy method of CSV extracts. Both methods are described in later parts of this document.

Enterprise Events

The Enterprise Event System (EES) provides a means of routing event notifications from the event publisher to one or more interested parties (consuming products) via a subscription model. The consumers, or subscribers, own subscriptions in EES that are defined with aid from EI&S Product Adoption. The subscriber can then opt to retrieve the events matching their subscription definition on their own, or the subscriber product can stand up a service to which events are pushed to by EES.

The events published by ODI to EES will contain information for use by the consumer to indicate data is ready for a specific institution and data contract.

EI&S will use these data elements as filters to construct the EES subscription for each consuming product. Each consuming product will need to either call a message in EES to retrieve the available events matching the subscription filters or stand up a service that must be made public to EES to have the events delivered. See the EES documentation for more details on subscription models.

EES Event 50040 Example XML (SQL View Access Method)

<EESEventInfo>
    <EventNum>50040</EventNum>
    <EventProd>ODI</EventProd>
    <EventTimeDt>2019-11-07T17:10:47.89</EventTimeDt>
    <InstId>{InstitutionrtId}</InstId>
    <ProdEnv>{Environment}</ProdEnv>
    <SystemId>GADEV</SystemId>
    <BusCorrelId>82a36f49-c956-412c-8b74-011942ea190c</BusCorrelId>
    <EventAppId>JHA - EEMS</EventAppId>
    <EventDataInfoArray>
        <EventDataInfo>
            <Name>ValidConsumerName</Name>
            <Val>{ConsumerVendorName}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>ValidConsumerProd</Name>
            <Val>{ConsumerProductName}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>InstRtId</Name>
            <Val>{InstitutionrtId}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>InstEnv</Name>
            <Val>{Environment}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>ContractName</Name>
            <Val>DepAcct</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>OutputName</Name>
            <Val>{ConsumerVendorName}{ConsumerProductName}_{ProviderContractName}_{InstitutionrtId}_{Environment}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>ExtractDateTime</Name>
            <Val>3/14/2018 5:10:47 PM</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>ConsumerName</Name>
            <Val>eems</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>TransformName</Name>
            <Val>DepAcct</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>ActivityId</Name>
            <Val>2a4fcd53-823c-404e-a5df-2a50ede54258</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>LoadSequence</Name>
            <Val>20200415.67100</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>ProviderExtractType</Name>
            <Val>Full</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>PrvdBusDt</Name>
            <Val>2020-02-20</Val>
        </EventDataInfo>
    </EventDataInfoArray>
    <EventUniqueId>3a7f68e3-4a81-4ea8-adb7-a3006d29ee17</EventUniqueId>
    <EESIdHistInfoArray>
        <EESIdHistInfo>
            <EESHistId>123456789-PROD-jXchange-jxchangeGA-development.dev.jha</EESHistId>
        </EESIdHistInfo>
    </EESIdHistInfoArray>
    <Custom/>
</EESEventInfo>

EES Event for OFI Data File ready

<EESEventInfo>
    <EventNum>50040</EventNum>
    <EventProd>ODI</EventProd>
    <EventTimeDt>2020-08-26T11:08:24.817</EventTimeDt>
    <InstId>{InstitutionrtId}</InstId>
    <ProdEnv>{Environment}</ProdEnv>
    <BusCorrelId>ab390d5c-4634-415d-baf7-4bdb11247630</BusCorrelId>
    <EventAppId>{Publisher of the data file}</EventAppId>
    <EventDataInfoArray>
        <EventDataInfo>
            <Name>ValidPrvdName</Name>
            <Val>{ProviderVendorName}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>ValidPrvdProd</Name>
            <Val>{ProviderVendorProduct}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>InstRtId</Name>
            <Val>{InstitutionrtId}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>InstEnv</Name>
            <Val>{Environment}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>PayloadType</Name>
            <Val>{Name of contract if registered OFI datafile type}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>OfiFileName</Name>
            <Val>{Datafile name}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>FileType</Name>
            <Val>OFI</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>Consumer</Name>
            <Val>{ConsumerProductName}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>ConsumerMessage</Name>
            <Val>OFI file is ready: {Datafile name}</Val>
        </EventDataInfo>
        <EventDataInfo>
            <Name>SvcTckt</Name>
            <Val>{ReferenceID}</Val>
        </EventDataInfo>
    </EventDataInfoArray>
    <EventUniqueId>8aa58ceb-2b67-4ba9-b915-63337e89e252</EventUniqueId>
    <EESIdHistInfoArray>
        <EESIdHistInfo>
            <EESHistId>0</EESHistId>
        </EESIdHistInfo>
    </EESIdHistInfoArray>
    <Custom/>
</EESEventInfo>

Direct Data Access

While the process of retrieving data from ODI won’t be much different from other data import routines you might have used previously, there are a few points worth mentioning that you will read about in more detail later in this document. Here is a quick overview of a typical data load process for a consumer using direct-access into the ODI database. For each data feed:

• Receive Data Ready Notification
• Request SQL Credentials from ODI Requestor service through JX Service Gateway
  • Obtain SQL server location from Requestor response
  • Obtain Temporary SQL credentials from Requestor response
• Access the SQL Views
  • Open a connection to SQL to access your custom view
• Update local SQL staging area
  • Copy the complete set of rows (or just the updates since your last access)
• Update product database

Connections to the database are controlled by credentials from the ODI credential request service. The next section describes how a consumer product can request credentials and build a connection string to access data directly.

Requesting Data Access Credentials

Consumers whose data plans specify direct data access can fetch data using views defined for their consumer data plan. The first step to accessing this data is to obtain credentials to allow the consumer to connect to the ODS.

Accessing the Database

With the information from the credentials response message the consumer program should be able to connect to the SQL server. The database name that should be accessed is:

• OdiWorking

This will be the same DB name across all ODI vNext environments, regardless of location.

SQL View Naming Conventions

The SQL View naming convention has been updated to include the {ConsumerVendorName} value. The full name of the SQL View is provided in the EventDataInfo Value/Name Pair “OutputName”.

{ConsumerVendorName}{ConsumerProductName}_{ProviderContractName}_{InstitutionrtId}_{Environment}

Example: JHABillpay_Cust_Email_121212121_Prod

Exceptions to the SQL View Naming Conventions:

• If the consumer vendor name starts with a number, the view name will be prefaced with an underscore.
• Any special characters in the consumer vendor name or consumer product name will be replaced with an underscore.

Securing the Database Connection

The installing institution has the choice as to whether to secure database connections with SSL. You should consult the team who installed ODI to find out whether a particular installation uses secure access. If it does, your connection strings must contain the parameter “Encrypt=true” or the connection to the database will be refused.

In addition, you must be given access to the certificate used to secure connections. The install team should be able to provide you with the certificate. You will need to install that certificate on any computers that run any portion of your product that will connect to the ODI database. The user IDs or accounts under which those portions of the product will run must be given read access to the private key for that certificate.

Using Multiple Database Connections

Single-tenant applications are expected to use a single database connection to retrieve their data from the ODI SQL Server. The connection should be opened, data retrieved, and then immediately closed. In a multi-tenant situation, the application should use an internal queue to manage retrieval of their data using a configurable number of database connections. This protects both the application (by throttling the rate of row insertion to the application database) and ODI (by ensuring that connections are available to other applications).

In addition, all applications, including those using a single database connection, should construct their processes to allow for the temporary refusal of SQL Server to allow new connections. The application should recognize this condition and go into a wait state for some period of time before retrying the connection. This will guard against future application failures due to connection management employed by the ODI SQL Server.

SQL Connection string with SQL AG

If SQL Availability Groups (AG) are in use for a given ODI instance, then consumers who retrieve data using SQL Views that are also SQL AG enabled in ODI, the inclusion of MultiSubnetFailover=Yes is required in the connection string. If a failover occurs, this switch will allow the renegotiation of the connection to the new primary SQL server.

Conversely, MultiSubnetFailover=Yes must NOT be included in a connection string if SQL AG is not present or when the consumer is not AG enabled. Otherwise the connection attempt will fail.

ODI Table Relationships

ODI table relationships and referential integrity are defined and maintained by each provider for the contracts (extracts) each supports. The SrcKey and FornKey columns are used for these purposes. The SrcKey is the unique value for each record within a specific table. If a table has a parent child relationship with another ODI table, the FornKey column is used to hold the SrcKey to the other table, from the same provider, for the same institution. Otherwise the FornKey value may null.

Using ODI-Supplied Data Values

The ODI operational store contains several columns that are filled in as data is loaded. Consumers can use these values for multiple purposes.

_LoadDT: Load Date/Time

The _LoadDt contains the ODI system date and time that the data was processed from the data file into the Staging database table.

_LoadSeq: Load Sequence Job Identifier

Each record in each data contract contains a unique job identifier in the format YYYYMMDD.sssss, where sssss is seconds past midnight (sssss). This value represents the order in which the provider sent the extract. The data is presented as nvarchar type.

The job identifier always increases; as such, one of the most common uses is to determine if there is new data to load. This will allow a consumer to load just those records that have been added, updated, or deleted since the last time the consumer got data from ODS.

To do this, assume “lastJob” is the highest value of _LoadSeq encountered the last time a consumer got data from ODI, “maxJob” is the highest value of _LoadSeq in the ODI ODS on the consumer’s current visit, and “minJob” is the minimum value of _LoadSeq in the ODI ODS on the current visit. The following scenarios could be used by the consumer to determine the minimum data to load:

lastJob == maxJob

Nothing for the consumer to load, there have been no updates

lastJob < maxJob

The consumer should fetch all records where _LoadSeq is greater than lastJob. Those are all the records that were updated since the visit

lastJob < minJob

This is a special case of the previous scenario, but it implies that all records in the database have been updated/deleted/reloaded. That doesn’t necessarily guarantee there was a full reload—there could have been a sequence of deltas that resulted in every record in the database being updated/deleted. But, from the consumer’s point of view, it’s the same—the consumer will need to rescan the whole data set.

_DelFlag: Deleted Record Indicator

The _DelFlag provides a Boolean value that can be used in filter criteria. The Provider should be setting the Del field to a value of “true” or “false”. The _DelFlag field provides for some variation on that to account for errors from the Provider. If the upper case value of Del is “TRUE”, “Y”, “YES”, or “1” then the value of _DelFlag is set to 1. For all other values of Del,_DelFlag is set to 0.

File Format Options

ODI can also generate data files in delimited format. Controls are provided in the data plan for the delimited format to allow the consumer to define the delimiter and “surrounding” characters. It is important to use surrounding characters in order to allow data fields to contain the delimiter character.

Another option in the data plan allows the consumer to receive only updated records instead of the entire dataset available. This can be useful for large files, like account masters, that have limited change per day. Please remember that this option, as with the other options, is at the data plan level. So, if the consumer wants to receive a full file on Mondays and updates Tuesday through Friday, two data plans will be needed. Also, if the provider sends a full file to ODI, the consumer will receive all of the records even when requesting a delta, as ODI will have no way to determine which records have been changed.

Data-Ready Notifications

Just as with the direct-access method, the consumer will receive a notification that the file is ready. The notification will contain the URI that identifies the FTP site that holds the file. The ODI requestor service must then be contacted to retrieve the FTP credentials for access to the ODI FTP site.

ODI SQL Usage Guide

Approved Behavior

• Request new SQL Credentials for every extract (1 request per extract)
• If credentials fail, send a new ODISvcCred request and retry with new credentials
• Query only the columns of data needed, request translations that have the specific data required for your usage
• Pull data as is from ODI views to a staging table for use with your application
• Use simple logic to reduce data returned, based on the primary key / index (_loadseq or SrcKey)
• Use RowCount() to segment and return specific rows in your query

with ResultSet as
(
Select *, row_number() over (order by SrcKey ASC) as RowNum
 FROM [OdiWorking].[dbo].[PRODUCTNAME_View_InstRtId_InstEnv]
 where _loadseq >= 20190722.07925
)
Select top (4000) * from ResultSet
Where RowNum > 136000

Unapproved Behavior

• Cache ODI SQL Credentials
• Request SQL credentials for every application node (many requests per 1 extract)
• Perform data manipulation prior to moving out of ODI’s database
• Perform complicated or high resource cost logic statements

case when TrnType = 'C' then 0
else 1 end as IsDebit,
 case when PostDt = '001-01-01' then null
else PostDt end as PostDt,

• Use OFFSET to return specific rows in your query

SELECT * FROM
[OdiWorking].[dbo].[ PRODUCTNAME_View_InstRtId_InstEnv] as X
Where X._LoadDt >= '2019-11-01'
order by x.SrcKey
offset 136000 rows fetch next 40000 rows only 

• Use where conditions or other logic that perform full table scans repeatedly.
• Use “WHERE 1=1” condition

where 1 = 1 and DATEADD(year, SUBSTRING(FornKey, charindex(',', FornKey)+1,7) /
1000 - 1900,
substring(FornKey, charindex(',', FornKey)+1,7) %'1000-1') > '2019-01-01'