qLIKtECH, iNC.
J.D. Edwards Primer v 1.1
A briefing for Consultants working on JDE Projects and Opportunities
Prepared by: Lucia Espinosa, Senior Presales Consultant (email: )
12/31/2009
This document is recommended for internal use but can be made available to partners, customers and prospects. It is intended to speed up and facilitate a consultant’s knowledge of JDE environments from a technical perspective.
Note: Unlike other ERP applications, there is no QV Connector required for J.D. Edwards environments.
Contents
JD Edwards Primer 3
What is JD Edwards and why is it different? 3
What is JD Edwards? 3
Why is it different? 3
Script Generation 4
Technical Quirks to Keep in Mind 4
Basic User Roles 4
Functional business analyst 4
Developer/programmer 4
CNC - ERP System architect, engineer and administrator 4
What is CNC? (Configurable Network Computing) 5
Files and Data Tables 5
ODBC Driver 7
Sample Script ideal for JDE Projects 7
The infamous JDE Julian Date 10
JD EDWARDS Specific Terminology – Must Know 10
Are there any JDE Templates? Key Downloads 11
Financials Documentation for QlikView Reporting 13
Important Details and helpful links 13
Appendix A – Complete listing of JDE Modules 15
Appendix B – Complete User Documentation for JD Edwards 17
JD Edwards Primer
What is JD Edwards and why is it different?
What is JD Edwards?
J.D. Edwards, also called JDE, was a software company founded in March 1977 in Denver, Colorado by Jack Thompson, C.T.P."Chuck" Hintze, Dan Gregory and Ed McVaney. The company made its name building accounting ERP software for IBM minicomputers, beginning with the System/34 and /36, focusing from the mid 1980s on System/38 minicomputers, switching to the AS/400 when it became available. Their main AS/400 offering was called J.D. Edwards WorldSoftware and is popularly called World. In 1996, J.D. Edwards also launched a client-server version of their software called OneWorld, later called EnterpriseOne. The company's official name was J.D. Edwards World Solution Company. The company headquarters was located at One Technology Way, in Denver, CO. JDE was bought out by PeopleSoft in 2003. PeopleSoft, in turn, was purchased by Oracle Corporation in 2005. Oracle Corp. continues to sell and support the J.D. Edwards EnterpriseOne and World ERP software line. (See Appendix B of this document for a complete listing of JD EDWARDS software products).
Why is it different?
This point focuses on why is it different as a data source for QlikView than most others, and why is it different for you, as a QlikView Consultant, to support a JD Edwards project.
The typical JD EDWARDS customer has a large portion of their IT budget dedicated to supporting the JD EDWARDS environment. The most common installation found are the traditional ERP modules (see Appendix B for a complete listing), and out of those the most common ones are the Financials, MRP (Manufacturing Resource Planning), and Sales. JD Edwards is prevalent in manufacturing companies and more than likely the implementation may have been running for over a decade and has been heavily customized. No two JD Edwards implementations are alike, by virtue of the inherent architecture and design of the software, built to be custom fitted to the customer’s environment and business processes.
Due to this flexible architecture and its relational database (RDBMS) oriented foundation it is not necessary to build a special connector for QlikView in JD Edwards environments. All access is performed via the standard ODBC connectors. With JD Edwards (JDE) running on the AS/400 platform (now IBM iSeries or IBM i (also previously known as i5/OS or OS/400), the customer can also generate export flat files known as DIFF files that can be used as a data source and imported ad Local Files in QlikView via the file data source wizard.
Two main databases are supported in JDE environments: DB2 and Oracle, versions will vary, and there are also some running on Ms SQL Server. Most customer installations will already have the appropriate ODBC drivers in house to extract information from JDE for existing reporting solutions.
The more modules the customer has implemented, the more tables to choose from as data sources for QlikView. A typical JD EDWARDS environment may have several hundred tables, also known as files. Some tables/files have more than 100 fields, out of which only about 25% will be in use at any given installation.
JD EDWARDS applications rest on Relational Databases that are Highly Normalized, orchestrated in an architecture style unique to JDE known as “networked architecture” or CNC (Configurable Network Computing ).
Data table names are very standard, though to the unfamiliar eye they may look cryptic. Table names start with the letter F (for File) followed by six digits, which organize the file into the area it belongs to based on the number. Field names are usually six characters in length, mnemonic for some business or system area abbreviation of the data element they represent, and begin with two letters indicating the business function they belong to, i.e. SDXXX would be fields in the Sales and Distribution module, ADXXX for the Address Book area.
Script Generation
The QlikView Script Builder will initially load the Data from the customer’s JD EDWARDS tables selected and will display all the tables and fields available for download. Most tables have system fields like time of update and time of creation which are bound to generate synthetic keys, therefore unless needed these fields need to be unselected or aliased.
What makes this environment different is that you will need to make sure a resource knowledgeable of the JD EDWARDS data and how the customer is using the JD EDWARDS data tables and fields is very important. This is because of the flexible nature of the JDE architecture.
Technical Quirks to Keep in Mind
This section covers aspects like Size and Structure of a JD EDWARDS Environment (i.e. tables, specifics about the architecture and configuration, etc).
One of the more important things in a JDE project is working closely with the people who best know the system and its implementation.
Basic User Roles
As an ERP system JDE comprises 3 basic areas of expertise, functional-business, programmer-developer and technical-CNC-system administration.
Functional business analyst
A JDE functional person is an expert on one or more of the JDE modules, financials, manufacturing, operations, transportation, sales and other areas. This person is the business subject matter expert. Often they started as a JDE user, then super user or power user and gradually developed the skill set of being able to support the business aspects of JDE. Other times, they might have a business degree and come into JDE on the job as a business analyst. This person seldom has any programming or development experience.
Developer/programmer
This person is trained in the software development and programming tools that translate the business requirements as identified by the functional people above into code and programming. Sometimes these individuals simply modify existing JDE objects and in other cases, develop entire suites of applications using the OneWorld/EnterpriseOne development tools including the Report Design Aide (RDA), Table Design Aide (TDA), business function C-code design tools and others using JDE's change management system, Object Management Workbench.
CNC - ERP System architect, engineer and administrator
This is a catch-all function comprising all that the two positions above do not cover including, installation, upgrades, updates, change management, system administration, security, performance tuning, package build and deployment and over-all architecture. The CNC title is taken from the term Configurable Network Computing which describes the overall JDE architecture.
What is CNC? (Configurable Network Computing)
Configurable Network Computing or CNC is JD Edwards's (JDE) client-server proprietary architecture and methodology that implements its highly-scalable enterprise-wide business solutions software that can run on a wide variety of hardware, operating systems (OS) and hardware platforms. Now a division of the Oracle Corporation, Oracle continues to sponsor ongoing development the JDEdwards Enterprise Resource Planning (ERP) system. While highly flexible, the CNC architecture is proprietary and, as such, it cannot be exported to any other systems. While the CNC architecture's chief claim to fame, insulation of applications from the underlying database and operating systems, was largely superseded by modern web-based technology, nevertheless CNC technology continues to be at the heart of both JDEdwards' OneWorld and EnterpriseOne architecture. While a proprietary architecture, CNC is neither an Oracle nor JDE product offering. The term CNC also refers to the systems analysts who install, maintain, manage and enhance this architecture. CNCs are also one of the three technical areas of expertise in the JDEdwards Enterprise Resource Planning ERP which include developer/report writer and functional/business analysts.
In what has been known traditionally as client-server environments, applications must communicate across a combination of different hardware platforms, operating systems and databases. The CNC architecture uses a layer of software, called middleware, which resides between the platform operating system and the JDE business applications. To accomplish this, JDE provides two types of middleware, JDENET Communication Middleware for communication of and JDEBASE Database Middleware. The JDEBASE middleware communicates with the database through ODBC, JDBC, or SQL*Net.
The back-end databases supported include Oracle, Microsoft SQL Server, and IBM DB2 databases. The application server can run on Windows platforms, Unix/Linux, and the IBM System i (formerly known as iSeries and AS/400). The Web server can be IBM WebSphere (on Windows, Unix/Llinux, or System i), or the Oracle Transaction Server.
Files and Data Tables
Every customer is unique and may have a specific requirement. However to assist in the selection of files for ETL extract ion we can consider four types of table / file. Database tables are stored with abbreviated names and the descriptive names are stored in a data dictionary. The table and field names can seem cryptic but are consistent with field naming standards such as:
· TO, SD or MCO at end of field, the first two letters dictate the system are that the data belongs to, so in sales area all field names start with SD SDMCO SDDOCO
· MCO is always representative of the business unit
· CO is company number everywhere
Usually the first two characters represent the system and last 3-4 are the actual data elements.
Following the description of the main four groups of files is a list of 34 files that are typically involved in most BI reporting projects which fall under one of these 4 file groups:
1. Security files
2. Configuration files
3. Entity files
4. Transactional files
Security Files
Security tables or files define both access control and what a user can do when they are in an application. Included in the file list are the menu tables. These hold masking attributes that can grant or deny access. All too frequently users will create their own instance of menus that call JDE or non JDE applications.
Configuration Files
There are many configuration files in JDE. It is these that make the application so flexible and user friendly. For example automatic accounting instructions define the path of automated transactional processing in terms of source and destination accounts.
Entity Files
These files are used to identify specific entities. In other ERPs these might be known as “master data”. This might be an account, supplier or employee. The most common entity definition files have been included in the listing below.
Transactional Files
The primary transactional files have been included in this section as a recommendation for BI reporting and analytics. While data volumes are unlikely to be an issue for the files in the other areas, the selected
fields in these tables should be well selected as there are many fields and contents that may not pertain to reporting or analysis for BI.
Most common Security files
File ID Description
F0001 Business Unit Security
F0003 Action Code Security
F0103 Action Code/Search type Security (A9.1)
F00042 User Defined Code Security
F0024 User Group Batch Security File
F0082 Menu Master
F00821 Menu Selections
F9612 Function Key Security
Most common Configuration files
File ID Description
F0002 Automatic Next Numbering File
F0004 User Defined Code Types
F0005 User Defined Codes
F0006 Cost Center Master
F0011 Batch Control Records
F0012 Automatic Accounting Instructions Master
F0014 Payment Terms
F0092 Library Lists – User
F0093 Library List Control
Most common Entity files
File ID Description
F0101 Address Book Master
F0301 Customer Master
F0401 Supplier Master
F060116 Employee Master
F06116 Employee Transactions Detail File
F0901 Account Master
F1201 Item Master File
F4101 Item Master
Most common Transactional files
File ID Description
F0311 Accounts Receivable Ledger
F0411 Accounts Payable Ledger
F06490 Payroll - Accounts Payable Detail File
F0911 Account Ledger
F4111 Item Ledger File
F4201 Sales Order Header File
F4211 Sales Order Detail File
F4301 Purchase Order Header File
F4311 Purchase Order Detail File
NOTE: Several JDE Tables refer to UDCs (User Defined Codes), the descriptions for these UDCs are stored in separate tables, particularly two configuration tables: F0004 and F0005 (see above). For most QlikView reporting and analysis requirements there are two general areas where data is stored: the main area of sales which is in tables F421 F444 and the main area of finance in the key tables F900, F901, F911, and F902.
ODBC Driver
You can access the J.D. Edwards World application using an ODBC driver called iSeries Access. Before creating a data source using this ODBC driver, you must install and configure the driver on the same computer on which you installed QlikView Developer and QlikView Server later on. You can use the JDE ODBC Administration utility to install and configure the driver. Database Tables are associated with libraries, so you must go through a library to get to a table. You may need to work with the on-site JDE expert to make sure that In the driver’s library list, are listed all the libraries needed in their particular J.D. Edwards environment.
Sample Script ideal for JDE Projects
The following code snippets go over many of the issues that could come up during a QlikView implementation and that the QlilkView consultant needs to be aware of:
1. Dealing with Joins
2. Dealing with Dates
3. Dealing with Numbers