Monday, May 25, 2020

// // 2 comments

Automation: Batch script to add today's date and time in file/folder name

Topic: Batch script to create files/folders with current (today) date and time in their name

Hi Friends,

In this post, we will see a batch script to add the current date and time in Windows files or folders' names.

This batch script is very useful especially when you need to take periodic backups of Hyperion applications, LCM exports, Essbase Level 0 data exports, Oracle database schemas, creating log files, etc. This backup process is part and parcel of any administration work including Hyperion Administration.

In Windows system, manipulating date and time value is not that straight-forward compared to Linux/Unix systems.

Concept:

As you know, most of the time following commands are used to display the current date and time in Windows system when you quickly want to check the same:

date /t
time /t

But when it comes to batch scripting where you want to use date and time values, there is a problem with these commands. 

date /t command returns the current date using the windows local system settings for the "short date format" so it is Windows-version and region-dependent. This local system 'date and time setting' is fully customizable. Each language/region has its own settings and the users can change this too. One user may configure its system to show the short date as Tue060719; while another user (even in the same Windows system) may choose 07/06/2019. So it is not really a reliable way to get the date. It becomes difficult for you to script using date /t command as the output of this command varies with Windows machines with a different regional/country settings.

To check the above, I randomly tested these commands on two different Windows systems.

On Windows Server 2012 R2 server:

Automation: Batch script to add today's date and time in file/folder name

On Windows 10 system:

Automation: Batch script to add today's date and time in file/folder name

Alright! here is the solution.

In order to overcome this issue, you should use the WMIC localdatetime command to get the date and time as this will work independent of the region setting in your Windows system/server. WMIC is the WMI command-line interface to WMI. WMIC localdatetime command provides you regionally independent date-time parsing.

I tested WMIC localdatetime command on the same set of Windows systems and got the output as shown below where we can see the output format is exactly the same in both:

On Windows Server 2012 R2 server:

Automation: Batch script to add today's date and time in file/folder name

On Windows 10 system:

Automation: Batch script to add today's date and time in file/folder name

From the above output, you can easily see that this WMIC command output needs some formatting to put the current date and time in more readable format.

Below is the batch script that formats the WMIC command output for you. You can directly use this script to add current/today date and time in your Windows files/folders.

Batch Script: Version-1

@echo off

for /f "delims=" %%a in ('wmic OS Get localdatetime  ^| find "."') do set "dt=%%a"

:: Format the WMIC command output in YY_MM_DD_hr_mn format
set "YY=%dt:~0,4%"
set "MM=%dt:~4,2%"
set "DD=%dt:~6,2%"
set "hr=%dt:~8,2%"
set "mn=%dt:~10,2%"

set "today_date_time=%YY%_%MM%_%DD%_%hr%_%mn%"
echo %today_date_time%

:: Create a folder in the current directory with name as today’s date and time as shown below
mkdir .\%today_date_time%

:: Append today’s date and time in your log file name created in the current directory as shown below
echo This information will be recorded in the following log file >> %today_date_time%_Output.log

Save the above code in a batch file (test.bat) as shown below:

Automation: Batch script to add today's date and time in file/folder name

Now let’s run this batch script (either from the command line or directly from the folder itself by double-clicking on test.bat).

Running this batch script will create the following two things:
  1. A log file in current directory with name: %today_date_time%_Output.log
  2. A folder in current directory with name: %today_date_time%

Automation: Batch script to add today's date and time in file/folder name

Batch Script: Version-2

If you want to display the month-name (with the first 3 characters like 'Feb') and not the month-number, use the below batch script code:

@echo off

for /f "delims=" %%a in ('wmic OS Get localdatetime  ^| find "."') do set "dt=%%a"

:: Format the WMIC command output in YY_MM_DD_hr_mn format
set "YY=%dt:~0,4%"
set "MM=%dt:~4,2%"
set "DD=%dt:~6,2%"
set "hr=%dt:~8,2%"
set "mn=%dt:~10,2%"

:: Format the MM (month-number) to display the month-name
if %MM%==01 set MM=Jan
if %MM%==02 set MM=Feb
if %MM%==03 set MM=Mar
if %MM%==04 set MM=Apr
if %MM%==05 set MM=May
if %MM%==06 set MM=Jun
if %MM%==07 set MM=Jul
if %MM%==08 set MM=Aug
if %MM%==09 set MM=Sep
if %MM%==10 set MM=Oct
if %MM%==11 set MM=Nov
if %MM%==12 set MM=Dec

