photo credit: HikingArtist.com

Have you ever wondered why the only way you can read SAP data is through SAP connection plug-ins for your ETL tool-of-choice?  Why can you not simply read SAP tables as is from the source database?  Why is everything data transfer-wise just a little bit harder with SAP?

Well, for years I just assumed that SAP (being a proprietary system) liked a tight grip on its data systems.  But as I was looking into a situation for a client, I learned the truth was a bit more complicated.

It all started when I wanted to join the transaction header table (BKPF) with the transaction detail table (BSEG).  Couldn’t this be done at the SAP source, instead of in the data warehouse staging area?  If this was a straight-up relational database, joining these tables would be a no-brainer.

Now anyone familiar with SAP knows about the cryptic table names and columns, and probably has at least a passing awareness of the ABAP/BAPI/IDOC programming languages that read SAP data.  So this alphabet soup is where we start.  But the cryptic names aren’t the half of it.

The first thing you need to understand is that SAP is not what you would consider to be a garden-variety relational database.  SAP actually maintains three different types of tables, 2 of which are unreadable outside of SAP’s programming environment.  One of these table types is called a cluster table.  It is a control table of sorts that reads from many different physical tables.  BSEG is just such a table, and is therefore not readable by straight SQL commands.  The second table type is called a pooled table, which is used to store program parameters.  The third and final table type is called transparent, and this is what most SQL programmers would call “normal”.  It is physically and logically the same table, and can therefore theoretically be read by standard SQL commands (although virtually all SAP interfacing is done through SAP programming in any case).

Knowing this helps SAP’s world make a little more sense.