Data Migration Enhancement for Asset (AX06US01)
Asset Module Data Migration Business Rules
1. Asset Data Types and Upload Categories
The Asset Module data migration supports the following data types for upload:
1.1 Primary Asset Data Types
- Assets (Physical asset inventory and specifications)
- Facilities (Infrastructure facilities and locations)
- Systems/Networks (Operational systems and network configurations)
- Asset Classifications (Asset categorization and types)
- System Types Management (System type definitions and parameters)
1.2 Supported Data Categories
Data Type | Description | Primary Use Case |
|---|---|---|
Assets | Individual asset records with technical and financial data | Asset inventory management, maintenance planning |
Facilities | Infrastructure facilities with operational details | Facility management, capacity planning |
Systems | Operational systems with performance metrics | System monitoring, operational management |
Networks | Network infrastructure and connectivity | Network management, operational oversight |
Asset Classifications | Standardized asset types and categories | Asset categorization, reporting standards |
System Types | System type definitions with parameters | System configuration, standardization |
2. Asset Upload Process Rules
2.1 Data Selection Process
- Users must select "Assets" from the available data type options
- The system displays asset-specific upload interface with relevant templates
- Asset Classification Template must be uploaded before individual asset records for proper categorization
2.2 File Upload Requirements
- File Format: CSV format only
- Maximum File Size: 50,000 rows per file upload
- File Naming: System auto-fills migration name with filename if not specified by user
- Upload Methods: Drag-and-drop or file browser selection
2.3 Template Download Requirements
Each asset data type provides downloadable CSV templates containing:
- Predefined column headers based on selected asset data type
- Sample data in first few rows for format guidance
- Field descriptions and validation rules
3. Asset Data Field Specifications
3.1 Assets Data Fields
Field Name | Data Type | Required | Validation Rules | Description |
|---|---|---|---|---|
Asset Name/Description | String | Yes | Max 255 chars, No special chars except hyphen/underscore | Primary asset identifier |
Facility ID | String | Yes | Must exist in Facilities table | Associated facility reference |
System/Network ID | String | No | Must exist in Systems/Networks table | Associated system/network reference |
Asset Class | String | Yes | Must exist in Asset Classifications | Primary asset category |
Asset Type | String | Yes | Must exist in Asset Classifications | Specific asset type |
Location | String | No | Max 500 chars | Physical location within facility |
Installation Year | Integer | No | Range: 1900-current year+5 | Year asset was installed |
Serial Number | String | No | Unique per manufacturer | Manufacturer serial number |
Useful Life | Integer | No | Range: 1-100 years | Expected useful life in years |
Manufacturer | String | No | Max 255 chars | Asset manufacturer name |
Status | String | Yes | Active, Inactive, Retired, Under Maintenance | Current operational status |
Installation Cost | Decimal | No | Positive number, 2 decimal places | Original installation cost |
Replacement Cost | Decimal | No | Positive number, 2 decimal places | Current replacement cost |
Depreciation Method | String | No | Straight Line, Declining Balance, Units of Production | Depreciation calculation method |
Current Value | Decimal | No | Positive number, 2 decimal places | Current asset value |
3.2 Facilities Data Fields
Field Name | Data Type | Required | Validation Rules | Description |
|---|---|---|---|---|
Facility Name | String | Yes | Max 255 chars | Facility name |
Facility Type | String | Yes | Treatment Plant, Pumping Station, Storage, Distribution | Facility category |
Operating Status | String | Yes | Active, Inactive, Under Construction, Decommissioned | Current status |
Operational Hours | String | No | 24/7, Business Hours, Custom | Operating schedule |
Address/Site Label | String | No | Max 500 chars | Physical address |
Latitude | Decimal | No | Range: -90 to 90, 6 decimal places | GPS coordinate |
Longitude | Decimal | No | Range: -180 to 180, 6 decimal places | GPS coordinate |
Service Zone/Division | String | No | Max 255 chars | Service area covered |
Facility Manager | String | No | Max 255 chars | Primary contact person |
Design Capacity (MLD) | Decimal | No | Positive number | Million liters per day capacity |
Commissioned Date | Date | No | YYYY-MM-DD format | Date facility became operational |
Design Useful Life | Integer | No | Range: 1-100 years | Expected facility lifespan |
Annual O&M Budget | Decimal | No | Positive number | Annual operations & maintenance budget |
3.3 Systems Data Fields
Field Name | Data Type | Required | Validation Rules | Description |
|---|---|---|---|---|
System Name | String | Yes | Max 255 chars | System name |
Facility ID | String | Yes | Must exist in Facilities table | Associated facility |
System Type | String | Yes | Must exist in System Types table | System category |
Status | String | Yes | Active, Inactive, Under Construction, Maintenance | Operational status |
Commissioned/Planned Date | Date | No | YYYY-MM-DD format | Date operational or planned |
Backup Status | String | No | Available, Not Available, Partial | Backup system availability |
Operational Hours | String | No | 24/7, Business Hours, Custom | Operating schedule |
Address/Site Label | String | No | Max 500 chars | Physical location |
Latitude | Decimal | No | Range: -90 to 90, 6 decimal places | GPS coordinate |
Longitude | Decimal | No | Range: -180 to 180, 6 decimal places | GPS coordinate |
Description/Purpose | String | No | Max 1000 chars | System description |
Design Capacity | Decimal | No | Positive number | System capacity value |
Design Capacity Unit | String | No | MLD, m³/h, kW, etc. | Capacity measurement unit |
Current Throughput | Decimal | No | Positive number | Current performance value |
Current Throughput Unit | String | No | Must match capacity unit type | Performance measurement unit |
3.4 Networks Data Fields
Field Name | Data Type | Required | Validation Rules | Description |
|---|---|---|---|---|
Network Name | String | Yes | Max 255 chars | Network identifier |
Facility ID | String | Yes | Must exist in Facilities table | Associated facility |
Network Type | String | Yes | Distribution, Transmission, Collection, Communication | Network category |
Operational Status | String | Yes | Active, Inactive, Under Construction, Maintenance | Current status |
Network Operator | String | No | Max 255 chars | Operating entity |
Backup Status | String | No | Available, Not Available, Partial | Backup availability |
Design Capacity | Decimal | No | Positive number | Network capacity |
Unit | String | No | m³/h, MLD, Mbps, etc. | Capacity unit |
Description/Notes | String | No | Max 1000 chars | Additional information |
3.5 Asset Classifications Data Fields
Field Name | Data Type | Required | Validation Rules | Description |
|---|---|---|---|---|
Asset Class | String | Yes | Max 100 chars, Unique | Primary asset category |
Asset Type | String | Yes | Max 100 chars | Specific asset type within class |
Dimension | String | No | Max 100 chars | Measurement dimension for asset type |
Unit | String | No | Max 50 chars | Unit of measurement for dimension |
3.6 System Types Management Data Fields
Field Name | Data Type | Required | Validation Rules | Description |
|---|---|---|---|---|
System Type Name | String | Yes | Max 100 chars, Unique | System type identifier |
Description | String | No | Max 500 chars | System type description |
Parameter Name | String | Yes | Max 100 chars | Performance parameter name |
Unit of Measurement | String | Yes | Max 50 chars | Parameter measurement unit |
Parameter Description | String | No | Max 500 chars | Parameter description |
4. Data Analysis and Mapping Rules
4.1 AI-Powered Column Mapping
- Automatic Analysis: AI analyzes uploaded files and suggests column mappings
- Confidence Levels: Each mapping assigned High, Medium, or Low confidence
- Manual Override: Users can manually adjust AI suggestions through dropdown selection
4.2 Confidence Level Criteria for Assets
Confidence Level | Criteria | Example |
|---|---|---|
High ✅ | - Exact name match<br>- Exact data type match<br>- Strong pattern similarity | "Asset_ID" → "Asset ID" |
Medium ⚠️ | - Partial name match<br>- Compatible data types<br>- Minor variations | "Asset_Name" → "Asset Name/Description" |
Low ❌ | - No name similarity<br>- Incompatible data types<br>- Unrecognized content | "Notes" → "Installation Cost" |
4.3 Mandatory Field Mapping
- Required fields must be mapped before migration can proceed
- System enforces proper field type mapping (e.g., dates to date fields, numbers to numeric fields)
- Sample data displayed for each mapping to verify accuracy
5. Primary Key Management for Assets
5.1 Required Primary Keys by Dataset
Dataset Type | Primary Key Required | Auto-Generation Rules |
|---|---|---|
Assets | Asset ID | ASSET + sequential number |
Facilities | Facility ID | FAC + sequential number |
Systems | System ID | SYS + sequential number |
Networks | Network ID | NET + sequential number |
Asset Classifications | Classification ID | CLASS + sequential number |
System Types | System Type ID | SYSTYPE + sequential number |
5.2 Primary Key Handling Scenarios
Scenario 1: ONB Has Predefined Prefix & Starting Number
- Action: Auto-fill missing primary keys using predefined settings
- Format: [Prefix][Starting Number + Increment]
- Example: ASSET1001, ASSET1002, ASSET1003...
- User Options: Confirm auto-filled keys or re-upload with custom keys
Scenario 2: ONB Missing Prefix & Starting Number
- Action: Prompt user for prefix and starting number
- Requirements:
- Prefix: 2-10 alphanumeric characters
- Starting Number: Positive integer
- Preview: Display generated keys before finalization
- Validation: Ensure no duplicate keys generated
6. Asset Data Validation Rules
6.1 Validation Categories
- Valid Records: Records passing all validation checks
- Warnings: Records with minor issues that don't prevent migration
- Errors: Records with critical issues requiring resolution
6.2 Asset-Specific Validation Rules
Financial Data Validation
- Installation Cost: Must be positive decimal with max 2 decimal places
- Replacement Cost: Must be ≥ Installation Cost (warning if lower)
- Current Value: Must be ≤ Replacement Cost (warning if higher)
- Depreciation: Calculate and validate against Current Value
Date Validation
- Installation Year: Must be between 1900 and current year + 5
- Commissioned Date: Must be valid date in YYYY-MM-DD format
- Future Dates: Warning for dates more than 1 year in future
Reference Data Validation
- Facility ID Reference: Must exist in uploaded or existing Facilities data
- System/Network ID Reference: Must exist in uploaded or existing Systems/Networks data
- Asset Class/Type: Must exist in Asset Classifications data
Technical Validation
- Useful Life: Must be realistic (1-100 years) for asset type
- Serial Numbers: Check for duplicates within same manufacturer
- Status Combinations: Validate status against operational dates
6.3 Cross-Reference Validation
- Asset-Facility Mapping: Verify asset belongs to correct facility
- System-Facility Mapping: Ensure systems are assigned to correct facilities
- Capacity Validation: Warn if total asset capacity exceeds facility capacity
7. Error Handling and Correction
7.1 Asset-Specific Error Types
Error Type | Message Format | Suggested Fix |
|---|---|---|
Missing Asset ID | "Asset ID missing in row [X]. Required for asset identification." | "Provide unique Asset ID or enable auto-generation." |
Invalid Facility ID Reference | "Facility ID '[ID]' not found in row [X]." | "Upload Facilities data first or correct facility ID." |
Invalid System/Network ID Reference | "System/Network ID '[ID]' not found in row [X]." | "Upload Systems/Networks data first or correct ID." |
Invalid Financial Data | "Installation Cost '[Value]' invalid in row [X]. Must be positive number." | "Enter valid positive number with max 2 decimal places." |
Date Format Error | "Installation Year '[Value]' invalid in row [X]. Expected format: YYYY." | "Use 4-digit year format (e.g., 2024)." |
Duplicate Asset | "Asset ID '[ID]' already exists in row [X]." | "Use unique Asset ID or update existing record." |
Invalid Status | "Asset Status '[Status]' invalid in row [X]." | "Use: Active, Inactive, Retired, or Under Maintenance." |
Missing Classification | "Asset Class '[Class]' not found in row [X]." | "Upload Asset Classifications data first or correct class name." |
Invalid System Type | "System Type '[Type]' not found in row [X]." | "Upload System Types data first or correct system type." |
8.2 Auto-Correction Capabilities
- Format Standardization: Auto-format dates, numbers, and text fields
- ID Reference Resolution: Suggest similar facility/system/network IDs for typos
- Default Values: Apply default values for optional fields where appropriate
8. Migration Process Monitoring
8.1 Progress Tracking
- Real-time Progress: Display processed vs. total records
- Success Rate: Track successful migrations and errors
- Processing Time: Monitor migration performance
- Batch Processing: Handle large asset inventories in manageable batches
8.2 Asset Migration Logs
- Timestamp: All activities logged with precise timestamps
- Asset Details: Log asset ID, type, and facility for each operation
- Status Changes: Track asset status changes during migration
- Error Details: Specific error messages with row numbers and asset identifiers
9. Migration Reporting
9.1 Asset Migration Report Metrics
- Total Asset Records: Count of all processed asset records
- Success Rate: Percentage of successfully migrated assets
- Error Rate: Percentage of failed asset migrations
- Processing Time: Total time for asset data migration
- Asset Distribution: Breakdown by facility, asset type, and status
9.2 Asset Error Report Format
Asset ID,Asset Name,Facility ID,System/Network ID,Asset Type,Row Number,Error Description
ASSET001,Water Pump,FAC001,SYS001,Pump,15,Installation Cost must be positive number
,Backup Generator,FAC002,,Generator,23,Asset ID is required and cannot be empty
ASSET003,Control Valve,FAC999,,Valve,31,Facility ID 'FAC999' not found
ASSET004,Flow Meter,FAC001,NET999,Meter,42,System/Network ID 'NET999' not found10. Security and Access Control
10.1 Asset Data Security Rules
- Multi-factor Authentication: Required for asset migration access
- Role-based Permissions: Asset managers, facility managers, and system administrators
- Audit Trail: Complete log of all asset data modifications
- Data Encryption: Encrypt sensitive asset financial data
10.2 Asset Data Integrity
- Referential Integrity: Maintain relationships between assets, facilities, and systems
- Version Control: Track asset data changes over time
- Backup Requirements: Automatic backup before migration execution
- Rollback Capability: Ability to reverse migration if critical errors detected
11. Post-Migration Validation
11.1 Asset Data Verification
- Count Verification: Confirm all valid records were migrated
- Relationship Validation: Verify asset-facility-system/network relationships
- Financial Data Integrity: Validate cost calculations and depreciation
- Status Consistency: Ensure asset statuses align with operational dates
- ID Reference Integrity: Verify all facility and system/network ID references are valid
11.2 Migration Success Criteria
- Data Completeness: All required fields populated correctly
- Reference Integrity: All cross-references resolved successfully
- Validation Compliance: All validation rules satisfied
- Performance Standards: Migration completed within acceptable timeframe
12. Enhanced Data Mapping & Update Rules
12.1 Data Upload Enhancement
- Users can now choose between:
- Upload New Data – traditional full-data migration
- Update Existing Data – partial field-level updates on existing records
- Unique Identifier Requirement:
- Updates require a valid system identifier (e.g.,
Asset ID,Facility ID,System ID) - If not provided, the row is skipped and flagged in logs
- Updates require a valid system identifier (e.g.,
- Field Selection Rules:
- Users can select specific fields to update (from a system-curated list of updatable fields)
- Only selected fields are included in the downloadable CSV template
- System enforces update validity (e.g., correct data type, allowed values)
- CSV Template Controls:
- Data update templates are mandatory to download before upload
- Upload is disabled until the downloaded template is used (template check enforced)
- File format: CSV only
12.2 System Data Mapping (Conditional Screen)
- If mandatory system-related fields are not mapped during field mapping, users are shown a System Data Mapping screen, similar to plan mapping for consumers
Trigger Conditions:
- System detects unmapped critical system fields, such as:
- Assets: Asset Class, Asset Type, System/Network
- Systems: System Type, Facility
- Networks: Network Type, Facility
- Facilities: Facility Type
Key Features:
- Tab-based UI: Each unmapped required field is shown as a tab
- Record List: Shows only records missing the corresponding system field
- Search & Filter:
- Search across all columns
- Multiselect filters per field
- Selection Options:
- Users can select rows individually, in bulk, or "Select All"
- Bulk Assignment:
- Assign system data (e.g., a System Type or Asset Class) to all selected rows
- Inline Tagging:
- Once assigned, values are visibly tagged to the respective rows
- Progress Tracker:
- Shows % of system field mappings completed
- User can only proceed to the next step after all required mappings are completed
12.3 Migration History & Rollback Enhancements
- History Logging:
- Every upload or update (new or partial) is logged with metadata:
- Timestamp, record count, user, method (new/upload)
- Modified fields (for update)
- Every upload or update (new or partial) is logged with metadata:
- Rollback Capability:
- Admins can rollback a data update/upload within 3 days
- Rollback will:
- Restore old values for updated records
- Delete new records in case of upload
- Remove all associated transaction logs tied to affected records
No Comments