Approaching mixed granularity date dimensions for operational periods in cube design

Question

I am building a cube in SSAS, modelling (amongst other things) activity of engineering teams. I have a fact table (TeamActivity), with facts such as Mileage and TimeOnSite on a DAILY granularity. This references a date dimension table (DimDate). DimDate contains typical attributes so data can be analysed by calendar/fiscal month or year etc. This is all fine.

In another fact table (TeamPay) we have more facts (HoursClaimed, AmountPaid) which are stored on a WEEKLY granularity per team. These are business-specific operational weeks which run from Saturday to Friday.

Business users want to correlate the data in these two fact tables (e.g. HoursClaimed-TimeOnSite) - obviously they can't go to a "per day" level, but will want to analyse it per operational week or per calendar/fiscal month or year etc.

How can I design the cube to accommodate this? I have looked at Lower Date Granularity for FactBudget which maybe relates to my issue but not sure if this applies in my situation?

Answer 1

For me it is always much more simple to modify the raw data and push it down to the more detailed granularity level.

So in this situation I would pick either first or last day of your week (let business decide whether they want it all on Fri or on Sat) and smack all the facts on to that day of week. Connect to day level in the Date dimension and it is good to go!