Read JDEAdhocReporting.pdf text version

J.D. Edwards® Adhoc Reporting

By Pat Nowak

Executive Summary

General Overview Companies are investing millions of dollars in ERP solutions such as J.D Edwards, to improve the way they conduct business. But while ERP provides significant benefits, organizations are finding that, in order to achieve critical business goals, they often need to supplement it with additional reporting capabilities to its end user community. With the wealth of information collected and stored by organizations in the ERP's relational databases, the ability to analyze and interpret the data is significantly impacted by the ability to access the information. With the development of end-user query and reporting software tools, users now have the ability to develop their own ad hoc queries and reports. The development of end-user query and reporting tools has greatly alleviated the backlog of report requests and has provided users with information in a timely manner. However, the configuration and implementation of these tools as well as the data accessibility is extremely important and directly impacts the results that end-users receive from the queries and reports as well as the impact of the ERP system performance when these queries are running. The objective of this white paper is to provide you with the knowledge necessary to understand what type of enduser ad hoc query and reporting tools are available, what impact they can cause on ERP performance, and provide an actual case study of a company that has implemented an end-user ad hoc query and reporting tools that access J.D. Edwards® ERP data. What is Ad Hoc Query and Reporting? Ad Hoc Query and Reporting is defined as empowering information users and decision-makers to extract data from sources when they want it, how they want it, with ability to turn data into information, and turn information into knowledge. We can further extend this definition to give knowledgeable users a general purpose, "easy to use", data formatting tool that allows them to make the system's data visible without requiring the services of a skilled programmer. The term "easy to use" has become so overused in the software industry that it has dissolved into a near-meaningless cliché. But it is clear that users won't drive the information discovery process unless the querying environment is · · Stimulative enough to lower the price of entry to a carefree impulse, flexible and forgiving enough so the user doesn't experience frustration or inadequacy, and

· fast enough so the iterative cycle of information, in which each answer stimulates the next question, can flow freely. Why does my organization need ad hoc query and reporting? As organizations migrate to an ERP system, organizations are flattened out and middle management is reduced in both headcount and authority, decisions with strategic implications are being driven down the hierarchy toward line responsibility. With this delegated responsibility comes a greater need for information on the part of a wider circle of workers, in order to provide a factual base for management decisions. In short, users will need access to data to support decision-making. Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 Contact : Pat Nowak Oracular, Inc.

J.D. Edwards Adhoc Reporting

Page 2

Why does my organization need ad hoc query and reporting in One World? Limited Reporting Options In order to make business decisions, almost all ERP solutions come with a reporting toolset with predefined reports and general-purpose query tools necessary to explore the data. For J.D. Edwards®, the standard reporting toolset is Enterprise Report Writer and for basic queries is the Universal Table Browser or commonly referred to as UTB. While the Enterprise Report Writer is limited to the "one-size-fits-all" category, it can be difficult and confusing to use due to the fact that this tool presents oceans of options when the only thing that matters to a user is to answer the question of the moment. Unless the user is also a J.D. Edwards® developer, this reporting tool is normally not available to end-users. Also, any Enterprise Report Developer must follow the J.D. Edwards® System Development Lifecycle in order for reports to be shared by others. In order to answer the question of the moment, users can utilize the Universal Table Browser (UTB). Although this is a quick solution to querying table data, it does not allow for joining of multiple tables, full address book descriptions, UDC value descriptions, and saving UTB queries. Also, no table formatting such as cross tab (matrix), group sorts, summary, or tabular is available. Business Requirements Change Although the reports that come "out of the box" with JDE Edwards maybe acceptable for day-to-day operations, but if business requirements change, then these static reports may need to changes as well. Waiting for a developer to make necessary changes to an existing report may take weeks, months, or may never be changed while your users are demanding it now. It is often changes to the business environment that drive the need for information. The deregulation of the telephone industry, for instance, causes the telecommunication service providers to make dramatic changes in the way they compete for new business, define products, provide service, and process revenue. Since it is impossible to predict next year's requirements, it is also impossible to design next year's queries and reports. By utilizing reporting toolset outside of J.D. Edwards®, and putting the Ad Hoc and Reporting process in the hands of the enduser, not only frees up valuable J.D. Edwards® developer time it also will meet the demanding thirst from the end users community to turn data into decision making information. What type of ad hoc query and reporting solutions are available for One World? Business Intelligence Reporting Tools As companies increasingly rely on their J.D. Edwards® ERP systems, a solution that can leverage ERP information for maximum bottom-line impact becomes mission-critical. At the core of such a solution is business intelligence (BI). BI allows you to share information with coworkers, customers, and business partners, enabling all parties to make smarter business decisions. Your operational data is transformed into consistent, reliable information for reporting and analysis. By quickly accessing information that would otherwise be inaccessible from your J.D. Edwards® software, you enrich the value of your ERP system. You can then more effectively: · · Identify new business opportunities Uncover the effects of organizational processes and their bottom-line impact

· Strengthen customer loyalty and partner relationships while gaining a significant competitive advantage in your industry The benefit will be to extend the value of your J.D. Edwards® system, transforming your operational data into consistent, reliable information optimized for reporting and analysis. The downfall of this type of solution is the cost. Although the long-term return on investment maybe justified, this type of investment maybe furloughed during the ERP implementation phase.

Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 Contact : Pat Nowak Oracular, Inc.

J.D. Edwards Adhoc Reporting Canned Reporting Tools

Page 3

With canned reporting tools, organizations can quickly and easily set up out-of-the-box reporting solutions designed to drive consistencies across all aspects of their business. Although these canned reports provide an ideal platform for companies looking to enhance their existing UBE reporting applications, they may find that they are paying for more reports than they will ever use. With this approach, your organization's users may be limited to do any additional reporting. ODBC Tools and Middleware J.D. Edwards® comes configured with an Open Data Access Driver commonly referred to as ODA. This driver enables window server and client application to interface with J.D. Edwards® tables for external query and reporting tools, web technologies, and decision support tools. Although the ODA appears beneficial, there are limitations to this ODBC driver: · · · · · No seamless access to all JDE Tables UDC descriptions are not available as a virtual column, except for joins done in the business view Limited to only being used by windows only applications ­ no platform independence Replication of data means installing another instance of J.D. Edwards® software environment Environment overhead for CNC

Other J.D. Edwards® solution providers have enhanced the ODA, such as JdeDirect®, to allow for full file and UDC field descriptions, vocabulary overrides, and language preferences accessibility. Business views are beneficial to users as they already join key tables together and this complexity is hidden from the user. Oracular's JIVE Metadata Access Solution Oracular has built a solution that addresses all the limitations of the above data access reporting options. Oracular JIVE Metadata Access Solution accesses the J.D. Edwards® data dictionary where the business views are stored and physically stores these as view objects in the database. The JIVE process also physically stores the UDC descriptions, correct date formatting, and numeric formatting within the stored views. Having physically stored the views, it becomes feasible to replicate the database tables and then point the business views access to this replicated database tables. Also, your organization can choose your own reporting tools based on your requirements. The following compares Oracular JIVE, J.D. Edward's ® ODA, and JdeDirect® options: Oracular Seamless access to all JDE Tables Seamless access to all JDE Business Views UDC Descriptions available as columns Database independent solution Platform independent solution No additional software required / user Secure access to underlying data Support for separate ad-hoc reporting database


ODA No Yes No Yes No No Yes Yes 1

JdeDirect ® Yes No Yes Yes No No Yes Yes1

JIVE Solution Yes Yes Yes Yes Yes Yes Yes Yes

Separate One World Env environment will need to be created

Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 Contact : Pat Nowak Oracular, Inc.

Seamless access to all JDE Tables

J.D. Edwards Adhoc Reporting JDE tables are stored in the database with abbreviated names. Their descriptive names are stored in a data dictionary. Oracular solution will automatically generate database views based on the same underlying tables. Security will be enforced using the database security infrastructure.

Page 4

Seamless access to all JDE Business Views

JDE stores the business view definition in its data dictionary. Oracular solution will automatically generate database views that represent the business views.

UDC descriptions available as columns

Several JDE tables refer to UDCs. The descriptions for these UDCs are stored in separate tables. While generating the business views, appropriate tables are joined such that the UDC description is exposed as a column.

Database independent solution Platform independent solution

Concept of database views exists in all major databases, so our solution is not database specific. Since our solution creates database views, it does not depend on the platform used by the ad-hoc query tool. ODBC solutions will require a Windows platform. This becomes a significant benefit when considering middleware solutions where the ad-hoc query tool is installed on a single server in order to serve several endusers. One example of such a solution is Oracle Application Server suite that includes Oracle Discoverer.

No additional software required / User

Since our solution creates database views, no separate software component is needed on the client-side. In order to provide an ODBC solution, the specific ODBC driver will be needed on every client machine. In addition to that some ODBC solutions may require JDE client to be installed as well.

Secure access to underlying data

JDE stores the security information inside its data dictionary. Our solution replicates this security information to the database.

Support for separate ad-hoc reporting database

Typically a separate database is needed for ad-hoc reporting needs in order to reduce the load on the production JDE instance. If an ODBC solution is employed, it may require a separate complete JDE installation along with a custom effort to keep the two databases in-synch. Our solution just looks at the raw database. Various databases provide simpler mechanism to perform complete database replication for reporting needs; for example Oracle provides a concept of stand-by database that may be used for reporting needs.

Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 Contact : Pat Nowak Oracular, Inc.

J.D. Edwards Adhoc Reporting

Page 5

