IBM Support

How to install/configure FAP when using Oracle

Troubleshooting


Problem

Customer would like to install/configure the Financial Analytics Publisher (FAP) capability in Controller, when the database(s) are based on Oracle.

Cause

Many technical consultants/administrator are familiar with how to install/configure FAP when using Microsoft SQL, but are unsure of how to achieve this when using Oracle.
  • Therefore (rather than give step-by-step guidance on all aspects of installing TM1/FAP) this Technote shall specifically only explain the steps that are specific to Oracle.

If the reader wishes to have a step-by-step guide for other parts of the FAP/TM1 install/configuration process, then it is recommended that they read separate Technote #6195375.

Environment

Controller databases hosted on Oracle database server.

Resolving The Problem

(1) Create an empty Financial Analytics Publisher database.
In SQL, you need to create a blank database (sometimes referred to as the ‘Controller Published Data Mart’) which stores the data that is continuously being ‘trickle-published’ out of the ‘Controller Operational Database’ (more commonly known as the ‘Controller application repository database’).
  • In Oracle, you do not (typically) create a new 'database'.
  • Instead, you create a new schema (user) inside an existing database.

TIP: There are two different databases when using Oracle:
  • Controller application repository database (which is often called "CCR")
  • Contentstore (BI/Report-Server configuration) database (which is often called "CS")

Since we want the same character set as our application uses, you should create the new Oracle schema (user) inside the Controller application repository database (often called "CCR").
  • Ask the Oracle DBA to logon to your database server and create a new ‘FAP data mart’ schema (for example called ‘CCR_FAP_data_mart’)

TIP:
  • Below is an example script that your Oracle DBA can use.
  • Modify as appropriate for the schema/password, and also to ensure that the 'default tablespace' is set to the correct value:
create user CCR_FAP_data_mart
identified by MyPassword
default tablespace controller_table;

grant create session to CCR_FAP_data_mart;
grant alter session to CCR_FAP_data_mart;
grant create table to CCR_FAP_data_mart;
grant create database link to CCR_FAP_data_mart;
grant create sequence to CCR_FAP_data_mart;
grant create trigger to CCR_FAP_data_mart;
grant create view to CCR_FAP_data_mart;
grant create procedure to CCR_FAP_data_mart;
grant create materialized view to CCR_FAP_data_mart;
grant create synonym to CCR_FAP_data_mart;
grant create job to CCR_FAP_data_mart;
grant select_catalog_role to CCR_FAP_data_mart;
grant unlimited tablespace to CCR_FAP_data_mart;
grant execute on dbms_lock to CCR_FAP_data_mart;

(2) Create tables inside FAP schema
Launch “Controller Configuration” and then:
  • Open "Database Connections" and create a new entry
  • Choose your settings for the FAP database, for example:
    • Database type: Oracle
    • Name: CCR_FAP_data_mart
    • Provider: OraOLEDB.Oracle.1
    • User id: <schemaname>
    • Password: <password>
    • Data source: <Database Name>
  • Click ‘Save’

Select this database connection (for example “CCR_FAP_data_mart”) and click the “run” button (green triangle). This will launch the "Database Conversion Utility"
  • IMPORTANT: Check that the "UDL File" is correctly pointing to your FAP database (sometimes by mistake it can be pointing to the wrong location)
  • Click the "FAP DB" radio button
  • Click "Connect"
  • Click “Create DB”
  • Afterwards, if the "Current Version" is less than "Upgrade to" then click “Upgrade”.

IMPORTANT: After doing the above steps, you should ‘tidy up’ the new Database Connection by moving the UDL file (for example C:\Program Files (x86)\ibm\cognos\c10\data\ CCR_FAP_data_mart.UDL) to a new folder (for example C:\Program Files (x86)\ibm\cognos\c10\data\FAP_UDL).
If you do not do this, then users will see this in their list of databases to choose from when they launch Controller.

(3) Install the database client software on the TM1 server
This step is only necessary if your TM1 server is NOT the same as your Controller application server (where the Oracle client will already be installed).
  • Logon to the TM1 server as a Windows Administrator account
  • Install the relevant database client
    • For example, if using Oracle 10G, then install the Oracle 10G release 2 client
    • For example, if using Oracle 11G, then install the Oracle 11G release 2 client

IMPORTANT:
  • Make sure that you install the correct Oracle client version (32-bit or 64-bit) depending on the version (32-bit or 64-bit) of TM1 server that you are using
    • For example, if using TM1 server 64-bit, then make sure you install the 64-bit version of the Oracle client.
  • During the client installation wizard, make sure you perform a FULL (complete) client installation (sometimes known as an 'Administrator' client install option).
  • After installing the Oracle client, you must also install one/some Oracle patches:
    • Oracle 10G - see IBM Technote 1347434
    • Oracle 11G - see IBM Technote 1448885

(4) Create an ODBC Data Source for TM1
You need to create an ODBC Data Source (pointing to the Financial Analytics Publisher schema) to be able to use the database from TM1. The ODBC Data Source must be named "FAP", and must be created on the same server as the TM1 installation (i.e. the TM1 server).
  • Logon to the TM1 server as a Windows administrator
  • Click "Start – All Programs - Administrative Tools"
  • Open ‘Data Sources (ODBC)’
  • Click tab ‘System DSN’
  • Click ‘Add’ and choose the relevant database driver (for example ‘Oracle in OraClient11g_home1’)
  • Click ‘Finish’
 
  • TIP: In the unlikely scenario where you are using 32-bit TM1 server on a 64-bit Windows server, then you will need to launch the 32-bit version of ‘Data Sources (ODBC)’, which you do by launching "C:\Windows\SysWOW64\odbcad32.exe".

Configure the ODBC connection, for example:
  • Data Source Name: FAP
  • Description: Controller FAP Connector
  • TNS Service Name: <Oracle database>
  • User ID: <schema name>/<password>

NOTE: It is VITAL that you store the password in here, after the character '/'

Accept all the other default options, then click ‘Test Connection’ to confirm. Then click OK.

(5) Installing and Configuring the Financial Analytics Publisher Service
You need to install the Financial Analytics Publisher Windows service (“IBM Cognos FAP Service”) to be able to retrieve data from the Controller database and send it to TM1 and the TM1 cube.
  • Using NOTEPAD, open the file ‘C:\Program Files\ ibm\Cognos\c10\Server\FAP\FAPService.properties’
  • Edit the file to include the correct database, host, database type, user and password:
    • db=<Oracle database> (sometimes known as "Oracle sid") For example "CCR_FAP_data_mart"
    • host=<TCP/IP hostname of the Oracle database server> - For example you could use the NetBIOS servername (e.g. "ORA10SVR1") or the FQDN version (e.g. "ORA10SVR1.location.companyname.com")
    • dbType=oracle
    • user=<schemaname> For example "CCR_FAP_data_mart"
    • passwd=<password> For example "MyPassword"

You also need to:
(a) download an OJDBC .jar file
  • There are several different possible file names/versions (for example ojdbc5.jar).
  • For full details, see separate IBM Technote #1988891.
(b) copy this file to the directory where the "lib" folder exists
  • typically "C:\Program Files (x86)\ibm\cognos\c10\server\FAP\lib"
 
More Information
Most Controller functionality uses Oracle TNSNAMES to connect to the database server. However, the Java-related functionality (for example FAP publishing) uses EZCONNECT (via JDBC). Therefore, the settings inside "FAPService.properties" need to be filled in using the EZCONNECT nomenclature/naming-convention.

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.1.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
22 April 2020

UID

swg21635338

Page Feedback