NULL field handling in FDMEE

In this blog, I would be talking about handling NULL fields in FDMEE. Now, there are different ways to handle NULL fields in FDMEE. This is one of the best things in FDMEE. You can choose an approach that is your cup of tea or coffee. 

Now, this is a snapshot of a file that was successfully imported, mapped and pushed to an Essbase application.

Now, this is a file that we will try to load using the existing rule file.

 

The first seven records have entity mapped to NULL. Note that FDMEE makes a difference between NULL and blank space character… (More on this later) 

Below is the snapshot of the above file that was imported and validated in FDMEE.

The problem with this is that it has rejected 7 records with the NULL entity and I have no way of knowing this unless I am superstitious enough to read the log file of the data load rule. There should be a way, I see through all the data in the file that comes through and the handle the NULL values by loading it to some specific member. For example, I would not want to store the data at a placeholder entity and move it later to some specific entity. 

The way I am going to be doing this is by using FillL expression that can be defined in the import format mappings. FillL basically ensures that there is a leading fill expression so that any input value has a minimum length. (Anyone who has worked with the outline load utility in Planning will know the trouble you get if you have a metadata member that begins with 0 and is a number. Excel by default will remove the 0. For example, if I have a member 0090, then it will become 90 in excel unless formatted as text.)

The way I will use FillL is as shown in the below snapshot. 

The import format after the mapping has been applied is as shown in the below snapshot.   

I will now reload the data as shown in the below snapshot. If you see it now, the NULL entities have come through after the validation against data mappings.

Since an entity with the name “00” would not be there in the application, I create an explicit mapping to map the “00” entity to a placeholder entity “Entity_NA” as shown in the below snapshot.

The placeholder entity in the application is as shown in the below snapshot.

 

Running a revalidate and export to the application is as shown in the below snapshot.

The data in the application as shown by a report script.

Disclaimer: The use of FillL assumes a lot of things on the data modelling front. For example, in this demo, all the entities have a minimum length of two. If instead of using “00”, I would have used “000” the result would be quite different. So analysis of the data model is important before doing NULL field handling using FillL. However, it’s a simple approach that can be easily implemented.