FDMEE: VBScript Error: ActiveX component can't create object: 'fdmapi.clsAppServerDM'

Hi Friends,

In Oracle Hyperion Financial Data Quality Management (FDMEE), while running data load jobs from your source system to Oracle Hyperion target applications, you might have encountered following error in data load run log:

Microsoft VBScript runtime error: ActiveX component can't create object:'fdmapi.clsAppServerDM'

Snippet from data load run log:

2020-04-23 05:11:06,542 DEBUG [AIF]: Comm.executeVBScript - START

2020-04-23 05:11:06,542 INFO  [AIF]: Executing the following script: \\Network_path\FDMData\data\scripts\event\AftValidate.vbs

2020-04-23 05:11:06,542 DEBUG [AIF]: The command to be executed is:

cscript \\Network_path\FDMData\data\scripts\event\AftValidate.vbs "275" "****" "E%3A%5Capps%5COracleEPM%5CMiddleware%5Cuser_projects%5Cepmsystem_fdm" "%25EPM_ORACLE_HOME%25%2F..%2Fjdk160_35" "ORAOLEDB.ORACLE"

Microsoft (R) Windows Script Host Version 5.8

Copyright (C) Microsoft Corporation. All rights reserved.

2020-04-23 05:11:06,590 ERROR [AIF]: The script has failed to execute:

2020-04-23 05:11:06,594 INFO  [AIF]: 
\\Network_path\FDMData\data\scripts\event\AftValidate.vbs(17, 1) Microsoft VBScript runtime error: ActiveX component can't create object: 'fdmapi.clsAppServerDM'

2020-04-23 05:11:06,594 FATAL [AIF]: Error in CommData.validateData

Traceback (most recent call last):

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

  File "<string>", line 453, in executeScript

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

RuntimeError: 0

Actually, this error is thrown when Oracle Hyperion Financial Data Quality Management (FDMEE) processes VB code in VB script AftValidate.vbs or any other VB Script for that matter.

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.
• Different paths and folders' names mentioned in this blog may slightly vary for different Hyperion setups but at large, it should be the same. 
• 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.
• The workaround of this issue requires Hyperion Financial Data Quality Management (FDMEE) services to be restarted so inform your users accordingly. 

Solution:

In order to fix this error, you need to execute the RegisterVBAPI.vbs script to register the FDMEE Visual Basic (VB) API in your FDMEE server. Oracle Hyperion Financial Data Quality Management (FDMEE) does not automatically register the Visual Basic API DLL at the time of installation. Therefore, if you use VB (Visual Basic) scripts in your FDMEE application then you have to manually run RegisterVBAPI.vbs to register the VB API before you can use it in your FDMEE vbs scripts. This part is also covered in the Oracle Hyperion Financial Data Quality Management (FDMEE) administration guide.

Note: Preferably, as a leading practice, you should use Jython in FDMEE and not VB (Visual Basic) as Oracle is moving away from its Microsoft dependency with each new update.

Steps to register the VB (Visual Basic) API DLL manually in Oracle Hyperion Financial Data Quality Management (FDMEE) server:

1- Login to all the FDMEE servers of your Hyperion environment and perform below steps on each one of them.

2- Open a command prompt and navigate to path: E:\apps\OracleEPM\Middleware\EPMSystem11R1\products\FinancialDataQuality\lib\Windows

3- Execute RegisterVBAPI.vbs script as mentioned below.

The RegisterVBAPI.vbs script requires your Oracle Instance path (wrapped in quotes) to be passed as input parameter to it. So enter the following command:

RegisterVBAPI.vbs "E:\apps\OracleEPM\Middleware\user_projects\epmsystem_fdm"

After running RegisterVBAPI.vbs script, first, you will see the below message. Click OK.

Then you will see 2^nd message as shown below. Click OK.

3rd and the last message will appear as shown below. Click OK.

You need to make sure RegisterVBAPI.vbs script run is successful. To confirm the same, above message: “FDMEE VB API Registration Finished” must appear on your screen. If you don’t see the above message, it means VB (Visual Basic) API DLL is not successfully registered and you may again face data load failure reporting the same error.

Once you are done with running RegisterVBAPI.vbs script, restart Hyperion Financial Data Quality Management (FDMEE) service in all your FDMEE servers.

Now retry to run your FDMEE data load job. It should be working fine this time. 

You may also like to refer below Oracle Document for related details:

Doc ID 2573674.1

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 Process Tables

Hi Friends,

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

If you have not yet read the previous three posts of this blog series, I would suggest you to go through them on below links 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

FDMEE: System Maintenance Tasks: Maintain Application Folder

In this fourth post, we will focus on the ‘Maintain Process Tables’ task and will see how to manually execute ‘Maintain Process Tables’ 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 Oracle Hyperion Financial Data Quality Management (FDMEE) Process Tables.
• Don't forget to take complete Oracle Hyperion Financial Data Quality Management (FDMEE) schema backup before attempting to run these purge scripts.

Maintain Process Tables

The purpose of running Maintain Process Tables script is to purge old/historical Process IDs and related details from associated execution tables in Oracle Hyperion Financial Data Quality Management (FDMEE). This will not only reduce the overall FDMEE schema size but also help improve the performance of your FDMEE application. 

The “Maintain Process Tables” script cleans up the Process IDs in the Process details related FDMEE tables by the number of days you enter as input parameter while running the script.

It deletes entries from following Oracle Hyperion Financial Data Quality Management (FDMEE) execution tables (AIF_ tables):

