Aug 20 2008 while at EMC Conchango
Having
done a number of Business Intelligence Projects as Test Manager, I have
found it very important to have some nil-null education, as I had a
rude “remembrance” the other day. It’s like knowing your times tables or
doing arithmetic ... it should be standard practice but it’s not.
Null pointe or Nil pointe?
What’s
the point of Null? A lot I say it could means several things to the
business, could be bad KPIs or deceptively nothing has happened or could
be that it has just thrown the 'blanket' over certain values - you just
dont know what to do with it. Nil we are quite familiar - you do this
in elementary maths (zero, zilch, nought) - we British known it well
when we get ‘Nil pointe’ at Eurovision Song contest. You are aware that
zero means just what it says zero; it doesn’t require much for others to understand what the data means.
The danger is beguiling, we kind of think they are the same - they are so not!
Analyse this!
Several things to do first -
Maths of Null (codename – ‘the blanket’)
5 + null = Null
5 - Null = Null (still the 'blanket' you would have thought different)
5 * Null = Null
5 / Null = Null
Maths of Nil (codename – ‘the realist’)
5 + 0 = 5
5 - 0 = 5
5 * 0 = 0
5 / 0 = error (div. by zero - considered as zero result)
Okay
so you have been educated! - This is by no means and exhaustive list -
but you can see why null is a blanket in its behaviour - it shrouds
everything making it mysterious. Nil on the other hand has a different
but expected behaviour.
The Intelligence
So armed with this education - what can you do to help the business understand what they are looking at? Take an example, I noticed a weird condition with data from a food retail business in the data warehouse - in a column had null and zero - made me to wonder ... well, what had happened? Then I found out that
'NULL' means we did not check the stock
'0' means we checked it and nothing was found.
Strange! But that’s how the business looks at their data.
Business Rules
Find
out what the business does with nulls and nils ensure you have an
exhaustive list of what they expect when data is presented to them. Keep
this close to your requirements list to help you meet any Test
criteria, ensuring that when you test, data integrity is maintained
through all the ETL Testing. It no easy feat but it has to be done, or
else everyone see twisted, stumped, elongated or fattened data similar
to going through a hall of mirrors.
Work it out
Once you have collated the business rules when writing your SQL, certain functions are helpful to use ISNULL stops the behaviour of a blanket and makes null behave like nil
5 + ISNULL (null, 0) = 5
5 - ISNULL (null, 0) = 5
5 * ISNULL (null, 0) = 0
NULLIF stops divide by zero and gives zero as the expected result
5 / NULLIF (null, 0) = 0 *
* correction: ISNULL (5/NULLIF(null, 0), 0) = 0
With
this knowledge we can tackle the null behaviour if we don’t trust it -
in my experience look at each column and check whether those nulls or
zeros are ever expected, for example at the end of a sales day all bread
stock (end of day stock) would register nil - as no fresh baked bread
would be kept till the next day, so if you ever saw a value something
has happened.
Envisage
any calculations that the variable might hit to protect the actual
meaning of its value, this will provide a safeguard against inerrant
behaviour and creates a certain and expected result.
Being
aware of the effects of Null and Nil and what it means for the business
will help your Clients get the true value of their data.
