Archiving older data

View previous topic View next topic Go down

Archiving older data

Post  revdpoel on Thu Jun 17, 2010 4:23 am

Suppose you archive detailed data which is older than three years on slower media. Decision is made. Which problems do you face then? Ok, it takes some time to load the data again if necessary. But that is no problem, as long as the user is aware of it.
What I am worried about is the fact that in time the layout of dimensions and fact-tables might change. Attributes added, which have had no meaning in the past. If then you want to reload the old facts in the existing fact table, you have to do something manually. You have to write a job to fill in the meaning of new attributes when reloading the past. The best solution would NOT to have two tables: one with the new layout and one with the layout of the past. Even worse, if more and more attributes are added in time, you get more different table layouts

How is this solved, doing it automatically, if possible in a DB2-environment?

Thanks
Ron

revdpoel

Posts : 24
Join date : 2010-06-11

View user profile

Back to top Go down

Re: Archiving older data

Post  ngalemmo on Mon Jun 21, 2010 11:24 am

This is normally handled with partitions. By partioning fact tables based on transaction dates it is a fairly simple matter to move older partitions to less expensive (but still on-line) media. Any schema changes would be reflected in the partitions as they are still part of the active database.

If your intent is to take the data offline, you should avoid using internal DB formats unless you are absolutely certain you will always be using the same DBMS (and future versions of the DBMS would be able to read the archive). A more generic approach would be to extract the data to a delimited text file and include a copy of the table's DDL with the file. Restoring would be a manual effort.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Archiving older data

Post  revdpoel on Mon Jun 21, 2010 4:01 pm

thanks for the answer

revdpoel

Posts : 24
Join date : 2010-06-11

View user profile

Back to top Go down

Re: Archiving older data

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