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

View previous topic View next topic Go down

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

Post  SnowShine429 on Tue Nov 25, 2014 5:58 pm

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!

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Nov 26, 2014 3:24 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  SnowShine429 on Wed Nov 26, 2014 10:18 am

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"

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Nov 26, 2014 1:23 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  SnowShine429 on Wed Nov 26, 2014 4:09 pm

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...

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Nov 26, 2014 7:08 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum