FDMEE: System Maintenance Tasks: Maintain FDMEE Data Tables

Hi Friends,

As we know Oracle Hyperion Financial Data Quality Management (FDMEE) data tables are used to store archives of your exported data for the purpose of audit and drill through. The size of these Hyperion Financial Data Quality Management (FDMEE) tables grows with time containing vast amounts of data. It consumes a lot of space on your database server as the size of your Hyperion Financial Data Quality Management (FDMEE) schema increases due to these tables. If the size of your FDMEE schema becomes very huge, it can not only cause performance issue during data load process but also can make maintaining FDMEE schema backup difficult for application disaster and recovery purposes, as you always need at least one healthy backup of FDMEE schema to deal with issues like application corruption. 

The good thing is you can purge these Hyperion Financial Data Quality Management (FDMEE) tables as per your requirement. Almost all organizations have a data retention policy defined for their applications. For example, some organizations keep only past 1 Financial Year (FY) data apart from current FY in the live tables and delete the rest but that should be completely your Business team call.

You need to evaluate the impact of performing FDMEE data purging activity in terms of audit, drill back requirements, etc. but if your business team is comfortable that they no longer require the data then there should be no adverse effects of removing this data other than it will no longer be available to you, because these processed data is also stored in the respective target applications' databases apart from FDMEE data tables.

In Oracle Hyperion Financial Data Quality Management (FDMEE), Oracle has supplied some default purge scripts for your use according to your retention policies. You can execute these scripts to cleanup runtime artifacts, such as Staging tables, Process tables or Inbox / Outbox folders.

These scripts can be found in:

Workspace—Navigate—Administer—Data Management--On the Workflow tab—Other—Script Execution link. Then select the ‘System Maintenance Tasks’ from the ‘Custom Script Group’ dropdown menu.

System Maintenance Tasks group contains the following purge scripts to purge standard FDMEE tables and folder:

1. Maintain Application Folder
2. Maintain EBS GL Balances Table
3. Maintain FDMEE Data Tables
4. Maintain ODI Session Data
5. Maintain PeopleSoft Ledger Table
6. Maintain Process Table

In this first post, we will focus on the ‘Maintain FDMEE Data Tables’ task only. We will see how to manually execute ‘Maintain FDMEE Data Tables’ purge script. Other purge scripts of Hyperion Financial Data Quality Management (FDMEE) System maintenance tasks are covered in subsequent posts of this blog series, which you can find on below links:

FDMEE: System Maintenance Tasks: Maintain ODI Session Data

FDMEE: System Maintenance Tasks: Maintain Application Folder

FDMEE: System Maintenance Tasks: Maintain Process Tables

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

Important Note: 

• This post has been written and associated activities have been demonstrated on Oracle Hyperion Financial Data Quality Management (FDMEE) version 11.1.2.4.210.
• The demonstrating Hyperion environment has Oracle database server 12.2.0.2 (18c) as backend database. 
• There is no outage required on the FDMEE application to execute these purge scripts. But it is advisable not to run any data loads during the purge activity.
• Don't forget to take complete FDMEE schema backup before attempting to run these purge scripts. 

Maintain FDMEE Data Tables

Maintain FDMEE Data Tables purge script deletes the data from the following tables:

• TDATAMAPSEG
• TDATASEG
• TLOGPROCESS
• TDATAARCHIVE
• TDATACHECK
• TDATAMEMOITEMS

Brief description of these FDMEE tables:

TDATAMAPSEG: When you import data, the mappings which you use to transform data are archived in the TDATAMAPSEG table for each point of view (POV). After the mappings have been processed in the TDATAMAP_T temporary table they are archived in the TDATAMAPSEG table. If you use a large number of maps, this table grows in size over a period of time and can create a performance issue during the data import process.

TDATASEG: The TDATASEG table is used to store the data loaded by the user, and the transformation between the source dimension members and results of the mapping process. This table stores the actual data. After the import stage and validate stage, the data is moved from the temporary table TDATASEG_T to TDATASEG. Only in a successful data rule execution, data transformations stored in TDATASEG_T is copied over to TDATASEG. A very large TDATASEG table can impact your FDMEE performance. During the data load run, if any query is made to the large-sized TDATASEG table, it may take a long time to complete the query. 

TLOGPROCESS: This table is used to store the workflow process status for a location, category, and period.

TDATAARCHIVE: Information for various FDMEE files/documents/data is stored in table TDATAARCHIVE.

TDATACHECK: Results of Validation rules/Data load rules are stored in table TDATACHECK.

TDATAMEMOITEMS: This table stores your Memo items.

When you will execute ‘Maintain FDMEE Data Tables’ purge script, it seeks the following input parameters:

• Target Application
• Start Period
• End Period
• Category

Here Start Period and End period specify the dates for which data will be deleted.

Before proceeding further with purging steps, I would suggest you to maintain an excel sheet to track pre-purge and post-purge records count for various tables and Hyperion Financial Data Quality Management (FDMEE) schema size to see how much space has been freed up post purge activity. Something like below:

Pre-purge and post-purge records count of these FDMEE tables can be fetched by logging to FDMEE schema and running simple SELECT queries:

select count(*) from TDATAMAPSEG;

select count(*) from TDATASEG;

select count(*) from TLOGPROCESS;

select count(*) from TDATAARCHIVE;

select count(*) from TDATACHECK;

select count(*) from TDATAMEMOITEMS;

Step-by-Step process to execute ‘Maintain FDMEE Data Tables’ purge script

1- Log in to Workspace with Admin credentials.

2- Open Oracle Hyperion Financial Data Quality Management (FDMEE) console (Data Management) from Workspace and on the Workflow tab, under Other, select Script Execution:

3- In Script Execution, and then in Custom Script Group, select System Maintenance Tasks.

4- From the Scripts grid, select Maintain FDMEE Data Tables script and Click Execute.

5- It will prompt to enter following parameter values on the Execute Script screen.

• Target Application
• Start Period
• End Period
• Category

6- Click on the search icon for Target Application. 

Select the Target Application name for which you want to purge tables and click OK.

7- Target Application will be selected in the parameters tab.

Now click on the search icon for the Start Period.

8- Select the Start Period for which you want to purge tables and click OK.

For example, If you want to purge all data from Nov-2014 till Oct-2018, then select Start Period as NOV_2014 as shown below:

9- Start Period will be selected in the parameters tab.

Now click on the search icon for the End Period.

10- Select the End Period as OCT_2018 as per the data deletion range Nov-2014 to Oct-2018 and click OK.

11- End Period will be selected in the parameters tab.

Now click on the search icon for the Category.

12- Select the Category (Scenario) for which you want to purge tables and click OK.

13- Category will be selected in the parameters tab.

14- Keep Execution mode selected as Online. The online method processes the report immediately.

Execution modes:

online  — ODI processes the data in sync mode (immediate processing).

offline — ODI processes the data in async mode (runs in background).

15- Optionally, If you want to schedule the purge script run rather than running it instantly, click on Schedule option and enter the scheduling details as per your requirement.

16- If you want to instantly run the purge script, then directly click on OK (not on the Schedule option).

You will see a message informing the process ID of your script run as shown below:

17- To see the status of your script execution, click on the Process Details link under the Workflow tab—Monitor—Process Details. 

18- Above we can see our script with Process ID 7805 executed successfully. 

Click on the Show button for Process ID 7805 to open the corresponding log file.

In the log file, look for the message "Maintain FDMEE Data Tables: Completed" to confirm that script has run successfully without any error. If you encounter any errors, troubleshoot them based on the cause mentioned in the log file. In our case, we see no issue in the script execution.

Similarly, you need to run this purge script selecting the same application and other categories. Note, you need to repeat all of the above steps for various combinations of target applications and categories where your FDMEE data loads are running. 

Once you are done with running the Maintain FDMEE Data Tables purge script for different target applications and categories combinations, check post purge records count of FDMEE tables to see how many records have been deleted. It will give you a fair idea on the growth rate of these tables and accordingly you can plan and decide the frequency of running these purge scripts. 

Now its time to check your Oracle Hyperion Financial Data Quality Management (FDMEE) schema size to see how much space has been cleared as a result of tables purging.

Are you surprised to see that FDMEE schema size is still the same despite deleting millions of records from FDMEE tables? 

Don't worry! You just need to inform your DBA and ask them to do table reorganization in order to reclaim freed space. 

Actually, when you delete the data from the Oracle tables, the data blocks will go onto the free list for that table. That free space will be used for the subsequent insert and update operations into the table. You need to perform table reorganization to reclaim that free space. 

Note, reorganization of tables requires outage in your Hyperion environment so you have to stop all your application services prior to the reorganization activity to be performed by your DBA.

Don't forget to ask your DBA team to 'Rebuild Indexes' in your Hyperion Financial Data Quality Management (FDMEE) database after performing the 'Tables Reorganisation' activity. 

If you miss rebuilding indexes after tables reorganization activity, your FDMEE data load job will be taking longer than normal to complete.  

Index rebuild becomes absolutely necessary especially when you delete millions of records from FDMEE tables as part of running FDMEE purge scripts because the corresponding indexes of those deleted records go in the unusable state after tables reorganization activity. Due to this bulk deletion of records, indexes become fragmented causing indexes taking more time to give the output and hence delaying your FDMEE data load job.

You will observe the following error during your FDMEE data load run if you do not rebuild database indexes after tables reorganization activity:

ORA-01502: index 'FDMEE_ODI.PK_TLOGPROCESS' or partition of such index is in unusable state.

Once both the DBA tasks (Tables reorganization + Rebuilding Indexes) are completed, compare pre and post purge size of your FDMEE schema to see how much has the schema size reduced. You might see a significant reduction in FDMEE schema size if you are doing this purging activity very first time or for a data deletion range covering many Financial Years.

Maintain FDMEE Data Tables purge script can also be automated to run using Executescript batch file located in the folder: 

<drive>\Oracle\Middleware\user_projects\epmsystem1_fdm\FinancialDataQuality.

We will cover the automation of Maintain FDMEE Data Tables purge script in a separate post of this blog series. 

That's all for this post. 

I hope this article has helped you. 

Your suggestions/feedback are most welcome.

Keep learning and Have a great day!!!