Often at go-live, reports are written that appear to work very well, and then a year or two later, these reports run very slowly. In extreme cases, your online (foreground) report takes more time to execute than your system’s "time-out" tolerance allows. Therefore, you can run it only in the background. The main reason for this annoying phenomenon is that the developer and analyst did not take into account the impact of growing data volumes in the initial design.

In some parts of R/3, the workarounds to improve performance can be complex. However, this is not usually true in the FI module. From my experience with slow-performing FI reports, the cause more often than not turns out to be a misunderstanding about the kinds of tables that FI debit and credit data is hiding in.

Yes, it’s true that one confusion point can be simply knowing the names of the data tables and which kind of data each one stores. The more confusing point, however, comes from the differences between transparent tables, cluster tables, and pooled tables. The aim of this article is to explain in simple language where your different kinds of FI data are stored, and why it’s important to your report’s performance to know if the table is transparent or clustered or pooled. This knowledge will allow you to optimize your data selection for reports by choosing transparent tables. You’ll also see why you should avoid the common programming error of trying to get data out of the database table BSEG for a report. First, I’ll describe the types of tables.

Transparent Tables

A transparent table is a data table in which the definition of the table in the SAP data dictionary is identical to the definition of the table in the underlying database system (e.g., an Oracle database). To improve reporting perform- ance, you can create indexes in the database system to speed up data selection.¹

Clustered Tables

In the case of clustered tables, R/3 tables share enough of the same key fields that different tables can be clustered in the same database table. The database system does not "see" the same table names like the SAP data dictionary does, and therefore cannot on its own, access the individual fields of the tables. You cannot create indexes to speed up table access. Any kinds of data filters you might want to put in your report (e.g., "Select data records only where the G/L account number equals ‘100020’") need to be processed within R/3, not in the database system. As this requires data to be passed back and forth between R/3 and the underlying database software, your report’s processing time becomes much greater than if the filter instruction had been processed by the database software alone.

Pooled Tables

A pooled table is somewhat similar to a cluster table. In this case, many tables are grouped together in a single database table. The database system does not "see" the same table as the SAP data dictionary, and therefore cannot access individual fields of the tables. You cannot create indexes to speed up table access. As with the cluster table, any kinds of data filters you might want to put in your report need to be processed within R/3, not in the database system.

Pooled tables were historically used for all of the T* configuration tables. Many of these have been converted to transparent tables by SAP, e.g., T001, the company code table. Generally, pooled tables do not cause performance problems, as the data volumes are low.

Your Best Bet for Speed: The Transparent Table

Transparent tables have many advantages over clustered and pooled tables because the database system can read the data directly and use indexes for faster data selection. Therefore, why does SAP bother to have anything other than transparent tables? The answer is that both clustered and pooled tables were created by SAP to get past inad-equacies in older database systems used by R/2. With modern relational database systems, the use of pooled and clustered tables is minimized in R/3. Minimized, but not completely eliminated. An example of a transparent table is BKBF, the place where R/3 stores header data about your FI documents, such as posting date and exchange rate. Unlike the main line items (i.e., debits and credits) table of the R/3 G/L (i.e., BSEG), the header table BKPF can be very useful for getting data. Not only is it a transparent table, but it also comes delivered with several database indexes (see Figure 1). Here is one quick example of how the BKPF table works.

Suppose you had a report requirement to find an FI document that matches an operational document, such as, "Find the FI doc for MM document 5400045123 year 2000." The BKPF table is a good choice for such a query. You can retrieve the data using the fields BKPF-AWTYP and BKPF-AMKEY. The AWTYP field is a code relating to the source document (such as MKPF to indicate that the source transaction is an MM document). The AWKEY field is a combination of source document number and source year.

Figure 1

The BKPF table with several ready-to-use index options

Why BSEG Is a Slow Source for Report Data

As I mentioned earlier, you should almost never try to directly get data out of table BSEG and into a report, even though it may appear to be a logical source of FI line item data. This is because it is a cluster table. (See Figure 2.)

