Thursday, February 6, 2014

Oracle Financial Data Quality Management (FDM) Import Scripting – Handling Source File Blank Fields/Intersections

This Mindstream Analytics blog post explains the way to successfully handle Source File records with blank fields by using an FDM Import Script. FDM by its nature does not allow blank fields to be imported and processed through its Web Client, and demands that they be handled. This script is short and efficient if you want to locate and ignore or replace a blank field value with other values, as well. It is also easy to implement.
In either way, this Import Script will run and perform the filtering operation prior to the Import Step (1st fish) of the FDM Web Application.
The following topics are discussed:

-        Creating an Import Script
-        Implementing the Import Script within FDM Web Client
-        Testing the Import Script
-        Modifying the Import Script to accommodate different needs

1. Creating an Import Script

As mentioned beforehand, the script itself is a small piece of VBA code that needs to be created within the Script Editor section of the FDM Workbench. When inside FDM Workbench, please follow these steps:

Switch to ‘Scripts’ tab -> Click on the ‘New Script’ button (see below)
In the ‘Script Type’ dropdown, select ‘Import (DataPump)’ and give the script a name

In the blank field place the following (see below) and save the script, which will then appear in the folder under ‘Import’ (see image below):
Function IMP_ACCT_Blank(strField, strRecord)
'Oracle Hyperion FDM IMPORT DataPump Script:
'Created By:              Mindstream Analytics
'Date Created:         2013-01-12 10:51:38
'Purpose: This is an FDM Example Script
If Trim(strField)="" Then
RES.pblnSkip = True
IMP_ACCT_Blank= strField
End If
End Function

2. Implementing the Import Script within FDM Web Client

In order to have the Import Script to start doing its work, we need to assign it to the Import Format that it’s intended for.
In this particular example, we will assign it to the Account Dimension mapping:
In FDM Web Client, go to Tools -> Import Format -> Locate the Import Format that you want the script to be implemented to -> Under ‘Account’ Dimension, click on the arrow under ‘Expression’ Column -> Choose ‘Add Expression’
In the ‘Expression Type’ dropdown, choose ‘Script’ -> Locate the Script that we created in the previous step

3. Testing the Import Script

As previously mentioned, the script will activate upon importing a Source File. The results will be visible in the ‘Import’ step, or the first fish.
For the purpose of demonstration, we have created a test Import Script and a test Comma Delimited Source file (see below):

From the above Source file, you can tell that we have left a blank field in the ‘Account’ Dimension, to which we have previously assigned our Import Script. The script should recognize this field, and ignore the whole row. Please see the image below after we have imported this Source file.

As intended, the script did leave out the row with the blank Account field.
NOTE - It is best recommendation to try to launch an import process with the script implemented in QA first.

4. Modifying the Import Script to accommodate different needs

Once you have created and setup the script inside the FDM Web Client, it is then easy to test and modify it to execute in different ways. For example, you could have the Import Script find intersections with blank fields and, instead of skipping them, populate them with a string.
Another example would be, as an addition, to have the Import Script notify you if/when it encounters a blank field within the Source file.

Friday, November 15, 2013

Oracle Financial Data Quality Management (FDM) Application Migration from one environment to another

This Mindstream Analytics blog post discusses the techniques of how to efficiently migrate an Oracle Financial Data Quality Management (FDM) Application from one environment to another (e.g. PRODUCTION TO DEVELOPMENT).
Note – In this particular demonstration, we used the MSSQL Server Management Studio as our RDBMS client. The name of the application being migrated over from DEVELOPMENT Environment to PRODUCTION Environment is called FINANCE.

The following topics are discussed:

-          Creating file and FDM Application related database backups
-          Copying the backups from one environment to the other
-          Acquiring root directory paths for storing File System Folders
-          Workbench, FDM Web Client and Data Validations
-          Connectivity and Data Load testing

1.      Migrating Files and Backups

1.1.          Create Database Backups

The first thing we need to do in order to successfully migrate an FDM Application from DEVELOPMENT (DEV) to PRODUCTION (PROD) is to migrate the Database that is related to the application.
First, create a Backup or extract the corresponding SQL Database from the DEV Environment. (The DB Backup and Extraction process can be done in many different ways, depending on how your databases are setup or what RDBMS Client you use). This backup should be stored on the same server, at a location that is easy to find

