Date Dimension and Time Dimension

If in a dimensional modeling, I have a Date Dimension and Time Dimension connecting to a Transactions Fact table, is there a query that I can use to query all the transactions happened in the past 4 hours?  Thanks.
thomaszhwangAsked:
Who is Participating?
 
ValentinoVBI ConsultantCommented:
Something like this?
with TheData as (
	select F.*, DD.Date, DT.Time, DD.Date + DT.Time as FullDatetime
	from FactTransactions F
	inner join S862.DimDate DD on DD.DateID = F.DateID
	inner join S862.DimTime DT on DT.TimeID	= F.TimeID
)
select * from TheData
where FullDatetime between DATEADD(hour, -4, GETDATE()) and GETDATE()

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Thomas,

You have a date dimension AND a time dimension?  

Your architect needs to be shot.



Kent
0
 
ValentinoVBI ConsultantCommented:
No he doesn't.  If you'd need second-level facts, how many records would your dimension contain?  I don't think you'll approve of the performance!

More info: http://www.kimballgroup.com/html/designtipsPDF/DesignTips2004/KimballDT51LatestThinking.pdf
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ValentinoVBI ConsultantCommented:
More info on that Time/Date split-up from a Microsoft perspective (halfway down the page): http://technet.microsoft.com/en-us/library/aa902672%28SQL.80%29.aspx
0
 
thomaszhwangAuthor Commented:
I used something like this.  d is the date dimension table and t is the time dimension table.  Bad on performance?

CAST(CAST(d.Date_Value AS VARCHAR) + ' ' + t.Time_Value + ':00' AS DATETIME)
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Hi Thomas,

How many rows are in the date, time, and fact tables?  About how many do you expect to be selected?


Kent
0
 
thomaszhwangAuthor Commented:
Well the number of rows are definitely not huge (less than 100,000)
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Well, with a relatively small dataset (100,000 rows) you can probably live with the performance.  Try it.

The better query may be one that limits date/time manipulation.  It's trickier to write, but should get better performance.

SELECT *
FROM facttable f
INNER JOIN datetable d
  ON d.id = f.dateid
INNER JOIN timetable t
  ON t.id = f.timeid
WHERE d.date >= date (current_timestamp - 4 hours)
  AND t.time >= time (current_timestamp - 4 hours)

That query works when the time is 4AM or later.  I needs to be modified to handle the rows from late yesterday with the current time is before 4AM.


Kent
0
 
ValentinoVBI ConsultantCommented:
That query works when the time is 4AM or later.  I needs to be modified to handle the rows from late yesterday with the current time is before 4AM.

That's the reason why I used a Common Table Expression (CTE) in my first post :-)
0
 
thomaszhwangAuthor Commented:
I'm using SQL Server 2008 R2.
0
 
thomaszhwangAuthor Commented:
@ValentinoV

CTE does not work on SQL Server 2008, does it?  Thanks.
0
 
thomaszhwangAuthor Commented:
@ValentinoV

It works.  I just looked up its usage.  Sorry.
0
 
ValentinoVBI ConsultantCommented:
You're faster than me :-)

CTEs exist since SQL Server 2005.
0
 
Reza RadConsultant, TrainerCommented:
could you let us know structure of date and time dimensions and fact ?
0
 
ValentinoVBI ConsultantCommented:
Thomas, you said "it works" in your last post.  Does that mean that your problem is solved now?
0
 
thomaszhwangAuthor Commented:
Thanks for your help, guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.