Data Migration (ONB01US01)

User Story: AI-Powered Data Migration & Transformation

1. Problem Statement

Utilities migrating from legacy systems to SMART360 face significant challenges in data migration and transformation. The process is:

• Time-consuming, taking weeks to migrate critical utility data such as consumer records, meter readings, and billing history.
• Error-prone, leading to data loss, incorrect meter-to-consumer mapping, and inconsistencies in billing and service requests.
• Manually intensive, requiring extensive effort to clean, format, and validate consumption data, payment records, and asset details.

These challenges directly impact key utility operations, including:

• Billing & Revenue Management – Incorrect data leads to overbilling, underbilling, or missing payments, causing financial discrepancies.
• Service Order Execution – Delays in consumer data migration result in service interruptions and slow request processing.
• Meter Data Management – Inaccurate meter readings and historical data errors affect consumption tracking and billing accuracy.
• Asset & Territory Management – Improper mapping of utility assets (transformers, substations, meters) leads to inefficiencies in maintenance and tracking.

Without an efficient migration system, utilities experience:

• Delayed operational readiness, increasing the time required to onboard and serve customers.
• High support dependency, as utility teams spend excessive time manually fixing migration errors.
• Revenue leakage, where incorrect billing data and missing transactions result in financial losses.

An optimized data migration solution for utilities is essential to ensure accurate, efficient, and seamless onboarding into SMART360.

---

2. Who Are the Users Facing the Problem?

• Tenant Admin: Unable to migrate data for specific utility
• Utility Admin: Unable to migrate territory data efficiently, leading to delays in system setup.
• CX Admin: Struggles with bulk uploads of consumer accounts, payments, complaints, and services, causing payment delays and incorrect account statuses.
• MX Admin: Cannot migrate bulk meter data, historical readings, or update large datasets, leading to errors in consumption tracking and billing.
• WX Admin: Lacks bulk import functionality for service order templates, slowing work order execution.
• AX Manager: Faces inefficiencies in importing plant, unit, and asset data, impacting asset tracking and maintenance scheduling.

These users spend weeks cleaning, transforming, and loading data manually using Postman, Excel, and external tools, creating operational bottlenecks.

---

User Role Permissions for Data Migration

1. Tenant Admin

• Can upload and migrate all tye of data across all utilities.
• Can manage and roll back migrations for all utilities.

2. Utility Admin

• Can upload and migrate data only within their assigned utility.
• Can manage and roll back migrations within their utility within its associated utility.

3. CX Admin (Consumer Data Management)

• Can upload and migrate consumer related data (accounts, payments, complaints, services) within its associated utility.
• Can manage and roll back consumer data migrations within its associated utility.

4. MX Admin (Meter Data Management)

• Can upload and migrate meter related data (meters, historical readings) within its associated utility.
• Can manage and roll back for meter data migrations within its associated utility.

5. WX Admin (Work & Service Orders)

• Can upload and migrate service orders related data within its associated utility.
• Can manage and roll back service order migrations within its associated utility.

6. AX Manager (Assets & Plant Data)

• Can upload and migrate assets related data within its associated utility.
• Can manage and roll back asset data migrations within its associated utility.

---

3. Jobs To Be Done

As a Utility Admin / CX Admin / MX Admin / WX Admin / AX Manager, I am responsible for managing vast amounts of consumer, meter, and billing data. Migrating this data from legacy systems to SMART360 has always been a challenge—manual processes are slow, error-prone, and disrupt daily operations.

I need a seamless and intuitive bulk data migration tool that allows me to upload large datasets effortlessly while ensuring accuracy and compliance. The system should automatically map fields, validate data, and highlight errors before final import, giving me confidence that the data is clean and reliable.

With this solution, I can focus on operations instead of data corrections, reduce migration time, and ensure a smooth transition to SMART360. This will improve efficiency, drive higher adoption, and allow my team to continue working without disruptions.

---

4. Solution

The AI-Powered Data Migration & Transformation feature introduces:

1. AI-Powered Column Mapping – Auto-detects & suggests field mappings with accuracy scores.
2. Data Transformation Rules – Standardizes formats, merges/splits columns, and applies regex corrections.
3. Live Validation & Error Handling – Detects missing/incorrect data with inline fixing suggestions.
4. Smart Auto-Correction & Recommendations – AI-powered anomaly detection and bulk edit options.
5. Real-Time Progress & Rollback – Track migration progress, pause, retry, or rollback if needed.
6. Post-Migration Reports & Audit Logs – Exportable logs, success/failure reports, and full audit trails.

