| Oracle® Retail Insights Cloud Service Suite Implementation Guide Release 18.0.001 F17008-02 |
|
 Previous |
 Next |
| Oracle® Retail Insights Cloud Service Suite Implementation Guide Release 18.0.001 F17008-02 |
|
Previous |
Next |
The Setup and Configuration chapter provides parameters for setting up Retail Insights. The following sections are included:
This section provides a list of factors that should be taken into account when making sizing plans.
There are two major hardware components that make up the Retail Insights physical environment:
Middle Tier Application Server - The middle tier application server hosts software components such as Oracle WebLogic Server and Oracle Business Intelligence Enterprise Edition (EE) or Oracle Business Intelligence Standard Edition One (SE One).
Database - The Oracle Database stores large amounts of data that are queried in generating Oracle BI reports. The daily data loading process and report query processing process are both heavily dependent on the hardware sizing decision.
Sizing is customer-specific. The sizing of the Retail Insights application is sensitive to a wide variety of factors. Therefore, sizing must be determined on an individual installation basis.
Testing is essential. As with any large application, extensive testing is essential for determining the best configuration of hardware.
Database tuning is essential, just like any other database. The Oracle database is the most critical performance and sizing component of Retail Insights. As with any database installation, regularly monitoring database performance and activity levels and regularly tuning the database operation are essential for optimal performance.
Application Server
Report Complexity - Reports processed through Oracle BI can range from very simple one-table reports to very complex reports with multiple-table joins and in-line nested queries. The application server receives data from the database and converts it into report screens. The mix of reports that will be run will heavily influence the sizing decision.
Number of Concurrent Users - The Retail Insights application is designed to be a multiple concurrent use system. When more users are running reports simultaneously, more resources are necessary to handle the reporting workload. For more details on Clustering and Load Balancing, refer to the Clustering, Load Balancing and Failover section in Oracle Business Intelligence chapter of the Oracle Business Intelligence Enterprise Edition Deployment Guide.
Back End Database
Functions Used Determine Tables to be populated - Retail Insights is designed to be a functional system so that some functions (such as supplier compliance or order processing) that are available do not have to be used. To the extent that some functions are not used, the amount of resources may be reduced correspondingly.
Fact Tables and Indexes - Disk space is required for tables and indexes. To identify the database objects necessary for the selected functions, refer to the Data Model.
Dimension Tables and Indexes - Dimension tables and indexes also require space and generally indicate the size of the data to be stored. Disk space must be planned on the basis of record counts in the dimension tables.
Data Purging Plan - How Much Data to be Stored - The number of years of data to be stored also contributes to the amount of disk space required. Disk space to store fact data is generally linear with the number of years to data to be stored.
Database Backup and Recovery - The importance of the data and the urgency with which a recovery must be made will drive the backup and recovery plan. The backup and recovery plan may have a significant impact on disk space requirements.
Data Storage Requirements
Transaction Volume - Sales - the higher the number of sales records, the higher the disk storage requirements and the higher the resource requirements to process queries against sales-oriented tables and indexes.
Positional Data - Inventory, Price, Cost - Positional data (data that is a snapshot at a specific point in time, such as inventory data "as of 9:00AM this morning") can result in very large tables. The Retail Insights concept of data compression (not to be confused with database table compression) is important in controlling the disk space requirement. For more information, see Chapter 4, "Compression and Partitioning".
Extract, Transform, Load - Daily Processing - The daily loading process is a batch process of loading data from external files into various temporary tables, then into permanent tables, and finally into aggregation tables. Disk space and other resources are necessary to support the ETL process.
Data Reclassification Requirements - Frequent hierarchy reclassification impacts resources.
Processing Report Queries - Report queries submitted to the back-end database have the potential to be large and complex. The size of the temporary tablespace and other resources are driven by the nature of the queries being processed.
Configuration issues
Archivelog mode - If the database is being operated in archivelog mode, additional disk space is required to store archived redo logs.
SGA and PGA sizing - the sizing of these memory structures is critical to efficient processing of report queries, particularly when there are multiple queries running simultaneously.
Initialization Parameters - The initialization parameter settings enable you to optimize the daily data loading and report query processing.
Data Storage Density - As is the case with many data warehouses, the data stored in the Retail Insights database is relatively static and dense storage of data in database data blocks results in more efficient report query processing.
Hardware Architecture
Number and Speed of Processors - More and faster processors speed both daily data loading and report query processing in the database. The application server needs fewer resources than the database.
Memory - More memory enables a larger SGA in the database and will reduce the processing time for report queries.
Network - Since the data from the report queries needs to go from the back-end database to the application server, a faster network is better than a slower network, but this is a relatively low priority.
Disk - RPMs, spindles, cache, cabling, JFS - I/O considerations are very critical to optimal performance. Selection of disk drives in the disk array should focus on speed. For example, faster RPMs, more spindles, larger cache, fiber optic cabling, JFS2 or equivalent.
RAID - The selection of a RAID configuration is also a critical decision. In particular, RAID5 involves computations that slows Disk I/O. The key is to select the RAID configuration that maximizes I/O while meeting redundancy requirements for data protection.
Backup and Recovery - The backup and recovery strategy drives disk configuration, disk size, and possibly the number of servers, if Dataguard or Real Application Clusters are used.
For base level positional fact data, Retail Insights uses a compression approach to reduce the data volume. Compression in Retail Insights refers to storing physical data that only reflects changes to the underlying data source and filling in the gaps between actual data records through the use of database views. For detailed information about compression, refer to Chapter 4, "Compression and Partitioning".
To report positional data correctly in the Retail Insights user interface, data seeding is required if clients launch Retail Insights later than a source system RDE (Extracted data from RMS). For performance reasons, it is recommended that all date range partitioned positional fact tables must seed data on the first date or first week of each partition. This avoids searching the data across partitions.Data used for seeding can come from RMS or from client legacy systems. The following are some recommendations to seed data:
If seeding data is for new added partition, you can run the Retail Insights script retailpartseedfactplp.ksh to seed new partition. This script moves seed data from Retail Insights CUR tables to new partitions.If seeding data is for new tables, you may need to provide snapshots of your positional fact data. See "Data Initial Load from RDE" for how to provide initial snapshots of positional fact data.
Retail Insights fact tables may not have data at the same granularity as a client has in its legacy system. The granularity of client history data can be higher or lower than what the Retail Insights data model supports.
If the granularity of client history data is lower than what the Retail Insights data model supports, the client can aggregate data to the same level that the Retail Insights data model supports, and then populate the Retail Insights base table.
If the granularity of client history is higher than what the Retail Insights data model supports, the client can aggregate data to the Retail Insights aggregation tables (if they are available). This could cause inconsistencies between the Retail Insights base tables and Retail Insights aggregation tables within the legacy time period. When the client reports data on those time periods, the client has to be aware of the inconsistencies between base level and high aggregation level.
Retail Insights provided APIs can be used for designing and developing the data extraction programs from legacy system. For more information on the APIs, refer to the Oracle Retail Interfaces Document.
By default, Retail Insights provides the features to use the following types of BI reporting scenarios:
As-Was
As-Is
Point in Time
For more information on the reporting scenarios, refer to the Oracle Retail Insights User Guide.
Based on business needs, you can configure to have one or all of these scenarios, or the combination. These configuration changes will be in ODI (the change is only in the batch scheduler, which is not available by default with Retail Insights), Oracle BI EE, and the Oracle Retail Insights Data Model.
If the business requirement is to see the history as it happened all the time, which is the As-Was scenario, then it is recommended that you disable all ODI jobs related to As-Is and vice versa. The reason for this is to reduce the load and avoid unnecessary jobs to improve the batch time. For more information on identifying these jobs, refer to chapter 6 ODI Program Dependency in the Oracle Retail Insights Operations Guide.
Once the ODI jobs are disabled, the appropriate tables/objects must be disabled in Oracle BI EE and the data model.
Lets take an example of Inventory Receipts fact and following are the required steps if As-Is is not needed.
ODI
Disable the jobs related to the following tables:
W_RTL_INVRC_SC_DY_CUR_AW_RTL_INVRC_SC_LC_WK_CUR_AW_RTL_INVRC_SC_WK_CUR_A
This information can be found in the Oracle Retail Insights Operations Guide.
Oracle BI EE
In the "Fact - Retail Inventory Receipts" logical table, disable the following sources:
Fact_W_RTL_INVRC_SC_DY_CUR_AFact_W_RTL_INVRC_SC_LC_WK_CUR_AFact_W_RTL_INVRC_SC_WK_CUR_A
With this change, these tables can never be accessed and As-Is reporting cannot be done for inventory receipts. The same changes must be done for all the other fact areas as well. Since Retail Insights has three subject areas (Retail As-Was, Retail As-Is and Retail Point in Time), all three are available to the user. Since As-Is components should be disabled, go to the Administration -> Manage Privileges on Oracle BI EE web and, for all the users/roles, change the permission setting to "Denied" for the As-Is subject area. This way, the As-Is subject area will not be available for reporting. The following figure displays the Administration screen.
Data Model
All the unused tables can still be in the schema and will be not used by ODI and Oracle BI EE programs. It can be maintained for future changes.
Point in Time reporting can be done with As-Was, As-Is, or with both. There are no separate ODI processes for PIT. PIT can be derived from either As-Is or As-Was data and the processing will happen during report execution. Only difference is, PIT reports cannot use aggregate tables and will always be reported from the base fact tables. There are some limitations to do this reporting in some fact areas owing to performance. For example, with the Positional facts, PIT is possible only for Product hierarchy because there are corporate aggregates for product which have the decompressed data.
The real differentiation of As-Is and As-Was in the data happens above the base fact table or when reporting is done at the parent level. Otherwise if the reporting is done at the lowest grain level, the result set will be the same for both.
In order to report Retail Insights positional data correctly, all Retail Insights positional compressed tables need to be seeded with source data (RDE) correctly before they can be loaded using Retail Insights batch ETL with daily data. This seeding process is to load positional fact data for each item location combination available from RDE to Retail Insights as initial data. This can be done by using following recommended approach. This approach assumes that user uses RDE (extract of RMS) as Retail Insights source system and the required data are available from RMS.
This initial inventory position data loading includes loading seeding date from RDE to RA W_RTL_INV_IT_LC_DY_F, W_RTL_INV_IT_LC_WK_A, and W_RTL_INV_IT_LC_G tables. Perform the following steps:
In the data extract received from RDE, make sure the Retail Insights table W_RTL_CURR_MCAL_G has the business date and week for the current Retail Insights business date. This is used as the date for seeding data. This date should match the RMS vdate set in the RDE environment.
Execute the Retail Insights SIL script invildsil.ksh to load the inventory seeding data from the staging table to the Retail Insights base fact table W_RTL_INV_IT_LC_DY_F and W_RTL_INV_IT_LC_G.
Execute the Retail Insights PLP script invildwplp.ksh to load inventory seeding data to the Retail Insights table W_RTL_INV_IT_LC_WK_A.
Execute other inventory PLP scripts to populate the Retail Insights inventory aggregation tables. These are chosen by the client for reporting purposes.
This initial Pricing data loading includes loading seeding date from RDE to Retail Insights W_RTL_PRICE_IT_LC_DY_F and W_RTL_PRICE_IT_LC_G tables. Perform the following steps:
In the data extract received from RDE, make sure the Retail Insights table W_RTL_CURR_MCAL_G has the business date and week for the current Retail Insights business date. This is used as the date for seeding data. This date should match RMS vdate set for the SDE program.
Execute the Retail Insights SIL script prcilsil.ksh to load pricing seeding data from the staging table to the Retail Insights base fact tables W_RTL_PRICE_IT_LC_DY_F and W_RTL_PRICE_IT_LC_G.
Execute the other Price PLP scripts to populate the Retail Insights Price aggregation tables. These are chosen by the client for reporting purposes.
This initial Net Cost data loading includes loading seeding date from RDE to the Retail Insights W_RTL_NCOST_IT_LC_DY_F and W_RTL_NCOST_IT_LC_G tables. Perform the following steps:
In the data extract received from RDE, make sure the Retail Insights table W_RTL_CURR_MCAL_G has the business date and week for the current Retail Insights business date. This is used as the date for seeding data. This date should match the RMS vdate set for the SDE program.
Execute the Retail Insights SIL script ncstildsil.ksh to load Net Cost seeding data from the staging table to the Retail Insights base fact table W_RTL_NCOST_IT_LC_DY_F and W_RTL_NCOST_IT_LC_G.
Execute other Net Cost PLP scripts to populate the Retail Insights Net Cost aggregation tables. These are chosen by the client for reporting purpose.
This initial Base Cost data loading includes loading seeding date from RDE to Retail Insights W_RTL_BCOST_IT_LC_DY_F and W_RTL_BCOST_IT_LC_G tables. Perform the following steps:
In the data extract received from RDE, make sure the Retail Insights table W_RTL_CURR_MCAL_G has the business date and week for the current Retail Insights business date. This is used as the date for seeding data. This date should match the RMS vdate set for the SDE program.
Execute the Retail Insights SIL script cstildsil.ksh to load Base Cost seeding data from staging table to the Retail Insights base fact table W_RTL_BCOST_IT_LC_DY_F and W_RTL_BCOST_IT_LC_G.
Execute the other Base Cost PLP scripts to populate the Retail Insights Base Cost aggregation tables. These are chosen by the client for reporting purposes.
This initial Competitor Pricing data loading includes loading seeding date from RDE to RI W_RTL_COMP_PRICE_IT_LC_DY_F and W_RTL_COMP_PRICE_IT_LC_G tables. Perform the following steps:
In the data extract received from RDE, make sure the Retail Insights table W_RTL_ CURR_MCAL_G has the business date and week for the current Retail Insights business date. This is used as the date for seeding data. This date should match the RMS vdate set in the RDE environment.
Execute the Retail Insights SIL script cprcfsil.ksh to load the Competitor Pricing seeding data from the staging table to the Retail Insights base fact table W_RTL_COMP_PRICE_IT_LC_DY_F and W_RTL_COMP_PRICE_IT_LC_G.
This initial unavailable inventory data loading includes loading seeding date from RDE to RI W_RTL_INVU_IT_LC_DY_F, W_RTL_INVU_IT_LC_WK_A, and W_RTL_INVU_IT_ LC_G tables. Perform the following steps:
In the data extract received from RDE, make sure the Retail Insights table W_RTL_ CURR_MCAL_G has the business date and week for the current Retail Insights business date. This is used as the date for seeding data. This date should match the RMS vdate set in the RDE environment.
Execute the Retail Insights SIL script unavinvildsil.ksh to load the unavailable inventory seeding data from the staging table to the Retail Insights base fact table W_RTL_INVU_IT_ LC_DY_F and W_RTL_INVU_IT_LC_G.
Execute the Retail Insights PLP script unavinvilwplp.ksh to load unavailable inventory seeding data to the Retail Insights table W_RTL_INVU_IT_LC_WK_A.
Execute other inventory PLP scripts to populate the Retail Insights unavailable inventory aggregation tables. These are chosen by the client for reporting purposes.
This section provides the setup and configuration of item images, item attribute image, and product hierarchy image in Oracle BI EE. Retail Insights only holds image location in a format of URL of a server where the images are hosted.
The following items need to be configured in Oracle BI EE in order to render these Item Images.
Create an attribute in the rpd mapping to the column where URL is stored.
In answers, add this new attribute to required report. Go to the column properties of this attribute and change the data format to Image URL. Save the report.
Bounce all the services (including WebLogic).
Run the report to see images.
