Reduce the Lengths of Your Microsoft Dynamics GP Archives by More than 90%
What is CDA?
Company Data Archive (CDA) is a tool developed by Professional Advantage that works within Microsoft Dynamics GP. It provides an easy way to move historical information from your ‘live’ company to an ‘archive’ company. History records provide useful information for audit and analysis purposes. Archiving your data will reduce the size of your ‘live’ company database and improve the system performance of posting, running reports, and other daily tasks. It will also improve the efficiency of larger, less frequently performed tasks such as file maintenance.
The earliest versions of CDA simply allowed companies to move data from their ‘live’ companies to ‘archive’ companies with no frills. You selected a cutoff date and the modules you wanted to archive and pressed the archive button. The lights in the building then dimmed and Dynamics GP would appear to be ‘Not Responding’ as one big set of SQL transactions ran to archive everything at once.
Companies benefited for two reasons. Archiving historical data allowed businesses to store pertinent information for audit and analysis purposes. It also kept Microsoft Dynamics GP lean in the ‘live’ company to maintain a high level of day-to-day performance.
Functionality was then added to CDA to allow users to pause and resume archives so they could stop the archiving process if needed and resume it again from the same point later on. Initially, adding this functionality increased the lengths of archives. Recent developments in CDA, however, including allowing users to optimize table indexes and to specify how many Customers/Vendors to archive in a single batch, have now drastically reduced the time it takes for archives to run.
How can CDA’s new features benefit your company?
Based on the structure of the day-to-day business you conduct, companies accumulate different types and quantities of transactional data. One company may sell only a few items each to many distinct buyers (e.g., convenience store selling millions of candy bars a few to each customer). Another company may sell many items each to many single buyers (e.g., a car part manufacturer selling millions of parts each to thousands of service centers). Regardless of which of these companies your business most closely resembles, CDA will efficiently archive your data.
Say your business is structured to resemble the convenience store. CDA’s new feature of allowing the user to specify how many Vendors or Customers to archive in a single batch, or archive them all in one batch, caters to you. For illustrative purposes, say you have 3,500 Customers who have each bought 1 item from you. Instead of archiving these transactions 1 by 1, a process which in one of our test environments used to take 2.5 hours, the new version archives all of these transactions in a single batch in as little as 8.5 minutes!
For the fastest archive performance, you want to select the option to archive All Customers/Vendors in a Single SQL Transaction. This option, however, will not allow you to pause or resume your archive. This is where the option to specify the number of Customers/Vendors to archive per batch is useful. You are allowed to enter any number you want. Also, if you choose this option, you are allowed to pause and resume your archives. For example, say you specify you want to archive 10 Customers/Vendors in a batch. You start the archive and leave for lunch. If your archive is still running when you return from lunch, simply click the Pause button, CDA will finish the current stack of 10 Customers/Vendors it is archiving (this only takes a few seconds), and you are free carry out your daily tasks without having to compete with CDA for your computer’s resources. Before you leave for the day, simply click Resume, and your archive will continue where it left off.
The larger the number of Customers/Vendors per batch you want to archive, the faster the archive will run, but the longer you will have to wait for the archive to pause if you need the full use of your computer for another purpose. The smaller the number per batch, the slower the archive will run, but the less time you have to wait for the archive to pause.
Maybe your business is structured more like the car part manufacturer from the second example mentioned above. This scenario warrants the use of two CDA features, specifying the number of Customers/Vendors per batch you want to archive AND optimizing your table indexes. If you determine you will need to utilize the Pause/Resume functionality frequently, you may want to archive your Customers/Vendors only 1, 10, or 50 at a time, depending on how many transactions you are archiving for each Customer or Vendor. The more transactions per Customer/Vendor you have, the smaller the number of Customers/Vendors you want to archive per batch, and vice versa. If you feel you have no need to pause or resume your archive (e.g., you run the archive over lunch or before you leave for the day) you can specify a large number, or you can choose the option to archive everything in one batch.
I mentioned optimizing table indexes in the last paragraph. You may be wondering, “What does ‘optimizing table indexes’ mean?” or “How do I ‘optimize table indexes’ in my databases?”. CDA offers the feature of optimizing table indexes to improve archive performance as well as the performance of Dynamics GP in general. Simply put, here is how it works. CDA will generate for you a SQL query that can be run against a database to reduce the fragmentation of the tables in that database. Basically, your daily use of Microsoft Dynamics GP (entering, adding to, reversing, voiding transactions) causes your SQL table indexes to become fragmented. The larger and more fragmented the tables in your ‘live’ database become, the more your day-to-day GP performance will be negatively affected. The larger and more fragmented the tables in your ‘archive’ company become, the more archive performance will suffer. By optimizing your indexes, you reduce the fragmentation in your SQL tables which in turn optimizes the efficiency of Dynamics GP and CDA. See Matt’s blog post, Optimizing Indexes and Improving Performance in Microsoft Dynamics GP for more in-depth information on optimizing your indexes with CDA.