IBM Support

How to perform a FAP reset sequence (to re-synchronise Controller data publishing with TM1)

Troubleshooting


Problem

Customer is experiencing an issue with their FAP publish process. - Example #1 - The 'initial publish' process is failing. - Example #2 - The Initial Publish works OK, but (afterwards) no future data changes trickle (from the Controller system to the TM1 cube). Customer has therefore decided to reset their FAP system (clear down all the current data, and start the system from fresh). - How can they achieve this? In other words, how can they restart FAP "from scratch" (to make the FAP processes work again)?

Symptom

For examples or possible symptoms, see Technote links at the end of this document.

Cause

In many cases, the root cause (for requiring a FAP reset) is that the 'sequence' numbers have become out of sync.

Resolving The Problem

Steps to perform a FAP reset sequence:

NOTE:

  • As a precaution, please make sure that you have valid backups of the databases (both the 'main' and 'FAP' databases).
  • During this process, ideally have downtime for Controller (so that there are no database changes, for example caused by people using Data Entry)
  • Be aware that these steps will completely delete/reset the TM1 cube (and associated FAP subsystem).
    • If you need to keep the TM1 data intact, then refer to instructions inside separate Technote 1585881.
  • The following instructions assume that the customer is using Microsoft SQL. If using a different database technology then modify the instructions slightly.

1. Logon to the Controller application server

2. Launch the 'FAP Client':


3. Select the tab 'Data Marts':
  • Stop the relevant Data Mart(s)

4. Open the tab "Sources".:

  • Highlight the relevant source(s) and click "Stop"

5. Locate the Windows service "IBM Cognos FAP Service". Stop this service:




6. Logon to the TM1 server (as a Windows administrator)
7. Inside Windows Services, stop the relevant TM1 Windows service.
  • TIP: The name of this service will (typically) start with ‘TM1 Server x64’, and then will also have the 'data mart' instance name after the slash, for example "TM1 Server x64 / test":

8. Launch Windows Explorer and browse to the folder for your TM1 server
  • for example: C:\TM1Servers\test
9. Rename the log file (tm1server.log), for example to: tm1server_<date>.backup
  • This ensures that a new log file will get created for this procedure.

10. Truncate the table "xdbtrickle" (inside the 'main' Controller database)

  • IMPORTANT: If using Controller 10.1.1 (or earlier), then performing this step will cause the 'audit log' information to be deleted/lost. For more information, see separate IBM Technote #1624409

To truncate this table, perform the following:

  • Launch relevant database management tool (for example 'SQL Server Management Studio')
  • Locate the Controller 'application repository' database (i.e. your 'main' Controller database that stores the financial configuration/data).
  • Expand the 'tables' and check the full/exact name of the xdbtrickle table:

    TIP: For most customers, this will be: dbo.xdbtrickle

    For some customers, this will contain the SQL login name, therefore it will be similar to: fastnet.xdbtrickle


  • Run the following SQL script (modifying it as appropriate to make sure that the table names are correct):

      truncate table dbo.xdbtrickle

====================================

NOTE: If using Controller 10.2.0 or later, then you can skip the next step (go directly to step 12 instead)

  • This is because (in 10.2 onwards) the sequence number is stored inside table xdbtrickle (which we have just reset above), so it will (in effect) have already been reset to 0.

====================================

11. If using Controller 10.1.1 (or earlier), reset the sequence numbers, by running the following SQL script:

    update dbo.xdbtricklesequence
    set sequenceno = 0

12. Truncate the relevant tables inside the 'FAP' database, by performing the following:

  • Launch relevant database management tool (for example 'SQL Server Management Studio')
  • Locate the FAP database

      TIP: If you are unsure which database this is, it is the one that is referred to when you launch 'FAP Client':
  • Expand the 'tables' and check the exact names of the tables (e.g. NRTR_ACCOUNT)

    • TIP: For most customers, this will be: dbo.NRTR_ACCOUNT

      For some customers, this will contain the SQL login name, therefore it will be similar to: fastnet.NRTR_ACCOUNT

  • Run the following SQL script (modifying it as appropriate to make sure that the table names are correct):
    • truncate table dbo.NRTR_ACCOUNT
      truncate table dbo.NRTR_ACTUALITY
      truncate table dbo.NRTR_ALLCOMPANIES
      truncate table dbo.NRTR_CLOSVER
      truncate table dbo.NRTR_COMPANYRELATION
      truncate table dbo.NRTR_CONSTYPE
      truncate table dbo.NRTR_CONTVER
      truncate table dbo.NRTR_CURRENCY
      truncate table dbo.NRTR_EXTDIM
      truncate table dbo.NRTR_EXTDIMNAME
      truncate table dbo.NRTR_JOURNALNUMBER
      truncate table dbo.NRTR_PERIOD
      truncate table dbo.NRTR_LOG

13. On the TM1 server, inside Windows Services, start the relevant TM1 Windows service.

14. Wait at least 30 seconds (to ensure that the service has started OK)

15. On the Controller application server, inside Windows Services, start the Windows service "IBM Cognos FAP Service"

16. Launch the 'FAP Client'

17. Open the tab "Sources". Highlight the relevant source, and click "Start"

18. Open the tab "Data Marts". Highlight the relevant data mart, and click "Start"

19. Wait until the Data Mart is showing status 'Running' on the FAP GUI.

Now you can test the system by:


20. Perform some data entry (no structure changes just yet) in the Controller client application
21. Inside the FAP client, observe the log table to see if a trickle took place

Internal Use Only

Email discussion about how script would work for 10.2 where the xdbtricklesequence table no longer exists.

Morvarid Aprin: "Truncating the table should set the seq no to 0 in xdbtrickle (truncate table dbo.xdbtrickle).
In the case that we want to still keep the existing defined data mart we need to have the latest (maximum) sequence no. then the query would be : DELETE FROM xdbtrickle WHERE sequenceno NOT IN(SELECT MAX(sequenceno) FROM xdbtrickle) the same query should be applied for xdbtexttrickle"
======================================

[{"Product":{"code":"SS9S6B","label":"Cognos Controller"},"Business Unit":{"code":"BU002","label":"Business Analytics"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.3;10.2.1;10.2.0;10.1.1;10.1;8.5.1","Edition":""}]

Document Information

Modified date:
15 June 2018

UID

swg21661294