Oracle® Retail Data Warehouse Database Installation Guide Release 13.
Oracle® Retail Data Warehouse Database Installation Guide, Release 13.0 Copyright © 2008, Oracle. All rights reserved. Primary Author: Paul Kehler The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws.
Value-Added Reseller (VAR) Language (i) the software component known as ACUMATE developed and licensed by Lucent Technologies Inc. of Murray Hill, New Jersey, to Oracle and imbedded in the Oracle Retail Predictive Application Server – Enterprise Engine, Oracle Retail Category Management, Oracle Retail Item Planning, Oracle Retail Merchandise Financial Planning, Oracle Retail Advanced Inventory Planning and Oracle Retail Demand Forecasting applications.
Contents Preface ............................................................................................................................. vii Audience ............................................................................................................................... vii Related Documents.............................................................................................................. vii Customer Support............................................................................
Preface Oracle Retail Installation Guides contain the requirements and procedures that are necessary for the retailer to install Oracle Retail products.
Conventions Navigate: This is a navigate statement. It tells you how to get to the start of the procedure and ends with a screen shot of the starting point and the statement “the Window Name window opens.” Note: This is a note. It is used to call out information that is important, but not necessarily part of the procedure. This is a code sample It is used to display examples of code A hyperlink appears like this.
1 Preinstallation Tasks This release of RDW incorporates optional interfaces with these Oracle Retail products which can be sources for the data warehouse: Oracle Retail Merchandising System (RMS), Oracle Retail Invoice Matching (ReIM), Oracle Retail Merchandise Financial Planning (MFP), and Oracle Retail Price Management (RPM). Additionally, the data warehouse can also operate as a standalone product and be fed from other legacy systems.
Preinstallation Tasks Implementation Capacity Planning There is significant complexity involved in the deployment of Oracle Retail applications, and capacity planning is site specific. Oracle Retail strongly suggests that before installation or implementation you engage your integrator (such as the Oracle Retail Consulting team) and hardware vendor to request a disk sizing and capacity planning effort.
Check Database Server Software Requirements Overall database size for the RDW. A capacity plan should be done for the database server and DASD requirements in general to assess how large the environment will need to be. Note: These data warehouse server requirements should only be used as guidelines because they reflect the hardware used to run a small environment (approximately 200 gigabytes of data).
Preinstallation Tasks Other Software Requirements for the Data Warehouse Please refer to the RDW 13.0 Middle Tier Installation Guide for the full installation instructions of the Oracle Business Intelligence Enterprise Edition components. Requirement Version Oracle Business Intelligence Enterprise Edition 10.1.3.3 Oracle Retail Extract Transform and Load (RETL) 13.0 Oracle Retail Merchandising System (RMS)/Oracle Retail Trade Management (RTM)/Oracle Retail Sales Audit (ReSA) 13.
Alter Directory Ownership and Privileges 2. Create RDW database directory structure: Log in to the database server as UNIX user rdwdba. This user owns the database directory structure. Extract the files from the downloaded zip file. FTP (in binary mode) the tar file, rdw13_db.zip, to on the UNIX server where the rdw13 database will be created. The directory is a working directory that should be designated at this time.
Preinstallation Tasks Copy Profiles for RDW User 1. Login to UNIX as the rdw13dev user and change directories to /rdw13.0/sample_profiles. 2. Copy the profile to the home directory of rdw13dev. Edit the .profile to correspond with the server file system, RETL installation and UNIX environment. Note: The sample profiles by default are set up for a development environment. Change the indicated variables as necessary for other environments.
2 Installation Tasks It is assumed that Oracle 10g release 2, with appropriate patches, has already been installed. If not, refer to “Check Database Server Software Requirements” in Chapter 1, “Preinstallation Tasks” before proceeding. Note: Become familiar with the RDW application in a development environment before setting up a production system. The following instructions are recommended for development and test environments only.
Installation Tasks Create the RDW Database and Tablespaces Create the Database as Follows 1. Login to the RDW database server as the rdwdba user. 2. Create the Oracle recommended OFA directory structure for the database (datafile directories, adump, bdump, cdump, arch, create, exp, pfile, udump, utl_file_dir). 3. Place an entry in the oratab file for the database and execute oraenv to set the ORACLE_SID and ORACLE_HOME environment variables. 4. Copy INSTALL_DIR/rdw13/create_db/init.
Load RDW Database Load RDW Database Create RDW Schema Owners 1. As the rdwdba user, change directories to INSTALL_DIR/rdw13/utility. 2. Review the crusers.sql file for the names and passwords of the rdw users that will be created. Note these as they are used later in the installation. 3. Login to SQL*Plus as SYSDBA and execute the following script: SQL>@crusers..sql 4. To secure the passwords in the create users script, change the privileges on the script by entering: chmod 700 crusers.
Installation Tasks SP2-0734: unknown command beginning "no rows se..." rest of line ignored. Note: These scripts are also used for maintenance. If you need to drop or add a table in the database, run these scripts again to give the appropriate privileges to your users. If a table has been dropped and recreated for maintenance, you also need to verify that all objects in the database are VALID. For a test or a production environment, you need to modify the rdw_users.
Data Population Populate Datamart Default Data 1. As the rdw13dev user, change directories to /rdw13.0/install. This directory contains the code for all default data installation scripts. 2. At the UNIX prompt, enter: default_load.ksh Note: Read the comments on every install script called within default_load.ksh, specifically load_maint_dim_key_dm.sql because the initial date for curr_load_dt might be different for every client.
Installation Tasks 8. On the UNIX server, cChange directories to /rdw13.0/log. 9. Review the log file that was created or modified. 10. On the UNIX server, change directories to /rdw13.0/error. 11. Review the error files that were created for time_static_load. Note: Refer to the RDW Operations Guide for more information on the log files and error files. 12. Import the localization_dm.dmp file to the localization_dm table following the steps below: a.
Data Population Create Time in RDW If more time data is needed, modify the parameters below for the new time period and run the module again. To minimize the load time for adding additional time, enter only the first year to be added as the response for the first year prompt in number 5. 1. If RMS is not being used as the source of the time calendar, move on to step 2. After running time extraction in RMS (see RMS Operations Guide volume 3 for details), FTP time_454.txt, start_of_half_month.
Installation Tasks the error files that were created for g_time_load and g_time_trnsfrm_load if 454 time with Gregorian calendar is used. 13. Refer to the RDW Operations Guide for more information on the log files and error files. Note: DAY_IDNT serves as a surrogate key for day of the time. It can represent 454 time, 13 period time or Gregorian time. Since it is a surrogate key, it does not have meaning to it as long as it is unique across all the days of the time used in RDW.
Install Oracle Configuration Manager Install Oracle Configuration Manager This Oracle Retail product has been instrumented for configuration discovery and collection by Oracle Configuration Manager (OCM). It is recommended that you take advantage of OCM in your environment. Please see MetaLink note 559539.1 for more information about OCM instrumentation in Oracle Retail products. Installing OCM for RDW: Determine your RDW_HOME directory path.
A Appendix: Development Database Schema Implementation The following figure shows the source system and RDW development schemas that are referred to in this installation guide. RDW 13.0 Development Environment Source Server (i.e. RMS, ReIM, etc.) Oracle Database Flat File Landing Directory Target Server RDW 13.0 Oracle Database RDW13DM RDW13DEV Source Database (Dimension Data, Fact Data, etc) RDW13SYS Flat Files Facts, Dimensions and Interfaces (RESA, MFP, etc.
B Appendix: RDW Code Tree The following table describes the contents of each of the RDW code tree directories created during the RDW installation. Path Directory Description dbasql This directory contains all SQL scripts necessary to maintain the permissions for the database users. batch Empty directory used for development and testing purposes only. data This directory contains the text files that serve as the input to RDW RETL load modules.
C Appendix: Oracle 10g Database Creation Scripts ############################################################################### # Oracle 10.2.0.x Parameter file # # NOTES: Before using this script: # 1. Change , , , and # values as appropriate. # 2. Replace the word SID with the database name. # 3. Size parameters as necessary for development, test, and production environments.
Appendix: Oracle 10g Database Creation Scripts nls_sort = BINARY # Should be explicitly set to ensure all sessions get the same order nls_territory = AMERICA # Default open_cursors = 900 # Oracle Retail required (minimum=900); default is 50 optimizer_features_enable = 10.2.0.
Install Oracle Configuration Manager GROUP 3 ('/redo3a.log') SIZE 1000M DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/temp01.dbf' SIZE 5000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M UNDO TABLESPACE undo_ts DATAFILE '/undo_ts01.dbf' SIZE 5000M SYSAUX DATAFILE '/sysaux01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M ; exit spool off -------------------------------------------------------------------------------------- Script: crdb2.
Appendix: Oracle 10g Database Creation Scripts spool off exit -------------------------------------------------------------------------------------- Script: crdb3.sql --- Execute as: sysdba in 10.1.0.2 databases or higher --- Note: This script installs java and xml components; --Do not change the order of the statements below due to dependencies -----------------------------------------------------------------------------------spool JServer.log @$ORACLE_HOME/javavm/install/initjvm.
D Appendix: Tablespace Creation Scripts -------------------------------------------------------------------------------------- Script: create_rdw_tablespaces.sql --- Execute as: sysdba --- Note: Before running this script: --Modify values. --Modify datafile storage parameters and sizes based on partitioning strategy. --------------------------------------------------------------------------------spool create_rdw_tablespaces.
Appendix: Tablespace Creation Scripts AUTOEXTEND ON NEXT 100M MAXSIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; CREATE TABLESPACE user_data DATAFILE '/user_data01.
E Appendix: Installation Order This section provides a guideline as to the order in which the Oracle Retail applications should be installed. If a retailer has chosen to use some, but not all, of the applications the order is still valid less the applications not being installed. 1. Oracle Retail Merchandising System (RMS), Oracle Retail Trade Management (RTM), Oracle Retail Sales Audit (ReSA) 2. Oracle Retail Service Layer (RSL) 3. Oracle Retail Extract, Transform, Load (RETL) 4.
