Thursday, September 06, 2007 9:03 PM
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.
Friday, September 07, 2007 11:00 AMModerator
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?
Friday, September 07, 2007 6:40 PM
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.
Friday, September 07, 2007 10:59 PMModeratorIf 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.
Sunday, September 09, 2007 9:31 AM
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.
Sunday, September 09, 2007 9:22 PMTHis 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?
Monday, September 10, 2007 3:19 AMModerator
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
Friday, January 07, 2011 9:08 PMThis solved my problem with the date attribute.