I have a fact table that has several dates

Open Date
Close Date
Due Date



The open date will always contain a valid date but at any given time
the other two may contain a valid date or a null value.

When I add a time dimension for open date the counts are correct.

When I add a time dimension for the other dates the counts are correct
(taking into consideration that the NULL values won't be included)

When I add the open date and close date dimensions to the cube the
counts reflect the numbers associated with the close date.



Is there any way to allow for each dimension to work independently?

How do I account for the NULL values in a time dimension?



Thanks for your assistance,

Analysis Services does not handle NULLs very well at all.
I would load your dimension (and fact) tables through a view and use a
ISNULL to specifically cast the NULL to a pre-defined Unknown member.
Obviously you need to create the unknown also.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"fhcole" <fhc40 (AT) yahoo (DOT) com> wrote

Thanks for putting me on the right track. I had been using views so
the solution was not terribly difficult. The part that stumped me for
a while was the fact that isnull with a blank returns a smalldatetime
value of '1900-01-01'. Once I got my thoughts around that, the rest
was simple. I created a view that mimiced a time dimension and then
created the dimension as a non-time dimension. The key is a still a
date so any date math in MDX will still apply.

The structure of the Dimension was:

YEAR
QUARTER
MONTH
DAY

Here is the code I used for the view

Select DayofYear doyKey,
year(DayOfYear) doyYearNo,
datepart(q,DayOfYear) doyQuarterNo,
month(DayOfYear) doyMonthNo,
day(DayOfYear) doyDayNo,
datename(yyyy,dayofyear) doyYearName,
'Quarter ' + ltrim(rtrim(datename(qq,dayofYear))) doyQuarterName,
datename(mm,dayofyear) doyMonth,
right(' ' + ltrim(rtrim(convert(char(2),day(DayOfYear)))),2) + ' - ' +
datename(dw,dayofyear) doyDayName,
convert(char(10),DayOfYear,101) doyDate
from TicketTimeDimension
union
select '1900-01-01',
1900,
5,
13,
35,
'UNKNOWN',
'UNKNOWN',
'UNKNOWN',
'UNKNOWN',
'UNKNOWN'

A sample hierarchy would be

2005
Quarter 2
May
1 - Sunday

The null value of 1900-01-01 would show up as:

UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN

Thanks again for your help.