Conclusion It is impossible to predict 100 percent of a company's Ad Hoc Query and Reporting needs, let alone develop all the report that will be needed by every J.D. Edwards® user. This is where ad hoc query and reporting solutions become critical. If a business user is looking for specific data not contained in a predefined J.D. Edwards report, they must rely on IT, consultants, or a report vendor to develop the report(s). With the typical IT department's backlog of project, requests, and priorities, it often takes a considerable amount of time to create or customize a report ­ time that the user does not have. To meet this reporting challenge, Oracular developed the JIVE solution to help companies unlock J.D. Edwards® business-critical data. JIVE is the only solution that pre generates a set of reporting objects (views and tables) which can be deployed on any platform ­ not just Windows®. Why JIVE? Providing users with a reporting solution can require weeks or months of costly consulting to analyze user needs and develop a set of standard corporate reports. JIVE automatically processes the J.D. Edwards® configuration and metadata. It then generates easy-to-use reporting objects, which can be accessed from any 4gl-reporting tool. If your company already has a standard reporting tool, this can even save your organization more money. The JIVE solution can easily be rolled out to end users, further reducing the strain on overburdened IT departments trying to meet the reporting demands during pre or post installation of J.D. Edwards®. When end users need additional reports, then can create these themselves and feel in control of their own data requirement needs. Real World Example Oracular was approached by a major computer component manufacturer that was in the process of implementing J.D. Edwards to all of their 12 global manufacturing facilities to provide an ad hoc reporting solution. Their old manufacturing system was running on the AS 400 platform and management empowered many of their users to run queries and reports against the database using Query 400. As more and more manufacturing sites were being converted to J.D. Edwards®, the queries and reports that the users were so accustom to running suddenly became obsolete on go-live. A huge demand and burden was immediately put on the IT staff and the J.D. Edwards implementation team for requests to redevelop the old reports in J.D. Edward's® enterprise report writer. Due to other implementation priorities, creating these reports was impossible in the short term. Oracular was contracted to solve this problem.

The Requirements The manufacturer wanted to make sure that this solution met the requirement of their users as well as removing the burden on the IT staff to create reports or install reporting software on each users computers. This manufacture also had Oracle 9ias® running with Oracle Portal® and Oracle Discoverer® and wanted to use this existing technology as the framework for creating, running, and viewing reports. In addition, this manufacturer knew they had a large reporting community and did not want these users querying the live system directly. Some of the main requirements and solutions were: 1. The reporting process (creating, running and viewing reports) must all be done using thin client technology and "owned" by the business user. Solution: Since the manufacturer was already utilizing Oracle 9ias Portal® and Discoverer in other areas of the corporation, this software platform was ideal for this project. Oracle Discoverer 9ias® is thin client and allows for report development and creation process from the web. Oracle Discoverer® was originally designed to provide and ad hoc reporting tool for data warehouse users. It has since become widely used for ad hoc reporting against production systems. It does this by storing metadata about the data and providing security to access specific data. User can then create reports going through a set of wizard steps from selecting the style of report, what fields they need, how it should be displayed and aggregated, and if it should be scheduled to run on a periodic basis. In addition, users can then share reports with others so duplication effort is avoided.

Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 Contact : Pat Nowak Oracular, Inc.

J.D. Edwards Adhoc Reporting

Page 6

2. Create a separate database with a copy of the data so users do not affect the performance of the live J.D. Edwards® system. Solution: The manufacture with 12 global sites and 6,000+ employees estimated that over 5,000 queries on the old system were run weekly. If they provided an ad hoc query and reporting tool that went against the live system, the performance of the operational J.D. Edwards® system would drop drastically. Oracular provided a solution by creating a copy of the J.D. Edward's® database tables that would be utilized specifically by reporting and query users. By pointing the reporting objects at this database, the main operational J.D. Edwards® system performance is not impacted. 3. Business views, which are embedded within the database, must be easily accessible to the report creators. The business view fields must have the long address descriptions as well as the UDC descriptions available. Solution: This is the main feature of JIVE. In simple terms, Oracular's solution is to access the data tables where the embedded views are stored and create view objects within a separate schema. JIVE also includes the long address and UDC description as well as formatting dates and numeric values correctly. Creating these objects within a separate database schemas allows any reporting tool or operating platform to access these ­ not just a windows ODBC driver.

Example of Business Views Available in the Foundation Environment ­ JIVE built

Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 Contact : Pat Nowak Oracular, Inc.

J.D. Edwards Adhoc Reporting

Page 7

Example of a view definition after JIVE reformats it:

Example of how JIVE reformats Business Views fields.

Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 Contact : Pat Nowak Oracular, Inc.

J.D. Edwards Adhoc Reporting

Page 8

4. J.D. Edward's organizes tables and views into specific system areas (Address Book, Human Resources, Account Payable, etc), so the objects that are being reported on must also follow this grouping logic. Security to these system areas must also be enforced. Solution: Through the use of roles and privileges with Oracle Discoverer®, users are allowed access to business areas (systems) that they have privileges to. The Oracle Discoverer® Administrator sets this up from the Oracle Administration tool. This is an example of Oracle Discoverer® Security screen

Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 Contact : Pat Nowak Oracular, Inc.


8 pages

Report File (DMCA)

Our content is added by our users. We aim to remove reported files within 1 working day. Please use this link to notify us:

Report this file as copyright or inappropriate


Notice: fwrite(): send of 210 bytes failed with errno=104 Connection reset by peer in /home/ on line 531