Implementing Date Range in OLAP systems

Question

Please bear with me if this is a trivial question,I am a new bee I am in the design phase of a OLAP system where i need to show cost for a date range. I have three other dimension like product,vendor and language. Should I add date as one more dimension?? My queries are mostly cost on a date range like from 5-11-1997 to 01-09-2-13 Which is the best way to do it.

Answer 1

You do need to add a Time Dimension. If all the Date/Time facts are just Dates (no Time part as in the example range) then you need to create a table/view which consists of a row for each Date in the domain range.

This table can also have extra fields for things like week, month, quarter, season, year that your users may be interested in querying. (If there are none of these, then just have one column with the date.)

You would need to tell the OLAP data model that this date column in the Time table is the PK, and that the dates in other tables are FK's to it. The OLAP engine will then allow this new Time Demension to be used is queries just like any other dimensions.