set "today_date_time=%YY%_%MM%_%DD%_%hr%_%mn%"
echo %today_date_time%

:: Create a folder in the current directory with name as today’s date and time as shown below
mkdir .\%today_date_time%

:: Append today’s date and time in your log file name created in the current directory as shown below
echo This information will be recorded in the following log file >> %today_date_time%_Output.log

Running above batch script will generate your output in below format (with month-name in place of month-number compared to the first batch script):

Automation: Batch script to add today's date and time in file/folder name

The purpose here is that you can use the above two batch script codes in any of your batch files where you either want to create a backup folder or a log file with the current date and timestamp added in the folder or file name.

WMIC localdatetime command can be run on any Windows platform and the result will be the same. You don’t need to worry about Windows system regional settings and the variation in date and time formats. The returned value of WMIC localdatetime is always in the same format and with time in the 24-hour format.

WMIC localdatetime command works universally on all locales or date-time formats that's why it is the most reliable way to get the date in batch scripting.

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

Wednesday, May 20, 2020

// // 3 comments

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

Topic: How to manually run FDMEE System Maintenance Tasks: Maintain EBS GL Balances Table

Hi Friends,

This is the fifth post under Oracle Hyperion Financial Data Quality Management (FDMEE): System Maintenance Tasks blog series where we are going to cover the Maintain EBS GL Balances Table script.

If you have not yet read the previous four 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

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

FDMEE is the EPM tool for data integration. Using FDMEE, ERP applications like Oracle EBS, PeopleSoft, SAP, etc. can be directly connected to your target Oracle Hyperion applications for data loading.

You would have seen FDMEE integration system having Oracle EBS General Ledger as data source system and HFM or Hyperion Planning as target applications. Data (GL_Balances) is extracted from Oracle EBS source system using ODI (FDMEE), moved to staging tables (AIF_EBS_GL_BALANCES_STG), and then eventually loaded to your target applications after transformation.

Maintain EBS GL Balances Table’ purge script maintains the AIF_EBS_GL_BALANCES_STG table in the FDMEE schema. EBS General Ledger balances contain a snapshot of the General Ledger balances and are deleted based on the General Ledger period. Maintain EBS GL Balances Table’ purge process determines the list of General Ledger periods between the start and end periods. 

Following are the input parameters which you need to enter while running ‘Maintain EBS GL Balances Table’ purge script:
  • Source System
  • Start Period
  • End Period
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 EBS GL Balances 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:

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

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

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

4- From the Scripts grid, select Maintain EBS GL Balances Table script and Click Execute.

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

5It will prompt to enter the following parameter values on Execute Script screen.
  • Source System
  • Start Period
  • End Period
FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

6- Click on the search icon for Source System

Select any Source System name for which you want to purge data and click OK.

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

7- Source System will be populated. 

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table


8- Now enter Start Period and End Period. 

For example: If you want to purge all data from Nov-2016 to Oct-2017, then select Start Period and End Period as shown below:


FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table


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

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

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

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

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

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

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

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

13- Above we can see our script with Process ID 8184 executed successfully. 

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

FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

In the log file, look for the message "Maintain EBS GL Table: Completed" to confirm that the script has run successfully without any error. If you encounter any errors, troubleshoot them based on the cause mentioned in the log file. 

You need to repeat all of the above steps for other Source System from where your FDMEE data extractions are running to target Hyperion applications. 

Once you are done with running the Maintain EBS GL Balances Table purge script, you can check your Hyperion Financial Data Quality Management (FDMEE) schema size to see how much space has been cleared.

Here it’s important to note that after running the Maintain EBS GL Balances Table 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.

You may also need 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 may take 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_EBS_GL_BALANCES_STG' 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 EBS GL Balances Table 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 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

Tuesday, May 19, 2020

// // Leave a Comment

DRM: How to check DRM User creation date


Topic:  How to find out 'Users creation date' in Oracle Data Relationship Management (DRM)

Hi Friends,

