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