Current Time

Hi. I have a critical design issue. Not a regular time dimension case.

i work for a set of schools where each school academic has a different academic calendar. say some schools start in august, others in september.. i generated the date keys per school to populate the time dimension. so my time dimension looks like this:

DATE_KEY,

DATE_SCHOOL,

DATE_DATE,

DATE_YEAR,

DATE_MONTH,

DATE_WEEK,

DATE_DAY_NUMBER,

DATE_FISCAL_ACAD_YEAR,

DATE_FISCAL_TERM, DATE_FISCAL_WEEK,

DATE_FISCAL_WEEKDAY_NUMBER,

DATE_FISCAL_ACAD_YEAR_DAY_NUMBER,

DATE_HOLIDAY, (y/n)

DATE_WEEKEND, (y/n)

DATE_DAY_NAME,

DATE_FISCAL_TERM_FIRST_DAY,

DATE_FISCAL_TERM_LAST_DAY,

DATE_FISCAL_WEEK_FIRST_DAY,

DATE_FISCAL_WEEK_LAST_DAY

I need help in finding a design to get the current term/current week/current day so that a school manager would get his current's school week's data or term data when he logs in to the system.

Thanks

Could it be that there are only a few sets of dates applicable? In which case you could create Calendars listing the relevant dates and link this back to the school.|||

I added fields to the schools dimension stating the current acad year, term,week,and day per school. and these fields are to be updated at each ETL process. I will use them in the queries.

thanks