Data Conversion White Paper
Data Conversion Options
Preparation for a data conversion
Checking data integrity
Starting to use the new system
This document provides guidance for law firms and other users of legal software (“firms”) considering or undertaking a Data Conversion from one system to another. It details how the Data Conversion processes may vary, what should be expected of the new system supplier and the responsibilities of the current system supplier.
Typically, a firm will have a considerable amount of client, matter, accounting and document history relating to both the cases it has worked on and the firm’s own accounts. When a firm takes on a new Practice Management, Case Management or Legal Accounting system, it is usually necessary to transfer some or all of the data from one or more existing systems to the new system. This process is known as a Data Conversion, Data Transfer or Data Migration.
The scope of the required data conversion will vary from firm to firm and from system to system. Depending on the scope, Data Conversions can be complex and time-consuming, requiring input from the old system supplier, the new supplier and the customer. The more complex conversions will require significant planning and usually a trial-run to ensure the end result meets the customer’s needs.
Data Conversion Options
A data transfer from one system to another can be a complex process, because each system will hold data in a different way. For example, one system could store an address in one field and another system could store the address in multiple fields. In this example the address field would need to be broken up into town/street etc. during the transfer process. Different systems will have different levels of sophistication in the data. For example, one system could have a link between a bill record and the time transactions included on the bill, whereas another system might not record this level of detail. All of this impacts the viability and technical challenges of a transfer.
Data transfer from one system to another can be completed at many different levels. One end of the scale would encompass perhaps just static data for live clients and matters, through to the other end of the scale where all data including historic transactions and other ledgers such as a Purchase Ledger would be included. Generally, the options would be;
- Static Data only: This would include static client and matter data with no balances or transactions.
- Static Data and Balances: This would include static client and matter data with top level balances only, but no transactions behind those balances.
- Static Data, Balances and Transactions. This would include static client and matter data, balances and transactions.
- Full Transfer: Includes all static data, balances, transactions, case management and peripheral data across the whole system.
Thought needs to be given to the old system. It is possible that the old system could be retained for a certain time for reporting and enquiry purposes. In other situations it is likely the old system won’t be available for long when the new system is live. In that scenario reports should be taken from the old system which will meet the requirements of external auditors and the SRA. Typically, these would include aged creditor, aged debtor, matter balance, nominal ledger, WIP, and purchase ledger reports etc. It is vital that these reports are taken at the point of closing the old system and should match the new system’s reports exactly. These reports need to be retained for the long term.
Preparing for a data transfer is critical. It is productive to tidy up the data in the old system as much as possible before beginning the process. It should be remembered that a data transfer, at transaction level, will probably transfer all data including records that don’t balance etc. so the adage of garbage in garbage out applies. A data transfer is probably not going to be a magic solution to long standing data irregularities.
Even where a full data transfer has been agreed there may be some detail lost during the transfer. For example, one system could store details of partially paid and billed disbursements. In an older system it is possible that this type of detail may not be available or may not be obtainable for other reasons. A test transfer should highlight circumstances where data cannot be transferred, and steps can be taken to progress accordingly.
For a Data Conversion to be successful the first thing that is needed is access to the firm’s current data. It should be recognised that the data is property of the firm and it is entitled to a complete copy of its own data in its entirety. This is usually easy to achieve as the firm’s data is often stored in a single standard database and there is no reason for the supplier not to allow the customer unfettered access to a backup of its own database. Where the data is not stored in a single industry standard database or where the supplier is not willing to release the database in its native format due to the structure containing its own proprietary intellectual property, then the current supplier must make a complete copy of the data available in another format. This could be either XML with a DTD, Microsoft Access, CSV, Excel or another industry standard format that allows for a complete copy of the firm’s data to be provided. It is not expected that a charge should be made for providing the firm a copy of its own data.
A firm should expect to receive a copy of its data within a reasonable time period of request. It may normally expect its incumbent supplier to require any outstanding fees to be settled prior to receiving a copy of the data. A firm may wish to refer to the supplier’s contract to check precise terms.
Along with the structured data that is normally held within a database, the firm may have a significant amount of case related documents which have been stored as a function of its current system. These documents are the property of the firm and there needs to be a practical method of obtaining the firm’s entire document history. This should be either in documents organised into logical folders or a complete set of documents along with an index file or table to provide details on which documents are related to which cases.
For any data extract, reports generated by the current system will provide an important mechanism to validate both the extracted data and any completed data conversion. It must be recognised that reports (even when exportable) cannot in themselves be relied upon to provide a complete copy of the firm’s data. Depending on the requirements and scope of the conversion, it may be prudent for the firm to print and store (either in hard copy or electronically) as many reports as it can produce from the current system for future reference.
In some situations; for instance, where the firm may be undertaking the data conversion on its own or where the new system supplier does not have the necessary expertise to work with the firm’s native database or the exported tables that the current supplier has provided, the current software provider may be required to provide assistance with producing a subset of data in a specific format. In this instance, as work will be required by the current supplier, a reasonable charge may be applicable. Any charge should be a realistic reflection of the work that is required and not a punitive charge for the supplier losing a customer.
For a successful data conversion, it is prudent to have at least one trial conversion prior to the live conversion. A trial conversion gives the firm an opportunity to check that all expected data is present in the new system and that it operates in the manner anticipated. Where the current supplier has been required to provide a specific data extract that deviates from a backup copy of the firm’s database it should be kept in mind that it will be required to provide this data for both the trial and the live conversions.
When completing a data conversion, some data may need to be transformed or translated to match differing formats or structures employed in the old and new systems. This could be as simple as transforming fee earner references from numbers to initials, or more complex where a more primitive chart of accounts and nominal references are transformed into a different structure to accommodate the new system.
For example; Fee Earner John Robert Smith might be referenced as Fee Earner 1 in one system but referred to as JRS in the new one. Where these transformations take place, it is important to check that they are implemented consistently across the whole of the data conversion. As well as the Fee Earner record holding the new reference, all associated matters, bills, time recording entries will also need to have the new refence implemented.
The considerations for data transformations will vary significantly depending on the types of systems that are involved. Most data conversions that involve Legal Accounts or Practice Management systems will require some level of data transformations. Where these are likely to occur the new supplier should make these apparent to the customer. It will be the firm’s responsibility to ensure that it is happy with the transformations and are able to adjust any internal processes to accommodate them.
Some data conversions may be too complex to achieve even with data transformations. Case Management Systems may be so disparate in their design and implementation that a conversion is not possible, even with data transformations. In these instances, the firm will have to plan for how it will continue to progress ongoing cases that are utilising the current Case Management System.
Many firms will have systems that store documents and other files with cases, clients and even transactions. These could include Microsoft Word Documents, PDF Files, Pictures, Videos, Audio Recordings, Invoice Documents and any other type of file that might be associated with a client, matter, transaction or other entity.
Bringing these documents forward to the new system will often be a fundamental requirement of the data conversion as they will be vital for ongoing cases and needed as reference for completed cases.
Where these documents have been stored via the current system, they will usually be either organised into folders that follow a logical scheme related to the client or matter references or will be in one or more directories and the firm’s database will hold a record of which file relates to which client, matter or other entity. Where these documents are stored on a local file server, it will usually be relatively simple for the Data Conversion process to be able to migrate them from the old system to the new one. Where they are stored in the cloud, the current supplier must provide a mechanism for them to be downloaded via a mapping, ftp site or similar. Requiring the firm to download them individually or by emailing them would be not reasonable or practical. It would not be considered reasonable for there to be a charge associated with the firm obtaining or downloading its own documents.
Where possible, the creation and modification dates and times should be retained on the files if this information is not stored in the database.
There may be a significant volume of data files stored by the firm, so it will be necessary to give consideration to how long these may take to download, copy or move to the new locations.
When a trial migration is to be performed, it may be practical to only perform the trial on a subset of the documents that are stored.
Preparation for a data conversion
In order to be able to convert the data accurately the firm will need a means of checking the data after the conversion. The LSSA strongly recommends a test conversion is done before the live data conversion to give ample chance to check the data has converted correctly and all the required data has been transferred. Doing a test conversion will allow corrections to be made which may prove to be very difficult after a live conversion when the system is being actively used.
The best way to check the data is to prepare a check-list plan of the data that is required/expected to be converted and produce a set of reports at the same time the data is taken for the conversion. These reports can then be used to check the converted data has come across correctly and matches the original data; e.g. a full list of matters that includes financial balances would allow a firm to check the overall totals as well as spot check individual clients and matters.
The level of data conversion required would dictate the variety and format of the reports used to check the data. A simple headers and balances conversion could be checked with a matter list and address list. A full detail conversion including all transactions would require random ledger cards showing both financial and time details along with full lists, cash book reports and nominal lists etc.
The following set of reports can be produced by most systems and are a good starting point for a basis to check the data following a conversion but must be produced at the same time as the data is taken for conversion. All reports should include overall report totals.
- Matter full list showing current balances on the client, deposit, disbursement, office ledgers and any work-in-progress. This report is normally one line per matter.
- Aged debtors report. This report is normally one line per unpaid bill.
- Aged WIP.
- Trial balance. This report shows all the nominal accounts.
- Balance sheet.
- Profit and Loss report.
- Aged creditors report. This report is normally one line per unpaid supplier bill.
- Cash book report for each bank showing opening balance and un-reconciled/un-presented transactions.
- A financial ledger report showing the detail behind the financial balances for a matter. A firm should choose several (circa 10) matters with a selection of entries.
- A financial ledger report showing the detail behind the financial balances for several nominals.
- A time ledger report showing the detail behind the time balances for several matters.
Not all details contained within a system can be printed through reports. To check this data an agreed set of screen shots should be taken and produced showing the relevant information that needs checking against the client/matter which it belongs to. A random set of files can then be used as the basis to check the conversion has worked correctly.
Some of the data which will be converted may be sensitive. A firm should consider entering in to a non-disclosure agreement with its new supplier, particularly if it is asking the new supplier to view (in any way) any data prior to a conversion; or prior to having signed a contract with the new supplier; or if it considered the new supplier’s contractual data protection provisions inadequate.
A firm should seek clarification from its incumbent supplier with regard to its data deletion policy post contract termination. If a supplier advises that data will be kept for a period of time without giving access, a firm should consider how it can fulfil its obligations as a Data Controller when it cannot access data that it is responsible for, taking into account that some systems involve third party applications. A firm should make sure that data from the third-party applications is deleted as part of the data deletion process.
Checking data integrity
A firm’s provider should go through certain data integrity checks to ensure that the data it returns is as precise as possible, for example the balances must match the original system. Regardless of this, checking the integrity of the data remains the responsibility of the firm. Unless there is unlimited information and a considerable amount of time available it is highly unlikely that every record will be checked. The more information that is checked the better. This is tedious work but will greatly minimise the risk of incorrect information on records which could lead to costly mistakes.
Doing a test conversion is always better than going straight for a live conversion, as it reduces pressure and allows more time for a thorough check of the data. The live conversion is then much safer and it will only be necessary to check a random set of details and that totals agree, along with any corrections and issues found in the test conversion.
The first thing to check is that the balances agree (if they have been converted) and then to begin to use the system as in a live environment.
Detailed and timely acceptance checking/testing is critical for a successful conversion. It is the firm’s responsibility to dedicate sufficient time and the right staff to thoroughly test the converted data. The testing team will likely comprise one or two staff members who are familiar with the firm’s data.
During acceptance testing, the following should be checked to make sure existing data reconciles with converted data:
- Was data converted according to the rules agreed?
- Are there any required fields in the system which were overlooked during the test conversion?
- Were the correct number of records from the data created in the new system?
- Are there any reports that are required from the new system?
- Is further data cleansing required prior to the final conversion?
- If there are known problems with existing data, was any action agreed to address these during the data transfer?
While the goal of the test conversion is to be as accurate as possible, it is quite likely that some items will not convert as expected and it may be prudent to run a subsequent test to verify any corrections which may be requested.
During acceptance testing, the testing team should document in detail any inconsistencies or problems, summarise the findings in a standardised format and send that information to the supplier for correction. After determining which items are to be corrected, the supplier and the firm should determine if another test conversion is required. If only a few simple issues are found, another test run may not be necessary prior to the final conversion. It is important that the firm plans for the data review in the conversion process, with ample time to provide feedback.
At the end of the acceptance testing process, the firm will typically be asked to approve and sign off the results. This final version of the conversion program will be used for the “live” data conversion into the new system according to the agreed implementation schedule.
Starting to use the new system
Teething problems are inevitable when implementing a new system. Users should take care to check the data while working through files and make changes if necessary. This is also a good time to set new policies on how information should be entered in the new software to avoid data tidy up going forward.
Firms should be aware that following a data conversion some adjustments may be needed to files as key data may not have been available on the old system that is required by the new system. This would normally be discussed and explained as part of the conversion process but, depending on the nature of the missing information, users may need to make minor adjustments before working with a converted file.
Certain functionality may also be restricted with converted data due to the differences between the systems. This may include such things as time entries not showing the correct billed status or not being available to be picked up when preparing a bill.
Data Conversions are often very complex and so it is not uncommon for problems to arise. Prevention is always better than cure and problems are best avoided by thoroughly understanding the scope of data conversion that is being offered and by comprehensively checking the trial/s (if one or more is done) and the completed conversion as soon as possible. The sooner an issue is identified, the easier it will be to successfully resolve.
Even with the most prudent planning and checking it is still possible for unforeseen problems to occur. If there is an issue, it should be communicated to the supplier as soon as practicable. Once the supplier is aware, it will be able to establish if the issue can be easily resolved or if it will require more significant corrective work.
Corrections to conversions can range from simple manipulation of converted data that may have been incorrectly transformed through to a more significant flaw that would require a complete re-run of the whole conversion.
Although the worst-case of requiring an entire re-conversion is unlikely, significant steps need to have been taken to mitigate that eventuality and it should be accepted that it is possible it will be required so it may be prudent to build that contingency into the project plan.
The Legal Software Suppliers Association