BSEG is the most important database table to know about in FI, as it is the line item detail table for FI documents. The data in the database software that your R/3 system is linked to (e.g., an Oracle-brand database) cannot see all the fields in R/3 that you can see. As a result, if a user selects data from BSEG based on a field such as "G/L account," the response time is very slow, because the database has to send the data to R/3, which then unpacks and checks all the data.

When an FI document is posted either automatically (e.g., in response to a goods receipt from a purchase order) or manually (e.g., FI journal entry), the data can be stored in many different tables in R/3, depending on the specifics of the posting. However, the following two tables are always updated, as they perform the "ledger of record" role in storing all of the G/L-relevant data entered for all accounting documents.

Figure 2

BSEG table — technically classified as a cluster table

Index Tables

To overcome this problem, R/3 comes with "index" tables – BSIS, BSAS, BSID, BSAD, BSIK, and BSAK – that duplicate the data in BSEG, together with header data from BKPF, in transparent tables. They should not be confused with "database indexes," such as the six predefined indexes on table BKPF. The reason they are called index tables is that they could be implemented as indexes, rather than as tables, if BSEG were a transparent table.

The database software can fully read the table entries in exactly the same manner as R/3 shows them to you. This allows data to be filtered with far fewer software-to-computer chip instructions. It also allows database indexes to be created and used on these tables to further speed up selection time.

To enable a user to quickly find information, such as the open items for customer 13577, R/3 uses the index tables. For example, the standard reports you access via transaction codes FBL1, FBL3, and FBL5 use these index tables. They do not use the BSEG table. Note that the BSAS, BSAD, and BSAK tables only hold debits and credits that you or the system have flagged with the status of "cleared," as opposed to not-yet-paid or not-yet- completed (i.e., status "open"). The BSIS, BSID and BSIK tables hold the open items.

Use FI-module index tables when you know the company code and account/ customer/vendor number, and you want line-item detail of records. These are the first two key fields of the tables and they return results very quickly.

The index tables contain only the most frequently used data for the line items, not all the data. Some field values in the BSEG table for a given posting are not stored in the index table. Here’s a programming tip if you need one or two data values from BSEG in a report about a given debit or credit: Find the data from the index table first, and use that to inform the program about the FI document number that’s involved. At that point, you can ask your program to read table BSEG directly, because you now know the full company code, document number, year, and line item. A fully qualified read of the BSEG table, like this, is quick and efficient.

Summary Tables for FI-G/L, A/P and A/R Modules

Summary tables are another example of transparent tables. They are used by R/3 to quickly identify the balance in a given account number for a given fiscal period. For example: "Show the total balance in G/L account number 141065 for period 02/2002." When only balances are needed in your report, getting data from the summary table is much quicker than adding up individual debits and credits selected from one of the index tables. The R/3 standard balance sheet report RFBIBL00 and standard G/L report RFSSLD00 use these tables.

Use summary tables when you know the company code, fiscal year, and account/customer/vendor number, and you want monthly balances only. These tables return results very quickly. Rohana Gunawardena is a senior consultant with Exium, Inc., a specialist SAP consulting firm.

Rohana Gunawardena

Rohana Gunawardena heads the SAP practice division at Exium Inc. Exium is a leading business and technology consulting firm that enables companies to achieve their strategic business goals. Exium specializes in delivering superior IT solutions using ERP systems, with a special focus on SAP products. Rohana has been working with SAP since 1992. During his career he has assisted multiple clients on detailed system correction projects, such as correcting inventory balances, controlling area reorganizations, retrospectively activating group currency, and optimizing inter-company accounting transactions. He has spoken at many SAP conferences and has published more than 20 articles in Financials Expert, SCM Expert, and SAPtips on various aspects of SAP. His presentations have focused on Financials module selection, the order-to-cash process, global rollouts, business segment reporting, cross-module integration, and the financial impact of SCM transactions. Rohana is widely acknowledged as a leading SAP expert. Rohana is a Fellow of the Institute of Chartered Accountants in England & Wales. Previously Rohana has worked with the consulting practices of Accenture, Deloitte, and PwC.

Rohana will be presenting at the upcoming SAPinsider Financials 2018 conference October 16-18 in Prague. For information on the event, click here.

You may contact the author at Rohana@Exium.com .

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.