Data Transformation

Learn how to transform, cleanse, and restructure data within BijouCore entities using built-in transformation capabilities.

🔄 Overview

Data Transformation encompasses the processes and tools for modifying, cleansing, enriching, and restructuring data within your BijouCore application. These capabilities allow you to ensure data quality, standardize values, and prepare data for various use cases.

BijouCore provides several built-in transformation types that can be configured through the user interface or API, without requiring custom code development for common scenarios.

⚙️ Built-in Transformation Types

Normalization Transformation

Performs data normalization by transforming source fields into a reference entity (lookup table), improving data structure and integrity.

How It Works

  1. The system checks if a record with matching values exists in the reference entity
  2. If a matching record is found:
    • Update the reference record with additional field values (optional)
    • Set the target field in the source entity to reference the found record
    • Execute custom code if provided (optional)
  3. If a matching record is not found:
    • Either create a new record in the reference entity or skip the record
    • Execute custom code for skipped records if provided (optional)

Key Configuration Options

  • Matching Fields: Define which fields to match against
  • Matching Combination: All fields must match (AND) or any field can match (OR)
  • Target Field: Where to store the reference ID in source entity
  • Reference Entity: The lookup table to normalize against
  • Record Not Found Action: Create new record or skip
  • Update Existing Records: Whether to update matched records
  • Additional Field Mappings: Extra fields to copy between entities

Common Use Cases

  • Replacing repeated strings with normalized references
  • Normalizing complex objects like persons, addresses, products
  • Merging and deduplicating related data
  • Simplifying data structure and enforcing referential integrity
  • Standardizing category values across multiple entities
  • Creating master data from transactional data

Advanced Example: Normalizing Hierarchical Data

A powerful application of Normalization Transformation is handling hierarchical data structures such as geographic locations (Country → Region → City) or organizational structures. Here's how to implement multi-level normalization:

  1. Create separate reference entities for each level of the hierarchy (e.g., Countries, Regions, Cities)
  2. Configure multiple normalization transformations to process the hierarchy sequentially:
    • First Pass: Normalize top-level entities (e.g., Countries) using name/code as matching fields
    • Second Pass: Normalize second-level entities (e.g., Regions) using name and the parent reference ID from the first pass
    • Third Pass: Normalize third-level entities (e.g., Cities) using name and the parent reference ID from the second pass
  3. Run the transformations in sequence (from top level down)

This approach allows you to build clean hierarchical reference data from flat or denormalized input data. By using the generated IDs from each level as matching fields for the next level, you ensure proper hierarchical relationships in your normalized data.

Duplicate Detection Transformation

Identifies and manages duplicate records within an entity based on configurable matching rules.

How It Works

  1. Define fields to use for determining duplicates and matching types (exact, case-insensitive, fuzzy)
  2. Specify which record to keep when duplicates are found (first, last)
  3. Choose the action to take for duplicates (delete or custom action)
  4. The system processes records, identifies duplicates, and applies the configured action

Key Configuration Options

  • Matching Fields: Fields to compare for duplication check
  • Resolution Strategy: Which record to keep (first or last)
  • Detection Action Mode: Delete duplicates or apply custom logic
  • Custom Action Code: Optional code for custom handling of duplicates

Common Use Cases

  • Cleaning up imported data with duplicates
  • Merging records from multiple sources
  • Identifying and resolving duplicate customer records
  • Maintaining data integrity in master data
  • Pre-processing data before migration
  • Implementing "fuzzy" matching for similar but not identical records

Text Transformation

Performs various text operations on string fields, allowing formatting, cleaning, and restructuring of text data.

How It Works

  1. Define source fields to transform and target fields to store results
  2. Select transformation type for each field mapping
  3. Configure specific parameters for the selected transformation type
  4. The system applies the transformations and updates the target fields

