background image

 

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. Reverse engineering, disassembly, or decompilation of the Programs, except to the 
extent required to obtain interoperability with other independently created software or as specified 
by law, is prohibited. 

The information contained in this document is subject to change without notice. If you find any 
problems in the documentation, please report them to us in writing. This document is not 
warranted to be error-free. Except as may be expressly permitted in your license agreement for 
these Programs, no part of these Programs may be reproduced or transmitted in any form or by 
any means, electronic or mechanical, for any purpose. 

If the Programs are delivered to the United States Government or anyone licensing or using the 
Programs on behalf of the United States Government, the following notice is applicable: 

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and 
technical data delivered to U.S. Government customers are "commercial computer software" or 
"commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-
specific supplemental regulations. As such, use, duplication, disclosure, modification, and 
adaptation of the Programs, including documentation and technical data, shall be subject to the 
licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent 
applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software—
Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065 

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other 
inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate 
fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the 
Programs are used for such purposes, and we disclaim liability for any damages caused by such 
use of the Programs.  

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation 
and/or its affiliates. Other names may be trademarks of their respective owners. 

The Programs may provide links to Web sites and access to content, products, and services from 
third parties. Oracle is not responsible for the availability of, or any content provided on, third-
party Web sites. You bear all risks associated with the use of such content. If you choose to 
purchase any products or services from a third party, the relationship is directly between you and 
the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or 
(b) fulfilling any of the terms of the agreement with the third party, including delivery of products 
or services and warranty obligations related to purchased products or services. Oracle is not 
responsible for any loss or damage of any sort that you may incur from dealing with any third 
party.  

Summary of Contents for Retail Data Warehouse

Page 1: ...Oracle Retail Data Warehouse Database Installation Guide Release 13 0 April 2008 ...

Page 2: ...tion disclosure modification and adaptation of the Programs including documentation and technical data shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement and to the extent applicable the additional rights set forth in FAR 52 227 19 Commercial Computer Software Restricted Rights June 1987 Oracle Corporation 500 Oracle Parkway Redwood City CA 94065 Th...

Page 3: ...imbedded in Oracle Retail Store Inventory Management v the software component known as Crystal Enterprise Professional and or Crystal Reports Professional licensed by Business Objects Software Limited Business Objects and imbedded in Oracle Retail Store Inventory Management vi the software component known as Access Via licensed by Access Via of Seattle Washington and imbedded in Oracle Retail Sign...

Page 4: ......

Page 5: ... and Privileges 5 Copy Profiles for RDW User 6 2 Installation Tasks 7 Establish Data Warehouse Partitioning Strategy 7 Create the RDW Database and Tablespaces 8 Create the Database as Follows 8 Create RDW Tablespaces 8 Load RDW Database 9 Create RDW Schema Owners 9 Create RDW Datamart level tables 9 Set up RDW User Permissions 9 Configure RETL 10 Data Population 10 Populate Datamart Default Data 1...

Page 6: ......

Page 7: ...ersion and program module name Functional and technical description of the problem include business impact Detailed step by step instructions to re create Exact error message received Screen shots of each step you take Review Patch Documentation For a base release 0 release such as 13 0 Oracle Retail strongly recommends that you read all patch documentation before you begin installation procedures...

Page 8: ...edure 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 Ahyperlinkappearslikethis ...

Page 9: ... can also operate as a standalone product and be fed from other legacy systems If Oracle Retail applications are used as the source systems follow the requirements in the installation guides for each of these applications It is recommended that the source systems be on a separate server from the data warehouse which is considered the target server Refer to Appendix A for more detailed information ...

Page 10: ...ements Minimum Recommended Memory 8GB 12GB Multi processors 4 highest MHz 8 highest MHz CD ROM drive either local or network Disk Space 300 GB Site specific refer to your RDW sizing information Swap Space Equal to physical RAM 2 5 times the physical RAM Note This reflects the amount of space needed to install the database software RETL software as well as the RDW software and still maintain an acc...

Page 11: ...on Check Database Server Software Requirements General Requirements for a database server running RDW include Supported on Versions Supported Database Server OS UNIX based OS certified with Oracle RDBMS 10g Enterprise Edition Oracle Enterprise Linux 4 Patch 5 Database Server Oracle RDBMS 10g Release 2 Enterprise Edition minimum 10 2 0 3 patchset required with the following patches and components P...

Page 12: ...include See specific database platform documentation for client requirements See the Oracle Retail Data Warehouse Middle Tier Installation Guide for other client requirements Create UNIX User Accounts 1 Log in as the root user 2 Create the following groups dba admin group which controls RDW access dev development group 3 Create the following user accounts using ksh as the default shell oretail dba...

Page 13: ...base directory is a working directory that you should designate at this time As the oretail user extract the rdw13_code zip file in the base directory specified above by entering the following at the UNIX prompt unzip rdw13_code zip Note It is highly recommended that clients have a separate server for production installations To set up a production environment create a prd directory then copy the ...

Page 14: ...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 Any variables that may require modification are noted in the sample profile comments Be sure to set up these UNIX accounts to automatically run the profile wi...

Page 15: ...es not exist ORA 01432 public synonym to be dropped does not exist ORA 00942 table or view does not exist ORA 29833 indextype does not exist ORA 29807 specified operator does not exist ORA 29931 specified association does not exist and ORA 29816 object being disassociated is not present These errors can be ignored The ORA errors are caused by dropping the objects the script is about to create Esta...

Page 16: ...s init ORACLE_SID ora 6 Modify the INSTALL_DIR rdw13 create_db crdb1 sql file Refer to comments in this file regarding modifications that need to be made 7 Login to SQL Plus as SYSDBA and execute INSTALL_DIR rdw13 create_db crdb1 sql Review crdb1 log for errors and correct as needed 8 Login to SQL Plus as SYSDBA and execute INSTALL_DIR rdw13 create_db crdb2 sql Review crdb2 log for errors and corr...

Page 17: ...a user change directories on the UNIX server to base_directory rdw13 0 dbasql This directory contains scripts that set up all necessary synonyms and grants for the RDW environment The header of each file contains information regarding which user should execute the script when to run them and additional details about the scripts The scripts spool to the tmp directory This directory should already e...

Page 18: ...a as described in the rest of this chapter 1 Log in to the UNIX server with the rdw13dev account 2 Change directories to base_directory rdw13 0 rfx etc 3 Modify the rdw_config env script to match your environment For detail information about each setting refer to section rdw_config env settings within the RDW Operation Guide Data Population In order to successfully populate the database with datam...

Page 19: ...able for RDW batch modules to reference load_prod_diff_type_dm sql populates the diff type dimension with default diff type values load_rtl_type_dm sql populates the retail type dimension table with default retail types load_cde_dtl_com_dm sql populates the customer order code abbreviation table for RDW batch modules to reference load_tsf_type_dm sql populates the transfer type dimension table wit...

Page 20: ...er to the RMS Operations Guide volume 3 for details of how to extract time After the calendar information has been extracted proceed to section Create Time in RDW to load it in RDW To populate time for 454 13 period or 454 with Gregorian calendar standalone proceed to section Create Standalone Time For information on the tables loaded for the Time dimension refer to the RDW Data Model documentatio...

Page 21: ... I o cr_time_454 c_utils c cr_time_454 c cc g I o cr_time_13 c_utils c cr_time_13 c cc g I o cr_time_g c_utils c cr_time_g c 6 Execute the cr_time ksh module At the UNIX prompt enter cr_time ksh 7 This script prompts for the calendar type Choose 1 for 454 time 2 for 13 period time and 3 for 454 with Gregorian time 8 At the prompt enter the 4 digit year for the beginning and ending of the time cale...

Page 22: ... to be loaded 1 Login to the RDW database server as the UNIX user rdw13dev Verify the RETL executable is in the path of your UNIX session by typing which rfx 2 Change directories on the UNIX server to base_directory rdw13 0 install This directory contains the code for all default data install scripts 3 The script timelfldm ksh loads the following Time Like for Like tables that are used exclusively...

