Multi-tenant ETL Server Segmentation for Resource Utilization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional business intelligence systems waste resources due to idle Extract, Transform, Load (ETL) systems, as each customer is allocated a separate ETL system that sits idle after initial data import, except for scheduled data pulls, leading to inefficient resource utilization.
Innovation Solution
A system and method that enable multiple tenants to share an ETL server while maintaining data segregation, allowing for efficient resource usage and customization of ETL processes, with each tenant having a dedicated database instance and staging area, and utilizing a shared data integrator repository and agents for ETL processes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If each customer is allocated a separate ETL system, then data security and isolation are improved, but resource utilization deteriorates due to idle ETL systems
Solution Approach 1:
The system segments ETL resources into two distinct layers: customer-specific components (staging areas, data warehouses, data integrator agents) and shared infrastructure (ETL server, repository). This segmentation allows each customer to maintain isolated data spaces while sharing the computational ETL engine, thereby achieving both data isolation and resource utilization efficiency.
Solution Approach 2:
The patent merges multiple customers' ETL processes onto a single shared ETL server infrastructure. The ETL server executes load plans for multiple customers sequentially or in parallel, sharing common resources such as the repository, server memory, and processing units. This consolidation eliminates idle resources while maintaining customer-specific data isolation through logical separation of staging areas and target warehouses.
2Adaptability or versatility
If each customer has a dedicated ETL system, then customization for each tenant is improved, but system complexity and maintenance difficulty increase
Solution Approach 1:
The shared ETL server is designed with multi-functionality to serve multiple customers simultaneously. It can execute different load plans, apply customer-specific transformations, and interact with various source and target systems for different tenants. The repository stores universal ETL objects (extractors, transformers, loaders) that can be configured and reused across multiple customers, reducing the need for separate customized systems while maintaining adaptability.
Solution Approach 2:
Instead of creating entirely separate ETL systems for each customer, the patent uses copying of ETL templates and load plans from a centralized repository. Customers can have customized load plans that are copies or variations of base templates, stored in their respective namespaces within the shared repository. This approach maintains customization while avoiding the complexity of completely separate systems.
3Reliability
If separate ETL systems are used for each customer, then data security is improved, but resource waste increases due to idle systems
Solution Approach 1:
The patent introduces a new dimensional organization of data resources, separating the computational layer (ETL server) from the data storage layers (staging areas and warehouses). Customer data remains isolated in their own staging areas and target warehouses (vertical isolation), while the ETL server operates in a shared computational dimension (horizontal sharing). This dimensional separation allows secure data isolation while enabling resource sharing, eliminating the resource waste of idle ETL systems.
Data Source
AI summary
Systems and methods for automatic error rejection are provided. Systems and methods described herein bypass the creation of a staging table at the outset and, instead, attempt a direct merge from a source data location to a target data location. In the event that the merge fails, then a temporary/staging table can be loaded where errors can be logged, validations can be performed, and erroneous data can be corrected.