BI- From JD Edwards UTB to Oracle BI EssBaseBI – 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 ContinuumIn 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 EdwardsAnalyzes 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 BrowserA 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.
Data BrowserIf 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.
Standard screensJD 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 QueriesSaved 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.
OneViewReportingIn 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.
Standard reportsIn 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 FRWEnterprise 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.
Manager DashboardsIt 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)
- Accounts Payable
- Accounts Receivable
- Advanced Cost Accounting.
- General Accounting
- Sales Order Management
- Purchase Order Management
- Inventory Management
- Work Order Management (Manufacturing)
Externe query-toolsThe 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.
PublisherOracle 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 EditionWhen 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.
ApplicationsOracle 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? Contact us Author: Rick Brobbel, BI Consultant at Cadran Consultancy
Advice about our solutions?
Rick Brobbel, BI Consultant at Cadran, would be happy to talk about the possibilities for your organization.