Min(NULL) shows up as 12/30/1899 in cube

Hi!

I have a cube with a measure based on a datetime column with aggregation Min in AS 2000 SP4. The Data Type is set to Date and the Format is set to Short Date. Somehow the the measure shows 12/30/1899 when all values in the datetime column are NULL. If I check the properties in the cube browser it says Formatted Value: 12/30/1899 and Value: 12:00:00 AM.

I've implemented a work around in the reports that use this measure to look for 1899 or AM and replace with it NULL, but what I really want is the cube to display the real value (= NULL) when I browse it.

Any help would be greatly appreciated. Thanks!

Nulls don't behave the same in an OLAP database. In fact they often evaluate to 0 which is what is happening here.

In a relational DB 1 + NULL = NULL

In an OLAP DB 1 + NULL = 1

You might have to change your approach, so possible avenues to explore would be

use an aggregation of None and use a calculation to rollup the values, but this could be slow as you would need to drill down to the leaf level to get any values when using an aggregtion type of none. You might be able to attach the date as an attribute to another dimension, it depends what the date measure is.|||

So basically what I have to do is to create a calculated member based on this measure and use that one in the reports?:

iif(the_date_measure == 12/30/1899, NULL, iif(the_date_measure == 12:00:00 AM, NULL, the_date_measure )), which is the same thing I do in the reports.

I've verified it and a date column with NULL becomes 12/30/1899 in AS (in ver 2000 at least). So gotta use a work around as above to fix this.

|||

That would work too.

You could even put that type of calculation in the cube. If it were me I would hide the "raw" measure and create a calculated measure with this sort of logic in it.

|||

I've now created a calculated member as:

IIF( (InStr([Measures].[Adate], "00:00") > 0) OR (InStr([Measures].[Adate], "00.00") > 0), NULL, [Measures].[Adate])

and it seems to work very well. Looking for AM or 1899 does not work since the value will be 12:00:00 AM, 0:00:00, 00:00:00 depending on regional settings. I looked through all regional settings on my computer and only Faeroese and Italy have a dot separator instead of colon in their time setting (00.00.00).

Btw, do you know how date columns with NULL show up in AS 2005? Same behavior here?

Thanks for all your help!

|||Well you have some more options in AS2005, it is not really typical to use dates based measures so it is hard to say without knowing more details. The Min aggregation should behave the same, but you could also potential use something like LastNonEmpty or None with a calculation to do the rollup.

Migration Wizard Error

When I attempt to migrate any AS2000 database to AS2005, I get an error during the "Validating Databases" step, during the Cube validation, as follows:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Interop.Dso.IPartition90Ex'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{F82195C2-53A0-41B5-B7D8-7D087BAE32CF}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Needless to say, this prevents me from performing the migration. I have even created a super-simple cube with one dimension, and I still get the same error. I have tried this using 3 different source servers. Any suggestions on where to look? I'm running SSMS and Analysis Manager on a workstation, not the server itself.

Gary

This could be related to the order you installed your components. Try uninstalling and re-installing SQL Server 2005 components on your workstation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thank you. I uninstalled and re-installed SQL Server 2005 on the workstation and that solved the problem.