---

5. Process Flow

---

6. Major Steps Involved

1. User uploads bulk data file (CSV, Excel, JSON, etc.).
2. AI-powered column mapping suggests auto-matched fields with manual override.
3. Data transformation engine cleans and formats entries (e.g., date standardization, merging duplicate records).
4. Live validation detects issues (missing fields, incorrect formats, duplicate entries).
5. Inline fixing suggestions are provided for errors.
6. Preview step allows users to compare source vs. transformed data.
7. User initiates migration, tracking real-time progress.
8. Audit logs & post-migration reports confirm success/failures and rollback options.

---

7. Business Rules

Process Authorization Rules

1. Only authorized users with data migration permissions may initiate and monitor migration processes
2. System administrators must approve migration plans before execution begins
3. Users must authenticate with multi-factor authentication before accessing migration controls

Upload Process

1. The system supports AI-powered data upload functionality for seamless integration into the Smart360 system.
2. Users can upload data for various data types including:
3. • Consumers (customer accounts and profiles)
   • Meters (water meter information and details)
   • Meter Data (usage readings and consumption data)
   • Plans (service plans and rate configurations)
   • Tariffs (pricing structures and rate cards)
   • Service Orders (work orders and service requests)
   • Payments (customer payment history and transactions)
   • Complaints (customer support tickets and issues)
   • Assets(Asset configuration data)
   • User Data (system users and administrative accounts)
3. File upload supports CSV, Excel, and JSON formats
4. The system should allow a maximum of 50,000 rows per file upload to ensure performance optimization and prevent system overload.
5. The system provides a preview of the uploaded data showing a sample of records (e.g., "Showing 5 of 7052 records").
6. Files can be uploaded either through drag-and-drop functionality or by browsing for files.
7. User can download the data template file in CSV format
8. The template will contain:
   • Predefined column headers based on the selected data type.
   • Sample data in the first few rows to help users understand the expected format.
9. The system displays upload progress with percentage completion indicators.
10. Once uploaded, the system displays a confirmation message for successful uploads.

Handling Missing Primary Keys for Data Upload

• If the uploaded file does not contain the required primary key for the selected dataset, the system will take action based on the Onboarding (ONB) settings.

Handling Scenarios

1. ONB Has Predefined Prefix & Starting Number

• If the prefix and starting number are already set in ONB, the system will:
  • Auto-fill the missing primary key using the predefined prefix and starting number.
  • Generate unique primary keys in sequence.
  • Show a preview for confirmation before migration.

Example:

• Dataset: Consumer Data
• ONB Settings: Prefix = CUST, Starting Number = 1001
• Generated Keys: CUST1001, CUST1002, CUST1003 …
• User Options:
  • Confirm the auto-filled primary keys.
  • Re-upload the file if the user wants to provide their own primary keys.

2. ONB Does Not Have Predefined Prefix & Starting Number

• If ONB settings do not have a predefined prefix and starting number:
  • The system will prompt the user to choose between:
    • Manual Setup: Enter a prefix and starting number.
    • AI-Assisted Setup: Auto-generate primary keys based on sequential logic.
  • The system will display a preview before finalization.

Primary Keys Required by Dataset

Dataset Type	Primary Key Required
Consumer Data	Customer Number
Meter Data	Meter Number
Asset Data	Asset ID, Plant ID, Unit ID

• Auto-fill when ONB settings exist to ensure seamless migration.
• Prompt user only when ONB settings are missing, reducing unnecessary steps.
• Uniqueness Check: Ensure no duplicate keys are generated.
• Preview & Confirmation: Users must verify generated primary keys before finalizing.
• Re-upload Option: If users want to provide their own primary keys, they can re-upload the file.

Data Analysis and Mapping

1. AI automatically analyzes the file and suggests column mappings between source columns and target Smart360 fields.
2. Each suggested mapping is assigned a confidence level (High, Medium, Low) to indicate the reliability of the mapping.
3. File information is displayed showing:
4. • Filename
   • Total columns (e.g., 8)
   • Total rows (e.g., 2,426)
   • Mapping quality indicators (e.g., 5 High, 2 Medium, 1 Low)
