IBM Support

How to configure Financial Analytics Publish (FAP), to allow publishing of Controller data to Planning Analytics

How To


Summary

Customer would like to publish Controller data to Planning Analytics (PA - formerly known as 'TM1').

How do they do this?

Objective

Customer would like to use Controller's "Financial Analytics Publish" (FAP) feature, which allows publishing of data from Controller to PA.
Customer has already read the official installation documentation (link at the end of this Technote).
- However, they would like some more information (such as printscreens) to see step-by-step instructions on how to install/configure FAP.

Environment

The steps below are based on the following environment:
  • Controller 10.4.2
  • Planning Analytics 2.0.8
  • Microsoft SQL 2019
  • Windows 2019 server
  • The printscreens (shown below) assume that all components (Controller, PA and SQL) are installed on the same server
    • This "All-in-one" server is not a recommended configuration for a production system. However, it is enough to demonstrate the general concepts related to installation/configuration of FAP.
Therefore the instructions may need to be amended slightly if your environment is different. For example, there are links (at the end of this Technote) for instructions for:
  • Using old (no longer supported) Controller 10.2.0 - see Technote 721265
  • Oracle - see Technote 490785

Steps

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(A) Prerequisites:
1. As a prerequisite, install Controller server and make sure that everything is working OK
  • For example, users can logon using the Controller classic client.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(B) SQL server:
2. Create a FAP 'trickle' database
This is used to store:
(a) The configuration settings that are used in the FAP client
(b) The temporary 'trickle' data as it is being passed from the Controller database to the PA (TM1) cube:
image 2974
For example, create a new database called 'FAP':
image 2975
  • Make sure that your SQL login (for example 'fastnet' has db_owner rights to this database)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(C) Planning Analytics (PA) server:
3. If not already installed, then install the relevant version of Planning Analytics server
For example
  • Download and extract:  PA_2.0.8_MICROSOFT_WINDOWS_ML.tar.gz
  • Double-click on "issetup.exe"
  • When asked, make sure you choose ALL the optional components:
image 2980
4. Create a PA (TM1) instance (sometimes known as a 'PA server' or a 'PA database', but the author shall always refer to it as a PA instance) called ‘FAP’
  • Create a folder to store your instance, for example:   C:\TM1Data\FAP
  • From the Start Menu, click "IBM Cognos TM1 - 64" then launch Cognos Configuration
  • Create a new TM1 instance by right-clicking on ‘TM1 Server’ and choose ‘New resource – TM1 Server instances’:
 image 2988
  • Call it ‘FAP’
  • Change the configuration path to the correct path, for example:   C:\TM1Data\FAP
image 2989
  • Press 'Save'
  • Browse to the relevant folder, for example: C:\TM1Data\FAP
  • Edit the following file in Notepad:  tm1s.cfg
  • Add the line (underneath the [TM1S] section):   GroupsCreationLimit=1000
For example:
image 2990

  • Save changes
  • Right-click on ‘FAP’ and choose ‘Start’:
image 2991
  • After the TM1 server 'FAP' has finished starting, close ‘Cognos Configuration’
    • Choose NO (when asked if you want to start the other services)
5. Install the Microsoft SQL 2012 Native client (sqlncli.msi)
  • For more details, see separate IBM Technote #6151713.
6. Create a 64-bit ODBC connection called 'FAP' by doing the following:
  • Open "Windows Administrative Tools"
  • Launch:   ODBC Data Sources (64-bit)
  • Click tab ‘System DSN’
  • Click "Add" and choose "SQL Server Native Client 11.0":
image 2981
  • VITAL: The name MUST be called:    FAP
image 2982
  • Connect using your SQL login, for example:
image 2983
  • Change from the default database to 'FAP':
image 2985
  • Test to make sure the connection is OK:
image 2986
Afterwards, it should look similar to:
image 2987
(D) Controller application server:
7. Create tables inside FAP database by launching “Controller Configuration” and then:
  • Open "Database Connections" and create a new entry (for example called 'FAP')
  • Choose your settings for the FAP database, for example:
image 2976
  • Click ‘Save’

Select this database connection (for example “FAP”) and click the “Play/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"
    • The 'Current Version' should say 0 (zero) because it is currently a blank database
image 2977
  • Click “Create DB”
image 2978
  • 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 from the current location (by default C:\Program Files\ibm\cognos\ccr_64\Data\) to a new folder (for example C:\Program Files\ibm\cognos\ccr_64\Data\FAP).
image 2979
If you do not do this, then users will see this in their list of databases to choose from when they launch Controller, which will be confusing for them.
8. Configure the 'IBM Cognos FAP Service' service by doing the following:
  • Browse to this folder: C:\Program Files\ibm\cognos\ccr_64\server\FAP
  • Open this file in NOTEPAD: FAPService.properties
  • Change the values as appropriate, for example:
    db=FAP
    host=mysqlservername
    dbType=sqlserver
    user=fastnet
    passwd=xxxxxx
  • Save and close
  • Change the Windows service ‘IBM Cognos FAP Service’ to be ‘Automatic’, and start it:
image 2993
9. Optional - Install the 64-bit PA client onto the Controller application server
This step is only necessary if you have a separate "PA" and "Controller" server.
  • In other words, if you have already installed PA Server onto the Controller application server, then you can skip this step.
Install the PA 64-bit client by performing the following:
  • Download and extract the relevant file, for example:  PA_CLIENT_64-B_2.0.8_MS_WINDOWS_M.tar.gz
  • Double-click on "issetup.exe"
  • When asked, make sure you choose ALL the optional components:
image 2995
10.    Add a Path for the 64-bit TM1 API (dll) files, into System Variables, by doing the following:
  • Right-click on My Computer, and select Properties
  • Go to the Advanced (or Advanced system settings) tab
  • Click Environment Variables
  • Under System Variables, select Path, and click Edit
  • Append this with an extra entry at the end, for the path to the TM1 bin64 directory, for example:   C:\Program Files\ibm\cognos\tm1_64\bin64
image 2994
11.  Reboot Controller application server (to ensure System Variables change has been actioned)
 
12. From the Start menu, launch the FAP client (FAP Connect), and logon to the FAP database:
image 2996 

13.    Inside ‘Sources’ create a new (or modify the existing) connection to the Controller ‘source’ database
 image 2997
 
Start the source by pressing ‘Start’:
image 3000
 
14.    Inside ‘Data Mart’ create (or modify existing) cube (connection to TM1 server)
For example, something similar to:
image 2998
  
15. Start the Initial Publish by pressing ‘Start’:
image 2999
 
16. Watch the Initial Publish's progress by:
  • Click tab 'Logs'
  • Tick box 'Update automatically':
image 3001
  • Check that the initial publish progresses correctly.
  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(E) (Optional) Client device:
If you prefer to install/run the FAP client directly from the end user's PC, then perform the instructions inside separate IBM Technote #163865.
  • NOTE: If you upgrade to a later version of Controller server in the future, remember to upgrade this FAP client too!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"ARM Category":[{"code":"a8m0z000000brZSAAY","label":"FAP"}],"ARM Case Number":"TS003551598","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
22 April 2020

UID

ibm16195375

Page Feedback