Available Transformation Types

  • Trim: Remove whitespace from start and end
  • TrimStart/TrimEnd: Remove whitespace from start or end only
  • Replace: Replace text patterns with new values
  • Substring: Extract part of a string
  • Concatenate: Combine multiple fields
  • Split: Divide string and extract specific part
  • Pad/PadLeft/PadRight: Add padding characters
  • Format: Apply a format pattern
  • Case Transformations: Upper, lower, title, camel, pascal

Common Use Cases

  • Standardizing text formats (names, addresses)
  • Extracting components from complex strings
  • Combining separate fields into a single field
  • Normalizing capitalization and spacing
  • Cleaning up imported data
  • Preparing data for display or export
  • Formatting identifiers or codes to match standards

Copy Transformation

Copies data from one entity to another, with configurable field mappings and optional post-copy cleanup.

How It Works

  1. Define source and target entities
  2. Map fields between source and target entities
  3. Optionally provide custom conversion logic for complex mappings
  4. Specify whether to delete source records after successful copy
  5. The system copies data according to the configuration

Key Configuration Options

  • Field Mappings: Map source fields to target fields
  • Object Converter: Optional custom code for complex conversions
  • Delete Source After Copy: Whether to remove source records

Common Use Cases

  • Archiving data to history tables
  • Moving data between environments
  • Creating aggregate or summary tables
  • Migrating data between different schemas
  • Converting records from one entity type to another
  • Implementing ETL (Extract-Transform-Load) processes

Data Removal Transformation

Selectively removes records from an entity based on configurable conditions.

How It Works

  1. Define a condition that determines which records should be deleted
  2. The system evaluates each record against the condition
  3. Records that match the condition are deleted

Key Configuration Options

  • Condition: A code expression returning true for records to delete

Common Use Cases

  • Purging old or obsolete data
  • Cleaning up temporary or staging data
  • Removing records that fail validation
  • Implementing data retention policies
  • Deleting test or sample data
  • Removing duplicate or redundant information

🧰 Working with Transformations

Creating Transformations

  1. Navigate to the Data Transformation section in BijouCore Studio
  2. Click "New Transformation" and select the transformation type
  3. Configure the basic settings and transformation-specific options
  4. Save the transformation configuration

Running Transformations

  1. Find the transformation in the list and click "Run"
  2. Configure run-time options (like entity filters)
  3. Start the transformation job
  4. Monitor progress on the Jobs page

Transformations can be run on-demand or scheduled to run automatically using the Automation Jobs feature.

Monitoring and Troubleshooting

  • View transformation job history in the Jobs section
  • Check detailed logs for each job execution
  • See statistics like processed records, success rate, and duration
  • Review error messages for failed transformations
  • Test transformations on a subset of data before running on the full dataset

Extending with Custom Code

Most transformation types allow extending functionality with custom code snippets:

  • Use the code editors in transformation configuration
  • Write C# code to handle special cases or complex logic
  • Access the current record and related entities
  • Implement custom validation or transformation rules
  • Log information or handle errors programmatically

📋 Best Practices

  • Test First: Always test transformations on a copy or subset of data before applying to production data
  • Document Your Work: Add clear descriptions to transformations explaining their purpose and logic
  • Break It Down: Divide complex transformations into smaller, manageable steps
  • Monitor Performance: Watch resource usage for large datasets and consider chunking operations
  • Use Transactions: Ensure transformations that update multiple records are wrapped in transactions
  • Backup Data: Create backups before running destructive transformations (like Data Removal)
  • Validate Results: Check transformed data to ensure it meets your expectations
  • Consider Scheduling: For recurring transformations, set up automated jobs with appropriate scheduling
  • Reuse Configurations: Create transformation templates for commonly used patterns
  • Implement Error Handling: Use custom code options to handle edge cases and exceptions

🔗 Related Resources

Data Import

Learn how to import data from external sources into BijouCore entities.

Data Export

Explore options for exporting data from BijouCore to various formats.

Automation Workflow

Create complex data processing pipelines with the workflow engine.

Automation Jobs

Schedule and manage recurring transformation tasks.