Working with NULL Values in an ADF Data Flow?

Question

Working with NULL Values in an ADF Data Flow?

diponkar.paul

SSC-Addicted

Points: 425
More actions
January 14, 2021 at 12:00 am

#3827720

Comments posted to this topic are about the item Working with NULL Values in an ADF Data Flow?
“We're entering a new world in which data may be more important than software.”- Tim O'Reilly
I love to work with data and try to capture my learning through the blog post.
I have been working and studied in different Geo location includes Canada, Denmark, UK, Sweden.
January 14, 2021 at 10:54 am

This was removed by the editor as SPAM

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

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

nicksamuel SSC Enthusiast Points: 105 More actions · Answer 1

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

rick-1071254 Valued Member Points: 55 More actions · Answer 2

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.

diponkar.paul SSC-Addicted Points: 425 More actions · Answer 3

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.

“We're entering a new world in which data may be more important than software.”- Tim O'Reilly
I love to work with data and try to capture my learning through the blog post.

I have been working and studied in different Geo location includes Canada, Denmark, UK, Sweden.

rick-1071254 Valued Member Points: 55 More actions · Answer 4

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 3 years, 11 months ago by rick-1071254.

rick-1071254 Valued Member Points: 55 More actions · Answer 5

Nick, I think that technically, NULL does in fact mean unknown. Both TRUE = NULL and FALSE = NULL are evaluated as false.

diponkar.paul SSC-Addicted Points: 425 More actions · Answer 6

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.

“We're entering a new world in which data may be more important than software.”- Tim O'Reilly
I love to work with data and try to capture my learning through the blog post.

I have been working and studied in different Geo location includes Canada, Denmark, UK, Sweden.

rick-1071254 Valued Member Points: 55 More actions · Answer 7

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!

nicksamuel SSC Enthusiast Points: 105 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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.

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