Troubleshooting
Problem
- 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).
Symptom
Cause
Resolving The Problem
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
- 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):
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
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
- 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
- TIP: 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_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
- Source database = CCR01
- Trickle database = FAP
- DB2 user used to connect to source database = 'fastnet'
- DB2 user used to connect to trickle database = 'fapuser'
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;
========================================
Related Information
Document Information
Modified date:
08 April 2020
UID
swg21661294