Adding a fact table with start date and end date to a (SSAS) multidimensional cube

hi all,

i have a fact table with payroll data and contains columns such employee id, dollars, start date & end date (pay period). So the granularity is not at the daily level. How can we add this fact table to my cube and link to date dimension? I have a date a typical date dimension in the cube with date, month, quarter and year.

note - the start and end date do not always fall on the sames days of the calendar month. I know it is terrible idea to somehow "convert" the grain to daily level by diving the dollar amounts by the number of days between start date and end date but i can't figure out another/better option.

thanks in advance!

Usually you pick one of the dates (start or end) to represent the period. You would still store both dates.  Ask the business which date makes sense.

thank you. The business wants to look at how much they paid their staff over a period of months etc. and if I pick one date, they won't be able to do this because the dates are not serial(example: one record for one employee on 1/1/2014 and another on 1/8/2014 etc). An example scenario for business users would be - how much did we pay our staff in 2014 Q1?
Should I "convert" he fact table to a "daily gran"

It is unusual they would want a day-by-day accounting. Usually 'paid' is based on a pay period. But, if you must do day-by-day and any day is good, you would have to allocate it. I would verify this with the business…

The other issue is how would you handle variations, such as overtime, during a period? If someone wanted a true daily number, how would they arrive at it unless you have the actual timecard information.

thanks again for your reply.
they didn't directly say they want a day-by-day accounting but if they want to see how much they paid in a given quarter, i ma assuming that's what it implies?

Also, i want to understand how we should build this if the requirement was to just have it by pay period. Let's say we go by start date - how could users analyze data when there is only on record in the fact table for a given week? any advice on this will be greatly appreciated...

Never assume. If you are talking to accountants, 'what we paid in a quarter' usually means what they booked in a quarter. It usually is based on a fiscal calendar.

As far as analysis goes, you need to discuss the date with the business. It is usually the end of the week or it could be when they are paid. You would need to have a pay period dimension to support the latter.