MJB a PL/SQL - ETL Framework
In computer world the terminology ETL means a tool that combines at least the three important functions (extract, transform, load).
The requirement is to get some data from one big data environment then to transforming the data or part of it and finally to put it into another data environment.
Our MJB ETL tool provides to the business users a way to consolidate information to analyze and report on data relevant to their business focus..
Main purpose of the threads is to increase the performance as splitting some sessions in several sub sessions internally into the scope of the data base without using any OS processes.
Main points:
- Extract: Read data from the source database.
- Transform: Convert the format of the extracted data in order to adjust to the requirements of one or more target databases. The process of transformation is done by using predefined rules for merging data with other data.
- Load: Write transformed data to one or more databases used for business analysis.
The software solution is consisted by:
- Package.
- Procedures.
- Functions.
- Trigger.
- ETL Algorithm.
Features and Purposes:
- MJB ETL software provides custom solutions to maintain data extraction, transformation, and loading based on specific and traditional data management platforms.
- MJB ETL can support integration across transactional systems.
- MJB ETL also includes mapping instructions so that applications are told how to get the data they need to process.
- PL/SQL Threads.
- Oracle parallel processes.
- Scalable performance.
- Control the count of running procedures.
- Bulk data transformation.
- Etc.
Architecture
- Global picture.
- Create ETL plan for execution.
| MJB ETL execution plan | Comment |
Oracle Parallelism |
||
| Task ID | Group name | Execute PL/SQL | ||
| T_1 | Thread_pool_1 | MJB_PRODUCER_CONSUMER. start_tread_pool(2); |
Start thread pool, With 2 parallel processes | |
| T_1 | Thread_pool_1 | MJB_PRODUCER_CONSUMER. add_task_to_queue(‘proc1’); |
 |
|
| T_2 | Thread_pool_1 | MJB_PRODUCER_CONSUMER. add_task_to_queue(‘proc2’); |
||
| T_3 | Thread_pool_1 | MJB_PRODUCER_CONSUMER. add_task_to_queue(‘proc3’); |
||
| T_4 | Thread_pool_1 | MJB_PRODUCER_CONSUMER. add_task_to_queue(‘proc4’); |
||
| T_5 | Thread_pool_1 | MJB_PRODUCER_CONSUMER. wait(300); |
Wait 5min all tasks to finish | |
| T_6 | Thread_pool_1 | MJB_PRODUCER_CONSUMER. stop_tread_pool; |
||
| T_7 | Parallel proc | OPARALLEL.exec('proc_1', 'group_parallel'); | ||
| T_8 | Parallel proc | OPARALLEL.exec('proc_2', 'group_parallel'); | ||
| T_9 | Parallel proc | OPARALLEL.wait('group_parallel', 60); | Wait all jobs for 60 sec, then continue to next | |
| T_10 | Parallel proc | OPARALLEL.clear_job('group_parallel'); | Drop job 1 and 2 | |
| T_11 | proc_5 | Launch and wait to finish, Then continue with next task | ||
| T_12 | proc_6 | Launch and wait to finish, Then continue with next task | ||
Documentation
Licenses
Open Source License: Apache 2.
Commercial Licensing:
- SOS grants customers who purchased commercial licenses access to a wide range of Support Options and professional Services.
- Customers of commercial licenses help us to finance on-going development from license revenues.
- Customers benefit from on-going Product Maintenance Service, Support Options and Services, e.g. Training Service, Implementation Service and Migration Service.
- Customers receive Long Term Support by maintenance releases for longer periods whereas publicly available maintenance releases are provided for one year.
- Customers report issues to our SOS Ticket System, are guaranteed response times and receive immediate support in case of production standstill.
Cras mattis consectetur purus sit amet fermentum. Sed posuere consectetur est at lobortis.
Installation
Automatic installation.
@install.sqlInstallation details.
--Grants
GRANT SELECT ON V_$SESSION TO ;
grant execute on sys.dbms_lock to ;
GRANT CREATE JOB TO ;
grant create external job to ;