Date field in one of the tables is Nullable. Some rows contain NULL while some other have it as
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.
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?
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.
- Proposed as answer by Amir5656 Friday, January 07, 2011 9:07 PM