Introduction
In this post, I will review two use cases where my clients have reduced complexity and saved money by using APEX to store and access historical transactions from legacy ERP systems.
Solution 1 - Oracle EBS Archival
Increasingly, companies are migrating from Oracle E-Business Suite (EBS) to Oracle Fusion Cloud. They face three choices when considering what to do with historical transactions:
Convert all of their historical transactions to Fusion.
Perform a minimal conversion and keep their EBS instance up and running.
Perform a minimal conversion, build APEX Reports on top of the EBS database, and shut down the EBS Middleware components.
Convert Full History
Converting all of your historical transactions to Fusion will make your implementation project more complex and expensive. It will also burden your shiny new Fusion instance with transactions that may soon no longer be required.
Minimal Conversion Keep EBS Running
The other option is to perform a minimal conversion of master data and keep EBS up and running, allowing users to look up historical transactions. However, maintaining an EBS environment can be expensive, as it requires keeping all the tech stack components up and running and patched.
The good news is that there is an alternative.
APEX On-Premise
With APEX, you can keep your historical data in the same database you used for EBS, eliminating the complex architectural components required to keep EBS up and running. You need a small 4GB RAM VM to run ORDS and the database, and you are good to go.
Of course, there is a little more to it than that! You still have to build a User Interface in APEX to access the historical data in the old EBS database. If you make use of pre-built EBS views, it is easy to build powerful Interactive Reports on the EBS data. You can even use pre-built APEX functions to download EBS attachments via an APEX UI. You will also need to pay for a license for your EBS database and keep the database server and software up to date.
💡
This solution significantly reduces the complexity and cost of making historical transactions available to your users.
APEX on Oracle Cloud Infrastructure
To further reduce complexity, consider migrating the EBS database to Oracle Cloud Infrastructure (OCI). This would allow you to shut down the database when it is not needed, further reducing costs.
You can even migrate specific schemas (or the entire database) from EBS to the Oracle Autonomous Database on OCI. This would further reduce complexity, as Oracle manages Oracle REST Data Services for you! But wait, it gets better. Because archival solutions are read-only and typically don’t get used much, you can save even more money by:
Use the Oracle APEX Application Development Service, which is half the price of a full Autonomous Database Instance.
Use advanced compression to reduce storage costs.
Schedule automatic instance shutdowns on nights and weekends to reduce utilization costs.
💡
When all is said and done, you could pay as little as a few hundred dollars per month.
Solution 2 - AP Invoice Archival Solution
Another client of mine had migrated to Oracle Fusion but was maintaining a legacy AP solution from Ascend. They kept this software running on-premise to provide copies of AP invoices for annual audits.
They had over 3 million AP invoices and nearly 24 million attachments (in PDF and Excel formats), representing approximately 6.4 terabytes of file storage.
Migration to APEX on OCI
The solution for this client was to move the AP Invoice metadata to an Autonomous Transaction Processing (ATP) instance on OCI. Using advanced compression, we reduced the storage space required for the two metadata tables (comprising 31 million records) from 12 GB to 3 GB.
We migrated the metadata by generating CSV files from the legacy SQL Server database and copying these to OCI Object Storage. We then used DBMS_CLOUD.COPY_DATA to load the ATP tables. Using this approach, we were able to load the 31 million records in under 10 minutes.
Attachments
We migrated the 24 million attachments (5.4 TB) to OCI Object Storage and uploaded the files using the OCI Command Line Interface (CLI) to Oracle Object Storage. The object storage CLI can load 1,000 files in parallel, which allowed us to upload all 24 million attachments in just over 24 hours.
Because the historical attachments do not need to be accessed frequently, we created rules to move the files to the ‘Object Storage—Infrequent Acces’ tier to save even more money. This tier has storage costs that are half those of the standard tier, but retrieval costs are more expensive than those of the standard tier. Read more about how we copied all of these files here.
APEX Front End
We built an APEX front end that allowed users to query invoices requested by auditors and enter search criteria for bulk extracts. So, if an auditor asked for all invoices over $1,000 in June 2003, the user could enter the requirements, and an APEX Background Page Process would be initiated to fetch the attachments for the invoices from Object Storage and build an Excel file containing all of the invoices in the extract using the APEX_DATA_EXPORT PLSQL API.
We utilized the client’s existing Okta Single Sign-On to provide access to the APEX Application. This has the added benefit of removing the burden of managing password infrastructure on the legacy Ascend solution.
💡
The solution consisted of fewer than 10 APEX pages and approximately 500 lines of PL/SQL code, and was developed in under a month.
Conclusion
Of course, the best archival solution is no archive solution, but regulatory requirements often require you to keep specific data for many years. Combining Oracle APEX and Oracle Cloud Infrastructure can significantly reduce the costs and complexity of maintaining archival solutions.
