Processing Large Data in Oracle ERP System

Introduction

Oracle Fusion ERP helps businesses to manage their operations and to make decisions from data available in the system. However, the problem arises when data volumes increase as organizations start to grow. Working with large datasets in the system will lead to performance issues like executing large reports, integrations that rely on the data from the reports, or data extractions. For technical teams, handling huge volumes of data effectively may be quite challenging.

Large datasets can cause several performance issues, like:

1. Report Failures: Scheduled reports or online reports can often fail or time out due to large amounts of data causing system limits.
2. Slow integrations or failures: When working with big datasets, scheduled procedures or API-based integrations may suffer from performance deterioration, which might lead to delays in data processing or synchronization.
3. Issues in data extraction: It may be essential to split or segment data when exporting large datasets for analytics or compliance reasons, as they might exceed system-imposed size restrictions.
4. User frustration: Decision-making becomes more difficult when processes are disrupted by frequent failures or delays in getting crucial information.

Such challenges arise from inefficient techniques for process design and data extraction or reporting, along with the inherent limits of handling huge volumes of data. In addition, it’s critical to resolve these performance issues while recognizing Oracle Fusion ERP’s limitations, especially the absence of direct database access and a strong dependence on seeded tools like BI Publisher, OTBI (Oracle Transactional Business Intelligence), and REST/SOAP APIs for data management.

Reports and integrations that do not function properly could lead to delays in business-critical operations like supply chain management, regulatory filings, and financial closes. To ensure seamless operation, maintain user productivity, and increase system dependability, performance optimization for huge data reporting and associated procedures becomes vital.

This blog tackles the challenges of handling large data sets in Oracle Fusion ERP, offering practical solutions to enhance performance. From effective filtering to advanced techniques like chunking and bursting, we address the root causes of slow reports, integrations, and data extractions. By adopting these best practices, you can optimize performance and future-proof your processes against the growing demands of a data-intensive business environment.

Performance Tuning

When we talk about the improvement in the performance of the query or code, we can divide it into two major parts:

• Code Optimization
• Data Size

In code optimization, we can use various methods like rearranging the query in a structured way by checking the joins and removing or realigning the joins properly. In the data size part, we can break down data in the output itself by adding proper filters to reduce the size of the data generated by the SQL query, or we can use the chunking functionality provided by Oracle to split the data into multiple outputs.

Code Optimization

The first and most important step in deconstructing the problem of the big data collection is code optimization. We must first examine the SQL query’s flows and reorganize it in relation to the results.  The query might encounter issues with performance due to the possibility of missing or improper joins. To fix these kinds of problems, we must first divide the queries into smaller components and then debug the queries to find the problematic portion. Code optimization can majorly be divided into three parts:

1. Process outside ERP
   1. Process Data in Warehouse System
   2. Third Party Tools
2. Query Re-Arrange
   1. Use of proper joins
   2. Removal of subqueries
   3. Remove unnecessary data
3. Query Tuning
   1. Use of views
   2. Hints

The above diagram shows various ways to tackle the issue, which is caused by the code; we can use these different ways to resolve the issue. Let us discuss these methods in detail.

Process Outside ERP System

As we know, Oracle Fusion is a transactional database.  There will be some limitations to it; to overcome these limitations in performance, we can use Data warehouse systems, or we can get the help of various third-party tools available in the market like Power BI.

Process Data in Warehouse System:

Data warehouse systems efficiently handle large ERP data volumes. Oracle Analytics Cloud (OAC) seamlessly integrates with Oracle ERP, allowing custom subject areas in the presentation layer, similar to OBIEE, or direct SQL query building. To generate reports, ERP data must be periodically extracted via scheduled reports, Business Intelligence Cloud Connector (BICC), or integrations. This reduces data load, ensuring smooth processing. Warehousing is crucial for large reports like GL transactions or Aging reports, which are difficult to manage in transactional ERP databases

