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 (afterward) no future data changes trickle (from the IBM Cognos Controller system to the Planning Analytics cube).
Therefore, customer decides to reset their FAP system (clear down all the current data, and start the system from fresh).
How can they achieve this task?
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, between the IBM Cognos Controller and FAP databases) are out of sync.
Multiple stop and start of the FAP Service AND the data mart and source can cause an out of sync. These rapid and multiple actions are typically performed when FAP is not working properly and without understanding the cause of the initial problem. These actions can result in an out of sync.
Multiple stop and start of the FAP Service AND the data mart and source can cause an out of sync. These rapid and multiple actions are typically performed when FAP is not working properly and without understanding the cause of the initial problem. These actions can result in an out of sync.
Restoring one database to an environment and not the other, can cause an out of sync.
Resolving The Problem
Steps to perform a FAP reset sequence:
3. Select the tab "Data Marts":
6. Log on to the TM1 server (as a Windows administrator)
7. Inside Windows Services, stop the relevant TM1 Windows service.
8. Launch Windows Explorer and browse to the folder for your TM1 server
20. Perform some data entry (no structure changes just yet) in the IBM Cognos Controller client application
21. Inside the FAP client, observe the log table to see if a trickle took place
========================================
NOTE:
- As a precaution, ensure that you have valid backups of the databases (both the 'main' and 'FAP' databases).
- During this process, ideally have downtime for IBM Cognos Controller (so that there are no database changes, for example caused by people by using Data Entry)
- These steps completely delete the TM1/PA FAP 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 you are using Db2, modify the instructions slightly. For more information, see 'Appendix' at the end of this technote.
1. Log on to the IBM Cognos Controller application server
2. Launch the 'FAP Client':
3. Select the tab "Data Marts":
- Stop the relevant data mart
4. Open the tab "Sources":
- Highlight the relevant source and click "Stop"
5. Locate the Windows service "IBM Cognos FAP Service". Stop this service:
Note. Unless the customer is troubleshooting a complex issue that also requires a restart of the TM1 instance, steps 6 - 9, 13 and 14 are not necessary for the FAP reset to be completed. Important that steps 3 - 5 are already performed, it ensures that the TM1 instance is NOT updated (from FAP) during the reset exercise.
6. Log on to the TM1 server (as a Windows administrator)
7. Inside Windows Services, stop the relevant TM1 Windows service.
- 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
- This action ensures that a new log file gets created for this procedure.
10. Truncate the table "xdbtrickle" (inside the 'main' IBM Cognos Controller database)
- IMPORTANT: If using IBM Cognos Controller 10.1.1 (or earlier), then performing this step causes the 'audit log' information to be deleted. 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 IBM Cognos Controller 'application repository' database (i.e. your 'main' IBM Cognos Controller database that stores the financial configuration and data).
- Expand the 'tables' and check the exact name of the xdbtrickle table:
- For most customers, it is: dbo.xdbtrickle
Therefore, for some customers, this contains the SQL login name. It will be similar to: fastnet.xdbtrickle
- Run the following SQL script (modifying it to make sure that the table names are correct):
truncate table dbo.xdbtrickle
====================================
NOTE: If using IBM Cognos 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 onward) the sequence number is stored inside table xdbtrickle (which we have reset above), so it will (in effect) have already been reset to 0.
11. If using IBM Cognos 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
- 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 (for example NRTR_ACCOUNT)
- 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
- For most customers, this will be: dbo.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
13. On the TM1 server, inside Windows Services, start the relevant TM1 Windows service.
14. Review the tm1server.log to ensure that the service has started OK before continuing to the next step.
15. On the IBM Cognos 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 IBM Cognos 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
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;
========================================
Related Information
[{"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":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
14 October 2022
UID
swg21661294