The most common way to get data into Essbase is to use a data load rule. Load Rules are a simple, proven, and consistent object which make them ideal for almost any environment. Recently, I discovered a quirk in load rules that I believed to be a bug, but later discovered was Essbase functionality. I noticed it because my data load had no errors, and the data tied out at upper levels even though individual data points were incorrect. This issue was hard to notice since the data validates and no errors are created in the log.
(click to enlarge)
(click to enlarge)
The specific situation occurs when a data file contains a blank or SQL feed contains a NULL value in a member field section. This leads to an oddity where the records are loaded instead of being rejected for missing member fields. Since all the data still gets loaded, the upper level validations could tie making it hard to identify the load inconsistency.
This happens when a record in the SQL feed has a blank or null value or when a data file contains two consecutive delimiters, but only if previous records have successfully loaded. If the first record conatins this sort of error, the load will fail and you will receive the below message.
(click to enlarge)
When this occurs later after the first row, Essbase loads the record containing the NULL into Essbase using the last valid member name that was loaded. This means that if there are 2000 records in the middle of a file or table that are NULL, they will all get loaded to the member before the error. This behavior is due to the way the Essbase Grid API was implemented and exists to help adjust to different data files structures where all members may not be repeated on every line. However, if you are unaware of it it can cause issues. If you ever have concerns about the structure of your data sources or run across irregular data issues similar to what we have described here, it may be worth validating that your query doesn’t contain NULL values in member fields like in the first image.
