BI- From JD Edwards UTB to Oracle BI EssBase
BI – Thoughts (8) – Cadran publishes a series of articles about the ideas surrounding Business Intelligence in combination with Oracle JD Edwards ERP. In these articles various considerations and reflections are discussed, which can be helpful in making the right decisions in the implementation and application of both systems. The previous article discussed the development cycle and maturity steps. In this article this is applied by looking at all the reporting options in the broadest sense of the word for Oracle JD Edwards. From small to large, from flat to multi-dimensional, from historical to predictive and from operational to strategic. Depending on the maturity of the organization, ICT and data, the possibilities can be applied to a greater or lesser extent.
Business Intelligence Continuum
In organizations reporting and information needs to exist at all levels. The quadrant in the image places these levels from the bottom left as based on historical operational information requirements to the upper right as based on dynamic future-oriented information. In that corner the crystall ball comes into the picture in the form of What-If analyzes.
Oracle JD Edwards, as the back office ERP system, is an important source of data in many functional areas in the organization. If we lay Oracle JD Edwards and Oracle Business Intelligence side by side in the broadest sense of the word, then we can place the standard reporting capabilities of JD Edwards in the bottom left corner, Oracle BI in the middle and Oracle BI EssBase at the top right.
Oracle JD Edwards itself offers the necessary reporting options, but higher on the ladder requires heavier artillery.
Ad Hoc issues will always play at all levels. At a small operational level, the question may be: “What happened to this sales order line last week?”. At high strategic predictive level, the question may be: “What would happen with the margin in five years if the product range were expanded with a new product line to be launched?”.
From small to large, the key to definitions seeps through. How many truths would you have wanted? The ad hoc question always contains the danger of new truth and a new definition.
Oracle JD Edwards
Analyzes of the data from JD Edwards play across the entire organization. From a simple investigation into what happened to an order line to a consolidated Profit & Loss. All aspects are covered in the paragraphs below.
Universal Table Browser
A system administrator tries to find out what happened to a particular sales order line. On the JD Edwards Fat Client he starts the Universal Table Browser (UTB), opens the table F42199 (Sales Order Ledger) and enters the order number to be examined.
It gives him the desired information, but he will have to know what he is looking for and how to interpret the functional and technical information. He cann’t do more than look at the data and export to Excel is not available.
If the same question is asked to an application manager, he may possibly look up the information using the Data Browser. The data shown is the same, but the fields here are clearly functionally recognizable and more unambiguous to interpret. In addition, the tool offers a multitude of search and filter options. The grid can be adjusted to the sorting and whether or not columns are displayed. Furthermore, the user is given the opportunity to export this data to Excel and to create a so-called Saved Query. This is discussed in more detail later in this article.
JD Edwards offers a variety of standard applications, which normally start with a query screen. The desired data is searched and can be viewed and edited. For the investigation into this particular sales order line, the application manager could also start the program referred to above. Now more information is available. For example, this screen shows the name next to the customer’s address book number. Again, the user is able to choose and organize the displayed information, and here too, export to Excel is possible for further analysis and the Saved Query functionality is available. In addition, a standard screen in JD Edwards will offer exits to logically related information, such as in this example to the Customer Service Inquiry to gain insight into the outstanding accounts receivables.
Saved Queries is a powerful tool that offers the possibility to standardize recurring questions and formalize them for reuse. This application is available in the Data Browser and in virtually all programs in search and selection screens. This allows you to define, record and even share certain issues with other users. The amount of filtering and conditioning options go much further. An example of this is a sales employee, who regularly checks which sales orders are in back order.
This allows for various operational issues to be defined and recorded and reused. The information need shifts from completely ad-hoc to something more structured.
In combination with OneViewReporting, the results of Saved Queries in interactive applications in JD Edwards can evolve into well-organized interactive reports that open up the requested data with much more insight. The tool provides the means to produce these overviews, but Oracle already provides a large set of predefined reports for the various modules of JD Edwards, which can be accessed from many screens of JD Edwards.
The example here beside shows one of the possibilities that can be offered from Customer Ledger Inquiry (or the viewing screen with open accounts receivables). The definition is not so much in what is shown, but with which criteria (the saved query) that information is requested.
In addition to all kinds of standard question screens, JD Edwards also has a multitude of standard reports, ranging from daily integrity reports to periodic consolidated overviews. The example on the right shows the reports that are available in the standard software in the General Accounting – Periodic Processing – Integrity Reports menu. The advantage of the built-in reports in JD Edwards is that they take into account the standard logic of the software and the issues that played a role in the setup and implementation of the operational process. Consider the structure of the chart of accounts, the fiscal periods and the financial reporting structure. This has removed part of the care and proliferation of definitions.
ERW or RDA and FRW
Enterprise Report Writer (ERW) or Report Design Aid (RDA) is the toolset that is available in the JD Edwards case tool to develop reports that are not available in the standard software. This yields so-called batches or UBEs, which can be controlled with Processing Options and can use all sorts of conditions and criteria on the passing data with Data Selection.
Financial Report Writer (FRW) is a refinement that makes it possible to make financial consolidation reports, especially on the balance sheet and the general ledger, offering all kinds of drill-down, zoom-in and roll-up options.
For both resources, it is perfectly possible to present the right data, but the form and distribution options have room for improvement, unless they are combined with the possibilities of Report Definitions (BI Publisher embedded in JD Edwards UBEs).
But because one works within JD Edwards, the application logic and setup is fully available.
It has never really been widely reported, but in the days of data warehousing, data mining and other analysis options, two predefined dashboards have been developed within Oracle JD Edwards:
- Financial Manager Dashboard (P80D350 ZJDE0001)
- Plant Manager Dashboard (P80D350 ZJDE0002)
To this end, a number of aggregation tables have been defined in the database of JD Edwards (simple cubes). A number of batch jobs have been developed to feed these cubes from the transactional data. The dashboards use the setup, such as organizational structure, and can present and analyze data from various angles. The definition is clear, but fixed in these screens. There is integrated data in both dashboards from:
- Accounts Payable
- Accounts Receivable
- Advanced Cost Accounting.
- General Accounting
- Sales Order Management
- Purchase Order Management
- Inventory Management
- Work Order Management (Manufacturing)
This is sometimes referred to as the best kept secret of JD Edwards. The question is, however, to what extent this is still still useful in terms of current developments.
This Easter egg originates from version 8.11 but can not be found in 9.2. It is probably linked to the case-tool possibilities of the Tools Release. But of course there is other beauty today.
The JD Edwards database can be accessed via ODBC or JDBC from the outside and with it a range of query tools, ranging from MS Access and MS Excel External Query to a variety of simple, user-friendly and accessible means such as Brio and QlikView and of course dozens of SQL-based solutions. They are nowadays more and more equipped with all kinds of wizards and have more and more extensive layout possibilities, so that the most beautiful results can be presented quickly. Unlocking the database of JD Edwards in this way is endless in flexibility and application, as long as:
- Monitoring of definitions is guaranteed.
- Wild growth is prevented.
- JD Edwards is understood:
- Data model
- Business Logic
- Capacity and resources available to handle the data flow.
- Security issues are served in a different way.
Querying directly on an operational transactional database may however have impact on the performance of JD Edwards when the amount of data increases, locks may arise and there is insufficient knowledge of the tool and data source.
Oracle BI Publisher Enterprise Edition can itself be a query tool as mentioned above. The JD Edwards database can be accessed with it and all possible queries can be executed. When used in conjunction with the JD Edwards Data Access Driver for Oracle BI Publisher this even refines. This driver unlocks the data from JD Edwards via the JD Edwards security model and works via the JD Edwards Data Dictionary. This provides UDCs with the correct descriptions and dates and numbers are formatted in the correct format. Ideally, BI Publisher uses the Oracle BI Enterprise Edition model as a data source, applying the same definitions as on interactive dashboards.
When Oracle JD Edwards is used, the Technical Foundation is often available. This license entitles you to use Oracle BI Publisher Enterprise Edition, provided that only the database of JD Edwards is used as a data source. This makes getting on this platform very easy.
Oracle BI Enterprise Edition
When the business definitions are defined in such a way that they can be entrusted to the metadata in a Common Enterprise Information Model, Oracle Business Intelligence can play a role here in the broader sense of the word. In addition, this software offers the possibility to combine data from multiple sources. A classic example of this is the term Customer, which on the one hand comes from the JD Edwards Accounts Receivables combined with Prospects from a CRM system.
The BI Information Model forms the metadata with which the facts and the dimensions are captured and made accessible under the same definitions. One Version of the Truth is there.
The tools of the software are of course able to show measured values and facts. But when targets, budgets, forecasts, trends and appraisal are linked to this, the concept of Key Performance Indicator comes into play. The level of data is outweighed and steering information is born. Strong steering information is created, which helps to indicate where possible bottlenecks in capacity and desired results are. By assigning a value judgment to what is good, bad, too much or too little, analyzes can evolve and support management by exception.
When multiple KPIs from multiple processes and departments in the organization are combined, this can rise to strategic direction on goals in the form of KPI watchlists and strategic scorecards.
Oracle BI Applications in combination with the JD Edwards Adapter provides a ready-to-use retrieval of the JD Edwards data via pre-defined ETL to a predefined data warehouse and presents it in a large set of dashboards, analyzes and reports. When the line of these best-of-breeds developments can be used on the standard software, this is a next step in BI possibilities. However, as we move higher up this ladder, the importance and the accuracy and detail of the definitions of all concepts increases. Only then can the information form added value in the process improvement of the organization.
Want to learn more?
Author: Rick Brobbel, BI Consultant at Cadran Consultancy