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

About Fact Table ETL Process for Detail-Level Information for SAP R/3

The Siebel General Ledger Analytics application uses the Group ID when data is posted at a detail level. The Group ID identifies one set of offsetting debits and credits. Each time the debits and credits are offset, the Group ID is reset.

In relation to Siebel General Ledger Analytics, the Group ID changes each time the debits and credits offset each other. Although the sales order number is the same, the Group ID changes. In this way, the Group ID relates a single record on the sales order side to multiple records in Siebel General Ledger Analytics. From the sales order perspective, the Group ID changes when the line item changes.

Similar to loading header-level data, the Siebel General Ledger Analytics application also loads detail-level data from the SAP R/3 tables BKPF and BSEG into six staging tables, then into their IA tables. However, the Siebel General Ledger Analytics application uses staging tables for detail-level data to determine three data streams—invoice, sales order, and others.

When loading data at the detail level, Siebel General Ledger Analytics first extracts and loads transactional data from the SAP R/3 tables BKPF and BSEG into the same three staging tables that it does for loading header level information—TS_STAGE_GL, TS_STAGE_SO_HDR, and TS_STAGE_IV_HDR. Before loading data into TS_STAGE_GL, the Group ID is generated by checking the debit and credit amounts.

In this next phase of loading detail-level information, the Siebel General Ledger Analytics application selects all invoice lines from IA_SALES_IVCLNS that exist in TS_STAGE_IV_HDR, and generates Group IDs for each combination of invoice and line items. A unique Group ID is created when the invoice line changes. The data is then loaded into the staging table TS_STAGE_GL_ILN.

For invoice lines data, sales invoice lines data is extracted from IA_SALES_INVLNS that exists in the TS_STAGE_IV_HDR table. The Group ID is selected from IA_SALES_HIST, and the data is then loaded into the staging table TS_STAGE_GL_SLN.

When creating header tables, the tables TS_STAGE_GL, TS_STAGE_IV_HDR, and TS_STAGE_GL_ILN join to get the keys, split the transactions, and set the Balance ID. In addition, a lookup is performed on the TS_STAGE_FIN_STMT staging table. The data then loads into the six staging areas, which are AP Transactions, AR Transactions, Tax Transactions, General Ledger Revenue, General Ledger Cost of Goods Sold, and General Ledger Others.

Similar to the Invoice Lines data, when creating header tables, the subitems of the sales order lines are created. The tables (TS_STAGE_GL, TS_STAGE_SO_HDR, and TS_STAGE_GL_SLN) join to retrieve the keys, split the transactions, and set the Balance ID. In addition, a lookup is performed on the TS_STAGE_FIN_STMT staging table. The data then loads into the six staging areas.