Data reduction for major Amicus POS client.

Hello team

Thought I’d just knock up a quick blog in regards to a data reduction task for one of our major Amicus point of sale system clients.

The background for this task is that the client is a large multi-site organisation based predominantly around the Supermarket and Fuel delivery industries and has been running Amicus POS (Supermarket, Fuel, & Hospitality editions) for approximately five years. They have been utilising our synchronisation service between the various sites in order to pass information to and from the three main ‘back office’ data storage servers.

As you can imagine, there was a very high number of sale related transactional data (In excess of one hundred million records on each database) that has been accumulating over this time. The data is periodically backed up with certain tables made to archive non-essential data that is older than a given threshold.
The client uses these back office servers to run all reporting and customer maintenance tasks in order to free up the leaf node servers for sale processing.

The task was given to try and reduce the overall bulk of the database by removing as much data as possible prior to the 1st January 2009.

In order to accomplish this task, a script was written to determine which tables were hogging the majority of space. Not surprisingly, it was the sale related tables that featured most highly on the list.
After some discussion with the client, we were informed that all ‘cash sale’ data (I.e. not linked directly to an account customer) could be removed if the sale entry date was prior to 2009.

Unfortunately, due to the far reaching functionality of our software, this task was not as easy as it sounds due to dependencies of other tables on these core 5-6 tables, with the pre-script generation tasks eventually encompassing some twenty odd medium sized tables.

The next group of large data was found to be in the COGS (Cost of Goods Sold – I have posted a blog with more information about COGS for those who are interested) area. As the client has recently moved away from the Average to the fixed COGS method, we were able to completely remove the data in the four primary COGS tables.

Although some consideration was put into summarising older customer data (I.e. condensing old sales into a single sale (for each site, payment type etc.), this was ultimately rejected by the client, as the effort/gain ratio was not deemed significant enough due to the fact that the nature of their business did not lend to high account-sale related data.

By targeting the 5 major tables, we were able to reduce the total number of records of the database by about 50%. If you get a similar task, some of the scripts I prepared may be useful.

Regards
Dave