Using SQL to map null values to ignore

Author: Lydia Maksoud

 

Recently I have been attempting to map null entries to be ignored when importing data into FDMEE. This may sound like an easy thing to do, but if you have experimented much with FDMEE mappings, you’ll know that this isn’t as simple as just creating a mapping from ‘Null’ to ‘Ignore’.

However, there is a way around this using a custom SQL mapping script to define your own mappings and this blog is here to show you how!

Firstly, to access the ability to input a custom SQL mapping script, go to the ‘Between’, ‘In’ or ‘Like’ mappings tab (SQL mapping scripts are not available for Explicit or Multi-dimension mapping types). 

Add a new mapping and enter the Source Value and Rule Name. Then for the Target Value, enter either:

  •    #SQL to enable the SQL mapping script functionality
  •    #SCRIPT to enable the Jython mapping script functionality 

In the example below I have used a Like mapping on the Products dimension (data table column UD2) and set the Source Value to ‘*’, meaning this mapping will apply to all product values. The Target Value has been set to #SQL as we will be using a SQL mapping script, rather than Jython.

 

 

After saving the new mapping, the Script Editor icon will be enabled, allowing you to open and edit the custom SQL script.  

 

 

In a standard FDMEE mapping you simply set the Target Value as ‘IGNORE’ to ignore specified source values, so for my first attempt I tried the following script:

 

 

This should map any NULL fields to ‘IGNORE’ and add a PR_ prefix to any other product members. (Here 'UD2' refers to the data table column for the product dimension).

After importing, all seems well and the NULL fields have been mapped to have target value ‘IGNORE’ in the data load workbench. However, when you export the data to the target application, you get an error suggesting that the member ‘IGNORE’ is missing from your database. This is because FDMEE is treating the value ‘IGNORE’ as a product member, rather than ignoring this row when loading.

To get around this, we must use the VALID_FLAG setting which is used to indicate whether a row is valid (VALID_FLAG = ‘Y’), invalid (VALID_FLAG = ‘N’) or should be ignored (VALID_FLAG = ‘I’). To include this in the mapping, I edited the SQL mapping script as follows:

 

 

Now when exporting, the lines with NULL values in the product dimension are mapped to ‘IGNORE’ and have a VALID_FLAG setting of ‘I’, meaning they are successfully ignored. Job done!

More good news is that custom SQL mapping scripts are available to use in both on premise FDMEE or in PBCS Data Management.

For more Hyperion tips and tricks from me, go to http://lydia-maksoud-epm.blogspot.co.uk/.