Creating an FDM Application Without the DBA

For organizations where getting a DBA's dedicated time is close to impossible, here are some notes regarding how to go about creating the FDM application while minimizing requirements for the DBA.

FDM: Does not need DBA privileges to create an application, assuming the schema/tablespaces are created ahead of time.

Create the user id, and schemas for FDM. Oracle recomends this be in a new instance of Oracle, not with the other EPM products, so that a multiple of performance and other settings can be applied at the instance level which may help FDM.

These Oracle Privileges are safe for just about any Hyperion product - if possible, set these for every schema:

Create Procedure, Create Sequence, Create Session, Create Table, Create Trigger, Create View, and Query Rewrite.

Separate tablespaces are recommended:

   FDM (Default)

   FDM_WORK_TABLES

   FDM_WORK_TABLE_INDEXES

   FDM_DATA_MAP_SEG

   FDM_DATA_SEG

There is an entire hundred page DBA guide for FDM:

http://download.oracle.com/docs/cd/E12825_01/epm.111/fdm_dba.pdf

If something goes wrong and there is already a partial application with tables in the FDM schema and you need to recreate an application in the same schema:

Oracle Knowledgebase:

Message "DATABASE/SCHEMA ALREADY EXISTS" When Creating a New Financial Data Quality Management Application [ID 966752.1]

Each FDM Application requires a UNIQUE/BLANK user schema inside the Oracle DB. If the message comes back stating that it already exists then it is not unique or blank and already contains data.... and this violates the FDM policy.

Perform either of the following actions:

1. Drop/Recreate the User Schema

2. Create a new User Schema

You can run sqldeveper to export the schema metadata including “DROPS” which will allow you to drop all tables, indexes, keys…etc. However this leaves some objects in the recycle bin that fdm is finding still when trying to create the app saying “schema already exists”

Alternatively you can execute the following query against the User Schema being used for the FDM Application to ensure that zero records are returned; indicating it is blank:

select * from user_objects;

or

select OBJECT_NAME from user_objects;

There are some objects that might be left in the recycle bin of type "LOB" even if the schema is "clear"

SQL> purge recyclebin;

Recyclebin purged.

App should now create successfully.