Generally
when designing a data warehouse (at least in my experience) you don’t
think about NULL values appearing often in measure data of a fact table. You may have unknown foreign keys from your dimension data but even those unknown foreign keys relate to some kind of fact data. If you’re new to data warehousing measure data would be things like price, quantity, duration. These are usually the business numbers that you want to aggregate in your cube.
One
case where I have seen NULL data in measures is when a fact table is
designed by writing multiple UNION statements to bring in data from
multiple data sources. When retrieving results from the UNION there may be measures present from one table but not the other, as shown below.
In this example the only measure that is populated is the ExtendedAmount column. No problem right? So
I would expect when I bring this into an Analysis Services cube that
the ExtendedAmount column would be aggregated with the other similar
records and all the NULL measures would be left out.
Unfortunately, this is not the default behavior of Analysis Services. By default Analysis Services turns that NULL value into a 0. I think this is a little strange since NULL means that the record is absent of a value for this field but SSAS gives it a value. For some people they may actually want to see this 0 but generally I would imagine you would not want to see any value.
To fix this you must go the Cube Structure tab and select each measure that you would like to retain the original NULL value. Then go to the properties menu (F4) and expand the Source property. From there you want to change the NULLProcessing property to Preserve as shown below.
After reprocessing, this will fix the potential issue of NULL values being represented by 0 in your cube when users browse it. Test it yourself but quickly browsing the cube to make sure that the invalid 0 record is gone.
