Querying a data warehouse data involving time dimension

I have two tables for time dimension

date (unique row for each day)

time of the day (unique row for each minute in a day)

Given this schema what would a query look like开发者_StackOverflow社区 if one wants to retrieve facts for last X hours where X can be any number greater than 0.

Things start to be become tricky when the start time and end time happen to be in two different days of the year.

EDIT: My Fact table does not have a time stamp column

Fact tables do have (and should have) original timestamp in order to avoid weird by-time queries which happen over the boundary of a day. Weird means having some type of complicated date-time function in the WHERE clause.

In most DWs these type of queries are very rare, but you seem to be streaming data into your DW and using it for reporting at the same time.

So I would suggest:

1. Introduce the full timestamp in the fact table.

2. For the old records, re-create the timestamp from the Date and Time keys.

DW queries are all about not having any functions in the WHERE clause, or if a function has to be used, make sure it is SARGABLE.

You would probably be better served by converting the Start Date and End Date columns to TIMESTAMP and populating them.

Slicing the table would require taking the appropriate interval BETWEEN Start Date AND End Date. In Oracle the interval would be something along the lines of SYSDATE - (4/24) or SYSDATE - NUMTODSINTERVAL(4, 'HOUR')

This could also be rewritten as:

Start Date <= (SYSDATE - (4/24)) AND End Date >= (SYSDATE - (4/24))

It seems to me that given the current schema you have, that you will need to retrieve the appropriate time IDs from the time dimension table which meet your search criteria, and then search for matching rows in the fact table. Depending on the granularity of your time dimension, you might want to check the performance of doing either (SQL Server examples):

1. A subselect:

   SELECT X FROM FOO WHERE TIMEID IN (SELECT ID FROM DIMTIME WHERE HOUR >= DATEPART(HOUR, CURRENT_TIMESTAMP()) AND DATEID IN (SELECT ID FROM DIMDATE WHERE DATE = GETDATE())

2. An inner join:

   SELECT X FROM FOO INNER JOIN DIMTIME ON TIMEID = DIMTIME.ID WHERE HOUR >= DATEPART(HOUR, CURRENT_TIMESTAMP()) INNER JOIN DIMDATE ON DATEID = DIMDATE.ID WHERE DATE = GETDATE()

Neither of these are truly attractive options.

Have you considered that you may be querying against a cube that is intended for roll-up analysis and not necessarily for "last X" analysis?

If this is not a "roll-up" cube, I would agree with the other posters in that you should re-stamp your fact tables with better keys, and if you do in fact intend to search off of hour frequently, you should probably include that in the fact table as well, as any other attempt will probably make the query non-sargable (see What makes a SQL statement sargable?).

Microsoft recommends at http://msdn.microsoft.com/en-us/library/aa902672%28v=sql.80%29.aspx that:

In contrast to surrogate keys used in other dimension tables, date and time dimension keys should be "smart." A suggested key for a date dimension is of the form "yyyymmdd". This format is easy for users to remember and incorporate into queries. It is also a recommended surrogate key format for fact tables that are partitioned into multiple tables by date.

Best luck!