FDMEE: EPMHFM-66076: HFM Server unavailable,connection could not be established

Hi Friends,

In this post, we will explore error EPMHFM-66076: HFM Server unavailable: connection could not be established which was encountered while running data load in Oracle Hyperion Financial Data Quality Management (FDMEE) from SAP (source) to Oracle Hyperion Financial Management (HFM) (target) application.

Here I want to emphasize the point that this issue had occurred in our newly built Oracle Hyperion environment, so you can think of the solution given below as a ‘MUST DO’ activity once you are done with the installation and configuration of any new Hyperion environment.

You can refer the following Oracle Hyperion Financial Management (HFM) documentation for details:

https://docs.oracle.com/cd/E57185_01/HFMAD/apbs06s01.html

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.
• Hyperion Financial Management (HFM) application servers and Hyperion Financial Data Quality Management (FDMEE) application servers mentioned in this post have Windows Server 2012 R2 operating system.
• It's very important to test any workaround/fix first in a non-Prod environment, and move/apply it to PROD only when you are fully sure about its workability.
• There will be outage required on Hyperion Financial Management (HFM) and Hyperion Financial Data Quality Management (FDMEE) applications, so make sure you inform your user base in advance before you apply this fix in your Hyperion environment. 

Description of Issue:

While running the Oracle Hyperion Financial Data Quality Management (FDMEE) data load from the SAP source system to target Oracle Hyperion Financial Management (HFM) application, we noticed that all loads are getting failed with following common error message reported in FDMEE data load run logs:

2020-04-23 03:12:48,882 DEBUG [AIF]: intersectionCheckXml(\\NetworkPath\EPM_Fileshare\FDMEE_DATA_DEV\FDMData\outbox\HFMAPP_VALIDATE.xml, false, 10000)

2020-04-23 03:12:48,866 ERROR [AIF]: Error: Server SERVERNAME is unavailable, connection could not be established.

2020-04-23 03:12:48,882 DEBUG [AIF]: oracle.epm.fm.common.exception.HFMException: EPMHFM-66076: Server SERVERNAME is unavailable, connection could not be established.

Caused by: org.apache.thrift.transport.TTransportException: java.net.BindException: Address already in use

2020-04-23 03:12:48,882 ERROR [AIF]: HFM Server is unable to process the number of requests.  Please review the data and try again.

2020-04-23 03:12:48,882 ERROR [AIF]: HFMException: Server SERVERNAME is unavailable, connection could not be established.

2020-04-23 03:12:48,906 ERROR [AIF]: The script has failed to execute:

2020-04-23 03:12:48,906 FATAL [AIF]: Error in Comm.executeJythonScript

Traceback (most recent call last):

  File "<string>", line 557, in executeJythonScriptFile "E:\apps\OracleEPM\Middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_VALIDATE.py", line 108, in <module>raise Exception("There was an exception in intersectionCheckXML method")

Exception: There was an exception in intersectionCheckXML method

2020-04-23 03:12:48,910 FATAL [AIF]: Error in CommData.validateData

Traceback (most recent call last):

  File "<string>", line 4499, in validateData

  File "<string>", line 578, in executeVBScript

  File "<string>", line 557, in executeJythonScript

The error message indicates that Python script HFM_VALIDATE.py is unable to connect to your Hyperion Financial Management (HFM) application servers in order to perform intersection validation of your target Hyperion Financial Management (HFM) application for the data load run.

In initial checks, we found that both the Hyperion Financial Management (HFM) application servers are up and running and HFM apps are accessible from the Workspace without any issue. 

Cause:

Actually whenever there is a heavy load or large data volume processing on the Windows servers (HFM and FDMEE servers), the default port limit may be exceeded causing disconnection to the server as the server will stop receiving any new TCP/IP connections.

So the port limit (MaxUserPort) and wait time (TcpTimedWaitDelay) of the server has to be fine-tuned in order to fulfill all connection requests coming to the server. 

MaxUserPort 

The MaxUserPort parameter sets the maximum number of user ports that TCP/IP can assign when an application requests an available user port from the system. You can adjust the value of this parameter to optimize network throughput. The MaxUserPort parameter applies only to the Windows operating system. The default start port value is 5000 and the maximum end port value you can put is 65535. The recommended value is 65534. This range is in addition to well-known ports that are used by services and by applications.

TcpTimedWaitDelay 

The TcpTimedWaitDelay value determines the length of time that a connection stays in the TIME_WAIT state before being closed. While a connection is in the TIME_WAIT state, the socket pair cannot be reused. So you can configure the TcpTimedWaitDelay to cause TIME_WAIT sockets to clear more quickly. The Default TcpTimedWaitDelay value is 240 seconds (4 mins). The recommended value is 30 seconds.

