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

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':

• 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:

• 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":

• for example: C:\TM1Servers\test

• 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: