by Data Migration
Setting the scene:
An enterprise transformation project aims to introduce a shiny new ERP. Customers, sites, contracts and contacts need to be migrated to the new system. The data migration team has not provided requirements detailing their transformation. Due to the timing of the engagement and resourcing, limited information was provided regarding the workflows and business processes to be carried out in the new ERP.
The task:
How will the data migration be verified?
The approach:
This was a difficult, but not unique situation. Most testers are paralysed by lack of requirements, but this approach is quite limiting. Too often, smaller companies due to budgetary constraints (or lack of knowledge) gloss over the formal requirements gathering phase. As professional testers we need to be fluid in our approach and use whatever tools at our disposal to solve the problem.
The standard approach with data migration is to simply perform a reconciliation. If the old system had 10,342 records, then the new system also needs to have 10,342 records. A typical tester will perform these reconciliations, hand it over to their stakeholders and walk away. Job done.
However, this assumes that the data structures are going to be the same between systems. If the Transformation Project really does transform the business, then the new system will need to support (or at least offer) better functionality than the old system. This new functionality will need new structures to support it. How do we ensure that these are there as well?
I tackled this problem by first speaking to the SMEs and Business representatives, to determine the Mission Critical processes. Using this risk-based testing approach, I was able to reduce the scope of my investigations to certain key processes. Then, I worked with the vendor to determine precisely how these processes need to be done in the new system. It was only when I was intimately familiar with the solution that I set about designing my data verification.
Constraints:
- The data migration team had planned an iterative process. Monthly migrations will take place, with each migration requiring verification and fixes. An automated, rerunnable tool is therefore required.
- Due to budgetary constraints, the tool needs to be free. However, it also needs to return results that can be read by anyone.
- Whilst SQL access was provided to the local ERP (in the form of a separate DB, because we NEVER run our queries against the production environment), access to the data in the new ERP was limited to API calls.
The solution (in a nutshell):
- Python will be used to make API calls to the new ERP and push the data structures to a local SQL database.
- The local SQL Database will connect to the old ERP DB as a linked server.
- Each test will be written in the form of a stored procedure and will output the results to two places – a summary table and its own results table.
Python:
- Due to the queued implementation of the vendor’s API, it was necessary to split into 3 scripts.
- The first script iterated through every entity of interest, making a download request.
- The second script checked the status of the requests and noted the result
- The third script took down the result, wrote it to CSV and called the SQL stored procedure that pushed the CSV to SQL.
- Note (actual parsing of the xml file was done using a CROSS APPLY stored proc in SQL)
SQL:
Each test was written as a stored proc that returned the number of records that failed. If no records have failed, then the test will return NULL. A sample test:
CREATE PROCEDURE [dbo].[Test_RORO_Rates_001]
— Add the parameters for the stored procedure here
.
.
.
–********************************************************************************
–the objective of this test is to ensure that each ROLLOF sequence has a delivery action. ********************************************************************************
SET @testcond = ‘Each ROLLOFF has a Delivery, unless the Sequence is for a Sale of Material or Tippler’
SET @start = GETDATE()
DELETE [RORO_Rates_001]
SELECT @countOfRecords = count(*)
FROM (
select distinct(Q1.customername),Q1.sa_description
from
(
SELECT
C.Customername,
CSA.description as ‘SA_Description’,
S.Description as ‘Service’,
A.Description as ‘Action’,
CT.Description as ‘Container’,
M.Description as ‘Material’,
SAP2.price
FROM customerServiceAgreement CSA left join dbo.ServiceAgreementPrice2 SAP2 on SAP2.ServiceAgreementId=CSA.ServiceAgreementId
LEFT JOIN dbo.DefaultAction DA on SAP2.DefaultActionId=DA.DefaultActionId
LEFT JOIN dbo.action A on DA.ActionId=A.ActionId
LEFT JOIN dbo.Service S on DA.ServiceId=S.ServiceId
LEFT JOIN dbo.Material M on SAP2.MaterialId=M.MaterialId
LEFT JOIN dbo.ContainerType CT on SAP2.ContainerTypeId=CT.ContainerTypeId
LEFT JOIN dbo.Customers2 C on C.CustomerId=CSA.CustomerId
where S.description like ‘ROLLOFF’
) Q1
WHERE Q1.SA_Description NOT IN
(
SELECT distinct(CSA.description)
FROM customerServiceAgreement CSA left join dbo.ServiceAgreementPrice2 SAP2 on SAP2.ServiceAgreementId=CSA.ServiceAgreementId
LEFT JOIN dbo.DefaultAction DA on SAP2.DefaultActionId=DA.DefaultActionId
LEFT JOIN dbo.action A on DA.ActionId=A.ActionId
LEFT JOIN dbo.Service S on DA.ServiceId=S.ServiceId
LEFT JOIN dbo.Material M on SAP2.MaterialId=M.MaterialId
LEFT JOIN dbo.ContainerType CT on SAP2.ContainerTypeId=CT.ContainerTypeId
LEFT JOIN dbo.Customers2 C on C.CustomerId=CSA.CustomerId
WHERE S.description like ‘ROLLOFF’ and (A.Description = ‘Delivery’ or A.Description = ‘Sale of Material’ or CT.Description like ‘%Tippler%’)
GROUP BY CSA.Description
UNION
SELECT DISTINCT(EX.SA_Description)
FROM RORO_RATES_001_Exceptions EX
)
GROUP BY Q1.CustomerName, q1.SA_Description
) B
IF @countOfRecords > 0
BEGIN
INSERT INTO RORO_Rates_001 (customername,sa_description)
SELECT DISTINCT(Q1.customername),Q1.sa_description
FROM
(
SELECT
C.Customername,
CSA.description as ‘SA_Description’,
S.Description as ‘Service’,
A.Description as ‘Action’,
CT.Description as ‘Container’,
M.Description as ‘Material’,
SAP2.price
FROM customerServiceAgreement CSA left join dbo.ServiceAgreementPrice2 SAP2 on SAP2.ServiceAgreementId=CSA.ServiceAgreementId
LEFT JOIN dbo.DefaultAction DA on SAP2.DefaultActionId=DA.DefaultActionId
LEFT JOIN dbo.action A on DA.ActionId=A.ActionId
LEFT JOIN dbo.Service S on DA.ServiceId=S.ServiceId
LEFT JOIN dbo.Material M on SAP2.MaterialId=M.MaterialId
LEFT JOIN dbo.ContainerType CT on SAP2.ContainerTypeId=CT.ContainerTypeId
LEFT JOIN dbo.Customers2 C on C.CustomerId=CSA.CustomerId
WHERE S.description like ‘ROLLOFF’
) Q1
WHERE Q1.SA_Description NOT IN
(
SELECT distinct(CSA.description)
FROM customerServiceAgreement CSA left join dbo.ServiceAgreementPrice2 SAP2 on SAP2.ServiceAgreementId=CSA.ServiceAgreementId
LEFT JOIN dbo.DefaultAction DA on SAP2.DefaultActionId=DA.DefaultActionId
LEFT JOIN dbo.action A on DA.ActionId=A.ActionId
LEFT JOIN dbo.Service S on DA.ServiceId=S.ServiceId
LEFT JOIN dbo.Material M on SAP2.MaterialId=M.MaterialId
LEFT JOIN dbo.ContainerType CT on SAP2.ContainerTypeId=CT.ContainerTypeId
LEFT JOIN dbo.Customers2 C on C.CustomerId=CSA.CustomerId
WHERE S.description like ‘ROLLOFF’ and (A.Description = ‘Delivery’ or A.Description = ‘Sale of Material’ or CT.Description like ‘%Tippler%’)
GROUP BY CSA.Description
UNION
SELECT DISTINCT(EX.SA_Description)
FROM RORO_RATES_001_Exceptions EX
)
GROUP BY Q1.CustomerName, q1.SA_Description
—-EXTRA FIELDS REQUESTED BY SMEs—–
update dbo.RORO_Rates_001
set sa_seq = trim(left(sa_description,charindex(‘ ‘,sa_description)))
from [PIER_DM].[dbo].[RORO_Rates_001]
update dbo.RORO_Rates_001
set sa = trim(left(sa_seq,charindex(‘-‘,sa_seq)-1))
from [PIER_DM].[dbo].[RORO_Rates_001]
update dbo.RORO_Rates_001
set seq = trim(right(sa_seq,len(sa_seq)-charindex(‘-‘,sa_seq)))
from [PIER_DM].[dbo].[RORO_Rates_001]
update dbo.RORO_Rates_001
SET profit_centre=F5811.QB@PC,
container_size=F5811.QB@CSZ/100,
purchase_order=F5811.QB@PO,
waste_type=F5811.QB@WTY,
container_type=F5811.QB@C11
from dbo.RORO_Rates_001 g left join dwdevsql01.stgods.dbo.f5811 f5811 on g.sa=f5811.qb@sa and g.seq=f5811.qb@ssn
———
SET @errMsg = concat(@countOfRecords,’ ROLLOFF service agreements have no Delivery price. See RORO_Rates_001 for a list.’)
SET @end = GETDATE()
INSERT INTO dbo.results (testid,testcond,status,msg,testDate,[scriptDuration]) values (‘RORO_Rates_001’,@testcond,0, @errMsg,getdate(),@end-@start)
END
ELSE
BEGIN
SET @end = GETDATE()
INSERT INTO dbo.results(testid, testcond,status,testDate,[scriptDuration]) values (‘RORO_Rates_001’,@testcond,1,getdate(),@end-@start)
END
END
Test case writing approach
Apart from basic reconciliations, most of the SQL tests were written looking for mandatory entities/structures necessary for the FUTURE state. As an example, if, in the old ERP, not every customer had an email address, but the new system requires an email address.
This approach ensures that the functional testing team will not be looking for the proverbial needle in a haystack. With such a large magnitude of records, functional testing will only be able to traverse a small proportion of records.
Sample test results
The results table can be easily exported to an Excel file. A sample section is attached:
