AboutYourData on Google PlusAboutYourData on linked in

Confectionery - Data Migration Case Study

Problem: A wholesaler had selected a new computer system to run their business. Their current system was DOS based with no way of exporting the data from their current system into their new system.

Consequence: They were faced with the tedious and time consuming task of keying their data into the new system which was time consuming, prone to error and had to be done a number of times - for training, for user acceptance testing and finally for going live. To make matters even more difficult, the configuration of the new system had to be completed before they could start. Having to key the data would have meant a business outage of almost a week when they went live.

Solution: Even using Excel to manipulate the data into the correct format was difficult as it was also subject to user error and there was no guarantee that it would be done exactly the same way each time.

We created query reports that contained the data we needed to migrate and directed the output to files rather than to a printer. Using the Data Migration Tool (DMT) we were able to extract the required data from the printer reports, identify any data elements that were incorrect or inconsistent so they could be fixed then created new files in the format required for the new system ready for import. Once the Data Migration Maps had been defined we could run the entire data migration over and over again whenever it was required.

Cleaning Products - Data Migration Case Study

Problem: A manufacturer / wholesaler with a number of sites around Australia and New Zealand had selected a new computer system into which they intended to consolidate the independent systems at each site into two consolidated systems, one for Australia and one for New Zealand. The financial systems, which also had to be consolidated for each site, were being processed by their parent company.

In the new system they also wanted to make changes to a number of data elements – inventory part numbers, warehouse numbers and formats and general ledger account numbers and formats to name a few.

Consequence: With multiple standalone sites they needed to verify that all the data was consistently described before it could be consolidated or merged. A significant amount of data also had to be transformed into the new format required for the new computer system and it had to be done in a specific sequence to ensure that the integrity of the new database was not compromised.

Solution: We were able to download the required data from the standalone databases and from the consolidated systems in the parent company – over 140 separate files. We used the data migration tool to compare similar data from each site and report data elements that were described inconsistently. We found inconsistencies in part descriptions, units of measure and bill of materials. These were corrected in the source systems and the data re extracted and checked. As the data was migrated and consolidated, changes were also made to the data elements that were to be “renamed” in the new system. The resulting 14 files were then loaded into the new system with no errors. The entire process took 90 minutes from start to finish. We ran this many times to validate the configuration of the new system, train users, perform user acceptance testing and ultimately go live.

Food Manufacturer/Wholesaler - Data Representation Case Study

Problem: A large multinational corporation ran payrolls for over ten subsidiary companies using three different payroll systems. They wanted to consolidate these different payrolls into one centralised (new) inhouse payroll system. The format of the data to be entered was different for each of the existing payrolls while the conversions required to create test data for the new system were complicated and being changed constantly.

Consequence: There was no easy way to create test data for the new consolidated system so that the outputs of the new payroll system could be compared to the existing payroll systems. The only option was to run partial tests over a small subset of data or spend time converting the test data into the required format. The complexity of the changes required was tedious, time consuming and error prone.

Solution: We were able to export the input transactions from the three current payroll systems as printer reports directed to a file rather than a printer for some systems and excel spreadsheets for others. Using the Data Migration Tool, we extracted the payroll date from each of the three separate file structures into a single generic format. We then used the Data Migration Tool to transform the resultant data set into transactions that could be input into the new system, incorporating all the required data transformations. This meant that once the existing payrolls had been completed, the same input data could be extracted and converted into the format for the new system. The payroll was then run for the new system.

Although not required for this project, the Data Migration Tool could have also extracted data from all systems after the payroll had been run and compared the results to validate that they were identical.

Education Provider - Data Migration Case Study

Problem: An education provider used two separate systems to manage their business. One for contacts (potential students) and one for the students, their courses and results. A new system had been selected to meet their expanding business requirements and the data from both systems was to be migrated to the new system. There was no unique data element that connected data in one system with data in the other system. While the data from each existing system could be exported, the data from one had all the data relating to a student in one variable length Excel record. Due to source system limitations, the data had to be downloaded in six parts (with different data structures) rather than one.In addition, there was no consistency in the way data was entered or the format of the information. In particular the data entered concerning the names and addresses of the contacts and students was very poor.

Consequence: As their was no way to match the data in the two different systems the matching had to be done using a combination of last name, first name, date of birth and email address. The process was complicated by the large amount of missing or incorrect data. The customer wanted to improve the quality of their data during the migration so all the data in the new system could be presented in a consistent format and as accurate as possible.

Solution: After extracting the existing data from the two systems, we used the data migration tool to identify inconsistencies in the data so that they could be corrected. The data was then extracted again and the data cleaned and formatted so that each data element was presented consistenly. We even detected a few duplicate students and consolidated their results. Using a combination of data we matched those records that were a certain match and reported on those that were close but would need some human intervention to make them match. Mostly, this was making the first name consistent, for example “Matt” in one “Matthew” in another. Once the data was linked, a new student number was allocated and the files created for upload into the new student management system.

Food Manufacturer/Wholesaler - Data Representation Case Study

Problem: A manufacturing wholesaler used forecasts to determine how much stock to manufacture and how much inventory to hold in order to meet the demand forecast for one of its bigger customers. Although they had the data available to monitor their ability to meet these requirements, they had no way of measuring their performance against forecast on a day to day basis. The calulations that needed to be made required more than 4 hours work in Excel each time to produce the report. Even then, there was no certainty that a mistake hadn’t been made when manipulating the data.

Consequence: As a result, it wasn’t until the end of the period that the sales people knew if they had made their forecast or not. On occasions this also meant that they didn’t have enough stock to fill their orders or ended up with too much stock.

Solution: Using three reports that were produced on a daily basis, we used the Data Migration Tool to extract the relevant details from each report, consolidate the information at product level and produce a report that showed invoiced sales, open orders and future orders along with a measurement of current forecast vs expected sales amount. This report takes less than 60 seconds to process and allows the sales staff to identify areas where more or less stock may be required, making it easier to control inventory levels, sales forecasts and giving their manufacturing team time to react to changes in the manufacturing mix.

Service Consultancy - Data Process Automation Case Study

Problem: A small service business spends a day each month processing transactions into its accounting system so that it can produce its BAS at the end of each quarter. Apart from invoicing, most of the transaction data comes from bank staments and credit card statements.

Consequence: The process is time consuming, tedious and takes time away from its service business.

Solution: Using the Data Process Automation Tool, the information from the bank statements is downloaded from their Internet banking site. The description is used to create transactions for their accounting system. 

These transactions are then processed by the Data Entry Automation Tool using the existing accounting system user interface. The process now takes 10 minutes each month.