Agile Product Lifecycle Management: Agile PLM Data Mart - Setup Guide

Agile Product Lifecycle Management
Agile PLM Data Mart - Setup Guide

v3.0
Part No. E12162-01

April 2008
Copyright and Trademarks

Copyright © 1995, 2008, Oracle. All rights reserved.
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.
Oracle Data Integrator can be used for initial Oracle Agile PLM source to Oracle Agile PLM Data
Mart target synchronizations. Oracle Data Integrator can be used for subsequent bulk loads and
batch data transfers from Oracle Agile PLM source to Oracle Agile PLM Data Mart target.
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 USA, Inc., 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 and Agile 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.
ii Agile Product Lifecycle Management

CONTENTS
Copyright and Trademarks................................................................................................................... ii
Overview of Agile PLM Data Mart ..................................................................................... 1

Data Mart Features...............................................................................................................................1
Data Mart Architecture .........................................................................................................................2
Agile PLM Data Mart Database............................................................................................................5
Data Mart Database Sizing ..................................................................................................................6
ODI Repository Database ....................................................................................................................7
Software Requirements........................................................................................................................7
Hardware Requirements ......................................................................................................................7
Installing Agile PLM Data Mart ......................................................................................... 9

Prerequisites.........................................................................................................................................9
Oracle Database Server...................................................................................................................................................10
Agile PLM Database.........................................................................................................................................................10
Oracle Data Integrator......................................................................................................................................................11
Pre-installation Checks.......................................................................................................................11
Recommendations............................................................................................................................................................12
Installing in Windows........................................................................................................................................................12
Installing in Solaris............................................................................................................................................................12
Installing in Linux..............................................................................................................................................................13
Data Mart Installer ..............................................................................................................................14
Data Mart Installation Process..........................................................................................................................................15
Getting Started .................................................................................................................................................................17
Installing Data Mart Schema ............................................................................................................................................18
Installing Data Mart ETL Components..............................................................................................................................22
Installing Data Mart Schema and ETL Components ........................................................................................................24
Adding DB Services in Listener..........................................................................................................29
Executing Installation Scripts .............................................................................................................29
Validating the Data Mart Installation...................................................................................................29
Database Schemas ..........................................................................................................................................................30
Install Directory Structure .................................................................................................................................................31
Verifying ODI Repositories ...............................................................................................................................................31
iii
Executing ETL.................................................................................................................. 33
Setting up ODI Users .........................................................................................................................33
Executing ETL from ODI ....................................................................................................................35
Executing ETL from Command Prompt .............................................................................................35
Configuring the Agile PLM Data Mart ............................................................................. 37

List Dimension Configurator ...............................................................................................................37
Changing List Names in Dimension Table .........................................................................................39
Validating Dimension Tables..............................................................................................................39
Enabling and disabling the Modules...................................................................................................39
Scheduling ETL Instances..................................................................................................................40
Changing the Data Mart Passwords...................................................................................................41
Troubleshooting .............................................................................................................. 43
Installation Issues ...............................................................................................................................43
ETL Runtime Issues ...........................................................................................................................44
PL/SQL Loggings ...............................................................................................................................44
General Issues ...................................................................................................................................45
Detecting Errors in ODI coming from Oracle......................................................................................45
Best Practices.................................................................................................................. 49
iv Agile Product Lifecycle Management

