Using SQL to map Null values to Ignore in Data Management

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.

Comments

Post a Comment

Popular posts from this blog

Loading multi-period row data files using Data Management

Image
The ability to load multi-period data with the Period and/or Year in the rows is not particularly new (since release 16.10), however it is something that most people seem to forget is possible in Data Management. This really is something worth remembering as it eliminates the need to either split the data files into one file per month or try to pivot the month into the columns. In order to set this up, let’s firstly look at how to add the Period and Year columns to the import format. Add the Period and Year columns using the Add -> Source Period Row dropdown, and input: The Source Column name – e.g. PERIOD The Field Number (file column) – e.g. 3 Any required expressions for that field  As these are Period/Year dimensions, the mappings are not held in the usual Data Load Mappings area. Instead they are configured in the Period Mappings , under the Source Mapping tab. Select the Source System as File to add a new mapping calendar for loading the data ...
Read more

Executing Smart View Retrievals using VBA

Image
When working with Hyperion systems, Smart View is a key tool used for reporting, as well as inputting data. It provides a valuable link between Hyperion and Excel, which allows users to retrieve data from the database straight into Excel with ease. Smart View functions are available through the Smart View toolbar and can be used with Essbase, HFM, Planning or Cloud connections. Occasionally, it is necessary to perform Smart View functions behind the scenes using VBA code, rather than manually using the Smart View toolbar. For example, a spreadsheet which will refresh all the Essbase retrieval sheets on opening, so that users can view the most up to date data, without having to remember to refresh each of the retrieval sheets themselves. This blog will be cover how to retrieve data using a Smart View refresh function in VBA. For more information about Smart View Excel VBA functions, go to the Oracle Hyperion Smart View Developers Guide .  System Requirements ...
Read more

Loading Actuals from Fusion ERP Cloud to PBCS

Image
If you’re looking to set up a data load of Actuals from ERP to PBCS and have heard this is an “Out of the box” integration, then good news! Not only is the OOTB connector included within PBCS Data Management but the integration is simple to setup and to schedule using EPM Automate. This blog will detail how to setup the ERP to PBCS connector in Data Management and some of the common errors found when running the data load. The first thing to know about this integration is that the data source is actually the ASO Essbase cube which sits underneath the Fusion ERP tables, holding the period Actuals values. This ERP Essbase cube is created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system. This cube must be created before the integration can be set up correctly and can be accessed directly via Smart View for reconciliations. Set up the Source System As this is an Oracle built connector, it’s as simple as providing a name for the source system ...
Read more