Installing myObjectiveOLAP Server


Requirements


Database software


Oracle Database 11g with OLAP option.


Access


sqlPlus access as SYS (SYSDBA)


File system access on the same machine as sqlPlus.


File system access which is visible to the Oracle RDBMS and which Read Write Directory aliases can be defined.


Overview


Two sql scripts are provided to install a base myObjectiveOLAP Server install:


Script

Purpose

mooSetupUser.sql

Defines two RDBMS users: MOOUSER and MOOSERVER.  Grants the correct roles and responsibilities to these users.

mooSetupEnvironment.sql

Defines the base myObjectiveOLAP Server Analytic Workspace environment and imports the core structures from a series of supplied binary EIF files.

[myObjectiveOLAP-Server-Install-Table-1]


Getting Ready for Installation.


Before executing the following mooSetupUser.sql you should enable the OLAPSYS Oracle Database user:


If this is not a new Oracle OLAP installation, and you are adding to an existing environment then you may not need to complete this step.


This can be done by logging into the database as SYS as shown below:




moo12$ $ORACLE_HOME/bin/sqlplus / as SYSDBA


SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 1 20:10:04 2012


Copyright (c) 1982, 2011, Oracle.  All rights reserved.



Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL>

SQL> GRANT CREATE SESSION       TO "OLAPSYS";

SQL> GRANT ALTER SYSTEM         TO "OLAPSYS";

SQL> commit;

SQL> EXIT


mooSetupUser.sql Overview


Users


mooSetupUser.sql defines two Oracle Database users:


Username

Purpose

MOOSERVER

This is the account used by the myObjectiveOLAP application administrator as the Oracle Database account, this is separate from the myObjectiveOLAP application user of the same name, although both are often used in conjunction.  This user is assigned a custom Oracle Database profile called MOOSERVER as discussed below.


This user is granted access to objects a normal user would not be able to access such as some of the directory aliases shown below.

MOOUSER

This is the primary Oracle Database account used as part of the connection by all normal end-users.  This user is assigned a custom Oracle Database profile called MOOSERVER as discussed below.

[myObjectiveOLAP-Server-Install-Table-2]


Passwords


mooSetupUser.sql defines the passwords for the two Oracle Database users MOOSERVER and MOOUSER.  You should change the password section of the script (RED) below before execution of the installation script:


Username

Purpose

MOOSERVER

 -- Change the password below

 -- This is the username you use in an application DBA user db connection screen

CREATE USER "MOOSERVER" PROFILE MOOSERVER

       IDENTIFIED BY "myObjectiveOLAP4321"

       ACCOUNT UNLOCK;        

MOOUSER

-- Change the password below

-- This is the username you use in a normal user db connection screen

CREATE USER "MOOUSER" PROFILE MOOSERVER

       IDENTIFIED BY "myObjectiveOLAP4321"

       ACCOUNT UNLOCK;

[myObjectiveOLAP-Server-Install-Table-3]


Profiles


The following profile, based on the DEFAULT Oracle Database profile is created during the execution of the mooUserSetup.sql script.  Differences to the DEFAULT profile are show in RED.


Profile

Adaptations From DEFAULT Profile

CREATE PROFILE "MOOSERVER" LIMIT CPU_PER_SESSION UNLIMITED

CPU_PER_CALL UNLIMITED

CONNECT_TIME UNLIMITED

IDLE_TIME UNLIMITED

SESSIONS_PER_USER UNLIMITED

LOGICAL_READS_PER_SESSION UNLIMITED

LOGICAL_READS_PER_CALL UNLIMITED

PRIVATE_SGA UNLIMITED

COMPOSITE_LIMIT UNLIMITED

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_LOCK_TIME DEFAULT

FAILED_LOGIN_ATTEMPTS UNLIMITED

PASSWORD_VERIFY_FUNCTION NULL;

User expiration and failed attempt locking is delegated to the MOOSERVER application

[myObjectiveOLAP-Server-Install-Table-4]


It is permissible to adapt the mooUserSetup.sql script to use the DEFAULT Oracle Database profile instead of the MOOSERVER profile, however, you must ensure that a new connection.xml file is distributed to the user base every time the password times-out.  It would also be possible for an individual user to disable all users access if the DISABLE_PASSWORD_CHANGE tag is not enabled in your mooApplicationSettings.xml file.


Roles, Responsibilities and Permissions


The following roles, responsibilities and permissions are granted during execution of the mooUserSetup.sql installation script.  Where appropriate a reason is included.


USER

Change

Reason

MOOUSER

CONNECT

MOOUSER

OLAP_DBA

MOOUSER

OLAP_USER

MOOUSER

CREATE CUBE

MOOUSER

CREATE SESSION


MOOSERVER

UNLIMITED TABLESPACE

MOOSERVER

CONNECT

MOOSERVER

OLAP_DBA