1.2.          Copy the Backups to PROD

The backup created in Step 1 needs to be copied over and imported to the PROD Server where your RDBMS Client is setup, again at a location that is convenient and easy to find. In our case, we created a new Empty Database Shells in MSSQL Server Management Studio, called FINANCE (Right-click on Databases -> New Database -> Create Database (FINANCE)

1.3.          Migrate the backups from DEV

In MSSQL Server Management Studio, Right-click on one the newly created FINANCE Empty Database Shell and go to Tasks-> Restore -> Database -> Click on ‘From Device’ -> Browse -> Choose the backup copied in Step2

Now that we have successfully migrated the FDM Application Database over to PROD SQL Server, we need to move over the FDM root folder files

1.4.          Copy the File System folders for FINANCE Application in DEV

In your Oracle Home Directory <home_partition>\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\Shared Components\Config on the FDM DEV Server, open the ‘HyperionFDMApplications.xml’ file via Internet Explorer (Right-click on the file -> Open with -> Internet Explorer). Here you can see which Database belongs to the FINANCE application and what the FDM Root folder is (see image below)

1.5.          Copy the File System Folders to PROD

In the FDM Root Folder Location, select only the FINANCE Folder and copy it inside the FDM PROD Server. As before, it is very important that you choose/create a location that is easy to find. For the purpose of demonstrating, we created a FDMDATA_fromDev_Migration inside the FDM PROD Server

1.6.          Get the root Folder for FINANCE FDM Application in PROD

In <home_partition>\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\Shared Components\Config on the FDM PROD server, open the ‘HyperionFDMApplications.xml’ file via Internet Explorer (Right-click on the file -> Open with -> Internet Explorer)

Now, we assume that a FDM Application shell has already been created inside FDM. For assistance on how to create an FDM Application Shell, please go to
In our example, there is already an FDM Applications previously created (FINANCE)
As in DEV, please memorize the Root Folder for the File System folders in PROD (In this case, it is D:\FDMDATA)

1.7.          Copy the File System Folders from the Backup Location to the PROD FDM Root Folder

The FINANCE File System Folder from the D:\FDMDATA_fromDev_Migration folder is copied over to the FDM Root Folder (D:\FDMDATA) in PROD

2.     Data Validations
Access the Application and make sure that all of the settings in the two FDM Applications in PROD are analog to what we have in DEV

1.8.          Workbench Validations

In workbench (Start-> Workbench), choose the existing application that you are migrating and enter the credentials to login - Check Machine Profiles: Expand Target System Adapters -> Expand the adapter e.g. (ESX11X-G4-H)Hyperion Essbase-> Expand Machine Profiles and right-click on the machine profile already in there (dev-fdm) -> Click on Properties -> Change Source machine to what the it should be for PROD (e.g. prod-fdm) and Target Machine (e.g. prod-essbase)

Go through Tools -> Application Settings, Integration Settings, and Configuration Settings. Make sure that they all match the Settings that are in the DEV environment for the same application

1.9.          Web Client Validations

Open Internet Explorer -> Go to your FDM Hyperlink. Pick the application that you are migrating, and Login to the FDM Web Client

1.10.          Compare the Mappings for every Type and Dimension

In Activities -> Maps, a comparison was made to check whether all of the Mappings for each Dimension in PROD are the same to the ones in DEV

1.11.          Test whether the connection to Essbase has been successfully established in PROD

In Activities -> Maps -> Click on Target Account twice -> Click on a small triangle on the right-side of the field -> Choose Browse from Target Value. If a window with a list of all Target Key Lookups opens, this means that the connection is established

Go through a handful of the locations (Metadata > Locations, Import Formats, Validation Entities etc.), as well as other tabs (Activities, Analysis, etc.) in the FDM Web Client,  and make sure that all of the settings match the ones in DEV. Some examples shown below

1.12.          Try to load data

You can test the functionality of the migrated application either with a test file that you can load to a future period, or you can reload the latest period. This will tell us what is working and what is not.
Other approaches that can be used (depending on the way you load data)
·         Importing a File from the FDM Web Client
·         EAS Outline Load from a Batch Script
·         File load through a batch script which is scheduled to run daily with Windows Scheduler.