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’ production company to an ‘archive’ company. 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.
You can also refer to the ‘archive’ company for historical records. You can run reports, inquiries, smartlists, etc. just like you do today in your ‘live’ production company. This is useful for audit and analysis purposes.
The earliest versions of CDA simply allowed companies to move data from their ‘live’ production companies to ‘archive’ companies with no frills. You selected a cut-off date, 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.
We then added functionality to CDA that allows users to pause and resume archives so they could stop the archiving process if needed, and then resume it again from the same point later on. Initially, adding this functionality increased the lengths of archives. However, in following releases of CDA, we added a feature called “pre & post archiving” which gives users the ability to optimize table indexes and to specify how many Customers/Vendors to archive in a single batch, which drastically reduced the time it takes for archives to run.
How can CDA’s pause/resume & pre-post archive 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. a 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 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 have3,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 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 can enter any number you want. Also, if you choose this option, you can 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 to 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 must 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 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 before you leave for the day or on the weekends) 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 mydatabases?”. 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 becomes, 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.
Taking CDA even one step further, and you can now schedule your archives in advance and automate the process.