• AIF_PROCESSES
• AIF_PROCESS_DETAILS
• AIF_PROCESS_LOGS
• AIF_PROCESS_PARAMETERS
• AIF_PROCESS_PERIODS
• AIF_PROCESS_STEPS
• AIF_BAL_RULE_LOADS
• AIF_BAL_RULE_LOAD_PARAMS
• AIF_BATCH_JOBS
• AIF_BATCH_LOAD_AUDIT
• AIF_TEMP

Before proceeding further, with purging steps, I would suggest you to check and note down the pre-purge FDMEE schema size to see how much space has been freed up post purge activity.

Step-by-Step process to execute ‘Maintain Process 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 Process Tables script and Click Execute.

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

Days to keep records

Enter the number of days you want to retain entries for in FDMEE Process Tables from today’s date. For example, if you want to delete all the entries except all 3 months (90 days) entries in FDMEE Process Tables, enter 90 in Days to keep records.

Note: Maintain Process Tables scrip takes some time to execute so it’s 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.

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

Click on the Show button for the Process ID of your script run to open the corresponding log file.

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

Once you are done with running the Maintain Process Tables purge script, you can check your Hyperion Financial Data Quality Management (FDMEE) schema size to see how much space has been cleared as a result of FDMEE Process Tables purging.

Here it’s important to note that after running the FDMEE Process Tables purge script, your DBA needs to do FDMEE tables reorganization activity 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 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 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_AIF_*' 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 Hyperion Financial Data Quality Management (FDMEE) schema to see how much has the schema size reduced. 

Maintain Process 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 Process 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

EPMA: Transform Classic to EPMA Wizard: Communication error occurred: %1

Hi Friends,

In this post, we will discuss an Oracle Hyperion Enterprise Performance Management Architect (EPMA) error “communication error occurred: %1” which we encountered while using ‘Transform classic to EPM Architect Wizard’ in Workspace and will see how it got fixed.

Important Note: 

• This post has been written and associated activities have been demonstrated on Oracle Hyperion Enterprise Performance Management Architect (EPMA) version 11.1.2.4.000.2614
• 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.

Problem description

Actually, as part of Oracle Hyperion Financial Management (HFM) application cloning activity from PROD to TEST, we imported our PROD HFM app (an EPMA app) to the TEST environment. 

Then to complete the migration, we were trying to convert the imported Classic HFM app to EPMA in our TEST environment. But when we opened ‘Transform classic to EPM Architect Wizard’ from Workspace (Launch Workspace -> Navigate ->Administer-> Transform Classic to EPM Architect), the application summary page first took 30 mins showing the “Loading….” message. Eventually, it threw this below message:

Error: communication error occurred: %1

The following images will show you the issue replication:

Troubleshooting

We immediately checked the Oracle Hyperion Enterprise Performance Management Architect (EPMA) log: (E:\apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\EpmaWebReports0\logs\EPMA.log) to see EPMA web-tier activities during the live issue and noticed following error message appeared multiple times:

[SRC_CLASS:com.hyperion.awb.web.util.MessageRetriever] [SRC_METHOD:getMessage] [[java.util.MissingResourceException: Can't find resource for bundle java.util.PropertyResourceBundle, key The application ID (HSS) for application name: ESBAPP............................

Point to note was that 39 of the registered Essbase apps were causing an error like above. 

Actually, the Application Summary page of ‘Transform classic to EPM Architect Wizard’ shows the applications list to which the user has access to transform. If the ‘logged in user’ is not provisioned as ‘Application Administrator’ for an application; the user will not be able to migrate that application to EPMA. For all those applications which cannot be transformed, an explanation is provided in the ‘Comment’ column. Following are a few messages which are normally observed in the 'comment' column of Application Summary page for the apps which are nontransformable:  

• Application view already exists
• User must be provisioned as Application Administrator for this application.
• Connection to the Essbase server failed.

The Application Summary page’s applications list is extracted from the Hyperion Shared Services server/schema. It displays all the applications of your Oracle Hyperion environment like Hyperion Essbase, Hyperion Planning, Hyperion HFM, etc. which are registered with Oracle Hyperion Shared services.

Before populating the applications list for the transformation, it also validates that application by connecting to the respective application server (like Essbase Server, HFM Server), EPMA application library, and Hyperion Shared Services provisioning of that application, etc. So if the number of applications is very large, the Application Summary page is supposed to take some time in order to populate the list. 

In our case, Transform classic to EPM Architect Wizard was encountering a communication error while fetching the details of those 39 Essbase apps from Oracle Hyperion Shared Services and validating the same with the Hyperion Essbase server. For sure there was some conflict that was delaying the process of populating the applications list eventually causing that communication error after 30 mins of loading. The reason why it was happening is still unknown (let me know your thoughts on the same). 

Solution that worked for us:

We first tried with restarting all the Hyperion services including EPMA and Hyperion Essbase services but that didn't help.

As the error in EPMA.log was pointing to Essbase apps, we next focussed our attention to the same and noticed there were a total of 86 Essbase cubes/apps in our environment including so many ‘not so important’ cubes created for various testing, data validation etc. purposes.

Deleting unwanted apps from your Oracle Hyperion environment not only improves the apps loading time in Application Summary page of Transform Classic to EPM Architect Wizard but also saves your server resources from being unnecessarily consumed. 

So we triggered an Essbase apps cleanup drive and deleted around 40 unwanted Essbase applications after consulting their usability with business users and applications team (similarly you can plan for deleting other unwanted apps like HFM too).

Post this unwanted Essbase apps cleanup drive, we again ran ‘Transform Classic to EPM Architect Wizard’ and observed that the Application Summary page got populated with all the applications in less than 4 mins as shown below:

Thus the communication error ‘communication error occurred: %1’ got resolved for us.

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