Freezing Previous Years Data

ViperMAN

Hi All,

I have quite a complicated question so bare with me for a second.

I have a database that tracks employee’s data for the current year and previous years.

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

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

Tom Dunne

What RDBMS are you using?

ViperMAN

Tom Dunne wrote: »

What RDBMS are you using?

I'm using SQL Server 2008.

Tom Dunne

ViperMAN wrote: »

I'm using SQL Server 2008.

Well that's me out of the loop, so. Oracle is my background.

However, is there a way in SQL server 2008 to partition data (in your case, by date) and then make selective partitions read-only?

ViperMAN

Tom Dunne wrote: »

Well that's me out of the loop, so. Oracle is my background.

However, is there a way in SQL server 2008 to partition data (in your case, by date) and then make selective partitions read-only?

Thanks, I'm just reading up on it now. It looks like it could be overkill for my needs but I'll have a look at it anyway.

Any more ideas?

ViperMAN.

ViperMAN

Also note that not all users have the logical January to Decemeber start and finish to their years data.
In some cases their previous years data starts in July and ends in June, and in other cases it runs from April to March.

So I couldn't just partition tables broadly for 2008, 2009, etc as this does not suit all users.

Thanks,
ViperMAN.

amen

There are multiple ways to do this but the most important question is the years data used for a legal/statutory reporting? If so then you shouldn't really be able to change the data as your would have to restate your legal/statutory reports.

Thinking on the fly here but as a users report year may not match with a real year why not have an additional column called report year and populate it with the report year for the data.

You would leave all of this years data set to Null and then you could partition based on anything with a report year in the past?

ViperMAN

amen wrote: »

There are multiple ways to do this but the most important question is the years data used for a legal/statutory reporting? If so then you shouldn't really be able to change the data as your would have to restate your legal/statutory reports.

Thinking on the fly here but as a users report year may not match with a real year why not have an additional column called report year and populate it with the report year for the data.

You would leave all of this years data set to Null and then you could partition based on anything with a report year in the past?

Hi Amen,

The extra column could be a winner alright.

Another option I was thinking was to:
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:
---4.1 Check if today was the first day of an employees 'year'
---4.2 If yes, get all of the elapsed year's data and add them to all the previous years.

This is a simplified version obviously, but I think it might work.

Any ideas?

stevenmu

I'd suggest considering a completely different route. Since you're using SQL Server already, you could use Analysis Services to define an OLAP cube. Assuming that you haven't used analysis services before, the basic idea is that you can define an 'OLAP cube' which encapsulates all of your calculations and the data points that you report on, you can then schedule a process which runs periodically (say nightly, or every hour etc) which performs all the calculations and stores them in the 'cube'. If you want you can try to visualise it as a normal DB table being a square, the calculations you use then extend that in other dimensions to become a 'cube' and then any point in the cube contains the values for the calculations at that value.

Of course it means redoing your reports to support the cube as a data source, but analysis services is supported as a source by most things that also support SQL Server. It's also supported as a datasource by Excel so your users could build their own cool reports with pivot tables and charts, or you could very easily create a data model for the SQL Server Reporting Services Report Builder and let users easily define and share reports that way.

On the plus side it means leaving your existing app intact, and if users are editing previous years data then it's possibly with good reason and this would allow that to continue while providing instant running reports.

amen

you could use Analysis Services to define an OLAP cub

thats a nice idea.

ViperMAN

stevenmu wrote: »

I'd suggest considering a completely different route. Since you're using SQL Server already, you could use Analysis Services to define an OLAP cube. Assuming that you haven't used analysis services before, the basic idea is that you can define an 'OLAP cube' which encapsulates all of your calculations and the data points that you report on, you can then schedule a process which runs periodically (say nightly, or every hour etc) which performs all the calculations and stores them in the 'cube'. If you want you can try to visualise it as a normal DB table being a square, the calculations you use then extend that in other dimensions to become a 'cube' and then any point in the cube contains the values for the calculations at that value.

Of course it means redoing your reports to support the cube as a data source, but analysis services is supported as a source by most things that also support SQL Server. It's also supported as a datasource by Excel so your users could build their own cool reports with pivot tables and charts, or you could very easily create a data model for the SQL Server Reporting Services Report Builder and let users easily define and share reports that way.

On the plus side it means leaving your existing app intact, and if users are editing previous years data then it's possibly with good reason and this would allow that to continue while providing instant running reports.

Thanks for your input stevenmu.

OLAP Cubes looks very interesting.
I'll certainly have a look into them.

Thanks.