Hello!

I facing a tough design decision, so I thought I ask you guys what do you think.
I need to build data archiving for my system. I have a ticket table with 2 mill records. There are additionaly 8 other tables holding ticket's child records. The ticket table has only 15K open tickets, i.e. there are 2 mill - 15K closed tickets. The idea is, to make a copy of the ticket table and the 8 other tables, make an escalation that will check which records need archiving, create a copy of the records in the archive tables and then delete the original records.

Does this make sense?

When do you guys move your records? What is your archiving criteria? Our original idea was to leave closed tickets for 12 months in the ticket table and then move them into the archive table. This isn't good idea from my perspective, since we then still habe more than 1 mill records in the ticket table, which is still a lot! My original idea was, to archive every ticket that goes into status closed, becuase this gives me a table with only 15K records, which of course is a dream. The problem with that is, that many users are searching for closed tickets, so they will probably need to search on a separate table. Alternatively I was thinking about implementing a convenience option letting them search on the main ticket form as the did so far. If I don't find the ticket in the ticket table I could ask, if I should check the archives for them. Except for the case when they search for Status = 'Closed', which tells me, that I can start searching in the archive table immediately and skip the search on the ticket form. What do you think about that?

The middle thing would be something between 12 months and immediate archiving. But I'm still unsure about the exact no. of tickets I should leave in the main table to achieve a major performace jump.

Apart from that, there will be still lots of data in the archive table. I assume, that this data should be backed up every x months. What would be a reasonable amount of data to leave in the archive table?

So what do you think about this whole solution? I need to mention, that this is a global system, so there are no real after hours. Will this solution affect performance?

Regards


Anonymous

not much traffic here... after 2 days only 1 view and 0 replies.

There are a lot of details missing from what you're asking...

• What version of ARS?
  
• What Operating System?
  
• How powerful is your hardware platform?
  
• What RDBMS & Version?
  
• What are the business rules around keeping records available for use/review? (ie, do you have internal/external Audit requirements?)
  

Basically, how often and which records you should archive are generally Business Decisions made by your customers/Business Process Owners, and could be different for each application you run... for example, we can't archive anything from our Help Desk system until it has been Closed for 18 months... but at that point, I can move those records and records from two related forms into archive forms, and delete all related records in 8 other forms. Whereas, in a second application, they want me to archive items off every 6 months, and don't care about related data...

Then, there's always the question of how long the data should stay in the archive forms before being deleted from the system. Again, usually determined by Business needs.

Optimal performance from your forms (what's the "best" number of records to keep 'alive') is dependent on your server size, number of users, thread configuration, memory, indexes, etc. etc. etc... there is no *magic" number. The Performance Tuning class at Remedy is really good at covering all the information you'd need to know to make an educated guess for your specific environment.

Anyway, I guess that a start to an answer for you...

Hi Matt,

>What version of ARS?
ARS 5.0.1

>What Operating System?
HPUX 11

>How powerful is your hardware platform?
Very powerful

>What RDBMS & Version?
Oracle 8.1.7

>What are the business rules around keeping records available for use/review? (ie, >do you have internal/external Audit requirements?)

Users should be able to view closed records and use them as templates for new tickets. Closed tickets however cannot be re-opened


>Basically, how often and which records you should archive are generally Business >Decisions made by your customers/Business Process Owners, and could be different >for each application you run...

Correct. However, business is asking for performance of the tool as well, so we need to decide how many closed tickets do we really need in the ticket table and how much of them should we archive. So what do you usually do? How many records would you leave in the ticket table if you would be facing the decision?


>for example, we can't archive anything from our Help Desk system until it has been >Closed for 18 months... but at that point, I can move those records and records >from two related forms into archive forms, and delete all related records in 8 >other forms. Whereas, in a second application, they want me to archive items off >every 6 months, and don't care about related data...

Sure, I understand that. The problem is, that sometimes business decisions need to get support from the operations team, becuase for example there are to many tickets in the ticket form and that impacts the performace. So what do you do then?


>Then, there's always the question of how long the data should stay in the archive >forms before being deleted from the system. Again, usually determined by Business >needs.

I sounds like you never get any performace problems. What do you archive the tickets then?


>Optimal performance from your forms (what's the "best" number of records to >keep 'alive') is dependent on your server size, number of users, thread >configuration, memory, indexes, etc. etc. etc... there is no *magic" number. The >Performance Tuning class at Remedy is really good at covering all the information >you'd need to know to make an educated guess for your specific environment.

I know all of that theory. I just wanted the confirmation, that other people are guessing too.

>Anyway, I guess that a start to an answer for you...
Yes, indeed. Thanks.

If you are not allowing reopens of closed tickets, is there a reason to keep *any* closed tickets in your main form? I mean, you can run reports off of an archive form just as well as on your main form, so I would guess that there's no reason to keep them there. I'd probably suggest something like the following to your Business Process Owners:

• Create an Archive Form for each form you wish to archive
  
• Create workflow to automatically move items to the archive forms when they become 'Closed'
  
• Create a Display-only view of the archive forms (maybe a control panel-type interface to allow for querying/reporting) that allows them to Create a new Request using the archive record as a template
  
• Create workflow to delete the records from related forms that are no longer needed.
  

Basically, the fewer records in your 'live' form, the better... so, if you can get away with moving all of the Closed records, I would do it. Just be sure to build the appropriate controls on your archive form so that none of that data can be modified... just used for reporting and templates...

Hope that helps,

Matt R.

ps- this is all just my personal opinion... Take it with a grain of salt!