Essential ERP Tool for Analysis and Issue Resolution

Overview

As an ERP consultant, I frequently need to troubleshoot why something is not working in the ERP or other enterprise system.

Many times, I need access to a system's setups or I need to compare settings between objects or environments. Instead of having to take screenshots across multiple pages or environments, I use my low-code development tool, Oracle APEX, to quickly gather all the data in one place.

Take this recent example where I was guiding my client through an ERP upgrade from Oracle EBS (E-Business Suite) to Oracle Fusion Cloud. My client had 40+ Item Templates and needed guidance on how to best consolidate the 40+ templates to an essential few. When you realize that each template contains 300+ attributes, you are comparing a matrix with over 12,000 values. A typical ERP form is unable to display all 300+ attributes in one page, so it forces you to scroll down 18-20 times to get to the last attribute, per template.

Scrolling through the hundreds of attributes in Oracle Cloud Fusion

Scrolling through the hundreds of attributes in Oracle EBS

There is a better way.

Better Ways To See Your Data

As you can see from the GIFs above, I need a more effective way to display and analyze my 12,000+ data points. Here is my workflow to quickly transform my data:

1. Extract data from the enterprise system

2. Download the data to Excel or csv

3. Upload file to Oracle APEX

4. Explore and analyze in APEX

Extract data from the enterprise system

I leverage SQL Developer for querying Oracle EBS records and BI Publisher for querying from Oracle Fusion. I find these tools offer the most efficient way of extracting setups and other necessary data from either ERP system.

My Data using the BI Publisher Tool in Oracle Fusion

My Data using SQL Developer for Oracle EBS

As you can see, these formats do not offer an optimal way of studying my data points.

Download data to Excel

Excel is a close second when it comes to analyzing data. I can filter, pivot and transform data to my heart's delight.

Upload Data to Oracle APEX

But look how much easier and funner (is this a word?) APEX makes this task for me. End users find this format simpler and more pleasant to work with. Compare the following two GIFs with the first two on this post - no comparison really.

This app took me two minutes to create, with just the upload of my file (here's how).

Notice the Faceted Search feature displayed on the left hand side of the page. APEX automatically detects the 'categories' in my file and builds the feature for me. The Search box feature is also automatically built-in, as displayed below.

This post on Faceted Search by Monica, from the APEX Development team, shows how APEX automatically builds dashboards and charts for each of your facets too.

These UIs makes a powerful statement when you're presenting in a meeting. The client loved this view of their setups so much they asked us to make it a permanent, real-time page in their Production environment. To transform this to a long-term solution, we built an integration to integrate the template data in ERP with APEX. This allowed us to provide additional features such as 1) dynamically adding templates when new ones were created in the ERP and 2) allow users to filter by template. See below.

Consequently, we ended up transforming other settings, setups, and similar pages to APEX as well.

Introduce APEX for one task, and you'll be asked to apply it everywhere in the company :)

And...

Explore & Analyze with Oracle APEX + Other Tools

APEX is a terrific starting point for visualization and analysis of large amounts of data. I complement it with advanced SQL queries to compare data across columns and identify patterns.

As a fun exercise, I uploaded my file up to ChatGPT to see if it could find similarities across templates. Here's what it returned:

And...

These pointers from ChatGPT are excellent starting points for any analysis. In order to consolidate the templates from 40 to a few, however, requires in-depth understanding of the dependencies AND inter-dependencies between the 300 attributes as well as their impact to other setups across the ERP. Mapping the features and functions of these attributes with the ultimately business goals of the client, is what allows me to eventually narrow down the number from 40 to five. AI can't do this for us...yet.

Summary

Transforming settings, setups and similar pages are one of my favorite use cases for using Oracle APEX to simplify the view of data. With APEX's easy file upload, I can transform any file into a modern, UI-friendly web page in just seven steps and under two minutes. And its automatic faceted search capability accelerates my data analysis because I can focus on identifying patterns, similarities, and variances in my data instead of building and coding an app.

I hope this post encourages you to use Oracle APEX to debug and troubleshoot your next issue. It makes me look good in front of clients, every time. :).