TDIing out loud

Ramblings on the paradigm-shift that is TDI.

Monday, August 4, 2014

Null Behavior


I've gotten this question (again) and decided to explain it here so that Google can find it.

Null Behavior allows you to deal with missing data without having to write Javascript. The Null Behavior feature lets you define what a 'null' attribute is and how it should be handled. By default the definition of 'null' is that the source attribute is missing or has no value (null value). The default handling is that an attribute with this name will be found in the target entry. So for an Input Map then the Work Entry will not have this attribute after the mapping is done. For an Output Map it will be the Conn Entry that does not have the attribute. Furthermore, if an attribute with this name was found in the target entry prior to the map, then it is deleted.

To illustrate this functionality, imagine you have an input map from a database connector with an attribute that gets its source value from a db column named 'TITLE', and that this column is nullable. In other words, not all rows need to have this column value. Alternatively, it could be an object repository (like an LDAP directory) and that the attribute in question is not found in all entries you are reading. During the mapping processes, SDI discovers that the source attribute (conn.TITLE) is not found. Null behavior will detect the 'null' and remove the attribute from the Work Entry.

Now imagine you are reading from a CSV file. In this case there are no missing attributes or null values, only empty string values for some attributes. So now you can change Null Behavior to define 'null' as being an empty string (plus all the other definitions over it in the Null Behavior dialog). Furthermore, you can define handling to be that you want a default value returned - for example N/A. ViolĂ , now all rows with an empty value for 'TITLE' will be returned with this attribute value set to 'N/A'.

Final note: Null Behavior can be defined at the map level using the More... button at the top of the map, or for a single attribute by right-clicking it and selecting Null Behavior.

2 comments:

Christopher Lohse said...

This one always vexes me.

So, what't the right way to deal with a null during an update? Following your example with TITLE, if the "target" system currently has a value for TITLE, but the "source" file/system now says TITLE should be empty (i.e. it needs to be changed from it's current value to null), what's the best way to get that change down the line? Seems if I leave defaults in place, the null/empty source value for the attribute causes that attribute to not make it into Work. Since the attribute isn't there in Work, it doesn't make it into the output map, and the target won't get updated (nulled out).

I'm guessing there's a really smart, obvious way to make the update (setting to null) happen that will end my struggle. Will you recommend?

Cheers,
Chris

Eddie Hartman said...

@Chris - Remember that every att map has Null Behavior that can be configured, just like every individual attribute mapping rule. So you decide if an empty string is the same as null, or a specific value (e.g. N/A). And then you define what is to happen. Do you want the attribute mapped as a null then choose 'Return null value' in the 'Null behavior' column of the Null Behavior popup dialog.

But remember that getting a null value written to the connected system depends on the type of system. If its a DB table then that column has to be nullable. For a directory, the same goes for the attribute definition.