5 Retail Data Extractor Universal Adapter

This chapter describes the process of implementing the Retail Insights Universal Adapter Framework.

Overview of Retail Insights Universal Adapter Framework

The Retail Data Extractor BI product offering is intended to work closely with Oracle Retail's transactional schema, RMS. As such, the earlier version of Retail Insights shipped with source dependent extraction (SDE) routines designed to move data from RMS tables into RI staging tables. The new version of Universal Adapter for RDE loads extracted data from RMS to RDE Stage tables which is then passed to Retail Insights in the form of text files. (see Figure 5-1, "RDE to Retail Insights Staging Data Flow").

Figure 5-1 RDE to Retail Insights Staging Data Flow

The goal of the RDE Universal Adapter Framework (UAF) is to simplify the process of moving source dependent extracts into Retail Insights staging tables for customers . In terms of implementation, the UAF first requires RMS extracted data in RDE staging tables and the Universal Adapter unloader program generates pipe ('|') separated value (DAT file) text file extracts to be provided as inputs to RI. All date column should use a format of "YYYY-MM-DD;HH24:MI:SS". Once the DAT files are in place, these can be transferred to the Retail Insights system by zipping them into a single directory and FTP the zip file to Retail Insights server mount point.

• The control files(*.ctx) can be created by the Universal Adapter unloading program in case there is a difference in the datatype of a stage table between RDE environment and RI environment.

• Customers can use this utility to generate dat files and FTP the data in a more secured way. The earlier approach of DBlink is not required anymore, thereby providing higher security

Universal Adapter Configuration

By Default Universal adapter generates only the dat files (with data) and generation of ctx control file is disabled. In order to generate the ctx file for adjusting datatypes between RDE and RI environments please follow the below steps.

1. Navigate to Oracle BI Applications 11g -> Utilities_and_Execution -> Utilities -> Procedures -> RABE_GENERATE_CTXFILE.

   Figure 5-2 Projects Hierarchy

   
   

2. Modify the procedure RABE_GENERATE_CTXFILE -> Options and set GENERATE_CTXFILE =1.

   Figure 5-3 RABE_GENERATE_CTXFILE

   
   

3. Navigate to procedure RA_GENERATE_CTXFILE -> Options and set GENERATE_CTXFILE =1.

   Figure 5-4 RA_GENERATE_CTXFILE

   
   

4. Save the above changes and Regenerate the scenarios. Right-click on the folder (SDE_RetailUniversalAdapterLoad) and select the option to generate all scenarios. Select generate mode to re-generate and check the Packages option under Objects to Generate as show in the following screen.

   Figure 5-5 Scenario Generation

   
   
   
   

   Note: The generation of ctxfile is required only once to adjust the ctl file in Retail Insights and can be restored to original value default(0) for all subsequent runs by reverting the above configuration changes to original.

   
   

Universal Adapter Execution

Execute the Retail Data Extractor SDE script rtluasde.ksh and rabeuasde.ksh to run the Universal Adapter for generating the staging DAT files. Script rabeuasde.ksh is for the target tables owned by RI batch user and script rtluasde.ksh is for the target tables owned by RI data owner.

Syntax:

rtluasde.ksh <Target table>
rabeuasde.ksh <Target table>

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.

File Transfer to Retail Insights

Universal adapter generates the data and control files (*.dat and *.ctx) which needs to be transferred to Retail Insights for loading Facts and Dimensions.

Automate the below steps in order to transfer the files to Retail Insights.

1. Navigate the to data/staging directory where Universal Adapter files are generated.

   Path:  cd <$MMIHOME>/data/staging
   

2. Generate the tar file with all the files in data/staging directory.

   Command: tar cf $MMHOME/data/staging/< Merch_Extract_date>.tar $MMHOME/data/staging/*.dat $MMHOME/data/staging/*.dat.ctx

3. Connect to <server> port 22.

4. Log in with the SFTP User credentials.

5. Transfer all data files to the directory /<SFTP User>/EXPORT.

6. Create a directory called COMMAND under /<SFTP User>if it does not already exist.

7. Change to the /<SFTP User>/COMMAND directory.

8. Transfer an empty file called COMPLETE.