4. The system evaluates the uploaded file and assigns confidence levels based on three key factors:

Confidence Level	Criteria	Example
High ✅	- Exact Name Match between the uploaded field and system field. - Exact Data Type Match (e.g., String → String, Date → Date, Integer → Integer). - Strong Pattern Similarity (e.g., Phone numbers, ZIP codes).	Uploaded File Field: Consumer IDSystem Field: Consumer ID ✅ High confidence because the name matches exactly and the expected data type is correct.
Medium ⚠️	- Partial Name Match (e.g., synonyms, abbreviations, slight variations). - Compatible but Different Data Types (e.g., Integer vs. String for an ID field). - Pattern Recognition with Minor Variations (e.g., "Meter No." vs. "Meter ID").	Uploaded File Field: Cust_IDSystem Field: Consumer ID ⚠️ Medium confidence because "Cust_ID" is a partial match for "Consumer ID", but it's not exact.
Low ❌	- No Name Similarity between the uploaded field and system field. - Incompatible Data Types (e.g., Text mapped to Date). - Unrecognized Field Content (e.g., a completely unrelated field).	Uploaded File Field: Billing AddressSystem Field: Consumer ID ❌ Low confidence because "Billing Address" is unrelated to "Consumer ID".

1. Example Scenario

A user uploads a file containing the following columns:

Uploaded Field	System Field (SMART360)	Confidence Level	Reason
Consumer ID	Consumer ID	High ✅	Exact name and data type match.
Cust_ID	Consumer ID	Medium ⚠️	Partial name match, slightly different format.
Meter_No	Meter Number	Medium ⚠️	Recognized abbreviation, slightly different spelling.
Bill Address	Consumer Address	Low ❌	No strong similarity between names.
Date of Birth	Last Payment Date	Low ❌	Incorrect field mapping; different meanings.

1. Source columns are mapped to target fields with sample data displayed for reference.
2. Users can manually adjust mappings through dropdown selection if the AI suggestion is incorrect.
3. The system enforces proper field type mapping (e.g., ensuring Customer ID fields map to appropriate ID fields).

Validation Process

1. The system performs data validation after mapping to identify issues requiring attention.
2. Validation results are categorized as:
3. • Valid records (with percentage)
   • Warnings (with percentage)
   • Errors (with percentage)
3. The system specifically identifies and flags:
4. • Format issues (e.g., "Missing Postal Code format")
   • Data inconsistencies (e.g., "Date format inconsistency")
   • Unmapped fields (e.g., "Unmapped field: Meter_Type")
4. For each issue type, the system shows the number of affected records.
5. Users can download a validation report for offline review.
6. The system provides detailed information for each issue, including specific records affected.
7. A "Fix All Issues" option is available for batch resolution of problems.

Data Correction

1. Users can view detailed information about each issue by expanding the issue sections.
2. For format issues (e.g., postal codes), the system allows:
3. • Viewing specific invalid records
   • Auto-formatting data to meet system requirements
3. For unmapped fields, the system:
4. • Suggests possible Smart360 mappings
   • Allows users to select the appropriate field mapping
4. The system tracks correction status, showing:
5. • Fixed issues (with green checkmarks)
   • Remaining issues (with warning or error icons)
5. When issues are corrected, the system updates validation statistics (e.g., Valid records percentage increases).
6. Auto-format functionality can standardize data formats (e.g., converting postal codes to standard formats).

Migration Process

1. The migration process follows a sequential workflow:
2. • Upload (choose file)
   • Map (define field mapping)
   • Validate (fix any issues)
   • Migrate (complete process)
2. All issues must be resolved before proceeding to the final migration step.
3. The system provides navigation options to move between steps (e.g., "Back to Mapping").
4. When all issues are resolved, the system displays a "Fix issues to proceed" message with a confirmation that data will be ready for migration.
5. The system provides confirmation messages for successful actions (e.g., "Format corrected - All postal codes have been standardized").
6. Error messages are displayed when system errors occur during the process.

Error Handling

1. Unexpected errors must be flagged for immediate administrator attention
2. The system must implement automatic retry logic for transient errors
3. Critical errors must halt the migration process until administrator intervention

Error messages must be specific and provide examples for corrections.

✅ Example of Effective Error Handling:

Error Type	Message	Suggested Fix
Incorrect Format	Date format inconsistent: "Jan 5 2021", "02/03/2024".	Use the standard format: YYYY-MM-DD (e.g., 2024-03-20) .
Missing Field	Meter ID missing in row 5. Required for meter mapping.	Enter a valid Meter ID before proceeding.
Duplicate Entry	Meter ID "5001" already exists in the uploaded data (Row 6, Row 12).	Remove duplicates from the uploaded file before re-uploading.
Overwriting Data	Existing consumer data for Consumer ID "C123" found.	Confirm if data should be updated or kept unchanged.
Invalid Value	Negative consumption (-250 kWh) in row 12.	Verify meter readings and enter a positive value.
Mapping Error	Meter ID "M1002" not linked to any consumer account.	Ensure meters are mapped to a valid consumer.
Unsupported Character	Special characters found in Consumer Name ("John@Doe").	Remove special characters; use only letters and spaces.

AI Assistant Integration Rules

1. AI assistant must provide real-time updates on migration progress
2. Assistant must offer troubleshooting guidance for common error patterns
3. AI recommendations must be reviewed by human operators before implementation
4. Assistant should suggest optimization strategies based on observed performance

Migration Reporting Rules

1. Each migration must generate a timestamped report (format: MM/DD/YYYY, HH:MM
   AM/PM)
2. Reports must include total record count, success rate, error rate, and processing time
   1. Total Record Count
   Definition: The total number of rows processed from the uploaded file.
   Formula: Total Record Count=Number of Rows in the Uploaded File\text{Total Record Count} = \text{Number of Rows in the Uploaded File}Total Record Count=Number of Rows in the Uploaded File
   Example: If a user uploads a file with 50,000 rows, the Total Record Count = 50,000.
   
   2. Success Rate (%)
   Definition: The percentage of records successfully migrated without errors.
   Formula: Success Rate=(Successfully Migrated RecordsTotal Record Count)×100\text{Success Rate} = \left( \frac{\text{Successfully Migrated Records}}{\text{Total Record Count}} \right) \times 100Success Rate=(Total Record CountSuccessfully Migrated Records​)×100
   Example:
   Uploaded file: 50,000 rows
   Successfully migrated records: 47,500 rows
   Success Rate = (47,500 / 50,000) × 100 = 95%
   
   3. Error Rate (%)
   Definition: The percentage of records that failed due to validation errors, incorrect field mappings, or missing data.
   Formula: Error Rate=(Failed RecordsTotal Record Count)×100\text{Error Rate} = \left( \frac{\text{Failed Records}}{\text{Total Record Count}} \right) \times 100Error Rate=(Total Record CountFailed Records​)×100
   Example:
   Uploaded file: 50,000 rows
   Failed records due to errors: 2,500 rows
   Error Rate = (2,500 / 50,000) × 100 = 5%
   
   4. Processing Time
   Definition: The total time taken to process and migrate the data.
   Formula: Processing Time=Migration End Time−Migration Start Time\text{Processing Time} = \text{Migration End Time} - \text{Migration Start Time}Processing Time=Migration End Time−Migration Start Time
   Example:
   Migration started at 10:00 AM
   Migration completed at 10:05 AM
   Processing Time = 5 minutes
3. Success/error distribution must be visually represented with color-coded indicators
4. Users must be able to return to the dashboard from the report view

Migration History Rules

1. The system must maintain a log of all migrations with date and type information
2. Migrations must be searchable by name or other parameters
3. Migrations must be filterable by:
   • Data format type (CSV, Excel, JSON)
   • Data category (Consumer, Meter, Billing, Payment, Service, Complaint)
4. New migrations must be clearly identified in the history
5. Migration history must display the most recent migrations at the top
6. Migration names must be descriptive of their content when specified by users

Security and Data Management

1. The system tracks and allows viewing of upload history.
2. The system appears to enforce data quality standards through validation rules.
3. The system maintains the relationship between related data entities (e.g., Customer IDs and Meter IDs).
4. The system enforces format standards for critical fields like postal codes, dates, and IDs.
5. The system allows for error handling and recovery from validation failures.
6. A delete icon will be use in histoy tab where users can only rollback data within a defined timeframe that is 7 days, after 7 days user cannot rollback
7. The rollback will completely remove the latest uploaded dataset and restore the previous dataset.
8. A rollback cannot be undone once executed.
9. Before executing a rollback, users must confirm the action via a confirmation dialog box.
10. System should prompt: “Are you sure you want to Delete the uploaded dataset? This action cannot be undone.”
11. After rollback completion, system will display a success message and notify the user via system notification.

