Improving the ETL process for a case company
Nguyen, Thuan (2023)
2023
All rights reserved. This publication is copyrighted. You may download, display and print it for Your own personal use. Commercial use is prohibited.
Julkaisun pysyvä osoite on
https://urn.fi/URN:NBN:fi:amk-2023060421158
https://urn.fi/URN:NBN:fi:amk-2023060421158
Tiivistelmä
This thesis aimed to propose new extract, transform, and load (ETL) processes for Oracle Hyperion Financial Management (HFM) using Python language, and to improve the existing ETLs for Oracle Enterprise Resource Planning (ERP) cloud by adjusting Oracle procedures PL/SQL and parameters on Oracle ERP Web Services. The scope of this thesis was focused on two major sources of data which are HFM and ERP.
There has been a steady shift of the cloud migration, including database, data, applications, and information technology (IT) processes from local to cloud. Thus, the current ETLs designed to perform in the local environment (built by i.e. batch scripts) have shown limitations in communicating with the cloud applications. For example, they may run out of memory when uploading very large file to cloud environment S3. Additionally, the current ETLs do not transfer the complex metadata extracted from HFM to the data warehouse. This metadata often requires more cleansing, formatting, and flattening into a specific hierarchy before it can be transferred to data warehouses. Currently, in the case company, this metadata is managed using Microsoft Excel and manually inserted into the database.
The currently existing ETL processes for Oracle ERP cloud were developed using Oracle Procedures PL/SQL. However, these processes faced challenges in extracting a large amount of data from Oracle ERP cloud via web services. While they functioned properly with small dataset, they would produce errors with the larger ones.
The study was based on action research that was carried out with qualitative and quantitative methodologies. This involved reviewing system documentation, consulting with IT experts, analysing the performance of existing ETL processes, and gathering user feedback.
This work produced improved Python-based ETLs that were implemented into the production environment. They are compatible with cloud environments enhancing daily processes.
There has been a steady shift of the cloud migration, including database, data, applications, and information technology (IT) processes from local to cloud. Thus, the current ETLs designed to perform in the local environment (built by i.e. batch scripts) have shown limitations in communicating with the cloud applications. For example, they may run out of memory when uploading very large file to cloud environment S3. Additionally, the current ETLs do not transfer the complex metadata extracted from HFM to the data warehouse. This metadata often requires more cleansing, formatting, and flattening into a specific hierarchy before it can be transferred to data warehouses. Currently, in the case company, this metadata is managed using Microsoft Excel and manually inserted into the database.
The currently existing ETL processes for Oracle ERP cloud were developed using Oracle Procedures PL/SQL. However, these processes faced challenges in extracting a large amount of data from Oracle ERP cloud via web services. While they functioned properly with small dataset, they would produce errors with the larger ones.
The study was based on action research that was carried out with qualitative and quantitative methodologies. This involved reviewing system documentation, consulting with IT experts, analysing the performance of existing ETL processes, and gathering user feedback.
This work produced improved Python-based ETLs that were implemented into the production environment. They are compatible with cloud environments enhancing daily processes.