Transcript Data Extraction at Line-Item Level in Financial Accounting

Data Extraction (Line Item Level) FI/CO
(Financial Accounting and Controlling)
Revised 06/05/03
Paula Gist (CPA), BW RIG
Lothar Schubert, BW RIG
October 3rd, 2002
Content
 Business Drivers
 FI Extraction
 FI line-item extraction
 Timestamp mechanism
 Customer extensions
 Global Settings
 Availability and further information
 CO-OM Extraction
 CO-OM line-item extraction
 Delta mechanism (Revised 06/05/03)
 Customer extensions (Revised 06/05/03)
 Availability and further information
 SAP Labs America 2002, FI / CO Line Item Extraction 2
Business drivers
 Flexibility:

Need for project specific choice of fields.

Check on status fields, like open / cleared items.
 Drill-down and drill-through (analysis):

Drill down capability from summarized data to line-item details.

Full through capability, e.g. from Accounts Payable to G/L accounts
information.
 Actuality:

Access to recent (or even real-time) data.
 Scalability:

High number / wide variety of users.

Large volumes of detailed R/3 transaction data are transferred to
BW  Daily full loads are inefficient.
In other words: Detailed data and delta capability
 SAP Labs America 2002, FI / CO Line Item Extraction 3
FI line-item extraction: Data Sources and Data Targets
BW
G/L accounts
Accts. receivable
Accts. payable
InfoCubes -> summarized data
analytical queries on cube data
0FIAR_C03
FIAR: Line item
0FIAP_C03
FIAP: Line item
0FI_AR_O03
FIAR: Line item
0FI_AP_O03
FIAP: Line item
Drilldown
ODS -> document details
operational queries on line-item data
InfoSources
R/3
DataSources
R/3 Tables
0FI_GL_O02
FIGL: Line item
0FI_GL_4
0FI_AR_4
0FI_AP_4
FIGL: Line item
FIAR: Line item
FIAP: Line item
Coupled extraction
Coupled extraction
BKPF
Header
BSEG
Segment
 SAP Labs America 2002, FI / CO Line Item Extraction 4
BSAD
Cleared Items
BSID
Open Items
BSAK
Cleared Items
BSIK
Open Items
FI line-item extraction: Features
0FI_GL_4 (G/L Accounts: line items)
No redundant fields are transferred into BW:
 Only fields from the FI document tables (BKPF/BSEG) that are
relevant to general ledger accounting (compare table BSIS),
 No customer or vendor related fields.
0FI_AP_4 (AP: line items) and 0FI_AR_4 (AR: line items)
Vendor / Customer related information (e.g. payment/dunning data).
“Coupled” extraction  consistent “snapshot” of FI data in BW:
 G/L account extraction determines selection criteria (comp.code,
fiscal period) and upper time limit of all extracted FI line-items.
 AP and AR extraction: no further selection criteria necessary /
possible.
 “Uncoupled” extraction possible with PlugIn PI 2002.2, see OSS
note 551044.
Actuality of extracted data
 The most recent FI documents, which are extracted to BW, are
posted in R/3 the day before.
 See OSS note 485958 if you need to circumvent this restriction.
 SAP Labs America 2002, FI / CO Line Item Extraction 5
FI line-item extraction: Delta mechanism
0FI_GL_4, 0FI_AP_4, 0FI_AR_4 use an After Image Delta
Delta type “Extractor“:
 Delta records are directly selected from the R/3 tables using a
timestamp mechanism.
 Delta records are directly transferred to BW. No record is written to
the BW delta queue.
After Image Delta:
 FI line items are transferred from the source system in their final
state (= “After Image“).
 This delta method is not suitable for direct InfoCube update.
 ODS object is obligatory to determine the delta for InfoCube update.
Where BW Delta Queue is used:
 Delta queue and BW scheduler ensure correct serialization of the
records (e.g. inserts must not pass changes)
 Distribution of delta records to multiple BW systems.
 Selection criteria of Delta-Init upload are used to “couple” the
datasources logically.
 SAP Labs America 2002, FI / CO Line Item Extraction 6
Timestamp Mechanism: New documents
New FI documents
 Posted in R/3 since the last line-item extraction.
 Selection based on the field BKPF-CPUDT.
Table BKPF
(FI document header)
MANDT
BUKRS
Table BWOM2_TIMEST
BELNR
FI document key
(Timestamp table)
GJAHR
:
Upper limit:
:
new documents
previous day
CPUDT
CPUTM
BWOM2_TIMEST
AEDAT
Lower limit:
:
changed documents upper limit of the
last G/L line-item
:
extraction
 SAP Labs America 2002, FI / CO Line Item Extraction 7
