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/Planning Analytics 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/PA 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. For more information, see 'Appendix' at the end of this Technote.
 

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
     
    ========================================
    Appendix
    If using DB2 (not Microsoft SQL) as your database platform, then some of the steps above will be slightly different.
    Imagine a scenario where:
    • Source database = CCR01
    • Trickle database = FAP
    • DB2 user used to connect to source database = 'fastnet'
    • DB2 user used to connect to trickle database = 'fapuser'
     
    In the above scenario, use your relevant tool (for example 'IBM Data Studio') to connect to the CCR01 database using the user 'fastnet', and run:    truncate table xdbtrickle;
    image 2761
     
    Then connect to FAP (using an administrative user) and run this:
     
    truncate table fapuser.NRTR_ACCOUNT;
    truncate table fapuser.NRTR_ACTUALITY;
    truncate table fapuser.NRTR_ALLCOMPANIES;
    truncate table fapuser.NRTR_CLOSVER;
    truncate table fapuser.NRTR_COMPANYRELATION;
    truncate table fapuser.NRTR_CONSTYPE;
    truncate table fapuser.NRTR_CONTVER;
    truncate table fapuser.NRTR_CURRENCY;
    truncate table fapuser.NRTR_EXTDIM;
    truncate table fapuser.NRTR_EXTDIMNAME;
    truncate table fapuser.NRTR_JOURNALNUMBER;
    truncate table fapuser.NRTR_PERIOD;
    truncate table fapuser.NRTR_LOG;
    image 2762
    ========================================

    [{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"ARM Category":[{"code":"a8m0z000000brZSAAY","label":"FAP"}],"ARM Case Number":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

    Document Information

    Modified date:
    08 April 2020

    UID

    swg21661294