none
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.

    Thursday, September 06, 2007 9:03 PM

All replies

  • 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 11:00 AM
  • 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 6:40 PM
  • 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.
    Friday, September 07, 2007 10:59 PM
  • 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

    Sunday, September 09, 2007 9:31 AM
  • 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?
    Sunday, September 09, 2007 9:22 PM
  • 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
    Monday, September 10, 2007 3:19 AM
  • This solved my problem with the date attribute.
    -
    Friday, January 07, 2011 9:08 PM