Automation of 'Maintain ODI Session Data' System Maintenance Task

In 'FDMEE System Maintenance Tasks' blog series, we have already covered the following topics :

Manual implementation:

FDMEE: System Maintenance Tasks: Maintain FDMEE Data Tables
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

Automation:

Automation of 'Maintain FDMEE Data Tables' System Maintenance Task

If you have not already gone through the above articles, I would suggest you read them first to get a comprehensive understanding of all the Hyperion Financial Data Quality Management (FDMEE) System Maintenance Tasks.

Note:

• The demonstrating Hyperion environment has the 'Windows Server 2012 R2 Standard' operating system. 
• The demonstrating Hyperion environment has 'Oracle database server 12.2.0.2 (18c)' as backend database. 
• This post has been written and associated activities have been demonstrated on Oracle FDMEE version 11.1.2.4.220.
• There is no outage required on the FDMEE application to execute these scripts. But it is advisable not to run any data loads during the activity.
• Don't forget to take complete FDMEE schema backup before attempting to run these scripts. 
• Different paths and folder names mentioned in this post may slightly vary for different Hyperion setups but at large it should be the same.  

Command syntax of FDMEE batch utility executescript.bat:
----------------------------------------------------------------------------------
executescript <username> <password> "<script name>" "<parameters>" <execution mode>

Where:

• <username>: is your FDMEE user running the script
• <password>: can be either the hard-coded password or a file with encoded password (-f:filename)
• <script name>: is the name of the script registered in Script Registration page (not the python script filename). You find the script name here: Navigate-Administer-Data Management-Setup tab-Script Registration
• <parameters>: are the script parameters in the form "Display Name=Parameter Value"
• <execution mode>: is either SYNC (synchronous) or ASYNC (asynchronous). SYNC—Process runs immediately and control returns when process completes. ASYNC—When the ODI job is submitted, control returns. The load process continues to execute in ODI.

Now the command syntax in more detailed form will look like below:

executescript <username> <password> "<script name>" "<parameterdisplay1=value1>" "<parameterdisplay2=value2>" <SYNC | ASYNC>

If you are going to use an encrypted password (which is the recommended best practice), following will be the command syntax:

executescript <username> <password | -f:password.txt> "<script name>" "<parameterdisplay1=value1>" "<parameterdisplay2=value2>" <SYNC | ASYNC>

You will find the configuration steps for password encryption in the FDMEE admin guide as shown below:

You need to ensure that your encrypted password.txt file is placed in the location defined in Navigate-Administer-Data Management-Setup tab-System Settings-Encrypted Password Folder.

Automating 'Maintain ODI Session Data' System Maintenance Task:

-------------------------------------------------------------------------------------------------
In this post, we will see how to automate 'Maintain ODI Session Data' System Maintenance Task. I suggest you to first read this post FDMEE: System Maintenance Tasks: Maintain ODI Session Data for theoretical understanding, manual implementation, and post-execution activities of 'Maintain ODI Session Data' System Maintenance Task.

When you execute ‘Maintain ODI Session Data’ purge script manually from Data Management console, it seeks one input parameter: Days to keep ODI Sessions

To run ‘Maintain ODI Session Data’ purge script using executescript.bat command-line utility below is the command syntax:

executescript username password "MaintainODISessionData" "Days to keep sessions=EnterDaysInNumber" SYNC

You need to enter the number of days you want to retain data for in ODI tables.

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

Suppose using FDMEE Admin user, you want to run 'Maintain ODI Session Data' purge script for 90 days (3 months) i.e. you want to delete all the ODI Sessions details keeping only last 90 days (3 months) details.

In order to achieve the above goal, create a batch script name 'MaintainODISessionData.bat' under folder E:\Admin\scripts having the following content: 

MaintainODISessionData.bat:
--------------------------------------------------
@echo off

REM Set the value for the variables  

set "username=Admin"
set "password=YourAdminPassword"

set "DaysToKeepODISessions=90"

set "logfile=E:\Admin\scripts\MaintainODISessionData.log"

REM Clear the content of existing logfile
rem.> %logfile%

REM Navigate the executescript.bat utility folder
cd "E:\apps\OracleEPM\Middleware\user_projects\epmsystem_fdm\FinancialDataQuality"

REM Run executescript to perform "MaintainODISessionData" for the mentioned days to keep ODI Sessions 

> %logfile% (

echo call executescript %username% %password% "MaintainODISessions" "Days to keep sessions=%DaysToKeepODISessions%" SYNC

call executescript %username% %password% "MaintainODISessions" "Days to keep sessions=%DaysToKeepODISessions%" SYNC

)

cd E:\Admin\scripts

Change the paths and variables mentioned in the script as per your requirement and FDMEE application setup.

Now login to your FDMEE server, open an Administrator Command prompt and run MaintainODISessionData.bat.

After completion of the script your MaintainODISessionData.log will be generated in below format:

To verify whether your process ID has successfully completed or not, you can navigate to Process Details page (In Data Management console, click on the Process Details link under the Workflow tab—Monitor—Process Details):

Click on the 'Show' button against the Process ID to open the corresponding log file to see more details. 

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