MOOSERVER

OLAP_USER

MOOSERVER

CREATE CUBE

MOOSERVER

CREATE SESSION


MOOSERVER

ALTER SYSTEM

Required for OLAP Session Management

MOOSERVER

SELECT on DBA_SCHEDULER_JOBS

Required for OLAP Session Management

MOOSERVER

SELECT on DBA_SYS_PRIVS

MOOSERVER

READ    ON DIRECTORY moocda

MOOSERVER

WRITE   ON DIRECTORY moocda

MOOSERVER

EXECUTE ON DIRECTORY moocda

MOOSERVER

READ    ON DIRECTORY logcda

MOOSERVER

WRITE   ON DIRECTORY logcda

MOOSERVER

READ    ON DIRECTORY olapcda

MOOSERVER

WRITE   ON DIRECTORY olapcda

MOOSERVER

READ    ON DIRECTORY eifcda


MOOSERVER

WRITE   ON DIRECTORY logcda

MOOUSER

SELECT ON MOOSERVER.AW$MOODATA

MOOUSER

SELECT ON MOOSERVER.AW$MOOUSER

MOOUSER

SELECT ON MOOSERVER.AW$MOOCODE

MOOUSER

SELECT ON MOOSERVER.AW$LOCALCODE

MOOUSER

SELECT ON MOOSERVER.AW$MOORUNNING

MOOUSER

SELECT ON MOOSERVER.AW$MOOUSERS

MOOUSER

SELECT ON MOOSERVER.AW$MOOUSERS

MOOUSER

SELECT ON MOOSERVER.AW$MOOAWM

MOOUSER

SELECT ON MOOSERVER.AW$MOOBACKUP

MOOUSER

SELECT ON MOOSERVER.AW$SUBDATA

MOOUSER

SELECT ON DBA_SCHEDULER_JOBS

Enables the end-user to view if the Process Manager is  running.

MOOUSER

Update ON MOOSERVER.AW$MOODATA

MOOUSER

Update ON MOOSERVER.AW$MOOUSERS

MOOUSER

Update ON MOOSERVER.AW$PRCONTROL

MOOUSER

Update ON MOOSERVER.AW$MOOAWM

MOOUSER

Update ON MOOSERVER.AW$SUBDATA

[myObjectiveOLAP-Server-Install-Table-5]


*  Unlike other parts of the installation script, whilst it is permissible to GRANT additional permissions to either MOOSERVER or MOOUSER it is NOT permissible to detract from the list above.  Doing so may result in unexpected operation or failure of your myObjectiveOLAP Server based installation


Directories


You should create at least one directory on the server for use by myObjectiveOLAP, the recommendation is to create three.

Your system or database administrator must ensure that the operating system directory has the correct permissions for the Oracle Database process.  


The default location for the directory aliases is shown below:


Directory

Default Location

Purpose

moocda

/u01/moocda

myObjectiveOLAP stores certain components on the file system temporarily during the execution of business logic, such as data submission.

olapcda

/u01/logcda

Used by myObjectiveOLAP server when debugging is enabled.

logcda

/u01/logcda

Various levels of logging can be enabled, optionally myObjectiveOLAP can output these logs as text to the logcda directory.

eifcda

/u01/eifcda

This directory is required during the install, but it is also used as part of a number of standard tasks, such as automated backup.

[myObjectiveOLAP-Server-Install-Table-6]


*  It is permissible for all the above directory aliases to physically be one file system directory, although it is recommended to separate them out for ease of analysis and maintenance.


You should edit the following section of the mooSetupUser.sql script prior to installation to match your local environment:



-- Change the directory file system locations as required.

CREATE OR REPLACE DIRECTORY moocda as  '/u01/moocda';

CREATE OR REPLACE DIRECTORY olapcda as '/u01/logcda';

CREATE OR REPLACE DIRECTORY logcda as  '/u01/logcda';

CREATE OR REPLACE DIRECTORY eifcda as  '/u01/eifcda';



Running mooSetupUser.sql


Complete the following steps:


Step

Adjust the mooSetupUser.sql script as documented above

Copy the mooSetupUser.sql file to the directory you are going to start sqlPlus in.

Start sqlPlus as Oracle user

Execute the mooSetupUser.sql script

If there are no errors then type COMMIT; [ENTER] or if you wish to exit without committing ROLLBACK; [ENTER]

[myObjectiveOLAP-Server-Install-Table-7]


Example below:



moo12$ $ORACLE_HOME/bin/sqlplus / as SYSDBA


SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 1 20:10:04 2012


Copyright (c) 1982, 2011, Oracle.  All rights reserved.



Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL>

SQL> @mooSetupUser.sql

Grant succeeded.

SQL>

Grant succeeded.


SQL>

Grant succeeded.


SQL>

Grant succeeded.

....[repeated]...

SQL>

SQL> commit;

