Freezing Previous Years Data

I have a database that tracks employee’s data for the current year and previous years. In examples below I will use calendar years (years start in Jan and end in Dec)- this is not always the case, some users have their year running from July to June- or April to March, etc.

There are many tables that, with a few heavy calculations, make a view of the employee’s data at this point in time.

This current year’s data is what users look at mostly. But data from previous years impact on this year (so, a change made to the 2008 data- will have a knock on effect on 2009, and then into 2010 and so onto this year).

Obviously, this has a negative impact on performance when viewing reports as viewing this year’s data will mean trudging through all the previous years- calculating and creating开发者_StackOverflow views until the end result is found. As the application ages, this problem will get worse and worse- say in 2015, anybody using the system from its inception (2008) will be waiting for a long time to get their data.

We plan to freeze previous years data so instead of having data from 2008, 2009, 2010 and this year’s data- we would have one block with the previous year’s data (with all calculations done for those previous years) and this year’s data.

In this way, we would have the end results data for all the previous year’s already calculated and we would only need to add to this year to get the final result.

Obviously, we would have to prevent users from entering/updating data in previous years.

My question is what is the best way to achieve this? I presume you would need some process that waits until the new year and does some calculations.

Thanks in advance,

ViperMAN.

the approach you describe is normally referred as data archiving, you can have some queries a DBA runs manually every year the first working day after the new year party so the calculated data is prepared and stored.

Also, your application needs to deny users to modify previous years data, if I have got it right.

One approach I was thinking works as follows:

1. Create a table that holds the result of the previous years calculations.
2. Prevent all addtions/deletions/updates to previous years from the app tier.
3. Change reporting so that queries would consult this table instead of trudging along, calculating everything out each time.
4. Have a daily process that would: Check if today was the first day of an employees year- If yes, get all of the elapsed year's data and add them to all the previous years.

Obviously, this is a simlified version- but one that I think could work.

Thoughts?

If you have data that should not be edited, and you can define what that data is, then I would use a combination of stored procedures and security settings to ensure the old data stays accurate.

If you used stored procedures as a filter, you can have logic in your stored procedure that checks the record against the current DateTime and only allows the update if everything fits your requirements.