Oracle Audio Technologies Retail Data Warehouse User Manual

Oracle® Retail Data Warehouse

Database Installation Guide Release 13.0

April 2008

Oracle® Retail Data Warehouse Database Installation Guide, Release 13.0

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 agencyspecific 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, thirdparty 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.

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.

(ii) the MicroStrategy (MicroStrategy) of McLean, Virginia to Oracle and imbedded in the MicroStrategy for Oracle Retail Data Warehouse and MicroStrategy for Oracle Retail Planning & Optimization applications.

(iii) the SeeBeyond Clara, California, to Oracle and imbedded in the Oracle Retail Integration Bus application.

(iv) the Wavelink component developed and licensed by Wavelink Corporation (Wavelink) of Kirkland, Washington, to Oracle and 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 Signs and Oracle Retail Labels and Tags.

(vii) the software component known as Adobe Flex™ San Jose, California, and imbedded in Oracle Retail Promotion Planning & Optimization application.

(viii) the software component known as Style Report™ Technology Corp. of Piscataway, New Jersey, to Oracle and imbedded in the Oracle Retail Value Chain Collaboration application.

(ix) the software component known as WebLogic™ of San Jose, California, to Oracle and imbedded in the Oracle Retail Value Chain Collaboration application.

(x) the software component known as DataBeacon™ Incorporated of Ottawa, Ontario, Canada, to Oracle and imbedded in the Oracle Retail Value Chain Collaboration application.

Components developed and licensed by MicroStrategy Services Corporation

component developed and licensed by Sun MicroSystems, Inc. (Sun) of Santa

licensed by Adobe Systems Incorporated of

developed and licensed by InetSoft

developed and licensed by BEA Systems, Inc.

developed and licensed by Cognos

Preface ............................................................................................................................. vii

Audience ...............................................................................................................................vii

Related Documents..............................................................................................................vii

Customer Support................................................................................................................ vii

Review Patch Documentation............................................................................................vii

Oracle Retail Documentation on the Oracle Technology Network...............................vii

Conventions.........................................................................................................................viii

1 Preinstallation Tasks................................................................................................... 1

Implementation Capacity Planning......................................................................................2

Check Database Server Hardware Requirements ..............................................................2

Check Database Server Software Requirements.................................................................3

Other Software Requirements for the Data Warehouse....................................................4

Check Client System Requirements .....................................................................................4

Create UNIX User Accounts..................................................................................................4

Create UNIX Directories........................................................................................................4

Alter Directory Ownership 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 .................................................................................11

Populate Time Dimension............................................................................................12

Populate Static Dimension Data ..................................................................................14

Install Oracle Configuration Manager...............................................................................15

A Appendix: Development Database Schema Implementation................................17

B Appendix: RDW Code Tree ....................................................................................... 19

C Appendix: Oracle 10g Database Creation Scripts.................................................. 21

D Appendix: Tablespace Creation Scripts.................................................................. 25

E Appendix: Installation Order .................................................................................... 27

Oracle Retail Installation Guides contain the requirements and procedures that are necessary for the retailer to install Oracle Retail products.

Audience

This Installation Guide is written for the following audiences:

 Database administrators (DBA)  System analysts and designers  Integrators and implementation staff

Preface

Customer Support

https://metalink.oracle.com

When contacting Customer Support, please provide the following:

 Product version 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. Patch documentation can contain critical information related to the base release, based on new information and code changes that have been made since the base release.

Oracle Retail Documentation on the Oracle Technology Network

In addition to being packaged with each product release (on the base or patch level), all Oracle Retail documentation is available on the following Web site:

http://www.oracle.com/technology/documentation/oracle_retail.html

Documentation should be available on this Web site within a month after a product release. Note that documentation is always available with the packaged code on the release date.

vii

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.”

This is a code sample It is used to display examples of code

A hyperlink appears like this.

Note: This is a note. It is used to call out information that is

important, but not necessarily part of the procedure.

viii

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. 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 on RDW and database schema setup.

RDW Architecture

on Oracle

Source Systems --

RMS and others

Interfaces

Target Server --

RDW data warehouse

Intelligence

Server

Web Server

files

End Users

Database Installation Guide 1

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.

Sizing estimates are based on a number of factors, including the following:

 Workload and peak concurrent users and batch transactions  Hardware configuration and parameters  Data sparcity  Application features utilized  Length of time history is retained

Additional considerations during this process include your high availability needs as well as your backup and recovery methods.

Check Database Server Hardware Requirements

Hardware requirements for the data warehouse database server or target server include:

General Hardware Requirements 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

Note: This reflects the amount of space needed to install the

2.5 times the physical RAM

database software, RETL software, as well as the RDW software, and still maintain an acceptable amount of usage on the UNIX file system. This may reflect external storage as well as internal, such as 50GB of internal space and a 250+ GB external RAID storage array.

Sizing Factors and other suggestions to factor into the selection of a data warehouse server include:

 Concurrent front-end user base. More concurrent users will require more database

processing power from the server.

 DASD connectivity. Typically, it is better if the fiber channel connectivity to provide

the maximum throughput to disk.

 Backup/Recovery requirements. Extra disks may be required for backup and

recovery procedures.

 Server backplane speeds. Depending on the architecture of the server backplane (or

comparable links), memory access and/or CPU utilization may be a factor in performance.

2 Oracle Retail Data Warehouse

 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). Actual requirements can be somewhat smaller, or typically much larger, depending on the intended size of the RDW environment upon full implementation. Oracle can assist with making these determinations by providing information on database size estimates, server architecture, and so 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:

Patches:  5397953 (ORA-07445: [KKPAPITGETALL()+2152]

[SIGSEGV] [ADDRESS NOT MAPPED TO OBJECT] [0X34)

 5648872 (SCHEDULER ORA-07445 [OPIDSA()+321]

WHEN SETTING UP CHAIN TEST)

 5921386 (WRONG RESULT WITH MERGE JOINT OUTER

IN THE EXECUTION PLAN)

RAC Only  5721821 (ORA-7445[KGLOBCL] OCCURED AND

INSTANCE WENT DOWN)

Components:

 Oracle Database 10g  Oracle Partitioning  Oracle Net Services  Oracle Call Interface (OCI)  Oracle Programmer  Oracle XML Development Kit

ANSI compliant C compiler (certified with OS and database version)

x-Windows interface

Check Database Server Software Requirements

Database Installation Guide 3

+ 24 hidden pages

Oracle Audio Technologies Retail Data Warehouse User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Audience

Related Documents

Preface

Customer Support

Review Patch Documentation

Oracle Retail Documentation on the Oracle Technology Network

Conventions

Preinstallation Tasks

Implementation Capacity Planning

Check Database Server Hardware Requirements

Check Database Server Software Requirements