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

AC_28

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.

Darren Gosbell

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

wizkid

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.

Darren Gosbell

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.

hmayer

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

wizkid

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

Darren Gosbell

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.