Solution:

If you see the above mentioned error messages in Hyperion Financial Data Quality Management (FDMEE) data load logs, then perform the following steps to fix this issue:

1- Login to all your Hyperion Financial Management (HFM) application servers and Hyperion Financial Data Quality Management (FDMEE) servers one by one and make the following registry changes.

2- First take a backup of the server registry.

3- Open Windows Registry Editor and goto:

 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Parameters

Note: By default, these two keys ("MaxUserPort" and " TcpTimedWaitDelay") does not exist in the Windows server registry.

4- Create a new DWORD with name ‘MaxUserPort’ and value ‘65534' (=00fffffe in hexadecimal) as shown below.

Right-click on Parameters and select New—DWORD (32-bit) value

Enter the name as MaxUserPort 

Right-click and select Modify

Enter the value as 0000fffe (=65534 in decimal)

MaxUserPort entry is added in the registry.

5- Now create a new DWORD with the name ‘TcpTimedWaitDelay’ and value ’30' (=0000001e in hexadecimal) as shown below.

Right-click on Parameters and select New—DWORD (32-bit) value

Enter the name as TcpTimedWaitDelay

Right-click and select Modify

Enter value as 0000001e (=30 in decimal)

TcpTimedWaitDelay entry is added in registry.

6- Once you are done with adding above two registry entries in all the HFM and FDMEE servers, reboot all the HFM and FDMEE servers for the changes to take effect.

7- Once the servers are up, start HFM and FDMEE services on respective servers.

8- Now trigger your FDMEE data load to HFM and you will not see the ‘HFM Server unavailable’ error any more.

As per Oracle update, this issue has been addressed in unpublished Bug 25616928 - minimize calls to hfm metadata api during intersection validation. So you should try to install Oracle Hyperion Financial Data Quality Management (FDMEE) Release 11.1.2.4.210 Patch Set Exception (PSE): 25616936 in your Oracle Hyperion environment and check whether the issue gets fixed or not.

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!!!

Read More

FDMEE: System Maintenance Tasks: Maintain Application Folder

Hi Friends,

This is the third post under Oracle Hyperion Financial Data Quality Management (FDMEE): System Maintenance Tasks series where we are going to cover Maintain Application Folder script.

If you have not yet read previous two posts of this blog series, I would suggest you to go through them here to get a comprehensive picture of Oracle Hyperion Financial Data Quality Management (FDMEE) System Maintenance Tasks:

FDMEE: System Maintenance Tasks: Maintain FDMEE Data Tables
FDMEE: System Maintenance Tasks: Maintain ODI Session Data

In this third post, we will focus on the ‘Maintain Application Folder’ task and will see how to manually execute ‘Maintain Application Folder’ purge script.  

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 Oracle Hyperion Financial Data Quality Management (FDMEE) application to execute these purge scripts. But it is advisable not to run any data loads during the purge activity.
• Based on the data retention policy of your organization, you should pre-decide how many days of data you want to retain for FDMEE Application folders.
• Don't forget to take complete Oracle Hyperion Financial Data Quality Management (FDMEE) schema backup before attempting to run these purge scripts.

Maintain Application Folder

Whenever you load data (files) through Hyperion Financial Data Quality Management (FDMEE), there are three types of files stored:

1. The file which you uploaded, is stored in the inbox directory
2. The export file, which is stored in the outbox directory
3. The data file, which is stored in the data directory.

All these files remain on your FDMEE server or network location (server) and often gradually cause your disk storage space to get full. These files themselves are not that important, as the data is stored in both, the FDMEE database as well as your target EPM application (Essbase, Planning, HFM, etc.) database.

The “Maintain Application Folder” script is used to clean up FDMEE Inbox, Outbox, and Data (excluding Data\Scripts subfolder) folders by the number of days entered as an input parameter for these folders.

Hyperion Financial Data Quality Management (FDMEE) checks the inbox and outbox subfolders under the respective folders and deletes any files present over there. In the data subfolder, FDMEE skips the scripts subfolder because it contains your custom, event, and import scripts. Cleanup also includes Reports and Logs under the Outbox folder that are associated with Process IDs.

Maintain Application Folder (deleting old files from FDMEE inbox, outbox and Data directories) script saves you a lot of disk space as these folders' size gets bigger with time, if not housekept regularly.

When you execute ‘Maintain Application Folder’ purge script, it seeks following input parameters to be entered:

• Target Application
• Days to keep Inbox directory
• Days to keep Outbox directory
• Days to keep Data directory

If the Days to keep value is not specified for a specific folder (Inbox/Outbox/Data), FDMEE skips that folder for file deletion.