Page 23: ...es file Add the following lines to this file substituting where appropriate RDW_name RDW_INSTALL_DATE yyyy MM dd HH mm ss RDW_name RDW_VERSION 13 0 0 Example rdw13inst1 RDW_INSTALL_DATE 2007 11 10 09 51 50 rdw13inst1 RDW_VERSION 13 0 0 yyyy MM dd HH mm ss refers to the date of RDW installation See example value above RDW_name refers to a unique name to identify this RDW installation This is arbitr...

Page 24: ......

Page 25: ...se Dimension Data Fact Data etc Source Server i e RMS ReIM etc Oracle Database RDW 13 0 Development Environment Target Server RDW 13 0 Oracle Database Flat File Landing Directory RDW USERS RDW13DM Owns datamart level _DM tables RDW13DEV Batch user to execute RDW modules RDW13SYS OBI EE User that connects to the metadata and the warehouse RDW13DM RDW13DEV RDW13SYS Flat Files Facts Dimensions and In...

Page 26: ......

Page 27: ...l This directory contains all RETL modules and SQL scripts needed only at installation base_directory log This directory holds log files of program execution Directory is empty on installation base_directory rfx This directory contains subdirectories for all the code and files related directly to RETL base_directory rfx bookmark This directory contains a file created during execution of each modul...

Page 28: ......

Page 29: ... creation log_buffer 10485760 shared_pool_size 350M 350M for initial db creation shared_pool_reserved_size 35M 10 of shared_pool_size The following parameters do not affect SGA size audit_file_dest admin_path adump background_dump_dest admin_path bdump compatible 10 2 0 control_files datafile_path control01 ctl datafile_path control02 ctl core_dump_dest admin_path cdump db_block_size 8192 Default ...

Page 30: ...utl_file_path workarea_size_policy auto Should be set to auto when pga_aggregate_target is set Set these parameters for Oracle Retail Data Warehouse RDW database nls_date_format DD MON RRRR Required by MicroStrategy query_rewrite_integrity TRUSTED star_transformation_enabled TRUE utl_file_dir Windows_utl_file_path UNIX_util_file_path Archive Logging set if needed log_archive_dest_1 location admin_...

Page 31: ...dmin catrep sql PROMPT Running owminst plb ORACLE_HOME rdbms admin owminst plb REM These privs needed for users to run proper grant code when creating users grant select on dba_jobs to public with grant option grant select on dba_roles to public with grant option grant select on dba_role_privs to public with grant option grant execute on dbms_rls to public with grant option grant execute on dbms_a...

Page 32: ...OME javavm install initjvm sql ORACLE_HOME xdk admin initxml sql ORACLE_HOME xdk admin xmlja sql ORACLE_HOME rdbms admin catjava sql ORACLE_HOME rdbms admin catexf sql spool off spool context log ORACLE_HOME ctx admin catctx change_on_install SYSAUX TEMP NOLOCK connect CTXSYS change_on_install ORACLE_HOME ctx admin defaults dr0defin sql AMERICAN spool off spool xdb_protocol log connect as sysdba O...

Page 33: ...dex01 dbf SIZE 300M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO CREATE TABLESPACE dm_fact_data DATAFILE datafile_path dm_fact_data01 dbf SIZE 300M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO CREATE TABLESPACE dm_fact_index DATAFILE datafile_path dm_fact_index01 dbf SIZE 300M AUTOEXTEND ON NEXT 100M MAXS...

Page 34: ...TOEXTEND ON NEXT 100M MAXSIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO CREATE TABLESPACE user_data DATAFILE datafile_path user_data01 dbf SIZE 300M AUTOEXTEND ON NEXT 50M MAXSIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO spool off exit ...

Page 35: ...ion of RPM you are asked for the RIBforRPM provider URL Since RIB is installed after RPM make a note of the URL you enter If you need to change the RIBforRPM provider URL after you install RIB you can do so by editing the jndi_provider xml file 9 Oracle Retail Central Office ORCO 10 Oracle Retail Back Office ORBO 11 Oracle Retail Store Inventory Management SIM Note During installation of SIM you a...

Reviews: