Some Context . . .
This is the sixth post in a multi-part blog educating readers on how to build a personal “sandbox” environment for Hyperion Planning. Click here to see all nine posts in the series.
Configure Repositories
Most products in the EPM stack require a relational repository. In the steps below, we will create blank schemas in our Oracle 12c database. Later, these schemas will be populated automatically during product configuration. But for now, all we need are blank schemas, user ID’s and some general settings.
To get started, make sure your VM is up and running.
Within your VM, open SQL Plus.
Enter the user ID “system”. Use the password entered during the Oracle 12c installation . . . this is the administrative password.
Paste the following commands into SQL Plus:
/* Database Settings */
ALTER SYSTEM SET OPEN_CURSORS=5000 scope=SPFILE;
ALTER SYSTEM SET PROCESSES=1000 scope=SPFILE;
ALTER SYSTEM SET SESSIONS=2000 scope=SPFILE;
ALTER SYSTEM SET SESSION_CACHED_CURSORS=200 scope=SPFILE;
/* Increase Temp Tablespace Size */
ALTER DATABASE
TEMPFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\TEMP01.DBF'
DROP INCLUDING DATAFILES;
ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\APP\ORACLE_SA\ORADATA\ORCL\TEMP01.DBF' SIZE 2G
AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
/* Create EAS Repository */
CREATE SMALLFILE TABLESPACE EAS
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\EAS_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;
CREATE USER EAS IDENTIFIED BY epm_EAS
DEFAULT TABLESPACE EAS 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 EAS;
/* Create Shared Services Repository */
CREATE SMALLFILE TABLESPACE SS
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\SS_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;
CREATE USER SS IDENTIFIED BY epm_SS
DEFAULT TABLESPACE SS 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 SS;
/* Create EPMA Repository */
CREATE SMALLFILE TABLESPACE EPMA
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\EPMA_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;
CREATE USER EPMA IDENTIFIED BY epm_EPMA
DEFAULT TABLESPACE EPMA 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 EPMA;
/* Create Planning Repository */
CREATE SMALLFILE TABLESPACE PLANSYS
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\PLANSYS_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;
CREATE USER PLANSYS IDENTIFIED BY epm_PLANSYS
DEFAULT TABLESPACE PLANSYS 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 PLANSYS;
/* Create Planning App Repository */
CREATE SMALLFILE TABLESPACE PLANAPP1
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\PLANAPP1_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;
CREATE USER PLANAPP1 IDENTIFIED BY epm_PLANAPP1
DEFAULT TABLESPACE PLANAPP1 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 PLANAPP1;
/* Create Calc Manager Repository */
CREATE SMALLFILE TABLESPACE CALC
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\CALC_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;
CREATE USER CALC IDENTIFIED BY epm_CALC
DEFAULT TABLESPACE CALC 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 CALC;
/* Create Reporting and Analysis Repository */
CREATE SMALLFILE TABLESPACE BIPLUS
DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\BIPLUS_DATA.dat'
SIZE 2G
AUTOEXTEND ON
NEXT 500M;
COMMIT;
CREATE USER BIPLUS IDENTIFIED BY epm_BIPLUS
DEFAULT TABLESPACE BIPLUS 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 BIPLUS;
The table below includes a summary of the repositories created in the SQL above.
| Schema | Description | User ID | Password |
| SS | Shared Services | SS | epm_SS |
| EPMA | Enterprise Performance Management Architect | EPMA | epm_EPMA |
| EAS | Essbase Administration Services | EAS | epm_EAS |
| PLANSYS | Planning System Repository | PLANSYS | epm_PLANSYS |
| PLANAPP1 | Planning Application Repository | PLANAPP1 | epm_PLANAPP1 |
| CALC | Calculation Manager | CALC | epm_CALC |
| BIPLUS | Financial Reporting and Web Analysis | BIPLUS | epm_BIPLUS |
The following screenshot illustrates the creation of the EPMA repository and user:
In the Next Post
Now that the application schemas and user ID’s have been created, we are finally ready to install Hyperion Planning and the related EPM products!!! Click here for the next post in the series.