ETL/Data Warehouse Testing of a GIS Spatial Application

Case Study

CLIENT PROFILE

Client is a reputed organization that deals with various planning and environmental aspects. The client uses geospatial technology in the delivery of planning and development services, such as the online lodgment and tracking of applications, viewing planning information on a web-based interactive map, and providing new ways for stakeholders to engage with the planning process.

CLIENT OVERVIEW

Our client, a leading educational authority at the national level, recognized the need for a comprehensive assessment platform capable of measuring students’ competencies across diverse subjects and skill domains. They envisioned a system that not only evaluates academic knowledge but also fosters critical thinking and problem-solving abilities among students in primary and secondary schools nationwide.

The tools and services developed by the client help businesses and communities access and transact with planning services from anywhere, anytime. The client deals with a massive volume of historical and current data from multiple sources and departments, each with their own data standards. To integrate these, an ETL Tool (FME Workbench 2015) was used to enable flawless data transformation across departments. Data is uploaded to Google Map Engine (GME) and Microsoft Azure for visualization on the web application.

BUSINESS SITUATION

This GIS Spatial Web-based Application uses Google Maps Engine to help users quickly navigate the planning rules that apply to individual land parcels, or search for properties that match certain planning controls.

It has been developed primarily for councils and professionals such as architects, builders, certifiers, developers, and planners. This is a cloud-based product developed on Azure and GME (Google Map Engine) cloud platforms.

As the client deals with large volumes of everyday and historical data from multiple sources and departments, each with their own data standards, ETL/Data Warehouse and Security Testing was performed by Adactin to ensure smooth Data Extraction, Transformation, and Loading (ETL) into the two clouds (GME and Azure) without any data loss or truncation, using an ETL tool as an integrator.

This data upload occurs on a monthly basis for the Web Application, ensuring that users of the GIS Spatial Web Application always view the latest data.

The Web Application supports various functionalities including:

Basic Search
Advanced Search
46 different layers spanning the whole of NSW
Many more features

The portal also provides links to Environmental Planning Instruments – including LEPs or State planning policies – hosted on a government website, which remains the authoritative source.

TECHINICAL SITUATION

As the web application was built on a Cloud Platform and data coming every day from the multiple sources and different departments in multiple formats, various ETLs with complicated logics were running at different stages to standardize the whole bunch of data into one standard format and then uploading that standardized data into the Google Map Engine Cloud and the Microsoft Azure Cloud.

Adactin provided a solution that helped to check the data from multiple sources, rectifying the data with inconsistent formats, find the incompatible and duplicate entries, loss of data while ETL process was running, find the wrongly entered data and enrich the data by verifying the ETL logic is properly working through FME Workbench and verify that all the data is transformed from the multiple sources into one standardized normalized data at one place without dropping any data, data loss, truncation, no null records are introduced etc.

Once all the data is transformed into one standardized format, then the client transforms that data onto the 2 clouds (GME and AZURE) via the FME Workbench 2015. Here again, Adactin provided a solution to check the ETL logics are working fine and transferring all the whole bunch of data to both the clouds according to various requirements and rules without any data loss and truncation, without changing the data format and disturbing the Schemas.

Client was using multiple accounts for various tool and cloud accessing, here Adactin did the Security Testing as well to check that only the authorized accounts are able to access the only allowed services and vice versa. Data between both the clouds is interrelated so a unique Asset ID was provided so that both the clouds represent the data properly in the client Web based Solution which provides the various planning services of the council’s data of NSW. Adactin also performed the Production Verification Test.

Various types of testing is conducted to test this product

SOLUTION

Adactin proposed and implemented a test process which was divided into 5 Phases for ETL/Data Warehouse Testing of a GIS Web-based Application.

Phase-1 – Test Plan

Liaise and consult Client test team to gather complete ETL and Functional requirements
Planning for Data Management and Transformation testing
Testing team spent time with SMEs to understand end-to-end business processes and business needs for the ETLs
Creation of high-level test scenarios

Phase-2 – ETL Test Cases Design/Enhance

Design of detailed test cases for system and integration ETL testing for the data transformation
Tools and software setup for ETL data transformation and migration testing
Test data setup

Phase-3 – Execution Cycle-1 – ETL/Data Warehouse Testing