SQL> EXIT



mooSetupEnvironment.sql Overview


The mooSetupEnvironment.sql installation script creates the Analytic Workspaces required to install the base myObjectiveOLAP Server objects and programs.


You should extract the contents of myObjectiveOLAPServer2012-R[XX].zip and transfer them to the physical file system location you defined for your eifcda Directory Alias as part of mooSetupUser.sql installation step.


Files used during this part of the installation:


File

Purpose

mooSetupEnvironement.sql

Installation script for the myObjectiveServer framework.  Can also be used to revert an existing application to a new install state.

2012-Release-[XX]-MOODATA.eif

Data and meta-data for use within the main data storage and reporting component of the environment.

2012-Release-[XX]-PRD.eif

Objects and programs relating to the Process Manager daemon.

2012-Release-[XX]-PRCONTROL.eif

Data and meta-data for use by the Process Manager framework

2012-Release-[XX]-MOOUSERS.eif

Data and meta-data for use by the User Management framework.

2012-Release-[XX]-MOOCODE.eif

Meta-data and Oracle OLAP DML.  This is the core program component of the myObjectiveOLAP Server framework.

2012-Release-[XX]-LOCALCODE.eif

Mata-data for housing localized code.

2012-Release-[XX]-MOOBACKUP.eif

Data and meta-data for automated backup of the environment.

[myObjectiveOLAP-Server-Install-Table-8]


Re-running mooSetupEnvironment.sql


The mooSetupEnvironment.sql installation script can be run at anytime to set your myObjectiveOLAP Severer environment to a new install state.  

!IMPORTANT! -- This will result in the loss of all data.


Objects and Workspaces Created during Installation mooSetupEnvironment.sql


The following table lists the objects and workspaces created during the execution of the mooSetupEnvironment.sql installation script.


Workspace / Object

Purpose

MOOSERVER.AW$DATA

Analytic Workspace designed to store data and act as the main reporting repository

Meta-data relating to data objects created within the

MOOSERVER.AW$PRD

Analytic Workspace holding objects and OLAP DML programs relating to the myObjectiveOLAP Process Manager daemon

MOOSERVER.AW$PRCONTROL

Analytic Workspace holding objects to store control and audit data for the  Process Manager

MOOSERVER.AW$MOOUSERS

Analytic Workspace holding objects relating to users, user control, audit and data scoping.

MOOSERVER.AW$MOOCODE

Analytic Workspace holding the core myObjectiveOLAP Server OLAP DML programs

MOOSERVER.AW$LOCALCODE

Empty Analytic Workspace in which custom local programs can be stored.  This workspace is not impacted by the myObjectiveOLAP upgrade process.

MOOSERVER.AW$MOOBACKUP

Analytic Workspace holding objects and OLAP DML relating to automated backups of the myObjectiveOLAP Server environment.

MOOSERVER.AW$SUBDATA

Empty Analytic Workspace used during the processing of user submissions of data and holds data audit and rollback data.

MOOSERVER.AW$MOOTEMP

Empty Analytic Workspace used temporarily during execution of business processes also used as a staging area during upgrade of myObjectiveOLAP Server.


Running mooSetupEnvironment.sql


Complete the following steps:


Step

Copy the EIF files listed in myObjectiveOLAP-Server-Install-Table-8 to the directory you defined for eifcda in table myObjectiveOLAP-Server-Install-Table-6

Copy the mooSetupUser.sql file to the directory you are going to start sqlPlus in.

Start sqlPlus as MOOSERVER user

Execute the mooSetupEnvironment.sql script

If there are no errors then type COMMIT; [ENTER] or if you wish to exit without committing ROLLBACK; [ENTER]


Example below:



moo12$ $ORACLE_HOME/bin/sqlplus


SQL*Plus: Release 11.2.0.3.0 Production on Sun Feb 5 16:33:40 2012


Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Enter user-name: mooserver

Enter password:


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL>

SQL> @mooSetupEnvironment.sql


{OUTPUT SHOWN}

{OUTPUT SHOWN}

{OUTPUT SHOWN}

{OUTPUT SHOWN}




....[repeated]...

SQL>


! MOOSERVER:>> If this is the first time you have run this you will see Analytic workspace XXX does not exist. Ignore these errors

! MOOSERVER:>> If you did not see any Errors? You should now issue a COMMIT or you could ROLLBACK

SQL> commit;

SQL> EXIT


Setting the initial mooserver application password.


Start Microsoft Excel and press the standard Connection Editor from the myObjectiveOLAP menu group



Complete the appropriate details for the server you have installed myObjectiveOLAP Server on.




Select the User Manager menu item from the myObjectiveOLAP menu group.




Ensure the MOOSERVER user is selected, enter a new password and press Save Changes from the myObjectiveOLAP Ribbon menu.


Your myObjectiveOLAP Server installation is now complete.