I don't know how many of you are jazz fans. If you are, you know John Coltrane and his uncanny ability to explore the heights and depths of emotion and thought through his tenor sax.

I love jazz because of its roots in the blues, its structure, and its flights of free form improvisation. At its best, it unites intense feeling with cool intellect and rationality.

John Coltrane — Trane as he was also known — was a master of improvisation. As a composer he understood the necessity for structure and discipline, which are the foundations of creativity and provide the framework for improvisation. It's like poetic license ... you have to know the rules before you can break them to achieve a particular result or effect.

Lately, one of my favorite Trane tunes has been spinning around my brain. Aptly titled My Favorite Things, it was originally performed by Mary Martin in Rogers and Hammerstein's Broadway hit The Sound of Music and by Julie Andrews in the movie adaptation.

The lyrics are beautiful and by themselves can carry the tune, which has become a jazz standard. If you're interested, you can find the lyrics here.

Trane performs the number without a vocalist but even though the words are absent, you don't miss them ... you can hear them recited through his sax. And what a sublime, poetic sax he plays on that number ...

So I was meditating on one of Trane's riffs from My Favorite Things when it came to me that the tables, structures, and data elements that define and give form to SAP — the data dictionary — are like a complex, multi-faceted jazz composition performed by a highly disciplined and creative combo.

The data dictionary provides the underlying structure to the entire system and is itself subject to the rules written into its own tables. The first thing that anybody ever told me about SAP on my first project was: "Everything is in a table, man! Everything!"

The Heart and Soul of SAP

Indeed everything in SAP is stored in a table: everything from business data to program code to the metadata that describes and defines the tables, structures, views, data elements, domains and so on of the data dictionary itself. It would not be an exageration to say that the data dictionary is the heart and soul of SAP. Get a good handle on the data dictionary and you have a good handle on SAP itself.

So I thought I'd like to write about some of my own favorite things from the data dictionary, tables that I work with often that help me understand the system and advance my work as an EDI developer.

So to paraphrase Trane's poetic sax, these are a few of my favorite things from the data dictionary, although this list is far from comprehensive.

Metadata, according to its simplest definition, is data about data. It describes the structure and context of data. In the SAP data dictionary, metadata for the fundamental objects used to store, structure, and process data is recorded in the DD0* series of tables. These tables are a useful starting point for understanding the data dictionary itself. A few of my favorite things from this group of tables include:

• DD01L, DD01T: All the domains defined in the system. The T table includes text descriptions.
• DD04L, DD04T: All data elements in SAP with their parameters, including their associated domain.
• DD02L, DD02T: Header level data about tables and structures defined in SAP. This includes IDoc segments. In version 4.7, DD02L has over 180,00 entries, which gives you a pretty good picture of just how big and complex SAP is.
• DD03L, DD03T: Structural definition of SAP tables and structures, inlcuding fieldnames and position.

In fact, there are 129 DD* tables and each one plays a key role in defining the data dictionary itself. A whole book could be written about these metadata tables. But they're fun to explore and can teach you a lot about how SAP is organized. But don't run any of these tables flat out, without selection parameters. Search for objects that you know and see what data is stored about them.

An IDoc's A Record, Therefore It Exists 

Another of my favorite things from the data dictionary is the IDoc database. It's not a standalone database but three tables that store and structure IDocs in SAP:

• EDIDC: The control record, matching the IDoc with its partner profile and providing the latest processing status. It is linked to the other tables through the IDoc number.
• EDID4: All of an IDoc's data records and a block of control fields including the IDoc number, segment name, sequence, and hierarchical relationships. Data is stored in a 1,000 byte unformatted SDATA field.
• EDIDS: The complete processing history of an IDoc from the moment it was written to the IDoc datbase until its current or end-point processing status, including message information.

As far as SAP is concerned, IDocs don't even exist until they are written to the IDoc database. An IDoc file from an EDI system is not a file full of IDocs. It's an ASCII file with messages that are structured like IDocs. They only become IDocs are they're written to the IDoc database.

Know these tables well and you'll have a good handle on IDoc organization and structure. And you'll always know where to go to find an IDoc.

Configuring IDocs  

There are some IDoc configuration tables that are among some of my favorite things from the data dictionary. These tables support IDoc processing and are a window on configuration. I am especially drawn to:

• EDPAR: External to internal partner number conversion. Maintained with transaction VOE4. Especially critical for order, delivery, and invoice processing. Good knowledge of EDPAR can save a lot of custom coding during IDoc processing. Hint: you need to understand how the relevant IDoc processing function modules read this table at run-time.
• KNMT: Customer-material info record. Maps internal SAP material number to customer's material number or UPC. Maintained with transaction VD51. Very useful in the order to cash processing cycle. Like EDPAR, it's important to know how the IDoc function processes material conversion at run-time.
• EDSDC: Maps SAP EDI partner to vendor ID, Sales Organization, and Order Type to be created. Can replace population of the E1EDK14 segment in the inbound ORDERS IDoc.
• EDP13: Outbound Partner Profile technical parameters. Behind the Partner Profile outbound parameters in WE20.
• EDP12: Outbound Partner Profile Message Control. Used with output control configuration to determine generation of an IDoc for a business partner or organization and document. Message Control is only relevant for SD and MM.
• TEDE1: Used by output determination logic to identify the IDoc processing function linked to the IDoc message type and process code in the message control screen of the outbound partner profile.
• EDP21: Inbound partner profile parameters in WE20.

Tables Primed for Speed 

The last group of tables that I want to look at hold application data. This is what SAP is all about: the business runs on data and the information that you can get from it. Master and transactional data: there are so many tables to choose from, you could easily fill a book on only a fraction of them. But I'll restrict myself to a handful of transactional tables from SD that make my life a lot easier by simplifying my document searches: the sales document index.

As the name implies, these tables are optimized with powerful indices to speed searches of the vast quantities of data that quickly fill the sales document tables. SAP uses these tables in a number of standard reports that would be almost impossible to run if they didn't exist just because of the vast amounts of data they have to churn through. These tables are useful for reporting or for any other application use where you need to identify sales documents but you don't have the document number in advance. The sales document index includes: 

• VAKPA: Speedy identification of sales orders by sold-to or ship-to partner number and date. You can also search by partner function, sales organization, order type, customer PO number, and so on. The partner's address ID number is also returned.
• VAPMA: Order items by material number. Index includes sales organization, order type, sold-to partner, customer PO number and so. Also returns item number, plant, and address number.
• VLKPA: Identifies delivery documents by sold-to or ship-to partner number and date. The index also includes shipping point, delivery type, and sales organization.
• VLPMA: Delivery items by material, ship-to, and sold-to partners. Shipping point, delivery date and type, also included in the index.
• VRKPA: Identifies billing documents by payer partner number, sales organization, date, billing type, sold-to partner, and so on.
• VRPMA: Billing documents by material number, sales organization, date, billing type, payer, sold-to partner, and so on.

There are many more tables that I enjoy exploring during my daily ramblings through the IDoc interface and that could be considered among some of my favorite things. I could go on and on but I'm sure you could to. It's a useful exercise to think about the tables you work with everyday. SAP, and all of its applications, is truly defined by its tables. So this is one area where a little play, a little exploration, can lead to a rich reward in terms of understanding how the system works.