Default date '1899-12-30 00:00:00.000'

Question

Hi,

 

Date field in one of the tables is Nullable. Some rows contain NULL while some other have it as

'1899-12-30 00:00:00.000'

While processing the cube on this table, both NULL and '1899-12-30 00:00:00.000' get converted to Blank.

Does anybody have any idea why this happens and if there is any workaround to show values exactly as they are.

 

Thanks.

Answer 1

Where are you trying to use these dates? As an attribute or a measure?

 

If you are trying to use them as a measure then this is because nulls in SSAS are not the same as in relational databases. They are empty cells which are equivalent to 0 and I think '1899-12-30 00:00:00.000' is the 0 value for the datetime data type. But then again dates don't really aggregate that well.

 

Do you have real data associated with 1899-12-30 or is this just a marker date, could you use another date 1899-12-31?

 

Answer 2

It is an attribute field. I just want to make sure that NULL and '1899-12-30 00:00:00.000'  are shown as two seperate date values on the report. This field is part of an external source so I can not change it.

Answer 3

If it's an attribute then you should not have any of the issues I was talking about. But you cannot have a null value for an attribute key value, what you could do is to create a named column in your DSV and map null values to empty strings.

Answer 4

be aware of the "NullProcessing" property of SSAS - the "Automatic" setting will convert - I am not sure if on all datatypes - a NULL to a zero value - if you set the property to "Preserve" the NULL is retained.

 

HANNES

Answer 5

THis date is not a key for the attribute. However I want to keep 1899-12-30 00:00:00.000' as it is and show only NULL as blank. Is there a way to achieve that?

Answer 6

The terminology can get a bit confusing, but I was not talking about the key attribute for a dimension. Every attribute has a key, value and name property. If you have not altered them, they will all be pointing to the same column. If you have a look at the properties for these columns (the KeyColumn, NameColumn and ValueColumn properties) you will find the NullProcessing option that hmayer referred to.

 

This link http://msdn2.microsoft.com/en-us/library/ms345138.aspx on handling data integrity issues has the following to say about the NullProcessing option

 

The NullProcessing property specifies what action the server should take when it encounters a NULL value. It can take five possible values:

• ZeroOrBlank—This tells the server to convert the NULL value to a zero (for numeric data items) or a blank string (for string data items). This is how Analysis Services 2000 handles NULL values.
• Preserve—This tells the server to preserve the NULL value. The server has the ability to store NULL just like any other value.
• Error—This tells the server that a NULL value is illegal in this data item. The server will generate a data integrity error and discard the record.
• UnknownMember—This tells the server to interpret the NULL value as the unknown member. The server will also generate a data integrity error. This option is applicable only for attribute key columns.
• Default—This is a conditional default. It implies ZeroOrBlank for dimensions and cubes, and UnknownMember for mining structures and models.

The default option will convert nulls to 0 for dates, so this would probably explain your issue. As hmayer suggested, try changing this to a value of Preserve.

Answer 7

This solved my problem with the date attribute.

---

-