Working with NULL Values in an ADF Data Flow?

  • Comments posted to this topic are about the item Working with NULL Values in an ADF Data Flow?

  • NULL is not the same as blank data. A blank can mean that it is known that there is no data - so it is not unknown. It should be replaced with "None" not "Unknown".

  • This was removed by the editor as SPAM

  • I agree with Nick, but let's go even father.  NULL is not a meaningless value.  NULL indicates a valid state in three state logic.  To simply replace it with some other value is no more appropriate than replacing a value of 1 with a value of 2 - in fact, less appropriate.

  • Hello, thanks for your comments. In general, NULL is null, you can't replace that with text or any other value, it stands as it is. However, this article focuses for BI/Data warehouse experts, and they surely know working with BI tool like SSAS (Tabular/multidimensional) must need to handle NULL value and translate that to something meaningful so that your model doesn't break and end user report looks meaningful.

  • Sorry, but you said it again.  You imply that NULL is not meaningful.  It is, and it has a very specific meaning in three state logic.  If you use three state logic and it is not appropriate, it is a design issue.  If you convert a NULL to another value, you have just destroyed the meaning of that value.  For instance, if you change NULLs to a zero, aren't you now overloading the zero value to serve two different states - one where the value is unknown and one where the value is zero.  Most BI tools DO know how to mange NULLs.  For instance, if you are computing an average for a column, and the value for that column is NULL, it should be not be included in the average calculation.  If the value is zero, it should be.  This will have a very material effect on the computed answer.

    • This reply was modified 4 days, 9 hours ago by  rick-1071254.
  • Nick, I think that technically, NULL does in fact mean unknown.  Both TRUE = NULL and FALSE = NULL are evaluated as false.

  • I think you took it too serious , I am just joking 🙂 You can think this as simply a validation process not to fail in SSAS Tabular/multidimensional or event in power BI report; e.g. if business say if you find NULL value show me 'TBD', Do you think that is wrong?  But sure remember to intact the meaning of NULL. Hope that clarifies.

  • No problem - I just took some exception to some of the language, based, I am sure, on decades of trying to get people to understand this.  Showing a NULL value as "TBD" is, I guess, OK, but all you are really doing is saying TBD = NULL, so why not say NULL?  I realize that the common user may have some trouble initially getting the concept of NULL, but it has it's own meaning and my preference is to use that.  Thanks for taking this is good spirits!

  • If you have a blank, it should be replaced with 'None' not 'Unknown' is what the post said.

  • Heh... as the old saying goes, you have to remember that "NULL is not nothing". 😀

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply