FME2012 Sneak Peek: Much Ado About NULL!
It’s been a while since I posted here. I was busy cataloguing all of the new functionality in FME2012, and deciding what updates I would be writing about or creating videos of.
On that note, I hope to create a lot more videos for the FME2012. They can make a point much quicker than trying to write about something.
The topics I will cover around the 2012 release include:
- New updates to the Workbench interface
- New formats such as CAT, Supermap, INSPIRE, and Fusion Tables
- Coordinate system updates for 2012
- Updates to FME Server, such as improved security tools
- New transformers such as the InlineQuerier, SliverRemover, XMLUpdater, and OffsetCurveGenerator
…and quite a bit more besides!
But this post is all about… nothing! In particular attributes that store nothing, how FME represents them, and how we’ve updated FME2012 to improve the user experience.
OK, I quite often see a question from users about how FME handles NULL attributes; but really there are three different states that a “nothing” attribute can take:
- The attribute exists and is an empty value (Empty)
- The attribute exists and is NULL (NULL)
- The attribute doesn’t exist (Non-existent)
NULL of course, is a specific state of attribute in database formats that represents “missing” data and is distinctly different to an empty or zero-length value.
FME and NULLS
FME currently (2011) does not have a representation for NULL in its geometry model. In essence, it treats NULL and “Non-existent” as the same thing. This – need I say – is not a good thing.
Also, individual tools vary in their treatment of “Empty” attributes.
For example, the FME Universal Viewer:
- Will show attributes with an empty value
- Will NOT show attributes that are NULL/Non-Existent
Alternatively, when you test for <attr>=”” in the Tester transformer, it is more consistent:
- Empty attributes: PASS
- NULL/Non-existent attributes: PASS
A key requirement for users is obviously to identify “nothing” values. Secondly there might be times you need to differentiate between Empty, Non-existent, and NULL attributes.
In FME2011 you could open a Tester and test:
…but that’s not particularly user-friendly, plus the only way to differentiate different null types is to open data in the FME Universal Viewer, to see whether an attribute is empty, or whether it is NULL/non-existent.
So, recognizing the problems, we decided to improve our “nothing” handling.
Updates for 2012
The first fix we’ve done is to add “nothing” operators to the Tester transformer. This will make it much easier to test for where such values exist (click to enlarge):
Also, notice there are three new operators (Attribute Exists, Attribute is Null, Attribute is Empty). This will let you identify NULL values (as opposed to empty) within a Workbench translation.
Incidentally, while we are looking at the Tester, also note the new Negate option. With this you can test equally easily for “Does Not Exist”, “Is Not Null”, and “Is Not Empty”, simply by ticking that checkbox.
Updates for 2013
However, the Tester operators are not able to fully differentiate between NULL and non-existent attributes while there is no NULL property in our geometry model.
So, in FME2013 we plan to add this capability. Then reading a NULL database attribute will cause it to be properly flagged as NULL within FME. With the Tester updated to match you will now be able to differentiate between all three cases.
Similarly, the NullAttributeReplacer is going to get an option in 2013 to handle different scenarios, so you can identify the correct type and set an attribute value when it is truly NULL.
I hope this post is useful in clarifying the different types of ‘null’ attributes and how FME handles them.
The first phase of updates in 2012 will be a big help, I think, and cover 90%+ of all user requirements. But because we like to make our solutions complete, the planned updates for 2013 should meet that final 10% of users’ needs.
As for 2012 in general, there are a whole bunch of updates like this that I think will have a big impact on usability. I, for one, can’t wait until it is released!