Data aggregation-loading, time dimension perspective

Question

I have two questions.

Source table contains data at weekly level. We have a fact table with Week_Key, Month_Key, Quarter_Key (& a beautiful time dimension).

1. Would it be possible to load-populate Month_Key & Quarter_Key in the fact table for the source data which is at weekly granularity ? Problem here would be the last week of the month which falls under the current month & the next month.
2. Anyhow if we load weekly data by populating the Month_Key (considering current month for last week) & so Quarter_Key, would it be possible to aggreate data Monthly & Weekly seperately at report level ?

Your thoughts on this would be very helpful ...

Answer 1

If I understand correctly, you have multiple different rollups and you are trying to handle them.

I think the best solution is for the fact table to be at a daily granularity. This is the unit of time that best rolls up into weeks, months, and quarters.

The time dimension would then contain the week, month, and quarter values for all days.

You would then roll-up the data to the appropriate level for reporting purposes. If the data is very voluminous, you may also incorporate summary tables at common levels of aggregation to improve performance for users.