Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

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.
0
thomaszhwang
Asked:
thomaszhwang
  • 6
  • 6
  • 3
  • +1
8 Solutions
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 RadCommented:
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now