Hi FME’ers,
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:

…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.

Nothing Attributes
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:

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:

Alternatively, when you test for <attr>=”” in the Tester transformer, it is more consistent:

Updates
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:

<attribute>=""

…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!

About FME Data Transformation Database FME Desktop Miscellaneous NULL NullAttributeReplacer Tester Usability

Mark Ireland

Mark, aka iMark, is the FME Evangelist (est. 2004) and has a passion for FME Training. He likes being able to help people understand and use technology in new and interesting ways. One of his other passions is football (aka. Soccer). He likes both technology and soccer so much that he wrote an article about the two together! Who would’ve thought? (Answer: iMark)

Comments

6 Responses to “FME2012 Sneak Peek: Much Ado About NULL!”

  1. Hans Gunnar Steen says:

    Thanks, that’s great! I was creating a workspace as we speak where this would be very useful. A question though: How will the Tester or TestFilter
    handle a query where you ask for a ‘not like’ – value? Will features that doesen’t have this attribute pass? They do in 2011, but will this be different with the new options? I’ve used software where features don’t pass on such queries .

  2. Michael W says:

    Holy cow, the CAT reader makes life so much easier! I love it, thanks to Safe for this update.

    Here’s to hoping we can get a CAT writer sometime soon.

  3. Jason Birch says:

    I think this is a great step forward too Mark. I’ve lamented the inability to discriminate between NULL and Empty in the past, especially in the cases where data modelers actually mean something by the NULL value.

    I’ve also run into cases where FME treated missing attributes (NULLs) oddly, such as in the FeatureMerger. I think this was addressed a couple releases ago, but taking an integrated approach to null/empty/missing attribute handling is a great step forward for FME and would allow for much more control over the translation/transformation process.

    Way to go!

  4. michael says:

    Definitely NULL value handling was a weak point in FME, and a good point to address for improvement.

    The backside being, that discussions about it easily drift into some kind of esoteric hick-hack up to philosophic postulations about the result.

    The final test would be to transfer NULL values from one database to another in a defined way …

    I remember some struggles when we had to handle INFORMIX and ORACLE compatibly … ;-)(

  5. mark2atsafe says:

    Thanks for the comments folks.

    Interesting point Hans, about the “not like”.

    I did a quick test and I think it works as you would expect.

    Feature 1: Attr Mark = ABC
    Mark LIKE AB% = Pass
    Mark NOT LIKE AB% = Fail

    Feature 2: No Attr Mark
    Mark LIKE AB% = Fail
    Mark NOT LIKE AB% = Pass

  6. mark2atsafe says:

    Ironically, I was going to not allow comments on future posts because all I get is spam. Then there are 4 valid comments on the next post. Figures!

Related Posts