---

8. Sample Data Format

Account	Sub A/c	Service	Surname,Given Name	Address	Meter#	Loc#	Book#	Cycle	Block#	Status	Prev Read	Prev Read Date	Last Payment	Last Payment Date	RATE	CELL PHONE	Service Area	EMAIL ADDRESS	Balance Owing/(Credit)	Meter Size	Meter Dials	Meter Type
30042	1	3	FLETCHER CONSTRUCTION	VAILELE TAI	12,133,911	10020020	2	Urban Central	2	2	2,254	6/8/2024	82	10/8/2024	UD1	6,857,640,863	U04-DMA02	kyliefauchelle@gmail.com	-25.52	15mm	5	ZENNER
10091	1	3	HANS, KRUSE	VAILELE TAI	12,134,262	10020050	2	Urban Central	2	2	916	6/8/2024	100	5/7/2024	UD1	0	U04-DMA02	Moana1932@yahoo.com	42.65	15mm	5	ZENNER
10089	1	3	FEIL, MARIE KRUSE	VAILELE TAI	12,400,659	10020030	2	Urban Central	2	2	445	6/8/2024	150	7/10/2023	UD1	0	U04-DMA02		0.01	15mm	5	ZENNER
10090	1	3	FEIL, MARIE	VAILELE TAI	12,400,678	10020040	2	Urban Central	2	2	1,472	6/8/2024	89	3/9/2024	UD1	0	U04-DMA02	beattieruth@gmail.com	-62.26	15mm	5	ZENNER

---

9. Acceptance Criteria

1. Users can successfully upload bulk data files.
2. AI-powered field mapping works with accuracy.
3. Live validation must detect incorrect/missing data.
4. Users can manually correct errors before migration.
5. Preview functionality allows comparison between original & processed data.
6. Migration progress can be tracked with a real-time status.
7. Audit logs provide detailed insights on data corrections.
8. Rollback functionality allows reverting migrations.

---

10. Process Changes

• Eliminates Postman/manual scripts for data import.
• Integrates AI-driven validation before actual migration.
• Replaces manual transformation steps with automated rule-based corrections.
• Reduces dependency on customer support for migration issues.

---

11. Impact from Solving This Problem

Impact Metrics: SMART360 Data Migration Module

Metric	Before Implementation (Legacy System Challenges)	After Implementation (SMART360 Solution)
Time to Migrate a Single Data Type	2-4 weeks per data type due to manual validation and cleaning.	Reduced to 2-5 days with automated validation and transformation.
Data Errors (Formatting, Duplicates, Missing Fields)	20-30% of uploaded records required manual fixes.	<5% error rate with structured validation and predefined templates.
Operational Readiness Delays	Onboarding delayed by 4-6 weeks due to incomplete or incorrect data.	Onboarding within 1-2 weeks , ensuring a faster go-live.
Support Dependency for Data Fixes	60-70% of migration cases required customer support intervention.	<20% support dependency , as most errors are resolved automatically.
Billing Inaccuracy Due to Data Issues	Up to 10% of bills generated with incorrect consumption or missing data.	<1% billing errors , ensuring accurate invoicing.
Service Order Execution Delay	Orders delayed due to missing consumer or meter data.	Immediate execution with correctly mapped consumer-meter relationships.
Customer Complaints on Migration Issues	40% of new utility users reported service issues due to incorrect data.	<10% complaints , as clean data is migrated correctly.
Financial Loss Due to Incorrect Billing	Revenue leakage from incorrect meter readings and missed payments.	Accurate billing ensures zero revenue loss from data inconsistencies.

---

12. Summary of Business Impact

By implementing the SMART360 Data Migration Module, utilities can expect:
✅ 80% faster data migration
✅ 85% reduction in errors
✅ 60% lower dependency on manual fixes
✅ Faster system onboarding (within weeks instead of months)
✅ Improved billing accuracy, reducing revenue leakage

13. Wireframe

https://docs.google.com/presentation/d/1MQQYjcWuYSv4zxLWzdrHlI-A2DfqInz3VNRAPcTBt2w/edit?usp=sharing