Auditing of users is an important part of any Oracle Hyperion Production system. You might have faced a scenario where you need to find out when a particular business user was added or assigned access into your Hyperion applications to cross-check whether there was a corresponding change request or incident number raised for record or not. These are some pertinent questions, which you have to find answers to, during Oracle Hyperion applications auditing.  

In this post, we will explore how to find out when was a new user created/added into your Oracle Data Relationship Management (DRM) system.

Important Note:
  • This post has been written and associated activities have been demonstrated on Oracle Data Relationship Management (DRM) version 11.1.2.4.344.
  • The demonstrating Hyperion environment has Oracle database server 12.2.0.2 (18c) as backend database. 
When you log in to Oracle Data Relationship Management (DRM) Web Client with DRM Administrator account and navigate to: Administer—Security—Users, you will see your DRM users list as shown below:

DRM: Users creation date in Oracle DRM

A new user is added/created to Oracle Data Relationship Management (DRM) system either directly from DRM Web Client (Administer—Security—Users) or synchronized from Hyperion Shared services when ‘Common User Provisioning’ is enabled in your Oracle DRM system.

Adding/creating a new user in Oracle DRM is a kind of transaction that is recorded in a table name RM_TRANSACTION_HISTORY in DRM_DB relational repository schema.

RM_TRANSACTION_HISTORY table contains a row for each transactional update to your DRM repository (DRM_DB schema).

There is a column name C_ACTION in the table RM_TRANSACTION_HISTORY that records activities/actions like Add User, Add Node, Insert Node, Delete Node, ChangeProp, etc. 

In order to extract Oracle DRM users creation date along with who created that user, perform below steps:

1- Login to your DRM_DB relational repository schema (using SQL developer).

2- Run the below query:

select C_USER_NAME as USER_NAME, C_ACTION as ACTION, C_ACTION_DESCRIPTION as ACTION_DESCRIPTION, D_TIMESTAMP as Date_Time from RM_Transaction_History where C_ACTION = 'Add User' order by Date_Time desc;

3- Your output will be generated as shown below:

DRM: Users creation date in Oracle DRM

Description of the output:
  • USER_NAME = The already existing DRM user who has created/added the new user.
  • ACTION = Add User activity is performed
  • ACTION_DESCRIPTION = User ‘NEW USERNAME’ added
  • DATE_TIME = Date and time when the NEW USERNAME was created/added into Oracle DRM system in descending order (Newest user creation date first--oldest user creation date in last).
Optionally, if you would like to display DATE_TIME in 'YYYY-MM-DD HH24:MM:SS' format:

Run the below query:

select C_USER_NAME as USER_NAME, C_ACTION as ACTION, C_ACTION_DESCRIPTION as ACTION_DESCRIPTION, TO_CHAR(D_TIMESTAMP, 'YYYY-MM-DD HH24:MM:SS') as Date_Time from RM_Transaction_History where C_ACTION = 'Add User' order by Date_Time desc;

DRM: Users creation date in Oracle DRM

Note: In the above two examples, USER_NAME column signifies different kinds of DRM users who can create/add new users into Oracle DRM system. Below is the detail of the same:
  • USER_NAME = ‘ADMIN’ implies that the new user has been created/added by DRM Default Administrator
  • USER_NAME = ‘@@PROCESS’ implies that the new user has been created/added by synchronization process from Hyperion Shared Services  in a ‘Common User Provisioning’ enabled Oracle DRM application. '@@PROCESS' is a default internal user in Oracle DRM set up to handle inter-process communication between server components.
  • USER_NAME = ‘Any existing application user’ implies that the new user has been created/added by an existing DRM business user who has been assigned privileges (Application Administrator privilege) to create/add a new user into Oracle DRM application. 
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

Tuesday, May 12, 2020

// // Leave a Comment

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"

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

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

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

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

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

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

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

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:


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

Friday, May 8, 2020

// // Leave a Comment

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

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:

FDMEE: System Maintenance Tasks: Maintain Process Tables

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

FDMEE: System Maintenance Tasks: Maintain Process Tables

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

FDMEE: System Maintenance Tasks: Maintain Process Tables

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

FDMEE: System Maintenance Tasks: Maintain Process Tables

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

FDMEE: System Maintenance Tasks: Maintain Process Tables

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.

FDMEE: System Maintenance Tasks: Maintain Process Tables
FDMEE: System Maintenance Tasks: Maintain Process Tables

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