Daily routine tasks in SAP Financial Consolidation (FC)

Hello colleagues,

When you have to support SAP Financial Consolidation (FC), you need to keep in mind that some tips.

Environment:

SAP Financial Consolidation 10.1
Windows Server
SAP HANA 2.0

Tip 1:

CT_LOG table has to be cleaned periodically. If the trace function is on, it grows very quickly and becomes huge.

SAP FC logs

How to clean:

1. (Optional) Create backup table

CREATE COLUMN TABLE "<SCHEMA_NAME>"."save_<date>_ct_log" ("ID" INTEGER CS_INT,
"USER_NAME" NVARCHAR(12),
"ACTION_DATE" NVARCHAR(10),
"ACTION_TIME" NVARCHAR(8),
"MAIN_OBJ_CODE" NVARCHAR(150),
"MAIN_OBJ_MGR" NVARCHAR(50),
"OBJECT1" NVARCHAR(150),
"OBJECT2" NVARCHAR(250),
"ACTION_ID" INTEGER CS_INT,
"IMPACTEDPROPERTY" NVARCHAR(250),
"DETAILS_ID" NVARCHAR(250)) UNLOAD PRIORITY 5 AUTO MERGE

2. (Optional) Save current data to the backup table

INSERT INTO <SCHEMA_NAME>.save_<date>_ct_log SELECT * FROM <SCHEMA_NAME>.CT_LOG

3. Delete old records (e.g. older than 2022-09)

DELETE FROM <SCHEMA_NAME>.CT_LOG WHERE TO_VARCHAR(ACTION_DATE,'YYYY-MM') < '2022-09';

Tip 2.

Check size and delete old records from: CT_HISTORY_DETAIL , CT_EXECUTIONS  and CT_HISTORY. These tables can occupy some memory in SAP HANA.

CT tables size in SAP HANA

How to clean:

1. Delete old records in CT_HISTORY_DETAIL (e.g. older than 2022-09)

DELETE FROM <SCHEMA_NAME>.CT_HISTORY_DETAIL WHERE ID IN (SELECT ID FROM <SCHEMA_NAME>.CT_HISTORY WHERE TO_VARCHAR(WLOGDATE,'YYYY-MM') < '2022-09');

2. Delete old records in CT_EXECUTIONS (e.g. older than 2022-09)

DELETE FROM <SCHEMA_NAME>.CT_EXECUTIONS WHERE WHISTORYID IN (SELECT ID FROM <SCHEMA_NAME>.CT_HISTORY WHERE TO_VARCHAR(WLOGDATE,'YYYY-MM') < '2022-09');

3. Delete old records in CT_HISTORY (e.g. older than 2022-09)

DELETE FROM <SCHEMA_NAME>.CT_HISTORY WHERE TO_VARCHAR(WLOGDATE,'YYYY-MM') < '2022-09';

Tip 3 (optional).

Check SAP Notes first or open SAP ticket

If you have some strange problems with SAP FC like performance or blocking situations, you may try to delete records in:

• ct_identifier_pool  (the first startup of the FC data sources will be slower)
• ct_recycled_identifier (the first startup of the FC data sources will be slower)
• ct_active_session
• ct_active_server
• ct_user_serial
• ct_md_lock
• ct_object_lock

Update ct_lock filed in CT_MUTEX table

• update <SCHEMA_NAME>.CT_MUTEX  set ct_lock=0;

Useful links:

SAP Notes: 
1969700 - SQL Statement Collection for SAP HANA
2178324 - How to delete visible logs in Financial Consolidation using SQL script ?-FC
2486273 - What does the function Trace Reports do ? - FC
2511264 - The consolidation task is interrupted randomly - FC
Clean-up of Objects
SAP Financial Consolidation Database Schema Guide