Step-by-Step process to execute ‘Maintain Application Folder’ purge script

1- Log in to Workspace with Admin credentials.

2- Open 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 Application Folder script and Click Execute.

5- It will prompt to enter the following input parameters: 

6- Click on the search icon for Target Application and select the application name from the list.

7- Now enter Days to keep for Inbox, Outbox and Data directories.

For example, to retain files under these three folders for the last 3 months, enter 90 (days):

8- 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).

7- 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.

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

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

10- Above we can see our script with Process ID 8144 has executed successfully. 

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

In the log file, look for the message "Maintain Application Folder: 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.

Note, you need to repeat all of the above steps for other target applications too where your FDMEE data loads are happening. 

Once you are done with running the Maintain Application Folder purge script for all of your selected target applications, you can check FDMEE Inbox, Outbox and Data directories to verify the deleted files.

Maintain Application Folder purge script can 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 part 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!!!    

Read More

FDMEE: System Maintenance Tasks: Maintain ODI Session Data

Hi Friends,

This is the second post under Oracle Hyperion Financial Data Quality Management (FDMEE): System Maintenance Tasks series where we are going to cover Maintain Oracle Data Integrator (ODI) Session Data script.

If you have not read the first post (Maintain FDMEE Data Tables) of this blog series yet, I would suggest you to first go through that post where Maintain FDMEE Data Tables script along with introductory details of Oracle Hyperion Financial Data Quality Management (FDMEE) System Maintenance Tasks has been covered in detail.

In this post, we will focus on the ‘Maintain ODI Session Data’ task and will see how to manually execute ‘Maintain ODI Session Data’ purge script. Other purge scripts of Oracle 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 Application Folder

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 Oracle Hyperion Financial Data Quality Management (FDMEE) application to execute these purge scripts. But it is advisable not to run any data loads during the purge activity.
• Based on the data retention policy of your organization, you should pre-decide how many days of Oracle Data Integrator (ODI) sessions’ data you want to retain for the analysis of Oracle Data Integrator (ODI) log history.
• Don't forget to take complete Oracle Hyperion Financial Data Quality Management (FDMEE) schema backup before attempting to run these purge scripts. 

Maintain ODI Session Data

It is very important to regularly purge the Oracle Data Integrator (ODI) sessions log to reduce the size of the ODI session tables and so the Oracle Data Integrator (ODI) work repository size and improve the performance of Oracle Data Integrator (ODI) studio. 

Maintain ODI Session Data script cleans up the Oracle Data Integrator (ODI) Sessions logs that are associated with the Process IDs by the number of days you enter as input parameter while running the script.

Primarily Maintain ODI Session Data script deletes log entries from following Oracle Data Integrator (ODI) tables (SNP_ tables):

• SNP_SESSION 
• SNP_EXP_TXT
• SNP_PARAM_SESS
• SNP_VAR_SESS
• SNP_SEQ_SESS
• SNP_SESS_STEP
• SNP_SESS_TASK
• SNP_SESS_TASK_LOG
• SNP_TASK_TXT
• SNP_STEP_LOG
• SNP_SESS_STEP_LV
• SNP_SESS_TASK_LS
• SNP_SESS_TXT_LOG
• SNP_EXP_TXT_HEADER
• SNP_STEP_REPORT

These SNP* tables consume a lot of space in your FDMEE schema.

Before proceeding further with purging steps, I would suggest you to maintain an excel sheet to track pre-purge and post-purge FDMEE schema size to see how much space has been freed up post purge activity. Something like below:

Step-by-Step process to execute ‘Maintain ODI Session Data’ 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 ODI Session Data script and Click Execute.

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

Days to keep ODI Sessions

Enter the number of days you want to retain data for in live ODI tables.

Note: Maintain ODI Session Data scrip takes some time to execute so its advisable not to purge too much in one go.

6- 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).

7- 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.

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

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

10- Above we can see our script with Process ID 8124 executed successfully. 

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

In the log file, look for the message "Maintain ODI Sessions: 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.

Once you are done with running the Maintain ODI Sessions Data purge script, now it’s time to check your FDMEE schema size to see how much space has been cleared as a result of ODI tables purging.

Are you surprised to see that FDMEE schema size is still the same despite deleting a significant number of records from Oracle Data Integrator (ODI) 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 application DBA.

Don't forget to ask your DBA team to 'Rebuild Indexes' in your 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 type of error during your FDMEE data load run if you do not rebuild database indexes after tables reorganization activity:

ORA-01502: index 'FDMEE_ODI.PK_SNP_*' 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.

Maintain ODI Session Data 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 ODI Session Data 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!!!

Read More

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!!!

Read More