Tools and software setup
Test data setup in the test environment
Execution of ETL test cases stage-wise as described below
Logging of bugs in the bug management tool
Publishing of test results

Stage 1 (Business Rules) – Testing to verify that ETL data transformation adhered to all specified requirements and business rules.
Stage 2 (BAU_DATA) – Testing of extracted data from multiple BAU sources. Data cleaning testing was performed.

Testing Type: Data Transformation Testing
Test Description:
This testing was done mainly for the data which was externally injected through Python scripts once the data was in one standardized format. Python code was implemented to extract the data and convert it into the desired format.
The testing team was involved in verifying the Python code logic and workflows. The client pulled data from multiple BAU sources, applied ETL logic, converted it into the target database, and uploaded the data into GME and Azure clouds.
The testing team validated FME Workbench ETL logic, database accuracy, and data upload activities.

Testing Type: Security Testing
Security testing was done to ensure that different user accounts had the correct rights/permissions in the respective environments.
Negative testing was conducted to confirm that accounts did not have unauthorized access. This was executed across multiple environments.

Testing Type: Cross-Browser and Functional Testing
This product was developed for both community and business use. The testing team validated compatibility across various operating systems and browsers.

Stage 3 (ETL_INTERNAL) – Verified that inconsistent data formats from multiple sources were standardized and transformed without any data loss or truncation.
Stage 4 (R1STD_DATA) – Confirmed complete data transformation from multiple BAU sources into a standardized format without data loss or truncation.
Stage 5 (PCO_Application Injection) – Verified SQL-based injection of external PCO links into the web application.
Stage 6 (ETL_EXTERNAL) – Ensured standardized data was uploaded to GME and Azure without data loss or schema issues.
Stage 7 (GME_DATA) – Validated successful data migration to GME Cloud via FME Workbench 2015.
Stage 8 (AZURE_DATA) – Validated successful data migration to Azure Cloud via FME Workbench 2015.
Stage 9 (SECURITY TESTING) – Confirmed that authorized accounts had proper access, and unauthorized users were restricted.
Stage 10 (PRODUCTION VERIFICATION TESTING) – Compared standardized source data with GME and Azure data using FME Workbench 2015.

Two data paths tested:

Standardised Source Data → ETL TOOL (FME Desktop) → GME Cloud
Standardised Source Data → ETL TOOL (FME Desktop) → Azure Cloud

Phase-4 – Regression Testing Cycle for the Front-End GIS Web-Based Application

Test data setup in the test environment
Execution of regression test cases for the application
Cross-browser and OS/device compatibility testing by parallel teams
Publishing of test results

Phase-5 – Security Testing (Non-Functional Testing)

Setup of test and production environments and user configurations
Design and execution of test scenarios
Publishing of test results

BENEFITS

Find below benefits of the technical solution proposed to the client:

Team logged more than 300 issues as part of testing phases, leading to improved quality of the application.
All key business rules were thoroughly tested within the limited timeframe to ensure correct functioning. No production issues were recorded in those areas.
Suggestions were made to improve the flow of business processes with the business team, resulting in a better application using clean and latest NSW data for all upcoming monthly data uploads.
Suggestions to improve the infrastructure of the application to avoid downtime, e.g.,
- Traffic Manager and File Configuration Server should have at least 2 instances
- Data types and lengths should be consistent across different environments
An effective bug tracking process ensured that open bugs could be easily tracked and resolved.

TECHNOLOGY STACK

Microsoft Excel – Test case creation and execution
SQL Developer – Building and execution of SQL queries
SSMS (SQL Server Management Studio) 2014 – Data verification and validation
GME (Google Map Engine) – Spatial data verification and validation
ArcGIS Server 10.2, ArcCatalog – Data verification and validation
FME Workbench 2015 – Data validation and ETL logic testing
Mantis – Defect management and reporting

ASSISTANCE PROVIDED BY CLIENT RESOURCES

Assistance provided by client SMEs in understanding business requirements and rules for all ETL logics
Client SMEs conducted knowledge sharing sessions on different modules of data transformation
Client’s IT team helped in accessing tools like FME, GME, Azure, SSMS 2014, ArcGIS 10.2, and cloud environments
Client’s development team assisted in bug fixing
Client’s Project Management and Test Management teams supported test coordination with business users and development team

Snapshot

Share On: