I'm looking for a very simple way to determine how many records exists
for a certain period of time. I have a time dimension created that
represents the dates (no hh:mm:ss) of the records. The dates in the
actual data contain both the mm/dd/yy and hh:mm:ss. The problem I'm
running into is the when processing the cubes by joining the two tables
by the date fields, it is coming back blank. I traced the query sent
by Analysis Services and can see why that's the case (it's trying to
exactly match the date/time). I simply want to ignore the time portion
of the date in the fact table. Does anyone have any suggestions?

Thanks,

Greg

Greg, the thing to do would be to create a view based upon your fact/table or
view that basicly just takes the day/month/year, and use it as the column to
join back to your time dim.

For example, say your column is called MyDate, you could have an expression
like:

select convert(smalldatetime, convert(varchar(2),datepart(m,MyDate) + '/'
+ convert(varchar(2),datepart(d,MyDate)) + '/' +
convert(varchar(4),datepart(yy,MyDate))) from YourFactTable

As you can see, this takes the individual parts of your date column as
seperate strings, and puts them back together again and converts that result
to a smalldatetime. Since there's no minute/sec info, it should join just
fine back to your time dim. (This is just the select for that column,
obviously, your other measure and dim columns would be necessary in the view
as well)

Good luck.

- Phil




"greg_wilkerson (AT) hotmail (DOT) com" wrote:

Thanks Phil. I don't know why I didn't think of that to begin with.
That implementation simplifies a few other things, too. Thanks again.