How to deal with NULL or deleted (numeric) values?

Marcel_Wiegand · Mar 20, 2012

Sometime the values from the source database turn into NULL values. There are two (or more) ways how to deal with this:

A. You can convert the value to zero before it is inserted in the Anchor Model

B. You can insert NULL values in the Anchor Model

C. Other?

Option 'B' is not possible.

Option 'A' looks correct, but sometimes NULL values serve a specific meaning in the database.

The AVG function is an example of that (see the example below).

What is the best way in order to deal with numeric values (or other values) that are deleted or turn into NULL. Without converting the complete Anchor Model database to BI-temporal and use the ErasedAt attribute for evey historic attribute.

Thanks!

----------------------------------------------------------------------------

-- Example:

----------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Budget]') AND type in (N'U'))

DROP TABLE [dbo].[Budget]

CREATE TABLE [dbo].[Budget](

[ID] [int] NOT NULL,

[Budget] [decimal](18, 2) NULL,

[ChangedAt] [datetime] NOT NULL

) ON [PRIMARY]

INSERT INTO [dbo].[Budget]([ID],[Budget],[ChangedAt])

VALUES (1,100.0,'2010-01-01')

,(2,120.0,'2011-01-01')

,(3,0.0,'2012-01-01')

SELECT AVG([Budget]) FROM [dbo].[Budget]

-- AVG = 73.333333!

DELETE FROM [dbo].[Budget]

INSERT INTO [dbo].[Budget]([ID],[Budget],[ChangedAt])

VALUES (1,100.0,'2010-01-01')

,(2,120.0,'2011-01-01')

,(3,NULL,'2012-01-01')

SELECT AVG([Budget]) FROM [dbo].[Budget]

-- AVG = Value = 110!

-- Conclusion: NULL or zero give different values!

Roenbaeck · Mar 22, 2012

As you may have guessed, we don't like null values. If you change a column to allow nulls and do B) "insert a NULL value" you can no longer tell the difference between missing data and a semantic NULL in your views, for example the latest view.

I would say you have two options in monotemporal AM:

1) Delete the row in your AM if it becomes NULL in the source.

2) Encode that which NULL represents in its own attribute.

For example, a BudgetIsSet attribute, and if you need to calculate an average only include those rows where BudgetIsSet = 'true', but if you need to count budgeted items, then all rows may be desired.