Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Siebel Financial Analytics > Extract, Transform, and Load for SAP R/3 >

Fact Table ETL Process for Header-Level Sales Data for SAP R/3

The Siebel General Ledger Analytics application extracts invoice and sales order header-level data from the SAP R/3 tables BKPF (Accounting Document Header table) and BSEG (Accounting Document Segment table) and loads them into six staging tables. The entire ETL data flow is comprised of four areas:

• Extracting Sales Order and Invoice Data
• Loading Invoice and Sales Order Header Tables
• Apportioning Amounts and Quantities
• Loading General Ledger Staging Table

Each of these areas is discussed in the following sections.

About Extracting Sales Order and Invoice Data

Before loading data at the header level for sales orders and invoice data, Siebel General Ledger Analytics extracts transactional data from the SAP R/3 tables BKPF and BSEG and loads it into three staging tables:

• Siebel General Ledger Analytics staging table (TS_STAGE_GL)
• Sales Order Header staging table (TS_STAGE_SO_HDR)
• Invoice Header staging table (TS_STAGE_IV_HDR)

By default, only those transactions from BKPF and BSEG that have a status of NULL or S are extracted. Null status implies that the transactions are already posted. The status S marks the record as a noted item.

About Loading Invoice and Sales Order Header Tables

In the load process, the Invoice Header and Sales Order Header tables are populated by different sources. The TS_STAGE_IV_HDR, IA_SALES_IVCLNS, and OD_SALES_IVCLNS tables load the Invoice Header table (TS_STAGE_GL_IHD). The TS_STAGE_IV_HDR supplies header records for invoice data. Using these headers, you can extract sales invoice line item data from IA_SALES_IVCLNS table and aggregate it to provide total amount (NET_DOC_AMT), total quantity (INVOICED_QTY), and total number of items (TOTAL_ITEMS) for each invoice. These aggregated amounts are stored in the TS_STAGE_GL_IHD header table.

The method in which Siebel General Ledger Analytics loads the Invoice Header table is similar to the loading of the Sales Order Header table. The TS_STAGE_SO_HDR, IA_SALES_ORDLNS, and IA_SALES_HIST tables load the Sales Order Header table (TS_STAGE_GL_SHD). The TS_STAGE_SO_HDR table supplies header records for sales order data. Using these headers, you can extract sales order line item data from IA_SALES_ORDLNS table and aggregate it to provide total amount (NET_DOC_AMT), total quantity (SALES_ORDER), and total number of items (TOTAL_ITEMS) for each sales order. These aggregated amounts are stored in the TS_STAGE_GL_SHD header table.

About Apportioning Amounts and Quantities

The Invoice and Sales Order Header staging tables record total amounts and quantities for each document number (invoice number or sales order number). However, you must break down these amounts into different segments, so that you can load the appropriate amounts into the applicable staging table. There are six finance-related staging tables involved with apportioning amounts and quantities:

• Accounts Payable Transaction staging table (TS_AP_XACTS)
• Accounts Receivable Transaction staging table (TS_AR_XACTS)
• Tax Transaction staging table (TS_TAX_XACTS)
• General Ledger Cost of Goods Sold staging table (TS_GL_COGS)
• General Ledger Revenue staging table (TS_GL_REVENUE)
• General Ledger Other staging table (TS_GL_OTHER)

The Siebel General Ledger Analytics application prepackages logic to apportion header-level data to load the appropriate amounts into the corresponding staging table. The following examples illustrate the concept of apportioning data, by looking at how Siebel General Ledger Analytics apportions header-level invoice data to derive the detail-level amounts.

As previously stated, the General Ledger Header staging table (TS_STAGE_GL_IHD) stores header-level invoice data, while the IA_SALES_IVCLNS table stores line item invoice data, including the line item amounts for each invoice document. These line item amounts are aggregated by invoice number and loaded into the appropriate header record in the TS_STAGE_GL_IHD staging table.

Segment ratios in the Siebel General Ledger Analytics application apportion the header-level amounts into separate amounts based on each of the following segments—Revenue, Tax, and Freight. This allows the total amount of each invoice to be separated into those same segments of Revenue, Tax and Freight.

The segment ratios in Siebel General Ledger Analytics are created using two tables—TS_STAGE_GL and TS_STAGE_GL_IHD. The TS_STAGE_GL table stores total amounts for each segment for each order, and the TS_STAGE_GL_IHD stores the total amount for each Invoice. Revenue ratio equals the revenue amount divided by the total invoiced amount. Tax ratio equals the tax amount divided by the total invoiced amount. Freight ratio equals the freight amount divided by total invoiced amount.

The segmentation can vary in many ways depending on your business requirements. There can be a different number of segments, different segment types, or you may use segments to apportion quantities instead of amounts. In addition to the invoice data, the same concept also applies to sales order data that is posted at the header level.

About Loading Siebel General Ledger Analytics Staging Tables

After the sales order and invoice data is apportioned, the data is loaded into the applicable staging table. Using a lookup performed in the TS_STAGE_FIN_STMT staging table on Company Code and General Ledger Account number, the Siebel General Ledger Analytics application determines the financial statement that each segment amount belongs to. The possible financial statements are Accounts Payable, Accounts Receivable, Tax, General Ledger Revenue, General Ledger Cost of Goods Sold, and General Ledger Others. Others are defined as any financial statements that do not belong to one of the other five defined categories.

When the lookup determines the type of financial statement item, it loads the data into the appropriate staging table.

NOTE:  The General Ledger Balance ID in the fact tables must be the same as the Key ID in the General Ledger Balance table. Keeping these two column values the same verifies that the same granularity, or incremental level, is maintained. If there is a disparity, the resulting balances may be skewed and misinterpreted. Also, note that Accounts Receivable and Accounts Payable have different Balance IDs due to their distinct granularity.