Page 1
Oracle Server Setup Guide for
BusinessObjects Planning
BusinessObjects Planning XI Release 2
Page 2
Copyright
© 2008 Business Objects. All rights reserved. Business Objects owns the following
U.S. patents, which may cover products that are offered and licensed by Business
Objects: 5,295,243; 5,339,390; 5,555,403; 5,590,250; 5,619,632; 5,632,009;
5,857,205; 5,880,742; 5,883,635; 6,085,202; 6,108,698; 6,247,008; 6,289,352;
6,300,957; 6,377,259; 6,490,593; 6,578,027; 6,581,068; 6,628,312; 6,654,761;
6,768,986; 6,772,409; 6,831,668; 6,882,998; 6,892,189; 6,901,555; 7,089,238;
7,107,266; 7,139,766; 7,178,099; 7,181,435; 7,181,440; 7,194,465; 7,222,130;
7,299,419; 7,320,122 and 7,356,779. Business Objects and the Business Objects
logo, BusinessObjects, Business Objects Crystal Vision, Business Process On
Demand, BusinessQuery, Cartesis, Crystal Analysis, Crystal Applications, Crystal
Decisions, Crystal Enterprise, Crystal Insider, Crystal Reports, Crystal Vision,
Desktop Intelligence, Inxight, the Inxight Logo, LinguistX, Star Tree, Table Lens,
ThingFinder, Timewall, Let There Be Light, Metify, NSite, Rapid Marts, RapidMarts,
the Spectrum Design, Web Intelligence, Workmail and Xcelsius are trademarks or
registered trademarks in the United States and/or other countries of Business
Objects and/or affiliated companies. Business Objects is an SAP company. SAP
is the trademark or registered trademark of SAP AG in Germany and in several
other countries. All other names mentioned herein may be trademarks of their
respective owners.
Third-party
Contributors
Business Objects products in this release may contain redistributions of software
licensed from third-party contributors. Some of these individual components may
also be available under alternative licenses. A partial listing of third-party
contributors that have requested or permitted acknowledgments, as well as required
notices, can be found at: http://www.businessobjects.com/thirdparty
2008-06-02
Page 3
Contents
Introduction 5Chapter 1
About this guide...........................................................................................6
Technical requirements................................................................................6
Configuring the Planning Database in Oracle 7Chapter 2
Creating a new database.............................................................................8
Creating new users......................................................................................9
Creating tablespaces...................................................................................9
Importing the original BusinessObjects Planning database......................10
Archive logging..........................................................................................11
Additional Oracle Setup and Administration Considerations 13Chapter 3
Installing the Oracle client..........................................................................14
Backing up the database...........................................................................14
Exporting a database for support purposes...............................................14
Editing the connection parameters............................................................15
Partitioning a data source table.................................................................16
Running ANALYZE TABLE statements on the Oracle database...............17
Who should read this guide....................................................................6
Changing the connection ID.................................................................15
To configure multiple schema instances .............................................15
Oracle Database Parameters 19Appendix A
Database settings......................................................................................20
Tablespaces...............................................................................................23
Oracle Server Setup Guide for BusinessObjects Planning 3
Page 4
Contents
Index 25
4 Oracle Server Setup Guide for BusinessObjects Planning
Page 5
Introduction
1
Page 6
Introduction
1
About this guide
About this guide
This document provides information necessary for the setup and
administration of an Oracle server and database for use with BusinessObjects
Planning.
This document applies to all supported versions of Oracle. Differences in
setup requirements and recommendations are noted in the text.
Who should read this guide
Installation, configuration and administration of the Oracle database is
typically handled by a trained Oracle database administrator in the
organization's Information Technology department. BusinessObjects Planning
Support is also available to assist you with issues relating to use of Oracle
with BusinessObjects Planning.
Technical requirements
BusinessObjects Planning is supported for use with the following Oracle
versions:
• Oracle Database 11g
• Oracle Database 10g
The minimum server version is 10.2.0.2; the minimum client version is
10.2.0.3.
• Oracle9i Database
Standard Edition Release 2 or Enterprise Edition, 9.2.0.4 or greater.
The recommended client and server hardware and software requirements
are outlined in the separately produced document entitled BusinessObjects
Planning Technical Requirements. Please contact the BusinessObjects
Planning Support department or visit www.businessobjects.com/support for
a copy of the most recent version.
6 Oracle Server Setup Guide for BusinessObjects Planning
Page 7
Configuring the Planning Database in Oracle
2
Page 8
Configuring the Planning Database in Oracle
2
Creating a new database
This chapter discusses configuration issues relating to the use of Oracle with
BusinessObjects Planning. It is assumed that you already have an operational
Oracle server.
The following basic steps are required to configure an Oracle system for use
with BusinessObjects Planning:
1. Create a new database. You can also use an existing database if the
BusinessObjects Planning schema fits into the existing database.
2. Create new users.
3. Create tablespaces.
4. Import a database provided by your implementation consultant.
Creating a new database
You can create a new database for use with BusinessObjects Planning, or
use an existing database. The database can be named anything you want,
but for the purposes of this document we will refer to it as the Planning
database.
Please consult Oracle Database Parameters on page 19 for a list of minimum
sizing and configuration settings. These settings should be used for any
database of up to approximately 150,000 records. If your database is
significantly larger than this, please contact BusinessObjects Planning
Support for assistance.
The SRCAdmin user relies on function-based indexes for optimal
performance. To enable function-based indexes, you must change the
initialization file for the database as follows.
1. Add the following two lines to the bottom of the INIT.ORA file:
query_rewrite_integrity = trusted
query_rewrite_enabled = true
2. After adding the information, make sure to stop and restart the database
for the changes to take effect.
8 Oracle Server Setup Guide for BusinessObjects Planning
Page 9
Creating new users
The Oracle objects (tables, indexes, etc.) used by BusinessObjects Planning
are owned by a single user account, named SRCAdmin. To create this
account, execute the script CreateUsers.sql. A copy of this script can be
found on your BusinessObjects Planning product distribution, in the folder
\Resources\Enterprise Scripts\Oracle.
Note that this script does not contain the SRCAdmin password. Your
BusinessObjects Planning representative will provide you with the password.
Note: It is possible to set up multiple schema instances within the
BusinessObjects Planning database. Likewise, you can override the
SRCAdmin user account if you want to use a different naming convention.
For more information, see Editing the connection parameters on page 15.
The script also creates an account called SRCView with a password
readdata. This account is a read-only account to the database, to allow
power users to view table data as read-only without granting the
write/create/drop privileges required by the SRCAdmin account.
Configuring the Planning Database in Oracle
Creating new users
2
Note: The SRCView account and password should only be given to users
who have full permission to view all data in the database.This account is not
required and can be removed if desired.
Creating tablespaces
To create the tablespaces required by BusinessObjects Planning, use the
script named CreateTableSpaces.sql. A copy of this script can be found
on your BusinessObjects Planning product distribution, in the folder
\Resources\Enterprise Scripts\Oracle.
The CreateTableSpaces.sql script must be edited as follows:
• Replace the <File Location> parameter with the path to the files in your
environment.
• Update the tablespaces parameter <Size> with the appropriate sizes for
your environment. See Tablespaces on page 23 for a list of recommended
sizes. If your system will have over 150,000 records, please speak with
your implementation consultant regarding proper tablespace sizes.
Oracle Server Setup Guide for BusinessObjects Planning 9
Page 10
Configuring the Planning Database in Oracle
2
Importing the original BusinessObjects Planning database
The "SRC" tablespaces have been created to assist you in optimizing the
performance of BusinessObjects Planning. A brief explanation of each
tablespace is shown below:
DescriptionTablespace
SRCTLARGE
SRCTSMALL
SRCILARGE
SRCISMALL
SRCTemp
This tablespace contains only a few, large tables.
A separate tablespace is used for performance
reasons.
This tablespace contains all of the remaining tables, whose sizes are relatively small.
This area is the corresponding index area for the
larger tables.
This area is the corresponding index area for the
smaller tables.
This tablespace is used for temporary tables created and removed by the BusinessObjects Planning program code.
Importing the original BusinessObjects
Planning database
Your BusinessObjects Planning representative will provide you with a file
containing an export of the original database. Import this database into the
new database you created for use with BusinessObjects Planning. No special
import settings are required – use all default settings.
Note: If you are restoring the Planning test system, the user name is
PlanningTest, not SRCAdmin. See the BusinessObjects Planning Test System
Instructions for more details.
10 Oracle Server Setup Guide for BusinessObjects Planning
Page 11
Archive logging
It is strongly recommended to disable archive logging for the BusinessObjects
Planning database. This feature is unnecessary for BusinessObjects Planning
due to the nature of updates to the database (low volume of large transactions
that are easily repeatable).
If you choose to enable archive logging, the archive file sizes should be
monitored initially for disk space consumption. You should also schedule the
automatic backup and removal of the archive log files.
Configuring the Planning Database in Oracle
Archive logging
2
Oracle Server Setup Guide for BusinessObjects Planning 11
Page 12
Configuring the Planning Database in Oracle
Archive logging
2
12 Oracle Server Setup Guide for BusinessObjects Planning
Page 13
Additional Oracle Setup and Administration Considerations
3
Page 14
Additional Oracle Setup and Administration Considerations
3
Installing the Oracle client
Installing the Oracle client
Each workstation that will access BusinessObjects Planning using the
Planning Professional Edition desktop client must also have an installation
of the Oracle client (11g, 10g, or 9i as appropriate). The Oracle client is not
required on workstations that will only use the BusinessObjects Planning
web access options.
Note that the Instant Client is not supported for use with BusinessObjects
Planning. The full client must be used.
If you did not specify a TNS Descriptor as the Oracle data source in the
Planning Configuration Manager (see Editing the connection parameters on
page 15), you must ensure that the Oracle client is correctly configured to
reach the BusinessObjects Planning database instance via Oracle Internet
Directory, Oracle Names, or a TNSNames.ora file.
Backing up the database
It is imperative to back up the SRCAdmin user account in coordination with
your normal backup activities. It is beyond the scope of this guide to discuss
backup procedures for various operating system environments.
Note: Most standard file system backups do NOT back up in-use files, such
as those associated with an Oracle database. You should configure a process
where the Oracle database is copied to a separate backup file, which can
then be backed up as normal (since it is not in use).
Before restoring a database from backup, you should stop the Planning
Application Service.
Exporting a database for support purposes
Occasionally it may be necessary to send a backup of the BusinessObjects
Planning database to Support to diagnose issues you may be facing. When
performing the export, use the standard Oracle Export (EXP) client and send
only the SRCAdmin user account.
14 Oracle Server Setup Guide for BusinessObjects Planning
Page 15
Additional Oracle Setup and Administration Considerations
Editing the connection parameters
Editing the connection parameters
The connection parameters for the Oracle database are defined within the
Planning Configuration Manager. After BusinessObjects Planning is installed,
these connection parameters must be completed as part of the configuration
process. For full details, see the BusinessObjects Planning Installation Guide.
To access the Oracle connection parameters:
1. On the server hosting Planning Application Server, choose Start >
Programs > BusinessObjects EPM > BusinessObjects EPM Tools >
BusinessObjects Planning > Configuration Manager.
2. From the tree menu in the left-hand pane, select Systems > System
Name > Connection Parameters (where SystemName is the name you
have defined for the system).
The database connection parameters appear in the right-hand pane.
Changing the connection ID
3
By default, BusinessObjects Planning attempts to connect to the database
as the user SRCAdmin. If you change the name of this user ID for the Oracle
database, you must edit the BusinessObjects Planning connection parameters
in the Configuration Manager for the new user ID.
If you decide to change the name of the user ID, make sure that all Oracle
objects (tables, indexes, etc.) are owned by the new user ID. For example,
if the ID is changed to OracleAdmin, then the OracleAdmin user must be the
owner of all tables, indexes, views, triggers, sequences, etc., that SRCAdmin
had previously owned. Please consult your Oracle documentation for
information on how to change object ownership.
To configure multiple schema instances
In some BusinessObjects Planning configurations, it may be necessary to
load multiple schemas into the same Planning database.
Many BusinessObjects Planning installations are comprised of multiple
systems – for example, one budgeting system and one sales planning system.
To accommodate multiple systems, you can use separate Oracle databases
Oracle Server Setup Guide for BusinessObjects Planning 15
Page 16
Additional Oracle Setup and Administration Considerations
3
Partitioning a data source table
for each system, or you can use the same Planning database with different
schema instances for each system. The latter approach would require you
to set up two distinct user IDs (for example, user_bud and user_sales) for
connection and ownership purposes.
1. Set the proper ownership rights for each user ID in Oracle.
2. Set the proper database security settings for each user ID. Each
connecting user must have the following permissions revoked:
• Delete Any Table
• Insert Any Table
• Lock Any Table
• Select Any Table
• Update Any Table
Revoking these rights ensures that the connecting user will only have
access to the designated schema for that user.
3. In the Planning Configuration Manager, edit the User ID connection
parameter for each system.
Partitioning a data source table
The database fact tables in BusinessObjects Planning are known as data
sources. The naming convention for data source tables is od_All#, where
# is the data source number (0 to 9).
Depending on the specifics of your implementation, your data source tables
can be quite large. To improve performance in larger environments, it is
possible to partition a data source table across multiple tablespaces.
BusinessObjects Planning will detect the partitioning and preserve it when
performing updates to the data source table.
The following limitations apply to data source partitioning in Oracle systems:
• Partitioning is only supported for Enterprise versions of Oracle.
• Only range and list partitions are supported. Hash and composite partitions
are not supported.
• The partitioning column must exist in the Planning Data Structures. If
the partitioning column is deleted from Data Structures, then the table
16 Oracle Server Setup Guide for BusinessObjects Planning
Page 17
Additional Oracle Setup and Administration Considerations
Running ANALYZE TABLE statements on the Oracle database
will be saved without partitions. A warning is logged in the Data Structures
status box if this occurs.
If for any reason BusinessObjects Planning is unable to create the table with
partitioning, then a warning is logged in the Data Structures status box, and
the table is saved without partitions.
Running ANALYZE TABLE statements
on the Oracle database
The Oracle ANALYZE TABLE statement is used to collect statistics about a
table. In certain situations, running ANALYZE TABLE may improve performance
on future queries to the table.
BusinessObjects Planning does not initiate ANALYZE TABLE statements as
part of table updates, except in the case of data imports. It is up to the Oracle
database administrator to manage the collection of table statistics at
appropriate intervals, as determined based on the specifics of each individual
implementation.
3
Business Objects recommends running ANALYZE TABLE after adding a new
dimension or making other significant changes to Data Structures, or after
making significant data additions to a data source.
By default, BusinessObjects Planning initiates an ANALYZE TABLE statement
after saving imported data to a table as part of an SRCUpdate import step.
You can disable this behavior by setting the third parameter of SRCUpdate
to false. For more information on SRCUpdate, see the Planning Professional
Edition Online Help or the Planning Professional Edition Administrator's
Guide.
Oracle Server Setup Guide for BusinessObjects Planning 17
Page 18
Additional Oracle Setup and Administration Considerations
Running ANALYZE TABLE statements on the Oracle database
3
18 Oracle Server Setup Guide for BusinessObjects Planning
Page 19
Oracle Database Parameters
A
Page 20
Oracle Database Parameters
A
Database settings
The tables in this appendix detail the parameters to use when configuring
your Oracle server for use with BusinessObjects Planning.
These values are the minimum recommended starting settings for a database.
After installation, the system should be tuned by an Oracle database
administrator for optimal performance.
Database settings
Table A-1: 11g settings
ValueParameter
11.1.0 or higherDatabase Structure Compatibility
PlanningDatabase Name (SID)
25Concurrent users
DedicatedServer Mode (Dedicated or Shared)
Control Files
Default tablespaces:
Table A-2: 10g settings
20 Oracle Server Setup Guide for BusinessObjects Planning
Recommend at least 3 control files on
3 separate physical disks, if possible.
OffArchive logging
100 MBSystem
500 MBUndo tablespaces
500 MBTemp
ValueParameter
10.2.0.1 or higherDatabase Structure Compatibility
Page 21
Oracle Database Parameters
Database settings
ValueParameter
WE8ISO8859P1Character set
PlanningDatabase Name (SID)
25Concurrent users
MultipurposeDatabase primary use
DedicatedServer Mode (Dedicated or Shared)
NoneOracle options
A
Control Files
Control File Settings:
Default tablespaces:
Recommend at least 3 control files on
3 separate physical disks, if possible.
25,000 KB eachRedo Logs (recommend 3)
OffArchive logging
150Processes
8,192 BytesBlock Size
2,048KSort Area Size
50MBPGA Aggregate Target
300MBSGA Target
25Maximum Datafiles
12Maximum Log Files
2Maximum Log Members
100 MBSystem
500 MBUndo tablespaces
Oracle Server Setup Guide for BusinessObjects Planning 21
Page 22
Oracle Database Parameters
A
Database settings
ValueParameter
500 MBTemp
Other tablespaces
Table A-3: 9i settings
Control Files
Only SYSTEM, OEM_REPOSITORY,
TEMP, UNDOTBS1, are needed. All
other tablespaces can be deleted.
ValueParameter
9.2.0.4 or higherDatabase Structure Compatibility
WE8ISO8859P1Character set
PlanningDatabase Name (SID)
25Concurrent users
MultipurposeDatabase primary use
DedicatedServer Mode (Dedicated or Shared)
NoneOracle options
Recommend at least 3 control files on
3 separate physical disks, if possible.
25,000 KB eachRedo Logs (recommend 3)
OffArchive logging
150Processes
8,192 BytesBlock Size
2,048KSort Area Size
100MBShared Pool Size
200MBDB Cache Size
22 Oracle Server Setup Guide for BusinessObjects Planning
Page 23
Control File Settings:
Default tablespaces:
Oracle Database Parameters
Tablespaces
ValueParameter
50MBPGA Aggregate Target
25Maximum Datafiles
12Maximum Log Files
2Maximum Log Members
100 MBSystem
500 MBUndo tablespaces
500 MBTemp
A
Other tablespaces
Tablespaces
Only SYSTEM, OEM_REPOSITORY,
TEMP, UNDOTBS1, are needed. All
other tablespaces can be deleted.
ValueParameter
250 MBSRCILARGE
200 MBSRCISMALL
500 MBSRCTLARGE
500 MBSRCTSMALL
Oracle Server Setup Guide for BusinessObjects Planning 23
Page 24
Oracle Database Parameters
A
Tablespaces
ValueParameter
500 MBSRCTEMP
24 Oracle Server Setup Guide for BusinessObjects Planning
Page 25
Index
A
ANALYZE TABLE statements 17
archive logging 11
E
exporting the database 14
B
backup procedures 14
C
changing the default connection ID 15
Configuration Manager 15
configuring for multiple schema instances 15
configuring the Planning database 8
connection ID 15
connection parameters, editing 15
CreateTableSpaces.SQL 9
CreateUsers.SQL 9
creating a new database 8
creating new users 9
D
database
backup procedures 14
configuring 8
creating 8
exporting 14
importing the BusinessObjects Planning
database 10
partitioning 16
database parameters 20
default connection ID 15
I
importing the BusinessObjects Planning
database 10
installing the Oracle client 14
Instant Client 14
M
multiple schema instances 15
O
OLTP 11
Oracle
supported versions 6
Oracle client, installing 14
Oracle Server Manager 9
P
partitioning a data source table 16
performance, improving 17
R
ROLAP 11
Oracle Server Setup Guide for BusinessObjects Planning 25
Page 26
Index
S
schema instances, configuring for multiple 15
srcadmin 8
SRCAdmin 9
SRCIA 15
SRCView 9
T
tablespaces
creating 9
SRC tablespaces 9, 23
technical requirements 6
U
users, creating 9
26 Oracle Server Setup Guide for BusinessObjects Planning
Loading...