Hello All,

I created a time dimension from a calendar table that I created to store all
time frames.

However, when I used the calendar table it is not grabing all the dates
because of the time associated with the dates, i.e. in the fact table I have
2005-03-04 16:35:50 and in the calendar table it is just 2005-03-04 00:00:00.

I would like this join to be used :

select DISTINCT lf.datecreated, lf.adnumber, lf.adreplyid from
dbo.Leads_FACT as lf
JOIN rcadreplies as r on lf.adreplyid = r.adreplyid
JOIN rcads as a on a.adid = r.adid
JOIN rczipcode as z on z.zipcode = a.zip
JOIN rcarea as ar on ar.areaid = z.primaryareaid
JOIN rcmetro as m on m.metroid = ar.primarymetroid
JOIN calendar as cl on DatePart(year,cl.dt) =
DatePart(year,lf.datecreated) and Datepart(month, cl.dt) =
DatePart(month,lf.datecreated)
and Datepart(dd, cl.dt) = DatePart(dd,lf.datecreated)

How can I modified the joins in OLAP to the above query?

Even if it could be done the join you suggest would be very
inefficient.

The easiest way to achieve what you need would be to create a view on
your fact table which has all the fact table columns plus an extar
column that has the date element without the time element You then
use the view instead of the table in MSAS. If you don't want to create
a view you could also create the additional column using a formula (or
calculated)column on the table itself.

There are several ways of extracting the extract the date element of a
datetime - this is one I prefer:

DateCreatedNoTime = CAST (
CAST(
(DatePart(year,lf.datecreated) * 10000) -- YYYY
+ (DatePart(month,lf.datecreated) * 100) -- MM
+ DatePart(day,lf.datecreated) -- DD
AS NVARCHAR) -- 'YYYYMMDD' - works independently of SQL
regional settings
AS DATETIME

You can then join to you time dimension using an plain old equi join
with this new column, i.e.

JOIN calendar as cl
ON cl.dt = If,DateCreatedNoTime

Hope this helps

SAM wrote: