Create a Planning Application Repository with Oracle DB
Disclaimer
Click the images in the article below to toggle between thumbnail and full-size. Enlarge all | Reduce all
Introduction
This article shows how to use scripts that can be run from the command line in SQL Developer or SQL *Plus to create a repository in an Oracle database for an Oracle Hyperion Planning application.
When you create a new Planning application, you must first create a Data Source for the application. When you create a Data Source, if you are using Oracle as your RDBMS you must specify the name of the tablespace that the Data Source will use. The tablespace is also known as the relational repository for the application. This article has two DDL scripts that are needed to create such a tablespace. The first script creates the tablespace itself and the second creates a user to manage the tablespace.
Both of the SQL scripts below can be run in either SQL Developer or SQL *Plus. You'll need to run them under the System account.
Create the Tablespace
The first script creates a tablespace called "Vision". A tablespace is where the tables, views, and other objects needed for the Planning app will reside. The initial size of the tablespace is 2GB. AUTOEXTEND is on, so it will automatically increase in size as needed, adding an additional 500MB each time it starts to run out of space.
In the SQL below, the path for the datafile will vary depending on 1) the user name you used to install Oracle Database; 2) the database file location you specified during installation; and 3) the global database name selected during installation. Adjust your pathname accordingly.
CREATE SMALLFILE TABLESPACE Vision
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\Vision_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;
Create the User
Next, run the following SQL script, which creates a user named "Vision" with a password of "epm_Vision" and grants the user the rights needed to manage a Planning repository. You can change the user name and password as appropriate for your environment.
CREATE USER Vision IDENTIFIED BY epm_Vision
DEFAULT TABLESPACE Vision TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT
CREATE ANY SYNONYM
, CREATE CLUSTER
, CREATE INDEXTYPE
, CREATE PROCEDURE
, CREATE SEQUENCE
, CREATE SESSION
, CREATE TABLE
, CREATE TRIGGER
, CREATE TYPE
, CREATE VIEW
, DROP ANY SYNONYM
, UNLIMITED TABLESPACE
TO Vision;
I’ve used the same name, Vision, for both the tablespace and the user. This isn’t necessary. You can assign them any valid name and they don’t have to be the same.
Final Remarks
Using these scripts gives you the ability to create the repository from the command line, which is generally more efficient than going through a UI such as DBCA, especially if you need to create multiple repositories.
If you have any questions about this article or would like to discuss a consulting engagement, please email me at my G-mail account, which has a userid of "tcockerline".
-Tom Cockerline, February 2017