Timestamp Mechanism: Entries in BWOM2_TIMEST
Table BWOM2_TIMEST (Timestamp table)
OLTPSOURCE
0FI_GL_4
0FI_GL_4
0FI_GL_4
0FI_GL_4
0FI_GL_4
0FI_AP_4
0FI_AP_4
0FI_AP_4
0FI_AR_4
0FI_AR_4
0FI_AR_4
AEDAT/AETIM
16.05.2000/20:15
24.05.2000/16:59
02.06.2000/21:45
15.06.2000/12:34
21.06.2000/18:12
18.05.2000/21:23
30.05.2000/12:48
10.06.2000/13:19
17.05.2000/18:45
04.06.2000/13:32
16.06.2000/15:41
UPD
Init
Delta
Delta
Delta
Delta
Init
Delta
Delta
Init
Delta
Delta
DATE_LOW
01.01.1990
16.05.2000
24.06.2000
02.06.2000
15.06.2000
01.01.1990
16.05.2000
24.06.2000
01.01.1990
16.05.2000
02.06.2000
DATE_HIGH
15.05.2000
23.05.2000
01.06.2000
14.06.2000
20.06.2000
15.05.2000
23.05.2000
01.06.2000
15.05.2000
01.06.2000
14.06.2000
 Timestamps are stored as Seconds since 01.01.1990”.
 Other DataSources (e.g. CO line-item extraction)
also use this table.
 Documentation of upload history: Recording of FI data
that has already been extracted into BW.
 Well-defined sync-points, if the delta upload has failed.
 SAP Labs America 2002, FI / CO Line Item Extraction 8
LAST_TS
X
X
X
Timestamp Mechanism: Changed documents
Changed FI documents
 Changed in R/3 since the last line-item extraction
 Cannot be selected based on field BKPF-AEDAT:
 BKPF-AEDAT is not updated by all BW relevant changes of FI
 Example: Clearing of an open invoice (by incoming payment) changes
the document state, but does not update BKPF-AEDAT.
Solution
 Recording of all changed FI documents (FI document key, date
of last change) in table BWFI_AEDAT.
MANDT
600
600
BUKRS
1000
1000
BELNR
1900000010
1000000016
GJAHR AEDAT
2001
21.02.2001
2001
21.02.2001
AETIM
10:23:17
10:28:34
 FI update programs in R/3 had to be changed.
 Selection of the changed FI documents based on tables
BWFI_AEDAT and (timestamp table) BWOM2_TIMEST.
 SAP Labs America 2002, FI / CO Line Item Extraction 9
Timestamp mechanism: Recording of changed docs
Simplified flow chart
function FI_DOCUMENT_CHANGE
...
update BSEG.
R/3 transactions that record
changed FI documents in
table BWFI_AEDAT:
endfunction.
•
•
•
•
Business Transaction Events (Tab. TPS31)
Process
Function
00005011 BWFIP_WRITE_AEDAT_POINTER
This entry in table TPS31
starts the recording of
changed FI documents.
call function OPEN_FI_PERFORM_00005011_P
...
function BWFIP_WRITE_AEDAT_POINTER
...
move-corresponding BKPF to I_T_BKPF.
call function BWFIP_INSERT_BWFI_AEDAT
tables I_T_BPKF.
endfunction.
 SAP Labs America 2002, FI / CO Line Item Extraction 10
Change document
Incoming/outgoing payments
Dunning
...
• It should be inserted before
the delta-init upload.
• See OSS note 401646
Customer Extensions
Extension of extract structures using customer includes
Data source R/3 table
Extract struct. Customer include
0FI_GL_4
0FI_AP_4
0FI_AR_4
DTFIGL_4
DTFIAP_3
DTFIAR_3
BSEG
BSIK/BSAK
BSID/BSAD
CI_BSIS
CI_BSIK
CI_BSID
Case 1: All fields of the customer include are part of the R/3 table.
 No additional ABAP-coding is required. The fields of the customer
include is automatically filled by the extractor.
Case 2: Some fields of the customer include are not part of the R/3 table.
 Use Business Transaction Event 00005021.
 Build a function module that fills the fields of the customer include.
Use the following template: SAMPLE_PROCESS_00005021.
 No modification of SAP standard extractors required.
 For more information: See OSS note 410799
Clean separation of SAP standard and customer extraction
 SAP Labs America 2002, FI / CO Line Item Extraction 11
Global Settings (BWOM_SETTINGS)
 BWFILOWLIM

Here, you can set the lower time limit of the time stamp selection for data requests in update mode initialization
of the delta method. Together with the selection criteria, this parameter is a decisive factor for the data volume
with data requests in update mode initialization of the delta method.
 BWFISAFETY


