Retail Insights Universal Adapter

Overview of Retail Insights Universal Adapter Framework

The Retail Insights BI product offering was intended to work closely with Oracle Retail's transactional schema, RMS. As such, Retail Analytics (the earlier versions of Retail Insights) shipped with source dependent extraction (SDE) routines designed to move data from RMS tables into Retail Analytics staging tables is now moved to Retail Data Extractor., The new version of Retail Insights works closely with Retail Data Extractor and processes the staging data sent in the form of flat files and loads to staging tables in RA Data Mart schema. The source independent load (SIL) moves data from staging tables into warehouse tables (see Figure 8-1).

Figure 8-1 RDE to Retail Insights Staging Data Flow

Customers who are working with third party (non-RMS) systems who wish to use Retail Insights would need to write their own ETL solution to move their data into the Retail Insights staging tables. The design of a custom ETL solution would be driven by such factors as:

The number and nature of data sources (relational, mainframe, file-based, etc.) containing the necessary transaction data.
The topology of the data sources. Such customers would either need to write custom SDE ETL interfaces for use with Retail Insights' ODI-based ETL system or create their own ETL logic from scratch.

The goal of the Universal Adapter Framework (UAF) is to simplify the process of moving source dependent extracts into Retail Insights staging tables for customers in a cloud/on-premise environment. The files arriving from RDE or non RMS systems should provide pipe ('|') separated value (DAT) text file extracts to be RI. All date columns should use a format of "YYYY-MM-DD;HH24:MI:SS". Once the DAT files are in place, the UAF can be used to move that data into Retail Insights staging tables through the use of Oracle sqlldr (see Figure 8-2). The control files required for sqlldr will be created automatically during the processing that is controlled in ODI.

Figure 8-2 Moving Third Party Extracts into Retail Insights Staging Tables

Benefits

Customers who elect to leverage the UAF will enjoy the following benefits:

For customers whose third party data sources are non-relational in nature (for example, mainframe data), their development efforts only need to be focused on delivering DAT text file extracts in a pre-defined format as inputs to the UAF.
DBlink that was used in Retail Analytics SDE programs is not required anymore. This will provide security compliance.

Universal Adapter Installation and Configuration

The installation of UAF is included in the RI standard installation. Please refer to the Oracle Retail Insights Administration Guide for UAF installation and configuration information.

Please ensure the following ODI installation files have permissions to execute the Universal adapter

From the ODI installation directory navigate to the ODI SDK library files and assign 775 permissions to the files mentioned below.
```
Path: cd <$ODIHOME>/../../oracledi.sdk/lib/Lib/ 
 
Command to Execute: chmod 775 <filename> 
```
Replace the below filenames to assign 775 permissions to each of these.
- os$py.class
- stat$py.class
- posixpath$py.class
- warnings$py.class
- types$py.class
- linecache$py.class

Universal Adapter Execution

Execute the Retail Insights script rtluasil.ksh and rabeuasil.ksh to run the Universal Adapter for loading. Script rabeuasil.ksh is for the target tables owned by RI batch user and script rtluasil.ksh is for the target tables owned by RI data owner.

Syntax:

rtluasil.ksh <Target table>
rabeuasil.ksh <Target table>

The Universal adapter programs accepts two types of file inputs.

Dat file – This file contains the staging data that will be loaded to Fact and Dimension tables. Data files are mandatory for all the Staging tables to be loaded. In order to load legless stage tables, It is customers responsibility to generate dat files with data and place them in the "$MMHOME/data/staging"directory.
Ctx file – This is an optional file and contains the metadata information that will be used to adjust ctl file generated by sqlloader in Universal adapter. This ctx file is only required if there is a mismatch in the datatypes in the source data in text files and the target loading database.

Before starting the execution download the exported zip file and extract staging data files into $MMHOME/data/staging directory.

The following is the download file process.

Connect to <server> port 22.
Log in with the SFTP User credentials.
Change directory to /<SFTP User>/EXPORT.
Extract the tar file <Merch_Extract_date>.tar into $MMHOME/data/staging directory.
The tar file <Merch_Extract_date>.tar can be deleted from the /<SFTP User> directory after the data files / ctx files are extracted, but Oracle recommends you archive these files for future reference and logging purposes.

Batch Logging:

The batch for Universal Adapter will have the same logging logic as other RI batch programs. The execution status can be found in RI batch maintenance table C_LOAD_DATES and ODI Operator. Besides these, Universal Adapter also provides sqlldr log files for more information of the loading in detail. The sqlldr log files can be found under $MMHOME/data/staging/log.