Use of third-party tools:

Various third-party tools for reporting are available in the market, which can be used to generate the report in various ways. These tools can provide more insights in a better way than the reporting tool available in the ERP system. Some of these tools are Power BI (a Microsoft Product), SmartView for Oracle (can be used in Excel), or APEX Dashboards. These tools provide greater flexibility and efficiency in handling large datasets compared to standard ERP reporting solutions. By offloading data processing to external tools, businesses can significantly enhance report performance, minimize timeouts, and ensure smoother operations without being constrained by ERP limitations.

Power BI allows users to connect directly to Oracle Fusion ERP data sources, process and visualize data more effectively, and apply advanced analytics without overloading the ERP system. By extracting and transforming data externally, reports can be generated faster and with richer insights.

Oracle APEX is another powerful tool that enables the development of lightweight web applications and dashboards. It can be used to create customized reports that fetch and process data outside of the ERP, reducing the burden on the system while improving response times.

Query Re-Arrange

When we talked about processing the data outside of the ERP system, we needed to use third-party tools or data warehouse systems, which can be costly and require high maintenance. To save a cost, we can optimize the query by removing subqueries and merging them into the main queries or we can relook at the joins we have used in the query and rearrange them better way we can remove unnecessary data from the query to optimize the performance. This way we can save money and improve the performance of the query and get the desired results.

Proper Joins

The query will not perform well if proper joins are not being used in SQL. As a standard, we have to always use a proper join to fetch the data. It is essential to use proper joins to retrieve the data. For large reports, if the joins are not maintained properly the query might not provide a desired result or in worse scenarios, it will not run at all. It is recommended to check the Oracle documents in case of the query performance issue to identify any obsolete columns or missed joins as the first approach in the query rearrangement.

Remove Subqueries

Another bottleneck issue in the time-consuming queries is sub-queries. Sub-queries are good if they do not consume more resources, but in most cases, subqueries consume more resources and can generate duplicate or improper data. Below are the pros and cons of using sub queries:

Before jumping to any conclusion about using the subqueries or not, it is always recommended to go through the explain plan generated by the SQL to identify the part that is causing the issue. If it is the subquery part that is causing the problem, then it is advisable to use it as Common Table Expressions (CTE). CTEs are generally faster in executing the way databases process and optimize them. Below is the explanation of when to use the subquery and when not to:

• When to use subqueries:
  • When dealing with the smaller amount of data
  • When using aggregation logics
  • When inline calculation needed
  • When filtering data in where clause

• When not to use subqueries:
  • When dealing with larger data sets
  • When it is feasible to use CTE for faster executions
  • When require using the same logic multiple times
  • When query performance is important

Remove Unnecessary Data

After applying the above-mentioned optimization techniques, if there is still a performance issue, then it is recommended to remove unnecessary columns from the query. We have seen many times there are not-so-important columns added in the query, which can cause performance issues and slow down the execution time. It is recommended to exclude these columns to improve the performance of the query.

Query Tuning

The final part in the optimization of the query is tuning. As mentioned earlier, we need to check the explain plan to identify the part that is causing the performance issue. In the tuning part we need to rewrite the code based on the available resources in the Oracle ERP system, as direct database access isn’t available in Fusion. As a developer, we can make proper use of the views provided by the oracle or usage of the hints.

Use of views:

Views that are provided by the oracle are generally faster than the normal tables in execution due to several factors like proper joins maintained in the view logic, performance enhancements via indexing, etc. For e.g., To fetch the data of supplier in oracle fusion Instead of using POZ_SUPPLIERS and joining it with multiple tables, it is advisable to use view POZ_SUPPLIERS_V which is provided by an oracle with some of the useful columns required for the query.

Use of Hints:

Oracle SQL hints are instructions that help the optimizer determine the optimal query execution strategy. Hints can help optimize performance in Oracle Fusion, particularly when querying huge datasets in BI Publisher.