Here, you can the upper time limit of the time stamp selection for data requests in all update modes. Value 1 in
this parameter means that the upper time limit of the time stamp selection is the previous day. With larger
parameter values, the upper limit of the time stamp selection is correspondingly further (in days) in the past.
Note that you cannot choose a parameter value cannot be smaller than 1, as this would prevent the delta
dataset being found completely.
 ORGSYSONLY

If you SAP R/3 system is part of an ALE scenario, set this parameter to X. This ensures that only those line
items that were originally posted to the BW system will be transferred there from the SAP R/3 system. This
prevents line items being transferred several times from various SAP R/3 system.
 DELTIMEST

With this parameter, you can set the retention period of entries that are no longer required in time stamp table
BWOM2_TIMEST. The default settings for deletion of entries that are no longer required in the time stamp table
is 60 days. The minimum retention period for entries in the time stamp table is 15 days. When entries are
deleted from table BWOM2_TIMEST, the corresponding entries for the changed FI line items are deleted
simultaneously from log table BWFI_AEDAT.
 SAP Labs America 2002, FI / CO Line Item Extraction 12
Further information and availability
System requirements
 R/3 source system:


Release 4.0B++
PlugIn 2001.1++
 BW:


Release 3.0A++
(Release 2.0b / 2.1c: See OSS note 401646)
SAPNet (OSS) notes
 Implementation of new DataSources:
401646
 Extension of extract structures:
410799 / 430303
 Extraction of recent updates:
485958
 Migration from 0FI_AP_3 / 0FI_AR_3:
410797 / 200576
 “Coupled” extraction:
428571
 “Uncoupled” extraction:
551044
Online documentation
 Check documentation on business content, BW 3.0b.
 SAP Labs America 2002, FI / CO Line Item Extraction 13
CO-OM line-item extraction
BW
InfoCubes ->
Cost Center
0CCA_C11
Internal Order Project
0WBS_C11
0OPA_C11
Bus. Process
0ABC_C06
summarized data
analytical queries
ODS (optional) ->
document details
operational queries
on line-item data
InfoSources
DataSources
0CO_OM_CCA_9 0CO_OM_OPA_6
R/3 Tables
R/3
0CO_OM_WBS_6
COBK
COEP
CO documents
 SAP Labs America 2002, FI / CO Line Item Extraction 14
0CO_OM_ABC_7
CO-OM Delta mechanism
0CO_OM_CCA_9, 0CO_OM_OPA_6, etc. use an Additive Delta
Delta type “Extractor“:
 Delta records are directly selected from the R/3 tables using a
timestamp mechanism (Timestamp table BWOM2_TIMEST).
 Field *COEP-TIMESTMP is used as a reliable timestamp for selecting
new CO-OM line-items.

* Reference Field name revised 06/05/03
 Delta records are directly transferred to BW. No record is written to
the BW delta queue.
Additive Delta :
 Only new CO-OM line-items are transferred from the source system
(=“Additive Delta“). No changes on CO documents are relevant for BW.
 This delta method is suitable for direct InfoCube update.
 ODS object is optional to store document details (for operational
queries on line-item data).
Where BW Delta Queue is used:
 Delta queue and BW scheduler ensure correct serialization of the
records (e.g. inserts must not pass changes)
 Distribution of delta records to multiple BW systems.
 SAP Labs America 2002, FI / CO Line Item Extraction 15
CO-OM Customer extensions
Extension of extract structures
* If there is a requirement to extract new fields into BW, use transaction
RSA6. The extract structure must be increased with the new fields
required to be extracted. The new data will be written automatically with
the extractor ,but please note, this will not automatically be sent to BW if
the fields are not in the extract structure.
* The includes CI_COBL e.g. for 0CO_OM_CCA_9 do not have anything to do
with BW but it is normal CO include where the customer can fill in some
new fields he needs in CO.
Data source
0CO_OM_CCA_9
0CO_OM_OPA_6
0CO_OM_WBS_6
0CO_OM_ABC_7
R/3 tables
COBK/COEP
COBK/COEP
COBK/COEP
COBK/COEP
Extract struct.
ICCTRCSTA1
ICORDCSTA1
ICWBSCSTA1
ICBPRCSTA1
Customer include
CI_COBL
CI_COBL
CI_COBL
CI_COBL
* Please review OSS Note 394491 – Handling Errors from DataSource Append
Structures (released 05/14/03) for further details
*Revised for content correction 06/05/03
 SAP Labs America 2002, FI / CO Line Item Extraction 16
CO-OM Further information and availability
System requirements
 R/3 source system:

Release 4.0B or higher
 BW:

Release 2.0B or higher
SAPNet (OSS) notes
 Technical background info:
416265
 Performance optimization:
382329, 387964
Online documentation
 Check documentation on business content, BW 3.0b.
 SAP Labs America 2002, FI / CO Line Item Extraction 17