7 Retail Analytics Universal Adapter

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

Overview of RA Universal Adapter Framework

The Retail Analytics BI product offering is intended to work closely with Oracle Retail's transactional schema, RMS. As such, Retail Analytics ships with source dependent extraction (SDE) routines designed to move data from RMS tables into Retail Analytics staging tables, and source independent load (SIL) routines to move data from staging tables into warehouse tables (see Figure 7-1, "RMS to Retail Analytics Staging Data Flow").

Figure 7-1 RMS to Retail Analytics Staging Data Flow

While the previously described ETL system works well for Retail Analytics customers who have already installed an RMS-based transactional system, customers who are working with third party (non-RMS) systems who wish to use Retail Analytics would need to write their own ETL solution to move their data into the Retail Analytics 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 Analytics' 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 Analytics staging tables for customers with non-RMS data sources. In terms of implementation, the UAF first requires comma separated value (CSV) text file extracts to be provided as inputs. Once the CSV files are in place, the UAF can be used to move that data into Retail Analytics staging tables through the use of Oracle external tables (see Figure 7-2, "Moving Third Party Extracts into Retail Analytics Staging Tables"). The DDL and DML necessary to create and populate the external tables with the data from the text file extracts will be housed within stored procedures that can either be invoked manually (for example, using a shell script) or via an ODI agent.

Figure 7-2 Moving Third Party Extracts into Retail Analytics Staging Tables

Benefits

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

Customers no longer need to invest in the design, development, testing, or maintenance of SDE ETL interfaces to move data into the Retail Analytics staging area.
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 CSV text file extracts in a pre-defined format as inputs to the UAF.

Universal Adapter Installation and Configuration

The following section outlines the steps necessary for installing the Universal Adapter. Note that while the Universal Adapter is bundled with Retail Analytics, it is an add-on component, and must be installed separately following the installation of the main Retail Analytics product.

File Manifest

Note:

The <STAGING_DIR> mentioned below is the Retail Analytics installer staging directory. Please refer to the Oracle Retail Analytics Installation Guide for additional information.

<STAGING_DIR>/ora/installer/UniversalAdpater/UniversalAdapter.jar
<STAGING_DIR>/ora/installer/UniversalAdpater/ log4j-1.2.17.jar
<STAGING_DIR>/ora/installer/ UniversalAdpater/UAF_create_procedures.sql
<STAGING_DIR>/ora/installer/UniversalAdpater/UAF_setup_grants.sql
<STAGING_DIR>/ora/installer/UniversalAdpater/UAF_create_log_table.sql
<STAGING_DIR>/ora/installer/UniversalAdpater/UAF_insert_c_odi_param.sql

Installation Steps

Installing the Universal Adapter Framework (UAF) database components

Deploy UniversalAdapter.jar and log4j-1.2.17.jar into the RA Batch schema.

Prerequisite: Please install the appropriate Oracle Client package for your system. The loadjava script is part of the Oracle Client.

Run the following commands:
```
loadjava -verbose -force -oci8 -user username/password@INSTANCE UniversalAdapter.jar
 
loadjava -verbose -force -oci8 -user username/password@INSTANCE log4j-1.2.17.jar
```
Create PL/SQL call specifications for Universal Adapter functions.

Run the following SQL script as the RA Batch user:
```
/Universal Adapter/UAF_create_procedures.sql
```
Create the UAF_LOG table.

Run the following SQL script as the RA Batch user:
```
/Universal Adapter/UAF_create_log_table.sql
```
Initialize UAF Configuration Parameters.

Run the following SQL script as the RA Batch user:
```
/Universal Adapter/UAF_insert_c_odi_param.sql
```
Set up the appropriate database grants.

Run the following SQL script as SYSTEM:
```
/Universal Adapter/UAF_setup_grants.sql
```

Note:

This step prompts the user to enter a directory alias (for example, UAF_DATA_DIR) and a physical directory path (for example, /path/to/datafiles) from which CSV data files will be loaded.

In order to run the Universal Adapter Framework, grant write permissions at the OTHER and GROUP levels to the physical directory path from which CSV data files will be loaded.

Importing the Universal Adapter Framework (UAF) ODI components

Make sure $ODI_HOME/bin/odiparams.sh is configured correctly.
Copy odi_import.ksh:
1. For new customers – Copy <STAG-ING_DIR>/ora/installer/ora14/mmhome/full/src/odi_import.ksh to <STAGING_DIR>/ora/installer/Universal_Adapter/odi/. In addition, update the LOGDIR variable in the script to a desired location (for example, <STAGING_DIR>/ora/installer/Universal_Adapter/odi/log).
2. For existing customers – Copy <STAG-ING_DIR>/ora/installer/ora14/mmhome/upgrade/odi-patches/odi_import.ksh to <STAGING_DIR>/ora/installer/Universal_Adapter/odi/.
Execute odi_import.ksh from <STAG-ING_DIR>/ora/installer/Universal_Adapter/odi/odi_import.ksh. This will import the following UAF ODI components:
- FOLD_UAF.xml
- PACK_RA_UAF.xml
- TRT_RA_UAF_INITIALIZE.xml
- TRT_RA_UAF_RUNETL.xml
- VAR_RA_UAF_DATA_DIR.xml
- VAR_RA_UAF_TABLEFILTER.xml
- VAR_RA_UAF_TABLEPREFIX.xml

Importing the UAF Shell script

Copy the uafsde.ksh shell script from <STAGING_DIR>/ora/installer/Universal_Adapter/ to the $MMHOME/src directory.

Configuration Steps

In the C_ODI_PARAM table, update the value of the PARAM_VALUE column to the directory/folder name as mentioned during the Set up the appropriate database grants installation step for the entry with PARAM_NAME = 'UAF_DATA_DIR'. For example:

UPDATE C_ODI_PARAM
SET PARAM_VALUE = 'UAF_DIR' /*This is the directory alias that is mentioned during the installation step */
WHERE PARAM_NAME = 'UAF_DATA_DIR'
AND SCENARIO_NAME = 'RA_UAF'

Universal Adapter Execution

Execute the Retail Analytics SDE script uafsde.ksh to run the Universal Aggregation Framework for loading.

Syntax: uafsde.ksh %stagingTableFilter %stagingTablePrefix

stagingTableFilter – a staging table filter that supports the use of wildcards using the % notation

For example, W_RTL% would search on all tables starting with a W_RTL prefix.

stagingTablePrefix – the UAF_Initialize procedure creates external tables that match up with corresponding staging tables. The stagingTablePrefix represents the initial set of characters in a staging table that represent the starting prefix of that table.

For example, a staging table prefix of W would allow the UAF_Initialize procedure to replace that character with a U. Therefore, W_RTL_SUPPLIER_DS would get an external table named U_RTL_SUPPLIER_DS

For example, a staging table prefix of W_RTL would allow the UAF_Initialize procedure to replace that character with a U. Therefore, W_RTL_SUPPLIER_DS would get an external table named U_SUPPLIER_DS.

Universal Adapter Process Flow

The following diagrams show the process flow of the Retail Analytics Universal Adapter.

Figure 7-3 Universal Adapter Initialization – UAF_Initialize

Figure 7-4 Universal Adapter ETL Execution – UAF_runETL