Preface
The Oracle|Agile documentation set includes Adobe® Acrobat™ PDF files. The Oracle Technology
Network (OTN) Web site (http://www.oracle.com/technology/documentation/agile.html) contains the
latest versions of the Oracle|Agile PLM PDF files. You can view or download these manuals from
the Web site, or you can ask your Agile administrator if there is an Oracle|Agile Documentation
folder available on your network from which you can access the Oracle|Agile documentation (PDF)
files.
Note To read the PDF files, you must use the free Adobe Acrobat Reader™ version 7.0 or later.
This program can be downloaded from the Adobe Web site (http://www.adobe.com).
The Oracle Technology Network (OTN) Web site

(http://www.oracle.com/technology/documentation/agile.html) can be accessed through Help >
Manuals in both the Agile Web Client and the Agile Java Client. If you need additional assistance or
information, please contact support (http://www.oracle.com/agile/support.html)
(http://www.oracle.com/agile/support.html) for assistance.
Note Before calling Agile Support about a problem with an Oracle|Agile PLM manual, please
have the full part number, which is located on the title page.
TTY Access to Oracle Support Services

Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the
United States of America 24 hours a day, 7 days a week. For TTY support, call 800.446.2398.
Outside the United States, call +1.407.458.2479.
Documentation Accessibility
Our goal is to make Oracle products, services, and supporting documentation accessible, with good
usability, to the disabled community. To that end, our documentation includes features that make
information available to users of assistive technology. This documentation is available in HTML
format, and contains markup to facilitate access by the disabled community. Accessibility standards
will continue to evolve over time, and Oracle is actively engaged with other market-leading
technology vendors to address technical obstacles so that our documentation can be accessible to
all of our customers. For more information, visit the Oracle Accessibility Program Web site at
http://www.oracle.com/accessibility/ http://www.oracle.com/accessibility/.
Readme
Any last-minute information about Oracle|Agile PLM can be found in the Readme file on the Oracle
Technology Network (OTN) Web site (http://www.oracle.com/technology/documentation/agile.html)
v
Agile Training Aids
Go to the Oracle University Web page
(http://www.oracle.com/education/chooser/selectcountry_new.html) for more information on Agile
Training offerings.
Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The
conventions for writing code require that closing braces should appear on an otherwise empty line;
however, some screen readers may not always read a line of text that consists solely of a bracket or
brace.
Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle
does not own or control. Oracle neither evaluates nor makes any representations regarding the
accessibility of these Web sites.
vi Agile Product Lifecycle Management

Chapter 1
Overview of Agile PLM Data Mart

This chapter includes the following:
Data Mart Features.............................................................................................................................................. 1

Data Mart Architecture......................................................................................................................................... 2
Agile PLM Data Mart is an operational data store for Agile PLM data integrated with ETL technology
that provides data foundation for your Enterprise Data Warehouse & Analytics solutions. The Agile
PLM Data Mart stores all relevant data available in the PPM, PC, and PQM Modules of Agile PLM.
The data model for the Agile PLM Data Mart is structured to facilitate querying and analysis.
The Agile PLM Data Mart operational data store facilitates the following use cases:
1. Integrate Agile Data with your Corporate Data Warehouse using corporate ETL’s
2. Build and deploy your corporate Business Reporting & Intelligence applications
3. Deploy pre-packaged Agile PLM Business Intelligence (BI) Solutions
For #2 above, we recommend that you design a Multi-dimensional schema (MDS) layer with the
Agile PLM Data Mart as the staging schema. MDS consists of Facts & Dimension tables and it’s
design is driven by Reporting & Intelligence business requirements.
Data Mart Features

Agile PLM Data Mart delivers a normalized schema database that captures the business object
data of Common, PC, PPM & PQM objects & their related data. In addition it captures selected
Administrator data and last modification timestamps which help to further extract data into any
down-stream database system.
Agile PLM Data Mart packages pre-built ETL that leverages Oracle Data Integrator (ODI)
Technology. Using the ODI operator, you can schedule & run scenarios that extract, load &
transform the Agile PLM transactional data into the Agile PLM Data Mart.
Agile PLM Data Mart can be set to extract data from one or more supported Agile PLM
modules.
Agile PLM Data Mart supports incremental extraction for Cover Page, Page 2 & Page 3 data of
the business objects.
Agile PLM Data Mart can be set to run full or Incremental load.
Agile Product Lifecycle Management Page - 1

Data Mart Architecture
Page - 2 Agile Product Lifecycle Management

Chapter 2
Resource and Capacity Planning

Deployment Architecture ..................................................................................................................................... 3

Agile PLM Data Mart Database ........................................................................................................................... 5
Data Mart Database Sizing.................................................................................................................................. 6
ODI Repository Database.................................................................................................................................... 7
Software Requirements ....................................................................................................................................... 7
Hardware Requirements...................................................................................................................................... 7
Deployment Architecture
Deployment Scenarios
Agile PLM Data Mart can be deployed on a single system or multiple systems. The systems should
have the requisite hardware and software configurations.

Single System
Same Instance
Different Instances

Chapter 2
Multiple Systems
Two Systems Three Systems
Agile PLM Data Mart Database

The Agile PLM Data Mart Database comprises of the following:
Staging tables
Metadata tables
Incremental extraction tables
Target Data Mart tables
Data dictionary tables
Normalized schema tables for Agile PLM classes
Dimension tables
Map tables for many to many relationships with dimension tables
The size of Agile PLM Data Mart Database would be approximately twice (2x) that of Agile PLM
Database.
Page - 5
Data Mart Database Sizing

The Agile PLM Data Mart is categorized into four sizes
1. Small
2. Medium
3. Large
4. Extra Large
The basis of this categorization is detailed in the table below.

Criterion Small Medium Large Extra Large
PLM Database Size < 1 GB 1 - 5 GB 5 - 10 GB > 10 GB
Number of Query Connections on PLM Data Mart 5 - 10 10 - 25 25 - 40 > 40
COMMON
Users <100 >100 >500 >1000
User Groups <10 >10 >50 >100
Suppliers < 600 > 1,000 > 2,500 > 5,000
Customers < 100 > 100 > 5000 > 35,000
Discussions < 1,000 > 10,000 > 50,000 > 75,000
Average Workflow steps for all Change objects <6 >6 >8 >12
(ECO, PSR and etc.)
Average Approvers for all changes <5 >5 >10 >20
Transfer Orders / week < 13,000 > 13,000 > 26,000 > 780,000
Files < 250,000 > 250,000 > 500,000 > 1,000,000
Items < 100,000 > 100,000 > 150,000 > 200,000
Manufacturers < 1,500 > 1,500 > 4,000 > 6,500
PC
Manufacturer Parts < 15,000 > 15,000 > 80,000 > 150,000
BoM Rows < 200,000 > 200,000 > 1,000,000 >10,000,000
AML Rows < 100,000 > 100,000 280,000 >450,000
Initial Changes < 10,000 > 10,000 > 40,000 > 70,000
Changes / day (derived) < 15 > 15 > 30 > 75
Avg Assembly BoM size <20 >20 >100 >500
Avg AML / Item <2 >2 >5 >10
Avg Affected Items / Change <2 >2 >5 >10
PQM
Initial Problem Service Requests <10,000 >10,000 >50,000 >100,000
Problem Service Requests / week <2 >2 > 80 > 400
Initial Problem Service Requests <1,000 >1,000 >10,000 >20,000
Quality Change Request / week <2 >2 > 25 > 100
Avg Items / PSR <2 >2 >5 >10
Avg PSRs / PSR <2 >2 >5 >10
Avg QCR / PSR <2 >2 >5 >10
Avg PSRs / QCR <2 >2 >5 >10
Avg Changes / QCR <2 >2 >5 >10
Activities / Year < 75,000 > 75,000 > 500,000 > 1,000,000
Decisions / Year < 1500 > 1500 > 10,000 > 20,000
Root Programs / Year < 500 > 500 > 5,000 > 10,000

Chapter 2
Avg Team size per Program <10 >10 >50 >75
ODI Repository Database

Following are the recommended ODI Repository DB settings
Memory & Disk Spaces Customer Size
Small Medium Large Extra Large
Disk Space 4 GB 8 GB 16 GB 32 GB
Default Extent Size 512K 1024K 2048K 4096K
Default Next Size 256K 512K 1024K 2048K
Datafile Size 512M 512M 1024M 1024M
Number of Datafiles 1 2 2 4
Datafile Autoextensible All All All All
Redo Log File Size 50M 100M 200M 400M
Log_buffer 1000000 1500000 2000000 2500000
Processes 100 150 200 250
Shared_pool_size 200000000 2500000000 3000000000 3500000000
Sort_area_size 10000000 150000000 200000000 250000000
Software Requirements
The following are the operating systems and softwares supported in the Agile PLM Data Mart
Components Type Platform
Operating Systems Microsoft Windows Server 2003
Unix Solaris 9 & 10
Linux RedHat 4
Databases Oracle 9i R2 Enterprise Edition *
10g R2 Enterprise Edition *
Note * It is required to have Oracle Enterprise
Edition for the PLM Source DB to use PLM
Data Mart
Tools Oracle Data Integrator (ODI) 10.1.3.4.2
Java Development Kit 1.5.x
Hardware Requirements
Before you begin installation of Agile PLM Data Mart, please ensure that at least 2 GB of disk space
is available on the server where Data Mart will be installed.
Important Agile PLM Datamart computers and databases should be dedicated to Agile and should
not have any other software installed, unless otherwise specified. Do not include other
database schemas or use the Agile host server as the primary domain controller (PDC) or
Page - 7
dynamic host configuration protocol (DHCP) server.
Note Disk compression must be disabled on Agile computers.
When choosing a hardware configuration, consider the number of total users, the number of
concurrent users, the size of your database, the number of ECOs processed per day, and overall
activity level. For specific technical guidance, please contact Oracle Technical Support or your Agile
PLM Solutions Consultant.
It is required that the computer system on which you are installing Agile PLM Data Mart
components, and the Oracle Database, has at least two physical drives, or two partitions. This
allows you to place the operating system on one drive/partition and use the other for Agile and/or
Oracle components, thus ensuring better performance.
The following are the minimum hardware requirements based on database server size.
Environment CPU RAM Minimum Disk Space
Development (DEV) 1 4 GB 2x Agile PLM DB Size
Testing or Staging (STAGE) 2 4 GB
Production (PROD) 2 or 4 8 GB
System Resources Customer Size

Small Medium Large Extra Large
CPU 2 2-4 4-8 8 ++
Disk Space 12 GB 24 GB 32 GB 64 GB
Memory (Windows) 2 GB 4 GB 6 GB 8 GB
Memory (Linux) 2 GB 4 GB 6 GB 8 GB
Memory (Windows) 2 GB 4 GB 6 GB 8 GB

Chapter 3
Installing Agile PLM Data Mart

Prerequisites........................................................................................................................................................ 9
Pre-installation Checks ........................................................................................................................................ 11
Data Mart Installer ............................................................................................................................................... 14
Adding DB Services in Listener ........................................................................................................................... 29
Executing Installation Scripts............................................................................................................................... 29
Validating the Data Mart Installation .................................................................................................................... 29
Important It is preferable that the Data Mart Database installation is carried out under the guidance
of a DBA.
Important The ETL installation can be carried out by anyone who has administrative privileges for
the machine.
Agile PLM Data Mart can be installed using the Installer program shipped in Installation Pack. It
works on the following operating systems:
Windows 2003 Server
Solaris
Linux
Note Refer Software Requirements on page 7 for the supported OS versions.
Prerequisites
The following should be installed and configured before you begin installilng the Agile PLM Data
Mart -
Oracle Database Server
Agile PLM Database
Oracle Data Integrator
Java Development Kit
If any of these is missing or improperly configured, the Data Mart installation process will fail.
Besides these, ensure that ODI_JAVA_HOME is pointing to JDK 1.5.x.
Agle PLM Data Mart Installer is based on Apache ANT, which is packaged within the Installation
Pack and is deployed automatically.

Oracle Database Server

The Oracle Installer seeks the paths of Database Datafile Directory (Destination Base) and Oracle
Home Directory (Destination Home). Hence, the system where you wish to install Data Mart
Database, the Target Database, should already have Oracle Database Server installed.
You are not required to create any DB Schema Users - the installer creates them.
Agile PLM Data Mart supports only 10g R2 and 9i R2 versions of Oracle Database, Enterprise Edition.
For complete information about Oracle Database and its installation procedures, please refer Oracle
DB Installation Guide. This is available for free download at Oracle Technology Network (OTN)
Web site (http://www.oracle.com/technology/documentation/agile.html).
Agile PLM Database

Agile PLM Data Mart extracts, loads and transforms data from Agile PLM Database, the Source
Database. The Agile PLM Database can be located anywhere in your enterprise, connected through
Local Area Network or Wide Area Network over TCP/IP.
The source PLM DB should already be existing. The target DB, i.e., the Data Mart DB, however,
can be created in an existing instance, or in a new instance, which can be created using our
installer.
The Agile PLM Data Mart Installer will seek the following information, which should be available with
your Agile PLM Administrator:
Database Host Name
Database Port Number
Database Name
Database SID
Sys User Password
System User Password
PLM DB Username
PLM DB Password
Agile PLM Data Mart is designed to extract data from the following Releases of Agile PLM
Agile PLM Release Schema Version
9.2.2 538
9.2.2 HF7 542
9.2.2.1 563
9.2.2.1 HF12 564
9.2.2.2 581
9.2.2.3 588
9.2.2.4 614

Chapter 3
Oracle Data Integrator

Agile PLM Data Mart operation is based on Oracle Data Integrator (ODI) tool. The Data Mart
Schema requires atleast one Master Repository and one Work Repository. These repositories are
used by ODI to operate on data during transformation.
The installer creates the Data Mart Schema on Target Database Server. The ODI should be
installed on the same system where you wish to install Agile PLM Data Mart ETL Components.
For complete information on ODI, refer its documentation, which is available for free download at
Oracle Technology Network (OTN) Web site
(http://www.oracle.com/technology/documentation/agile.html)
ODI, and its patch, can be downloaded from Oracle web site http://www.Oracle.com.
Pre-installation Checks
Several preliminary steps are required before you begin the installation process.
Verify that your computer meets the minimum hardware and software requirements. Refer
chapter on Resource and Capacity Planning in this guide.
Ensure that you have administrative privileges on your computer.
Ensure that the PLM Source DB is available.
Ensure correct Java and ODI versions are installed (as per our recommendations).
Ensure ODI_JAVA_HOME is pointing to JDK 1.5.x If not, please set it.
Ensure JAVA_HOME is pointing to JDK 1.5.x
Add JAVA_HOME to path
If your computer has a dynamic IP address, install a loopback adapter
If you are installing from a network drive, use Windows File Manager to map that drive to your
computer.
For Data Mart Database creation, make sure that SQLNET.AUTHENTICATION_SERVICES in
the Network Configuration File, sqlnet.ora, is set to (NTS).
Virus Protection is disabled.
If virus protection is enabled, components used in the installer can be falsely identified as being
infected and lockup the installation. You can turn the virus protection on after the installation is
complete.
Page - 11
Recommendations
Do not install the Agile PLM Data Mart on the same drive as that of the operating system.
Install the Agile PLM Data Mart directly under the root directory. For example,
D:\PLMDatamart.
There must be at least 20 GB of free disk space and 2 GB memory available for PLM Data Mart.
Important The complete path and folder names should NOT contain any spaces, because ODI fails
to recognize any spaces in folder names or paths. For example, the path d:\Program
Files\Data Mart will lead to failure of ODI operation.
Installing in Windows
Settings
Before you invoke Installer program, you are required to set the Environment Variables -
1. On your desktop, right click on My Computer icon and select Properties. The System Properties
panel appears.
2. Click Environment Variables in Advanced tab
3. Define User variable for Java Home.
Example : JAVA_HOME=C:\Java\jdk1.5
4. Set system variable for Path of Java Home
Example : PATH=%JAVA_HOME%\bin
5. Click OK to complete
To start the Agile PLM Data Mart installer on Windows:

1. Log in as a user with administrative previleges to install.
2. From Installer base directory in the Installer Disk, <double-click> PLMDMSetup.exe file.
3. In a few moments, the Welcome screen appears.
4. Click Next for subsequent screens, which will appear in a sequence, from Getting Started (on
page 17) stage of installation process (see Data Mart Installation Process (on page 15))
through to other stages in accordance with your selection at S-1.
Installing in Solaris
Settings
For the Oracle databases to work properly, the default semaphore settings in Solaris do not work.
These settings should be reconfigured as follows
To modify the system kernel parameters:

1. Change to the root user, and back up the /etc/system file:
$ su - [Enter]

Chapter 3
# cp /etc/system /etc/system_save [Enter]

1. Verify the following /etc/system kernel parameters. If the parameters do not exist, go to the next
step.
set shmsys:shminfo_shmmax = 4294967295
set shmsys:shminfo_shmmin = 1
set shmsys:shminfo_shmmni = 100
set shmsys:shminfo_shmseg = 10
set semsys:seminfo_semmns = 1024
set semsys:seminfo_semmni = 100
set semsys:seminfo_semmsl = 500
set semsys:seminfo_semopm = 100
set semsys:seminfo_semvmx = 32767
set noexec_user_stack = 1 (Note:On Oracle 10g only)
Important If you have been running the host computer as an Oracle database server, you have to
check with your Solaris system administrator before changing these parameters. For
these parameter settings, you can also refer to the Oracle documentation.
Note Restart the computer if you modify the /etc/system file.
Before you invoke Installer program, you are required to set the profile as follows -
(Oracle 9i) ORACLE_HOME=/u01/app/oracle/product/9.2.0SE; export
ORACLE_HOME
(Oracle 10g) ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1;
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin; export PATH
JAVA_HOME=/u01/oracle/software/jdk1.5.0_15; export JAVA_HOME
ODI_JAVA_HOME=$JAVA_HOME; export ODI_JAVA_HOME
ODI_HOME=/u01/oracle/software/odi/oracledi; export ODI_HOME
To start the Agile PLM Data Mart installer on Solaris:

2. Log on with Administrator rights
3. At the prompt, execute PLMDMSetup_Sol.bin file.
5. Click Next for subsequent screens, which will appear in a sequence in Getting Started (on page
17) stage of installation process (see Data Mart Installer) and branching off to other stages in
accordance with your selection at S-1.
Important The user should have full permissions for all the folders of Oracle DB and ODI. Else, the
Installation will fail.
Installing in Linux
Settings
Modify the system kernel parameters:
1. Change to the root user, and back up the /etc/sysctl.conf file:
$ su - [Enter]
# cp /etc/sysctl.conf /etc/sysctl.conf_save [Enter]
1. Verify the following /etc/sysctl.conf kernel parameters. If the parameters do not exist, go to the
Page - 13
next step.
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Important If you have been running the host computer as an Oracle database server, you have to
check with your Linux system administrator before changing these parameters. For these
parameter settings, you can also refer to the Oracle documentation.
Note Restart the computer if you modify the /etc/sysctl.conf file. Alternatively, you can use the
sysctl command to modify the semaphore parameters immediately. However, using systcl
command does not make the changes permanent. Permanent changes are required in
/etc/sysctl.conf file.
Before you invoke Installer program, you are required to set the profile as follows -
(Oracle 9i) ORACLE_HOME=/u01/app/oracle/product/9.2.0SE; export
ORACLE_HOME
(Oracle 10g) ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1;
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin; export PATH
JAVA_HOME=/u01/oracle/software/jdk1.5.0_15; export JAVA_HOME
ODI_JAVA_HOME=$JAVA_HOME; export ODI_JAVA_HOME
ODI_HOME=/u01/oracle/software/odi/oracledi; export ODI_HOME
To start the Agile PLM Data Mart installer on Linux:

2. Log on with Administrator rights.
3. At the prompt, execute PLMDMSetup_Lin.bin file.
5. Click Next for subsequent screens, which will appear in a sequence in Getting Started (on page
17) stage of installation process (see Data Mart Installer) and branching off to other stages in
accordance with your selection at S-1.
Important The user should have full permissions for all the folders of Oracle DB and ODI. Else, the
Installation will fail.
Data Mart Installer

The Agile PLM Data Mart Installer is a program that helps in installation of PLM Data Mart DB and
its components. Based on Apache ANT installation framework, it seeks requisite inputs about
Source Database, Destination Database and ODI from the user, configures the corresponding
properties, and installs all the components required for the Data Mart to perform its functions.

Chapter 3
The Installer deploys the following:-

Apache Ant
Data Mart components
Data Mart Database
The Data Mart Installer follows an installation process, shown and described in Data Mart
Installation Process. It is recommended to keep the Data Mart Installation Process diagram handy
while carrying out the installation.
Data Mart Installation Process

The Data Mart installation process has four prime stages:
1. Getting Started
2. Installing PLM Data Mart Database
3. Installing PLM Data Mart ETL components
4. Installing both, PLM Data Mart Database and ETL components
Each step of the installation process has been numbered, with a prefix "S-" for ease of
understanding. These numbers, for example S-2c, correspond to each of Installer screens appearing
in a sequence of installation steps for an installation stage. The characteristics of user inputs (fields
or selections) required in each step of installer screen are detailed in the tables under each stage
heading. The numbering of these tables corresponds to each screen that appears in a particular
sequence in accordance with your selections.
Note The step numbering does not appear on installer screens. It is only for ease of
referencing.
Page - 15

Chapter 3
Getting Started
The following tables list out the input fields and/or selection options that appears on each screen of
installer program, and the requisite input characteristics/attributes.
Note The Installer should be launched in system where the database is installed. Remote DB
connection is not supported.
S-0: Enter User Information

Input Fields / Options Characteristics
User Name Enter your name and your company name.
Company Name Should be 5 to 20 characters long.
Can be Alpha-Numeric, with special characters.
Is not case sensitive.
You cannot proceed to next steps without entering this information.
S-1: Select Installation

PLM Data Mart DB Creation Select this option to either create new PLM Datamart Database or to create PLM
Data Mart DB schemas using an existing instance.
PLM Data Mart ETL Select this option to install the ETL components that are used by ODI for
Extraction, Transformation & Loading of data from Source Database into the
destination Database.
Before you chose this option, you should have already installed PLM Data Mart
Schema. Else, the installer will abort.
PLM Data Mart Schema & ETL Select this option to install both, the Data Mart Database and ETL components in
the same system.
S-2: Specify PLM Data Mart Database Destination Location

Destination Location PLM Datamart should not be installed in any directory that has any space in it
(for example - d:\Program Files). Folder name should be a single word. Any
space between a multi-word folder name is not recognized by ODI.
Can be located in any system anywhere in the Local Area Network or Wide Area
Network (remote location).
The folder need not be already existing.
Note If you selected the Installation Option PLM Data Mart ETL on S-1, proceed to S-4b in Installing
Data Mart ETL Components (on page 21)
Page - 17
S-3: Select Database Version

Oracle 10g Database Server This is the destination Database Server for Data Mart.
Oracle 9i R2 Database Server Should be already installed in the system where the Data Mart is being installed.
Note If you selected the Installation PLM Data Mart Schema Creation on S-1, proceed to S-4a in
Installing Data Mart Schema (on page 18).
Note If you selected the Installation Option PLM Data Mart Schema & ETL on S-1, proceed to S-4c in
Installing Data Mart Schema and ETL Components (on page 24).
Installing Data Mart Schema

S-4a: Select Data Mart Schema
Create New Database Select this option to create a fresh Database, in which a new Instance will be
created.
Follow the steps S-5a through to S-00.
Use Existing Database and Tablespace Select this option to create a new Instance on an existing Database and
Schema.
Follow the steps S-5a through to S-8a, and then skip to S-00.
Generate SQL Scripts Select this option if you do not wish to install the Data Mart DB using the
Installer. However, you are required to enter the requisite inputs and/or make
selections in the Installer only.
At the end of your inputs, the Installer generates SQL files, which can be
executed to carry out the installation.
Follow steps S-5a through to S-00, and proceed to Executing
Installation Scripts (on page 29).
S-5a: Enter Oracle Home and Default Tablespace Locations

Oracle Home Directory Choose or enter the directory locations.
Database Datafile Directory Folder name should be a single word. Any space between a multi-word folder
name is not recognized by ODI.

Chapter 3
S-6a: Enter Data Mart DB and Schema Details

Data Mart Database Details
Host Name System name of target Database Server where the Data Mart Database will
be installed.
Example: Agile-DM9
Consult your Network and/or DB administrator for requisite information.
Database Name (SID) Define a name for the Database that will be created in Data Mart.
If you selected "Use existing Database and tablespaces", then you will need
to select existing Instance name.
Default Value: PLMDM
System User Password Enter a valid System User password.
Consult your DB administrator for requisite information.
Default Value: MANAGER
Sys User Password Enter a valid System User password.
Default Value: ORACLE
Database Port Number Change this value, if different (consult you DB administrator for correct value)
To change the value, first change the Port Number in Database server
listener.ora file, then stop and start Database server listener service.
Database server listener Port no: and user entered PLM DataMart Port no:
should be in sync.
Only Numeric value is permitted.
Default Value: 1521
Data Mart Schema Details
Data Mart User Name Define a name for the default User to be created who will be granted the
admin rights to access and control the Data Mart.
Should be 1 to 10 characters long.
Can be Alpha-Numeric.
Cannot contain space and special characters, other than "_".
Default Value: ODM
Data Mart User Password Define a password for this User.
Cannot contain space and special characters, other than "_" and "@"
Default Value: ODM
Page - 19
S-7a: Enter ODI Repository Schema Details

Master Repository Database User Name Define a name for the default User to be created who will be granted the
admin rights to access and control the Databases in the Master Repository of
ODI
Cannot contain space and special characters.
Default Value: ODIMASTER
Master Repository Database Password Define a password for this User
Cannot contain space and special characters, other than "_" and "@".
Default Value: ODIMASTER
Work Repository Database User Name Define a name for the default User to be created who will be granted the
admin rights to access and control the Databases in the Work Repository of
ODI
Default Value: ODIWORK
Work Repository Database Password Define a password for this User
Default Value: ODIWORK
S-8a: Enter Data Mart Tablespace Name Configuration

PLM Data Mart Repository Tablespace Define a name for the Repository Tablespaces to be created
Default Value: AGILEODI
PLM Data Mart Data Tablespace Define a name for the Data Tablespaces to be created
Default Value: AGILEODM

Chapter 3

PLM Data Mart Indexes Tablespace Define a name for the Index Tablespaces to be created
Default Value: AGILEODM_INDX
Note If you selected the option of Use Existing Database & Tablespace in S-4a: Select Data Mart
Schema, skip to S-00.
S-9a: Enter Datafile Configuration for System, Temp Tablespaces & Redo Log Files
System Tablespace Datafile Choose or enter the paths
Temp Tablespace Datafile The folder names should not contain any spaces, as ODI does not resolve
Redo Log file spaces and hence, fails to operate
S-10a: Enter Datafile Configuration for Data Mart, Control & Undo Log Files
Data Mart and ODI Tablespace Choose or enter the paths
Control Files The folder names should not contain any spaces, as ODI does not resolve
Undo Log files spaces and hence, fails to operate
S-00: Summary of inputs

1. Check the summary of all the inputs you provided. If you wish to make any changes, click on
Previous button or click Install button for the Installer to carry out the installation.
2. The installation will take several minutes. It depends on the number of activities
3. Upon completion of installation, the confirmation screen appears. Click OK to close.
4. If you selected Generate SQL Scripts in S-4a: Select Data Mart Schema, proceed to Executing
Installation Scripts (on page 29) followed by Adding DB Services in Listener on page 29. Else
proceed directly to Adding DB Services in Listener on page 29..
Page - 21
Installing Data Mart ETL Components

S-4b: Select ODI Home Directory
Select (or enter) the complete valid path of ODI Home directory.
There should be no spaces in the folder name(s).
S-5b: Enter Source DB Details

Database Host Name This is the System Name of Agile PLM Database Server
If the PLM DB server is not installed in your local system, delete it, and enter
the system name of PLM DB Server that may be located anywhere on your
Local Area Network (LAN) or Wide Area Network (remote location)
connected over TCP/IP.
Default Value: <your local system name> --- it automatically picks up the
name of system.
Database Port Number Change this value, if different. Consult you Agile PLM DB administrator for
correct value.
Only Numeric value is permitted
Default Value: 1521
Database Name (SID) The name PLM Database, from where the Data Mart tool will extract the
data
Should be already configured
Not case sensitive
PLM DB User Name A valid user name in source DB
Should be already created by Agile PLM DB administrator
PLM DB Password A valid password of PLM DB user
S-6b: Enter Data Mart DB and Schema Details

Target Database Details
Host Name System name of target Database Server where the Data Mart will be
installed
Is not case sensitive
Can be located in any system anywhere in your Local Area Network or Wide
Area Network (remote location) connected over TCP/IP
Example: Agile-DM9
Consult your Network and/or DB administrator for requisite information
Database Port Number Change this value, if different. Consult you DB administrator for correct
value.
Default Value: 1521
Database Name (SID) Define a name for the Database that will be created in Data Mart

Chapter 3

admin rights to access and control the Data Mart
Should be 1 to 10 characters long
Can be Alpha-Numeric
Can contain special characters; spaces not permitted
Data Mart Password Define a password for this User
Can contain special characters; spaces not permitted
S-7b: Enter ODI Schema and Work Repository Details

Oracle Data Integrator Schema Details
admin rights to access and control the Databases in the Master Repository
of ODI
Can be a Alpha-Numeric
Default: ODIMASTER
Default: ODIMASTER
ODI
Default: ODIWORK
Default: ODIWORK
Oracle Data Integrator Wrok Repository Details

ODI Work Repository Name Define a name for the Work Repository that will be created in ODI
Cannot be Alpha-Numeric
Default: WORKREP
Page - 23

ODI Work Repository Password Define a password for this User
Default: WORKREP
S-8b: Enter Load Parameters

Enable PLM Modules for Extraction Select the checkboxes against the PLM modules - PC, PPM, PQM, from
which you wish to extract the data.
You must select atleast one module.
By default, all the modules are selected.
Email ID for ETL Notifications If you wish to receive automatic notifications on ETL runs, specify email IDs
of all receipients.
Seperate multiple IDs with commas.
Mail Server Name Specify the name of Email Server that is installed on the same system
where ODI is installed.

4. Proceed to Validating the Data Mart Installation (on page 29).
Installing Data Mart Schema and ETL Components

S-4c: Select Data Mart Schema
Create New Database Select this option to create a fresh Database, in which a new Instance will be
created.
Follow the steps S-5c through to S-00.
Use Existing Database and Tablespace Select this option to create a new Instance on an existing Database and
Schema.
Follow the steps S-5c through to S-10c, and then skip to S-00.
S-5c: Enter Oracle Home and Default Tablespace Locations

Oracle Home Directory

Chapter 3

Database Datafile Directory Choose or enter the directory locations.
Folder name should be a single word. Any space between a multi-word folder
name is not recognized by ODI.
S-6c: Select ODI Home Directory

Select (or enter) the complete valid path of ODI Home directory.
There should be no spaces in the folder name(s).
S-7c: Enter Source DB Details

Database Host Name This is the System Name of Agile PLM Database Server
If the PLM DB server is not installed in your local system, delete it, and enter
the system name of PLM DB Server that may be located anywhere on your
Local Area Network (LAN) or Wide Area Network (remote location)
connected over TCP/IP.
Default Value: <your local system name> --- it automatically picks up the
name of system.
Database Port Number Change this value, if different. Consult you Agile PLM DB administrator for
correct value.
Default Value: 1521
Database Name (SID) The name PLM Database, from where the Data Mart tool will extract the
data
Should be already configured
Not case sensitive
PLM DB User Name A valid user name in source DB
Should be already created by Agile PLM DB administrator
PLM DB Password A valid password of PLM DB user
S-8c: Enter Data Mart Database and Schema Details

Data Mart Database Details
Host Name System name of target Database Server where the Data Mart Database will
be installed.
Example: Agile-DM9
Consult your Network and/or DB administrator for requisite information.
Database Name (SID) Define a name for the Database that will be created in Data Mart.
If you selected "Use existing Database and tablespaces", then you will need
to select existing Instance name.
Default Value: PLMDM
Page - 25

System User Password Enter a valid System User password.
Default Value: MANAGER
Sys User Password Enter a valid System User password.
Default Value: ORACLE
Database Port Number Change this value, if different (consult you DB administrator for correct value)
To change the value, first change the Port Number in Database server
listener.ora file, then stop and start Database server listener service.
Database server listener Port no: and user entered PLM DataMart Port no:
should be in sync.
Only Numeric value is permitted.
Default Value: 1521
admin rights to access and control the Data Mart.
Default Value: ODM
Data Mart User Password Define a password for this User.
Default Value: ODM
S-9c: Enter ODI Schema and Work Repository Details

Oracle Data Integrator Schema Details
admin rights to access and control the Databases in the Master Repository
of ODI
Default: ODIMASTER
Default: ODIMASTER

Chapter 3

ODI
Default: ODIWORK
Default: ODIWORK
Oracle Data Integrator Wrok Repository Details

ODI Work Repository Name Define a name for the Work Repository that will be created in ODI
Cannot be Alpha-Numeric
Default: WORKREP
ODI Work Repository Password Define a password for this User
Default: WORKREP
S-10c: Enter Data Mart Tablespace Name Configuration

PLM Data Mart Repository Tablespace Define a name for the Repository Tablespaces to be created
Default Value: AGILEODI
PLM Data Mart Data Tablespace Define a name for the Data Tablespaces to be created
Default Value: AGILEODM
PLM Data Mart Indexes Tablespace Define a name for the Index Tablespaces to be created
Default Value: AGILEODM_INDX
Note If you selected the option of Use Existing Database & Tablespace in S-4c: Select Data Mart
Schema, skip to S-13c.
Page - 27
S-11c: Enter Datafile Configuration for Data Mart & ODI , Control & Undo Log Files
Data Mart and ODI Tablespace Choose or enter the paths
Control Files The folder names should not contain any spaces, as ODI does not resolve
Undo Log files spaces and hence, fails to operate
S-12c: Enter Datafile Configuration for System, Temp Tablespaces & Redo Log Files
System Tablespace Datafile Choose or enter the paths
Temp Tablespace Datafile The folder names should not contain any spaces, as ODI does not resolve
Redo Log file spaces and hence, fails to operate
S-13c: Enter Load Parameters

Enable PLM Modules for Extraction Select the checkboxes against the PLM modules - PC, PPM, PQM, from
which you wish to extract the data.
You must select atleast one module.
By default, all the modules are selected.
Email ID for ETL Notifications If you wish to receive automatic notifications on failed ETL tasks, specify the
email IDs of all receipients.
Seperate multiple IDs with commas.
Mail Server Name Specify the name of Email Server that is installed on the same system
where ODI is installed.

4. Proceed to Adding DB Services in Listener on page 29.
Important The DB Listener should be running.

Chapter 3
Adding DB Services in Listener

Note This is only applicable only if you are creating a new Data Mart DB Instance.
Upon completion of the Installation process, you are required to add DB services in Listener. Use
the Oracle Net Manager to specify
Global Database Name [eg, PLMDM]
Oracle Home Directory [eg, D:\ORACLE\product\10.2.0\db_1]
SID [eg, PLMDM]
Once finished, Stop and restart the Listener.
Executing Installation Scripts

Note The database needs to be already created before executing these steps.
If you selected the option Generate SQL Scripts in S-4: Select Data Mart Schema stage of Installation,
the Installer generates a set of SQL files and stores them in Schema folder under Data Mart Home
directory. You need to execute the them in SQLplus in the order given below. The purpose of this is
to manually create PLM Data Mart DB schemas by executing the given scripts.
Note In SQLplus, connect to the Data Mart DB using the Data Mart User Name and Password.
Before doing so, set the following:

In Windows set oracle_sid = <sid_name>
In Solaris/Linux export ORACLE_SID = <sid_name>
1. TableSpaceCreation.sql - to create Tablespace. If the tablespace is already existing, skip this step.
2. UserCreation.sql - to create Users.
3. Run PLSQLlogger.bat for debug logs.
4. ODM.sql to install Operational Data Mart (ODM).
5. (Optionally) You can run ODM_DDL_COMMENTS.sql script to add comments for the tables.
The given sequence of execution is very important, else it will lead to failed installation.
Validating the Data Mart Installation

Upon completion of installation, you may verify if it was successful or not. Check for the following:
Execute the SQL @ Work Repository schema
select count(scen_no) from SNP_SCEN;
Check the following log files, located in the logs folder under Data Mart Home directory, for any
errors:
DatamartETLInstall.log file for information pertaining to installation of ETL components
Page - 29
DatamartDBInstall.log file for information on DB creation

DatamartInstall.log file
The next topics provide additional checks for validating and verifying the installation.
Database Schemas
Check to ensure that the following Database schemas are creating successfully:
Data Mart DB
ODI Master Repository
ODI Work Repository
Besides making sure the Directory structure and schemas are properly created, following validation
checks should be done to make sure Data Mart is installed properly:
Verifying Datamart Schema

On the Data Mart DB schema, make sure
1. ODM_LISTDIM_CTL table is populated with seed data.
2. Parameter table ic created and one row is populated in the table
3. Following PL/SQL packages (4), procedures (8) and functions (5) are created:
4. Make sure TLOG table, VLOG view and TLOG synonym are created.
If any any of the above verifications fail, look for any Oracle Database errors in the DatamartInstall.log
file. DatamartInstall.log file is located in the <PLM Datamart Home> \LOGS folder. It includes
information log details for the following installation steps:
Creation of Users (Datamart Schema User, ODI Master Repository user, ODI Work Repository

Chapter 3
User)
PL/SQL logger (Analyitcs Log user, creation of tables TLOG)
Datamart DB creation
ODI Repository creation
ODI Physical connection configuration, Logical connection configuration, importing Model
folder, Project folder
Install Directory Structure

After installation, the PLM Data Mart install directory structure, with the outlined content, should look
like:
Ant Ant installation required for Data Mart installation
Bin Contains configuration tools and miscellaneous utilities
Common Contains PL/SQL log files for ETL
Config Contains all PLM Data Mart configurations including ANT install configuration file
Install Installation components
JDK Java run-time used by the ODI
Lib Contains all dependent libraries specially used by Data Mart
Logs Install logs
Schema Contains SQL scripts for creating instance
Uninstaller Contains executable file to un-install the software
Verifying ODI Repositories

Log into ODI Designer to verify the following:
Page - 31
1. Model tab has the following models:
2. Projects tab has AGILE PLM ANALYTICS project and the project has following packages under
Agile Datamart Folder:
1. Open ODI Topology Manager and make sure the Source PLM Database SID and schema
user details are populated:
a. Double click on Physical Architecture tab > Technologies > Oracle > SRC_CONN_PHYSICAL
and verify Instance and Schema name details in the Definition tab.
b. Click on JDBC tab and verify that JDBC URL is pointing proper SID on Source PLM
Database machine.

Chapter 4
Executing ETL
Setting up ODI Users........................................................................................................................................... 33

Executing ETL from ODI...................................................................................................................................... 35
Executing ETL from Command Prompt ............................................................................................................... 35
You can perform Extraction, Transformation and Loading (ETL) of PLM Data from source Database
to the Data Mart Database by executing the ETL tasks. These tasks are installed in the Data Mart
Home directory and can be executed inside ODI or from command prompt.
Before you opt for either of the two ways, you are required to setup ODI users for the first time.
Setting up ODI Users

Agile PLM Data Mart is based on ODI, the Oracle Data Integrator. To execute the ETL tasks and to
operate on data, you are required to use ODI.
Note ODI User setup requires the information entered during the Data Mart installation, such
as, user names, passwords. Keep this access information handy.
To establish connection to Agile PLM Data Mart, you must first configure a User, as follows
1. In Windows, execute the program Operator from Start > Programs > OracleODI
The Oracle Data Integrator Login screen appears
2. Click the New Icon to create a new Work Repository Connection

The Work Repository Connection screen appears
3. Enter Login Name, User name and password for ODI connection.
These can be of your choice. The default User Name is SUPERVISOR and the password is
SUNOPSIS (case sensitive).
Note To configure additional user or change password for SUPERVISOR, please
refer to ODI documentation.
4. Enter the User name and password for Master Repository DB connection that you specified
during installation.
5. Select Oracle JDBC Driver from Driver List
The Driver Name field gets automatically filled with oracle.jdbc.driver.OracleDriver.
6. Enter the following URL
jdbc:oracle:thin:@<host>:<port>:<sid>
where
<host> Host name of Data Mart DB Server
<port> Port Number of Data Mart DB Server
<sid> SID or the Instance name of Data Mart DB
7. Enter the Repository Name for Work Repository
8. Click Test button to verify if the connection works. Click OK . You will be prompted to enter the
Work Repository Password.
9. Enter the work repository password that was assigned during the Data Mart installation and
Click OK.

Chapter 4
10. Click OK to finish.
Note For complete information on installation and usage of ODI, please refer its documentation
available for free download at
http://www.oracle.com/technology/documentation/index.html
http://www.oracle.com/technology/documentation/index.html
Executing ETL from ODI

Note If you want to see the status of all the tasks that are under execution, you are required to
increase the Operator Display Limit to 1000 (the default value is 100). To do so, in ODI
Operator, go to File Menu > User parameter >Set operator Display limit and change the
value.
To execute ETL from ODI, follow these steps:

1. Launch ODI Operator and select login, which is Repository Name
2. The username and password will populate automatically. Click OK.
3. Go to Scenarios tab
You will see all the components listed in the Left Frame.
4. Right-click on DATAMART_LOAD Version 001 componet and select Execute. A Variables window
will pop up.
5. Click OK. A Sessions Started window appears.
6. Click OK. The ETL process begins.
Executing ETL from Command Prompt

To execute ETL from command prompt, follow these steps:
1. In Windows, open a command window
2. Change directory to go to the bin folder in your Data Mart Home Directory, eg,
d:\PLMdatamart\bin
3. Enter the following command line
startdm DATAMART_LOAD 001 PLMDM
where
startdm is the batchfile that executes ETL tasks
DATAMART_LOAD is the ETL task
001 is the version number of ETL task
PLMDM is the ETL context
The ETL process starts.
Page - 35
To view the status of ETL process:

1. Launch ODI Operator and select login, which is Repository Name
2. The username and password will populate automatically. Click OK.
3. You can also go to Sessions List tab and select All Executions in the left frame, which will show all
the running tasks.
4. Also, you can go to Hierarchical Sessions tab and select Status or All Executions in the left frame to
check overall top level progress.

Chapter 5
Configuring the Agile PLM Data Mart

List Dimension Configurator ................................................................................................................................ 37

Changing List Names in Dimension Table........................................................................................................... 39
Validating Dimension Tables ............................................................................................................................... 39
Enabling and disabling the Modules .................................................................................................................... 39
Scheduling ETL Instances ................................................................................................................................... 40
Changing the Data Mart Passwords .................................................................................................................... 41
List Dimension Configurator

The PLM Data Mart Configurator allows customer to specify their own dimension table names in the
PLM Data Mart schema for any OOTB list or customer configured lists within Agile PLM. The
Dimension Table Names for Lists are stored in a ODM_LISTDIM_CTL table in PLM Data Mart schema.
This tool facilitates the users to create their own dimension table name by adding corresponding
entity to this table.
When you execute ETL, the Data Mart application creates Tables for each List Dimension. These
tables serve as master data source of list items.
These List Dimension Tables are named according to their List Names. Should you want, you may
rename them in accordance with your own naming conventions. For this objective, Agile PLM Data
Mart Installer is bundled with a configuration tool - List Dimension Configurator.
To use the List Dimension Configurator, follow these steps:

1. Execute the Configurator.bat file, located in bin folder of Data Mart Home directory, e.g.,
d:\PLMDatamart\bin.

The configurator console appears.
It displays all the List IDs, List Names and List Dimension Names, sorted on List ID. You can sort
the displayed data on List Name or List Dimension Name too.
2. Identify the the desired List Name whose List Dimension Name you wish to modify/rename.
3. Click in corresponding cell under List Dimension Name column. The cell attains Edit mode.
4. If the cell is empty, enter the desired name. Else, select the existing name, delete and then enter a
new name.
Note The List IDs and the correponding List Names are fixed, i.e., you cannot modify
them.
5. Click Save button. The new values are stored in the List Dimension Control table -
ODM_LISTDIM_CTL in the Data Mart.
Note The Save button remains inactive, that is, not-clickable, until you edit any List
Dimension Name.
Note Undo, or Ctrl-Z, does not work.
6. Continue editing the List Dimension Names or click Cancel button to exit.
Note Any updates made to List Dimension Names using Configurator shall only be processed
during next ETL run.
The ODM_LISTDIM_CTL table stores the latest List Dimension Names. Before commiting them, it
copies the existing data into its backup table, ODM_LISTDIM_CTL_BK, along with the timestamps
of each modification. Thus, it maintains the entire history of each and every earlier List Dimension
Name, facilitating you to switch back, should you want to.

Chapter 5
Changing List Names in Dimension Table

When a new List is added in Agile PLM, the Data Mart ETL will dynamically add appropriate
dimension table name to ODM_LISTDIM_CTL table and create it during next ETL run. You can change
the name of this dimension table once an ETL run is completed, as outlined in List Dimension
Configurator on page 37.
To create a new List in Agile PLM and also change it's dimension table in Target Data Mart:
1. Create a new List in Agile PLM Administrator
2. Execute Data Mart ETL using appropriate steps outlined in Executing ETL on page 33.
3. Run Configurator and identify the row for newly created List in Configurator.
4. (Optional) Delete dimension table that was already created during previous ETL run. This step
allows schema from having orphan dimension tables.
5. Change Dimension Table name for the newly created List using steps in List Dimension
Configurator on page 37.
6. Once changes are completed and saved, run Data Mart ETL using appropriate steps outlined
in Executing ETL on page 33.
Validating Dimension Tables

When you make any changes to Dimension table names using Configurator and you have not
deleted previously created dimension tables from Data Mart schema, any one with Oracle Database
Administration skills can take the following steps to identify and remove unused dimension tables
from the Data Mart schema.
1. Execute the following SQL query to identify all Dimension tables
SELECT LIST_DIM from ODM_LISTDIM_CTL_BKP
WHERE
LIST_DIM not in
(SELECT LIST_DIM from ODM_LISTDIM_CTL)
1. Make sure the table names returned by this query are not used by any downstream ETL
application
2. Drop every table identified as not used by Data Mart or the downstream ETL application from
the Data Mart schema
Enabling and disabling the Modules

You can enable or disable the Agile PLM modules, viz, PC, PPM, PQM to selectively extract their
data from PLM DB server. The properties of Enabled/Disabled modules is stored in
DataMartConfig.Properties file in Config folder under Data Mart Home Directory, e.g.,
c:\PLMDataMart\Config.
To Enable/Disable the modules:

1. Open the DataMartConfig.Properties file in a Text Editor.
2. Go to the section ###PLM Module###. The three PLM modules are listed here.
PQM=Y
Page - 39
PPM=Y
PC=Y
3. Change the property values to Y or N.
The properties value Y enables the module, and N disables.
4. In Windows, execute LoadParameters.bat located in bin folder under Data Mart Home Directory,
e.g., c:\PLMDataMart\bin.
5. In Solaris and Linux, execute LoadParameters.sh located in bin folder under Data Mart Home
Directory, e.g., $PLMDataMart\bin.
Alternately
1. Change and commit the values (Y or N) in the Parameter Table.
Note The module property cannot be empty. It should be either Y or N.
Scheduling ETL Instances

The ETL Instances are executed in ODI. ODI nomenclature for an Instance is Scenario. You can
schedule them for automatic execution at any time of your choice.
For complete information on scheduling and related actions, refer the following sections in the
Oracle Data Integrator User’s Guide 10g Release 3 (10.1.3). This guide is available for free
download at http://www.oracle.com/technology/documentation/index.html
http://www.oracle.com/technology/documentation/index.html.
Scheduling PLM Data Mart ETL with ODI scheduler

Creating a physical agent: Page 123
Creating a logical agent: Page 123
Launching a Scheduler Agent: Page 131
Displaying Scheduling Information - Page 133
The following top-level tasks needs to be performed to configure the Scheduler:

1. Insert a new AGENT in Physical Architecture tab in ODI Topology Manager
2. Associate the AGENT to PLM Data Mart Context in Logical Architecture tab ODI Topology
Manager
3. Update the Work Repository Details in odiparams.bat file (located in <odi installation folder>/bin
folder)
4. Run agentscheduler.bat file (located in <odi installation folder>/bin folder)
5. Setup schedule for the scenario in scheduler tab in ODI Operator
Note In ODI Operator, setup the DATAMART_LOAD Version 001 Scenario in the scheduler tab
based on your scheduling preference.
If you want use an external scheduler, refer Scheduling a Scenario with an external scheduler in the
Oracle Data Integrator User’s Guide 10g Release 3 (10.1.3) on page 99.

Chapter 5
Changing the Data Mart Passwords

Password changes are carried out directly in the DB. However, the changed passwords should also
be stored, in encrypted form, in the DataMartConfig.properties file. This is necessary as
the LoadParameters.bat use the access information (username, password) from properties file
to enable or disable the modules.
the Configurator.bat file uses the access information (username, password) from properties to
configure the new List Dimension Names.
Hence, any changes in any passwords in the DB should also be reflected in this properties file. The
encryption of password is required for security reasons.
Changing a password entails two staged action:

1. Generating an encrypted string for a character password using DMEncoder.bat in Windows, or
DMEncoder.sh in Solaris/Linux.
2. Replacing the existing encrypted password string in DataMartConfig.properties file with the new
encrypted string
Note The LoadParameters.bat file will use this properties file to change the PLM modules.
Note The configurator.bat will also use this prop file. hence pwds
To change a password :
1. Go to bin folder in Data Mart Home directory
2. Execute the batch file along with a desired password, as follows
dmencoder <pwd>
where <pwd> is an alpha-numeric character string password.
An encrypted string appears.
1. Select and copy the encrypted string
2. Open the DataMartConfig.properties file, located in config folder under Data Mart Home directory,
in a text editor.
3. Delete the password that you wish to change.
4. Paste the new encrypted string and save & close the file.
Note To reflect the changed password(s) in ODI, refer ODI Administration Guide.
Page - 41
Chapter 6
Troubleshooting
Installation Issues ................................................................................................................................................ 43

ETL Runtime Issues ............................................................................................................................................ 44
PL/SQL Loggings................................................................................................................................................. 44
General Issues..................................................................................................................................................... 45
Detecting Errors in ODI coming from Oracle ....................................................................................................... 45
Installation Issues
Installer failed to create Data Mart schema, ODI Work repository and/or ODI Master repository
schemas
Look for errors in DatamartInstall.log, located in the logs folder of the PLM Data Mart install home
directory, to root cause the issue
Possible root causes could be:
Database version specified is different from the one installed in the system. For example,
Oracle 10g option is selected during installer while the machine has Oracle 9i.
Path specified for Oracle Target DB Tablespaces could be invalid.
Oracle Database path specified is incorrect.
Database Instance exists, but the System user does not have proper privileges required to
create and grant appropriate roles to schema users.
Installer failed to create ODI Work repository and ODI Master repository
Look for errors under ODIRepCreation: tag in DatamartInstall.log to root cause following
Work Repository and Master Repository schemas are not created for possible root causes
outlined in #1.
Tablespace specified for Work & Master repository are invalid.
JAVA_HOME and JAVA_ODI_HOME environment variables are incorrect.
Specified ODI directory is incorrect or ODI is not installed at specified path.
ODI Project “AGILE PLM ANALYTICS” does not have any packages
Besides the root causes outlined in #2, look for errors under ODI-PHY-Creation section in
DatamartInstall.log for other issues:
OdiImportObject failed to execute for incorrect JRE specified
JDK version specified is either less than 1.5.x or 1.6 or above. Data Mart 3.0 release works
with JDK 1.5.0.x version.
Specified Work Reposity Name is already used in existing ODI
ODI already has projects that have conflicting Work and Master Repository IDs. PLM Data

Mart uses following repository IDs.

Work Repository ID = 102
Master Repository ID = 103
ODI Operator did not list any scenarios in Scenario tab, including DATAMART_LOAD_scenario
Root cause same as those outlined for #3.
ETL Runtime Issues

If any ETL task fails during run-time the best option is to check the Execution tab of the ETL task
in ODI Operator.
1. Login to ODI Operator
2. Select Sessions List tab
3. Expand All Executions in the left pane
4. Select the task which is failing by double clicking on it
5. Select Execution tab to view error details. Optionally, you can also export entire log file as
an XML file thru Operator to check for multiple errors.
6. Refer to section on Detecting errors in ODI coming from Oracle. (see at the end)
7. If the scenario name starts with ODI_PRO, look for PL/SQL errors logged in VLOG table.
See PL/SQL Logging section for more details on how to enable “debug mode” for detailed
PL/SQL traces. Debug Mode for PL/SQL should be enabled if you need to root cause the
issue further.
PL/SQL Loggings
The log details are stored in following table/view in the Data Mart schema
TLOG (table) This table contains information like timestamp, ID etc. This table will be appending, if the
the mode is ERRORMODE. This table will be purged, if the mode is DEBUGMODE
VLOG (view) This is a view created for TLOG table and contains only the ERROR messages. The
difference between ERROR_MODE and DEBUG_MODE is explained below
ERROR MODE This is a default mode and value for this is ‘0’ in DEBUG_MODE
column in PARAMETER Table in PLM Data Mart database
schema. This captures error message.
DEBUG MODE This mode contains the value as ‘1’ in DEBUG_MODE column in
PARAMETER table PLM Data Mart database schema. This
captures step by step information inside a PL/SQL Procedure. You
will need to update the DEBUG_MODE of the parameter table to
“1” to run in DEBUG mode.

Chapter 6
General Issues
DB Errors
1. Connectivity Errors
a. PLM Source DB is available and accessible from PLM Data Mart machine
b. Verify PLM Source DB schema details
c. Target (PLM Data Mart) DB is available
d. Verify Target DB schema details
2. Data Issues such as column width
a. Check the column is both Source and Target schema (refer to Schema documentation for
table/column details)
3. Disk space
a. Check the Target DB machine to ensure enough space is available for ETL to execute and
add data
4. Database Sessions to execute ETL
a. Check the DB for enough sessions (>500) with which the ODI will run smoothly. For
checking DB session and process parameters
Login as sys/<PWD> as sysdba in command prompt using sqlplus.
SHOW PARAMETER SESSIONS
SHOW PARAMETER PROCESSES
Alter system set processes=1000 scope=spfile; OR
Alter system set processes=1000 scope=both;
After altering the Database restart the instance.
1. Linux/Unix Specific only
a. If customer gets a ‘cannot execute’ message, need to do ‘chmod u+x PLMDMSetup*.bin’.
b. Need to make sure the TNS Listener is running. Can check by ‘ps –ef | grep tns’. If
nothing shows, then it is not running.
c. If the TNS Listener is running, need to check the status. Can do so by ‘lsnrctl status’.
Detecting Errors in ODI coming from Oracle

Errors appear often in Oracle Data Integrator in the following way:
java.sql.SQLException: ORA-01017: invalid username/password; logon denied
at ...
at ...
...
The java.sql.SQLException code simply indicates that a query was made to the database through the
JDBC driver, which has returned an error. This error is frequently a database or driver error, and
must be interpreted in this direction.
Only the part of text in bold must first be taken in account. It must be searched in the Oracle
Page - 45
documentation. If its contains an error code specific to Oracle, like here (in red), the error can be
immediately identified.
If such an error is identified in the execution log, it is necessary to analyze the SQL code send to
the database to find the source of the error. The code is displayed in the description tab of the
erroneous task.
The most common errors with an Oracle server are detailed below, with their principal causes.
Connection Errors
1. UnknownDriverException
The JDBC driver is incorrect. Check the name of the driver.
2. I/O Exception: Connection
refused(DESCRIPTION=(TMP=)(VSNNUM=135290880)(ERR=12505)(ERROR_STACK=(ERR
OR=(CODE=12505)(EMFI=4))))
The instance name in the JDBC URL is invalid. Check ODI Topology Manager to make sure
JDBC URL is proper.
3. I/O Exception: The Network Adapter could not establish the connection
The IP address, machine name of Oracle listener port is incorrect in the JDBC URL.
4. ORA-01017: invalid username/password; logon denied
The user and/or password specified in the data server definition is invalid. This error may also
appear for certain Oracle Data Integrator commands, such as SqlUnload.
5. Protocol violation
This error indicates an incompatibility between the Oracle JDBC driver and the database you
connect to. If it occurs at connection time, or at the first operation launched on the Oracle
database, then install the version of the Oracle JDBC driver provided with your database
installation.
6. ORA-00600 internal error code
Internal error of the Oracle database. May be caused by a driver incompatibility.
7. ORA-12154 TNS:could not resolve service name
TNS alias resolution. This problem may occur when using the OCI driver, or a KM using
DBLinks. Check the configuration of the TNS aliases on the machines.
8. ORA-02019 connection description for remote database not found
You use a KM using non existing DBLinks. Check the KM options and pre-requisites.
Errors in Interfaces
1. ORA-00900 invalid SQL statement
ORA-00923 FROM Keyword not found where expected.
The code generated by the interface, or typed in a procedure is invalid for Oracle. This is
usually related to an input error in the mapping, filter of join. The typical case is a missing quote
or an unclosed bracket.
A frequent cause is also the call made to a non SQL syntax, like the call to an Oracle stored
procedure using the syntax EXECUTE SCHEMA.PACKAGE.PROC(PARAM1, PARAM2).

Chapter 6
The valid SQL call for a stored procedure is:

BEGIN
SCHEMA.PACKAGE.PROC(PARAM1, PARAM2);
END;
The syntax EXECUTE SCHEMA.PACKAGE.PROC(PARAM1, PARAM2) is specific to
SQL*PLUS, and do not work on the Oracle JDBC Thin driver.
2. ORA-00904 invalid column name
Keying error in a mapping/join/filter. A string which is not a column name is interpreted as a
column name, or a column name is misspelled.
This error may also appear when accessing an error table associated to a datastore with a
recently modified structure. It is necessary to impact in the error table the modification, or drop
the error tables and let Oracle Data Integrator recreate it in the next execution.
3. ORA-00903 invalid table name
The table used (source or target) does not exist in the Oracle schema. Check the mapping
logical/physical schema for the context, and check that the table physically exists on the
schema accessed for this context.
4. ORA-00972 Identifier is too Long
There is a limit in the object identifier in Oracle (usually 30 characters). When going over this
limit, this error appears. A table created during the execution of the interface went over this
limit. and caused this error (see the execution log for more details).
Check in the topology for the oracle technology, that the maximum lengths for the object names
(tables and columns) correspond to your Oracle configuration.
5. ORA-01790 expression must have same datatype as corresponding expression
You are trying to connect two different values that can not be implicitly converted (in a
mapping, a join...). Use the explicit conversion functions on these values.
Page - 47
Chapter 7
Best Practices
1. Purging logs
Use “Purge Log” feature which is available in ODI Operator.
a. Login to ODI Operator
b. Go to File | Purge Logs option
Note Users have option to Purge Logs based on Timeline (From/To Date), Context,
Agent, Status, User Name and Session name.
In regards to purging PLM Data Mart logs recommendation, it is up to individual customer in
how they want to implement it. Refer ODI documentation from for more details.
2. Archiving
We recommend taking regular backup of the following,
Data Mart Target DB schema
VLOG (Parameter table)
ODI logs (this can be done by using the “Export Logs” feature in ODI Operator).
Note Perform following to use “Export Logs”
Login to ODI Operator
Select to “Export Logs” option from “File” drop down list
3. DB password change
In order to update the connection details for Source or Target DB
Update “DataMartconfig.properties” file in <Data Mart Home directory>/Config folder
Note Before updating encrypt the password using dmencoder utility (located in bin
directory of PLM Data Mart install folder).
Following sections needs to be updated based on change:
PLM Source DB Details : # PLM DATABASE DETAILS ###
Target Data Mart DB Details: ####### DataMart Database Details######
Target Data Mart DB Schema Details: ###PLM Database TNSEntry Name####
ODI Database Schema Details: ## Oracle Data Integrator Repository Details##
ODI Work Repository Details: # ODI Work Repository Details
4. ETL performance (increase Java Heap_Size)
We recommend increasing the Heap-Size to enhance ETL performance. This can be done in
ODIPARAMS.BAT file located in <ODI Home> / Bin directory
The default setting in the ODIPARAMS.bat is
set ODI_INIT_HEAP=32m
set ODI_MAX_HEAP=256m --- this gets mapped into -Xmx%ODI_MAX_HEAP%

5. Deployment
1. Schema Configuration
The source PLM Database schema and Target DB Schemas MUST be 10 characters or
less. Also, it may not contain any special characters except underscore "_"
2. Install Location Restrictions
PLM Data Mart should be installed in a directory which contains operating systems files
(e.g. c:\)
a. PLM Datamart should not be installed in any directory which has space in it (e.g.
d:\program files).
b. The ODI home directory should not exist in a directory which has space it. If it does,
the PLM Data Mart installation will not proceed.
3. Table space Data Sizing
Refer Resource and Capacity Planning "Resource and Capacity Planning" on page 3 for
complete information.

Agile Product Lifecycle Management: Agile PLM Data Mart - Setup Guide

Uploaded by

Copyright:

Available Formats

Agile Product Lifecycle Management: Agile PLM Data Mart - Setup Guide

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Agile Product Lifecycle Management: Agile PLM Data Mart - Setup Guide

Uploaded by

Copyright:

Available Formats

Agile Product Lifecycle Management

Agile PLM Data Mart - Setup Guide

Part No. E12162-01

Copyright and Trademarks

ii Agile Product Lifecycle Management

Overview of Agile PLM Data Mart ..................................................................................... 1

Installing Agile PLM Data Mart ......................................................................................... 9

Configuring the Agile PLM Data Mart ............................................................................. 37

iv Agile Product Lifecycle Management

The Oracle Technology Network (OTN) Web site

TTY Access to Oracle Support Services

Accessibility of Code Examples in Documentation

Accessibility of Links to External Web Sites in Documentation

vi Agile Product Lifecycle Management

Overview of Agile PLM Data Mart

Data Mart Features.............................................................................................................................................. 1

Data Mart Features

Agile Product Lifecycle Management Page - 1

Data Mart Architecture

Page - 2 Agile Product Lifecycle Management

Resource and Capacity Planning

Deployment Architecture ..................................................................................................................................... 3

Agile Product Lifecycle Management Page - 3

Page - 4 Agile Product Lifecycle Management

Agile PLM Data Mart Database

Data Mart Database Sizing

The basis of this categorization is detailed in the table below.

Page - 6 Agile Product Lifecycle Management

Avg Team size per Program <10 >10 >50 >75

ODI Repository Database

dynamic host configuration protocol (DHCP) server.

Note Disk compression must be disabled on Agile computers.

System Resources Customer Size

Page - 8 Agile Product Lifecycle Management

Installing Agile PLM Data Mart

Note Refer Software Requirements on page 7 for the supported OS versions.

Besides these, ensure that ODI_JAVA_HOME is pointing to JDK 1.5.x.

Agile Product Lifecycle Management Page - 9

Oracle Database Server

Agile PLM Database

Page - 10 Agile Product Lifecycle Management

Oracle Data Integrator

To start the Agile PLM Data Mart installer on Windows:

To modify the system kernel parameters:

Page - 12 Agile Product Lifecycle Management

# cp /etc/system /etc/system_save [Enter]

Note Restart the computer if you modify the /etc/system file.

To start the Agile PLM Data Mart installer on Solaris:

To start the Agile PLM Data Mart installer on Linux:

Data Mart Installer

Page - 14 Agile Product Lifecycle Management

The Installer deploys the following:-

Data Mart Installation Process

Page - 16 Agile Product Lifecycle Management

S-0: Enter User Information

S-1: Select Installation

S-2: Specify PLM Data Mart Database Destination Location

S-3: Select Database Version

Installing Data Mart Schema