For business management solutions email us or call 020 3004 4600

Reduce the time taken to archive your Dynamics GP data by 90%

This is a question that we hear from our customers time and time again about how they can improve the performance of their Dynamics GP solution. 

This is where Advantage can help your business through a Company Data Archive tool developed by one of our Dynamics GP partners, Professional Advantage which seamlessly sits within Dynamics GP.  

What is the Company Data Archive tool & what does it do? 

This tool provides a connection that makes it easier for your business to shift historical information from your ‘live’ company to an ‘archive’ company. What this will do is help to decrease the size of your ‘live’ company database & dramatically improve your system performance in terms of posting, reporting and other day to day tasks that you may perform as well as improve your overall efficiency within your GP system. 

Better still, should you need to access the ‘archive’ company for legacy records you can do so. You will be able to run reports and smartlists in the same way that you do within your ‘live’ production company. This can be extremely helpful when it comes to auditing and analysing your business. 

You will have found that in the earlier versions of the Company Data Archive (CDA) tool all you had to do was simply move data from your ‘live’ production companies to your ‘archive’ companies which involved you setting a cut off date, the modules you wished to archive and simply hit the archive option. After this point, Dynamics GP started to slow down as one big set of SQL transactions ran to archive everything in one go. 

This allowed companies to benefit in a number of ways: 

  • They could archive historical data which could later be used for audits and reviews. 
  • It also ensured that Dynamics GP was running at a high level of day-to-day performance. 

Following on from this, further functionality was introduced to allow users to pause & resume archives at a certain point then continue with them later. Granted, it did add time to the overall archiving process timings. However, in the CDA versions after this, a feature was included called ‘pre & post archiving’ which provides users with the opportunity to optimise table indexes as well as the ability to pick how many customers/vendors were archived in one batch which in itself dramatically reduced the time required to archive the data. 

How can your business gain from using the pause/resume & pre-post archiving features in the Company Data Archive tool? 

It goes without saying but each business is different when it comes to their day-to-day activities: some businesses accumulate different types and quantities of transactional data. You may find that some businesses such as office supplies companies only sell to a few customers in big quantities or some may sell just to individual buyers such as car parts distributors. No matter which bracket you fall into, the CDA tool will certainly help you to archive your data successfully. 

To give you an example of this, say you’re an office supplies company like the above. The features within the CDA tool will give the user the ability to select how many vendors or customers can be archived in one batch or archive them all at once. 

In the other scenario, let’s just say that you have over 3,500 customers who have all purchased a single item from your business. Moving on past the older ways of archiving these transactions one by one, a process that would have taken over 2.5 hours, the new CDA versions can achieve the same result in just one batch in less than 8.5 minutes! What a significant difference eh? 

Image 

In order to achieve the maximum archive performance, you will do it all in one go via a single SQL transaction, however the only problem with this approach is the fact that you can’t pause and resume it. This is where the option to determine how many customers/vendors you want to include in each batch proves handy as you can choose the number. Better still, if you go down this route, you will also be able to pause & resume the archives.  

This whole process largely depends on which option you wish to choose, the larger the number of customers/vendors per batch you want to archive the faster it will be run but the longer you will need to wait to pause it if you are planning on continuing to use your computer normally during the archiving process. On the other hand, the smaller the number per batch, granted the archive will be slower however you will spend less time waiting for the archive to pause. 

Image 

You may find that your business operates in a similar way to the car parts distributor example above. In this case, you will need to use two of the features within the CDA tool, choosing how many customer/vendors will run in a single batch to archive as well as optimising your table indexes. If you are looking to make use of the pause/resume functionality regularly, you will need to make sure that you only look to archive 1, 10 or 50 customers/vendors at a time which largely depends on how many transactions are involved for each customer or vendor. Put simply, the more transactions you have per customer/vendor, the smaller number you want to archive in each batch. However, if you don’t need to pause & resume at any part of the archive process, you can choose to do it all in one go. 

You will see that we briefly mentioned about optimising table indexes. Essentially, this feature was added to the CDA tool to improve your archive performance in addition to improving your overall performance in Dynamics GP. This is how it works in practice: 

You will see that the CDA tool will create an SQL query that can be ran against a database to reduce the fragmentation of the tables in that database. This essentially means that every time you use Dynamics GP, you will find that it fragments your SQL tables indexes even more. Taking this a step further, the more fragmented the tables in your ‘live’ database becomes, the worse your day-to-day performance in Dynamics GP becomes. This works in the same way in your ‘archive’ company. Therefore, taking the time to optimise your indexes will help to reduce the fragmentation in your SQL tables leading to better efficiency in Dynamics GP and within the CDA tool. 

Next Steps? 

If you are a business that is currently using Dynamics GP and has seen a noticeable drop off in your overall performance then please get in touch with one of our team of Dynamics GP experts today to see if they can help you implement the CDA tool or conduct a full Microsoft Dynamics System Audit to transform the way that you work. 

Want to get articles like the above delivered into your inbox? You can achieve this by simply signing up to our mailing list