JD Edwards Tables Relationships

building semantic layer
1

Hi

I have started up with a project where the data source for a universe is the “JD Edwards OLAP Tables”

So far we have identified the following 20 odd tables to be used in the universe…and i require some help from you guys to find out the relationships between these tables (Universe design)

I have already gone thru the RDT from techsupport site and each module is very restricted like the universes as designed based on specific subject area like GL,Account Receivable, Accounts Payable,Inventory,Procurement etc.,

But what i am looking for is a complete model specific to my 20 odd tables

Pls suggest the approach or send me some details if you have regarding this request for the better design of the universe with these tables

Here is the table list of JD Edwards tables

4301 PO Header

F4311 PO Detail

F43121 PO Receipt

F4101 Item Master

F4102 Branch Plant

F41021 Current Inventory

F4111 Item Ledger

F4801 Work Order Header

F3111 Parts List

F3112 Routing

F31122 Time Tickets

F4201 Sales Header

F4211 Sales Detail

F41002 Item Conversion

F41003 Branch PlantConversion

F0101 Address Book

F3003 Routing Std

F3002 BOM Std

thanks
Sudhir

goodmate (BOB member since 2004-07-12)

2

Sudhir,

Even if you could work in all the mentioned tables within one universe design, you would still work in contexts to avoid people creating illogical queries. I guess this is the reason why the RDT’s are created around the logical subject areas.

You would need to create a context for every set of transaction tables, like :

F4211-F4201 (orderlines / orderheader)

or:

F42119-F42019 (orderlines history / orderheader history)

and then use the master-tables within each context again.

I have found that JD Edwards (Peoplesoft and now ORACLE) have no experience whatsoever with BO. They are mostly annoyed that we would like to create reports on there data anyway :frowning:

Most BO companies that work with JD Edwards seem to use datamarts to first denormalize the whole lot. If you are aware that customer-related data is stored in 5-10 tables, then creating datamarts first is not a waste of time. (We do too)

The only universe I worked out DIRECTLY on the Edwards tables is the one on the warehouse, cause they need real-time reporting. For other departments we use datamarts that are refreshed hourly.

But I’m still in the proces of finding out a lot anyway.

The following query has been helpful in addressing the catalog tables to search for tablecolumns :


SELECT TDOBNM AS TableName, TDOBND as Field, FROWDI as Description, FRDTAS as Fieldlength, FRDTAT AS Type, TDPSEQ AS Seq FROM COPD810.F98711, DD810.F9210
WHERE TDOBNM = 'F0301'
AND   TDOBND = FRDTAI
ORDER BY TDPSEQ

This is an example on the customermaster F0301…

blom0344 :netherlands: (BOB member since 2002-09-04)

3

Thanks for the update Blom !!

To help me further could be expand your thoughts on the contexts that you mentioned so as to make sure i understand it clearly and do better universe design

Do you mean to say that all the transaction tables joins will exist in all the context except that the context would differ for each master table…the no. of master tables the no. of contexts?

Correct me if i am wrong

thanks
Sudhir

goodmate (BOB member since 2004-07-12)

4

Well, I am actually referring to the fan and chasm traps as described in the designer manuals. Consider an innocent user running a report on order data (F4211-F4201) while at the same time fetching stock information from F41021 and item data from Branch Item master F4102 This will result in a many-to-one-to-many path, cause combination of item/branch is non-unique in the data-tables.

By using contexts you can force BO to split the query into 2 subqueries and get the proper data returned in 2 cubes:

  1. Order data
  2. Stock data

The 2 cubes are merged within reporter as one dataprovider.

Secondly, there may be users trying to report data that is inherently incompatable, like fetching both 'balance’data (like stock) and ‘transaction’ data (like warehouse suggestions , F4611)

My suggestion would be to create universes on JDE based on the main processes, creating manageble structures. More complexity can always be build (to a certain level ) with multiple linked dataproviders.

blom0344 :netherlands: (BOB member since 2002-09-04)

5

Hi Blom

If you have any date model of the JD Edwards tables could you share?
That will be of real help to me

thanks
Sudhir

goodmate (BOB member since 2004-07-12)

6

Not sure what you mean with ‘date model’ .
I use a special calenderfile to convert the typical JD Edwards date format to a normal date. Or is this not what you mean?

blom0344 :netherlands: (BOB member since 2002-09-04)

7

Sorry …i mean the Data model of the JD Edwards tables

Thanks
Sudhir

goodmate (BOB member since 2004-07-12)

8

Sudhir,

I do not have a complete ER diagram of the JD Edwards tables. No doubt you could create one with reverse-engineering with a tool like ERwin.

However, you do not need a complete diagram to create your universes.
Check out the primary keys of the tables you want to use and categorize the tables into:

  1. Facttables (sometimes combination of header-detail)
  2. Mastertables (like itemmaster; addressbook)

Distinguish the type of facttables:

  1. Snapshot (like stocklevels; open orders)
  2. History (like orderhistory; invoices)
  3. Transactions (like warehouse suggestions;item ledger)

A universe should be typically build around a (set) (of) facttable(s) with all the logical mastertables joined to the foreign keys.

In case of JD Edwards the datamodel is quite ‘flat’; basically all relevant fields for reporting can be found in the detailtables.

Example: AN8 (addressbook number) can be found in both F4211 AND F4201. A truly normalized model would only store AN8 in the header (F4201)

To keep matters simple corresponding fields are usually coded the same way. The first 2 positions in the fieldname are a reference to the table.

Example: F0101.ABAN8 and F4201.SHAN8 share common ‘AN8’ in the code, and ‘AB’ stands for addressbook ; ‘SH’ for salesheader.

blom0344 :netherlands: (BOB member since 2002-09-04)

9

If you send me an email. I will send you on the data models that show the joins between the JDE tables. Please indicate which release of JDE you are using as there have been chanegs to the tables layouts in recent release. (james_halligan@srl.ie).

regards

James

coupland :ireland: (BOB member since 2005-03-08)

10

Thank you blom0344 and James

Blom, as you said the same fields could you find in many tables like the Address Number example you gave.Hence while creating classess the fields should be either picked from master table or detail table?

As the same field exist in multiple tables

thanks
Sudhir

goodmate (BOB member since 2004-07-12)

11

just a thought… but have you considered creating custom Business Views in JDE. there are size limitations on JDE business views, but you could created a logical join table within JDE or use subreports in Crystal to accomplish your report needs. this way your grouping tables on the database (AS400 et al) and just using the universe to bring the tables over for reporting.

Tanked :us: (BOB member since 2005-12-13)

12

Tanked – this is a one-year-old topic, so the original poster may not even be looking at it any more. Also, it was a Designer question, not a Crystal Reports question.

Finally – subreports in Crystal are very inefficient, and should be considered a last resort (according to the instructor who taught us intro to Crystal), so your suggestion may not have been truly viable for this problem. If you have 1,500 possible iterations of “sub-report” – you actually run an additional 1,500 queries!

Anita Craig :us: (BOB member since 2002-06-17)

13

yes Subreports are less efficient. but my point was that making the joins in JDE are far more efficient than making then in a universe. designer issue.

Tanked :us: (BOB member since 2005-12-13)