One stage stop to know all about BW Extractors-Part1
Types of Extractors:
Logistics refers to the process of getting a product or service to its desired location upon request which involves transportation, purchasing, warehousing etc.
Main areas in logistics are:
Sales and Distribution (SD) : application 11, 13, 08 (in LBWE T-code)
Materials Management (MM) : application 03, 02
Logistics Execution (LE) : application 12
Quality Management : application 05
Plant Maintenance (PM) : application 04, 17
Customer Service (CS) : application 18
Project System (PS) : application 20
SAP Retail : application 40,43,44,45
How the data extraction happens?
Extraction can be done using either Full update/delta update.
Full load: Incase of logistic application, Full/Initialization will extract the data from setup tables (contains only historical data).
So if you have decided to go for full load, wait a minute there is a road block
For full update the data will be taken from setup tables, so in order to capture the changes you need to fill setup tables every time ,which will be a laborious task.
So, it is always suggestible to go for delta loads which makes loading life easier.
Read the below note to get details on delta load-:
Initialization: Data will be fetched from application table to setup tables (In Lo extraction, the extractor won't allow the direct communication with the application tables) from here, data finally reaches the target (info cube/ODS).Remember this process is for onetime.
Pre-requisites: Prior to initialization make sure the following steps are completed:
Once a new transaction happens/an existing record is modified, upon saving it goes to the respective application table. From here depending on the update mode Direct/queued/Unserialized V3 update the data will be populated in delta queue (RSA7) and finally reaches to BW.
Pre-requisites: Prior to delta loads make sure the following steps are completed:
1.Define periodic V3 update jobs 2. Setting up the update mode (direct/queued/Un serialized v3 update)
LO- Delta Mode:
Info object 0Recordmode helps in identifying the delta
Check the field "delta "in ROOSOURCE /RODELTAM table
Incase of Lo extraction it is "ABR"
ABR: An after image shows the status after the change, a before image the status before the change with a negative sign and the reverse image also shows the negative sign next to the record while indicating it for deletion. This serializes the delta packets.This process supports an update in an ODS object as well as in an Info Cube.
Financial Accounting is broken down into the following sub-modules:
We can extract the financial data at totals level / line item level. Please Refer the link http://help.sap.com/saphelp_nw70/helpdata/en/af/16533bbb15b762e10000000a114084/frameset.htm
In general, we will use R/3 line item tables as the data source for extracting the data to allow drill down capability from summarized data to line-item details.
Financial Accounting data can be extracted directly from the tables.
Depending on the business requirement we can use either FI-SL or standard BW content extractors (FI-AR, FI-AP, and FI-GL) to fetch FI data.
Note: FI-SL will be discussed under "one stage stop to know all about BW Extractors -Part2 "Which explains about Application specific customer generated extractors
FI-AR, FI-AP, and FI-GL:
General Ledger: All accounting postings will be recorded in General Ledger. These postings are real time to provide up-to-date visibility of the financial accounts.
Account Receivable: Accounts Receivables record all account postings generated as a result of Customer sales activity. These postings are automatically updated in the General Ledger.
Accounts Payable: Accounts Payable records all account postings generated as a result of Vendor purchasing activity. Automatic postings are generated in the General Ledger as well.
Standard FI data sources:
0FI_GL_4 (G/L Accounts- line items)
Takes the data from the FI document tables (BKPF/BSEG) that are relevant to general ledger accounting (compare table BSIS).
0FI_AP_4 (AP-line items) and 0FI_AR_4 (AR- line items
Selections are made from tables BSID/BSAD (Accounts Receivable) and BSIK/BSAK (Accounts Payable)
With old G/L in R/3, the tables GLT0 (totals), BSEG, BKPF (Line Item) get filled in SAP BI side you should have to use data model.
0FI_GL_1 --> 0FI_GL_1 --> 0FIGL_C01 (for Totals)
0FI_GL_4 --> 0FI_GL_4 --> 0FIGL_O02 (for Line item)
With New G/L in SAP ECC 6.0 onwards, the tables FAGLFLEXT (totals), FAGLFLEXA, BSEG, BKPF (Line Item) get filled in BI side you have to use data model.
0FI_GL_10 --> 0FI_GL_10 --> 0FIGL_C10 (for Totals)
0FI_GL_14 --> 0FIGL_O14 (for Line item)
Functionally, this effects other financial modules like AP, AR, PCA (Profit Center Accounting)
for ex: while implementing new G/L in BI side, this fulfills most of profit center Accounting requirements, and you do not have to implement PCA module separately.
When I was in FI implementation, there was no 0FI_GL_14 datasource...
We had existing 0FI_GL_1 & 0FI_GL_4 flows and we implemented new GL totals flow i.e. 0FI_GL_10...
FAGLFLEXT --> 0FI_GL_10 --> 0FIGL_O10 --> 0FIGL_C10.... new GL Totals implementation was quite smooth, since this flow is completely different from old GL totals (GLT0 --> 0FI_GL_1 --> 0FIGL_C01).
We recreated existing (on 0FIGL_C01) queries on 0FIGL_C10 (&V10) and used jump targets (RRI) to old line item (0FIGL_O02) wherever required...
You can go ahead with new GL lineitems (FAGLFLEXA & BSEG & BKPF) --> 0FI_GL_14 --> 0FIGL_O14 in parallel with existing old one (BSEG & BKPF) --> 0FI_GL_4 --> 0FIGL_O02.
------------------------------------------------------------------------------------------------
0FI_GL_10 --> 0FI_GL_10 --> 0FIGL_C10 (for Totals)
0FI_GL_14 --> 0FIGL_O14 (for Line item)
Question1: The DSO 0FIGL_O14 does not feed the cube. Any particular reason for that?
So If we want line item reporting for best solution will be RRI? Correct!!
Already have GL totals available at 0FIGL_C10 and then no point in loading line items (O14) again to cube.
Yes, Best approach for line item reports is RRI i.e. from 0FIGL_C10 to O14.
Question2: SAP recommended dataflow for new GL uses virtual cube 0FIGL_V10. Why do we need it or what reporting needs it satisfies?
SAP delivered function module RS_BCT_FIGL_DATA_GET_VC10 pulls data from 0FIGL_C10 to load 0FIGL_V10... Logic for cumulative balances considering carry forward balances from previous year for Balance sheet & P/L reports, is maintained in that function module.
(So, make sure this FM got installed while u installed standard content for GL model
Do the following, to see weather FM got installed.
Go to change mode of virtual provider (ZFIGL_V10). click information icon (blue 'i')
click 'Type/ Namespace'.. Then click 'details' button
If there is no FM, we won't get any data to 0FIGL_V10 and related queries.
And also 0FIGL_V10 uses Financial Statement Version object (0GLACCEXT), which is not available in 0FIGL_C10.
For Balance sheet & P/L reports, 0GLACCEXT is key object, so 0FIGL_VC10 is must.
We should install & load master (attr, hier, text) data for 0GLACCEXT.
How the data extraction happens?
In FI extraction 0FI_AR_4 and 0FI_AP_4 are linked with 0FI_GL_4 in order to maintain consistent data transfer from OLTP system (it is called coupled data extraction, Ref OSS notes 428571).
Note: Uncoupled" extraction possible with Plug-In PI 2002.2, see OSS note 551044
0FI_GL_4 writes the entries into the time stamp table BWOM2_TIMEST in the SAP R/3 System with a new upper limit for the time stamp selection.
And now, 0FI_AP_4 and 0FI_AR_4 will copy this new upper limit for the time stamp selection during the next data extraction in the SAP R/3 System. This ensures the proper synchronization of accounts payable and accounts receivable accounting with respect to G/L accounting.
Full load: Not a valid choice because of large volumes of detailed R/3 transaction data.
Delta load:
Note: Here the delta identification process works differently for new financial records and for changed financial records.
New Financial accounting line items which are posted in SAP R/3 sytem will be identified by the extractor using the time stamp in the document header (Table BKPF-(field) CPUDT).
By scheduling an initialization IP all the historical data can be loaded into BW from the application tables and it also sets "X" indicator in field LAST_TS (Flag: 'X' = Last time stamp interval of the delta extraction).That means after the last delta, initialization was done.
After this, daily delta loads can be carried out depending on timestamp by scheduling delta info packages.
During the delta load , the SAP R/3 system logs two time stamps that delimit a selection interval for a Data Source in table BWOM2_TIMEST(fields TS_LOW and TS_HIGH).
In case of changed FI documents, selections will be based on tables:
BWFI_AEDAT and (timestamp table) BWOM2_TIMEST (See OSS note 401646 for more details).
Delta extraction using delta queue method can also be possible incase if we want,
A time stamp on the line items serves to identify the status of the delta. Time stamp intervals that have already been read are then stored in a time stamp table (BWOM2_TIMEST).
(Info object 0Recordmode plays vital role deciding delta's .Check the field "delta "in ROOSOURCE /RODELTAM table to identify the image)
The Financial Accounting line items are extracted from the SAP R/3 system in their most recent status (after-image delta method).
AIE: This delta method is not suitable for filling Info Cubes directly in the BW system. To start with therefore, the line items must be loaded in the BW system in an ODS object that identifies the changes made to individual characteristics and key figures within a delta data record. Other data destinations (Info Cubes) can be provided with data from this ODS object.
It uses delta type E(pull) means the delta data records are determined during the delta update by the data source extractor, updated to the delta queue and passed on to BI directly from there.
Application specific BW content extractors:
Lo Extraction:Logistics refers to the process of getting a product or service to its desired location upon request which involves transportation, purchasing, warehousing etc.
Main areas in logistics are:
Sales and Distribution (SD) : application 11, 13, 08 (in LBWE T-code)
Materials Management (MM) : application 03, 02
Logistics Execution (LE) : application 12
Quality Management : application 05
Plant Maintenance (PM) : application 04, 17
Customer Service (CS) : application 18
Project System (PS) : application 20
SAP Retail : application 40,43,44,45
How the data extraction happens?
Extraction can be done using either Full update/delta update.
Full load: Incase of logistic application, Full/Initialization will extract the data from setup tables (contains only historical data).
So if you have decided to go for full load, wait a minute there is a road block
For full update the data will be taken from setup tables, so in order to capture the changes you need to fill setup tables every time ,which will be a laborious task.
So, it is always suggestible to go for delta loads which makes loading life easier.
Read the below note to get details on delta load-:
Initialization: Data will be fetched from application table to setup tables (In Lo extraction, the extractor won't allow the direct communication with the application tables) from here, data finally reaches the target (info cube/ODS).Remember this process is for onetime.
Pre-requisites: Prior to initialization make sure the following steps are completed:
- Maintain Extract Structure
- Maintain data sources
- Activate Extract Structure
- Delete Setup tables
- Fill setup tables
Once a new transaction happens/an existing record is modified, upon saving it goes to the respective application table. From here depending on the update mode Direct/queued/Unserialized V3 update the data will be populated in delta queue (RSA7) and finally reaches to BW.
Pre-requisites: Prior to delta loads make sure the following steps are completed:
1.Define periodic V3 update jobs 2. Setting up the update mode (direct/queued/Un serialized v3 update)
LO- Delta Mode:
Info object 0Recordmode helps in identifying the delta
Check the field "delta "in ROOSOURCE /RODELTAM table
Incase of Lo extraction it is "ABR"
ABR: An after image shows the status after the change, a before image the status before the change with a negative sign and the reverse image also shows the negative sign next to the record while indicating it for deletion. This serializes the delta packets.This process supports an update in an ODS object as well as in an Info Cube.
FI Extraction:
FI Module deals with accounting and financial needs of an organization. Financial Accounting is broken down into the following sub-modules:
- Accounts Receivables
- Accounts Payable
- Asset Accounting
- Bank Accounting
- Consolidation
- Funds Management
- General Ledger
- Special Purpose Ledger
- Travel Management
We can extract the financial data at totals level / line item level. Please Refer the link http://help.sap.com/saphelp_nw70/helpdata/en/af/16533bbb15b762e10000000a114084/frameset.htm
In general, we will use R/3 line item tables as the data source for extracting the data to allow drill down capability from summarized data to line-item details.
Financial Accounting data can be extracted directly from the tables.
Depending on the business requirement we can use either FI-SL or standard BW content extractors (FI-AR, FI-AP, and FI-GL) to fetch FI data.
Note: FI-SL will be discussed under "one stage stop to know all about BW Extractors -Part2 "Which explains about Application specific customer generated extractors
FI-AR, FI-AP, and FI-GL:
General Ledger: All accounting postings will be recorded in General Ledger. These postings are real time to provide up-to-date visibility of the financial accounts.
Account Receivable: Accounts Receivables record all account postings generated as a result of Customer sales activity. These postings are automatically updated in the General Ledger.
Accounts Payable: Accounts Payable records all account postings generated as a result of Vendor purchasing activity. Automatic postings are generated in the General Ledger as well.
Standard FI data sources:
0FI_GL_4 (G/L Accounts- line items)
Takes the data from the FI document tables (BKPF/BSEG) that are relevant to general ledger accounting (compare table BSIS).
0FI_AP_4 (AP-line items) and 0FI_AR_4 (AR- line items
Selections are made from tables BSID/BSAD (Accounts Receivable) and BSIK/BSAK (Accounts Payable)
With old G/L in R/3, the tables GLT0 (totals), BSEG, BKPF (Line Item) get filled in SAP BI side you should have to use data model.
0FI_GL_1 --> 0FI_GL_1 --> 0FIGL_C01 (for Totals)
0FI_GL_4 --> 0FI_GL_4 --> 0FIGL_O02 (for Line item)
With New G/L in SAP ECC 6.0 onwards, the tables FAGLFLEXT (totals), FAGLFLEXA, BSEG, BKPF (Line Item) get filled in BI side you have to use data model.
0FI_GL_10 --> 0FI_GL_10 --> 0FIGL_C10 (for Totals)
0FI_GL_14 --> 0FIGL_O14 (for Line item)
Functionally, this effects other financial modules like AP, AR, PCA (Profit Center Accounting)
for ex: while implementing new G/L in BI side, this fulfills most of profit center Accounting requirements, and you do not have to implement PCA module separately.
When I was in FI implementation, there was no 0FI_GL_14 datasource...
We had existing 0FI_GL_1 & 0FI_GL_4 flows and we implemented new GL totals flow i.e. 0FI_GL_10...
FAGLFLEXT --> 0FI_GL_10 --> 0FIGL_O10 --> 0FIGL_C10.... new GL Totals implementation was quite smooth, since this flow is completely different from old GL totals (GLT0 --> 0FI_GL_1 --> 0FIGL_C01).
We recreated existing (on 0FIGL_C01) queries on 0FIGL_C10 (&V10) and used jump targets (RRI) to old line item (0FIGL_O02) wherever required...
You can go ahead with new GL lineitems (FAGLFLEXA & BSEG & BKPF) --> 0FI_GL_14 --> 0FIGL_O14 in parallel with existing old one (BSEG & BKPF) --> 0FI_GL_4 --> 0FIGL_O02.
------------------------------------------------------------------------------------------------
0FI_GL_10 --> 0FI_GL_10 --> 0FIGL_C10 (for Totals)
0FI_GL_14 --> 0FIGL_O14 (for Line item)
Question1: The DSO 0FIGL_O14 does not feed the cube. Any particular reason for that?
So If we want line item reporting for best solution will be RRI? Correct!!
Already have GL totals available at 0FIGL_C10 and then no point in loading line items (O14) again to cube.
Yes, Best approach for line item reports is RRI i.e. from 0FIGL_C10 to O14.
Question2: SAP recommended dataflow for new GL uses virtual cube 0FIGL_V10. Why do we need it or what reporting needs it satisfies?
SAP delivered function module RS_BCT_FIGL_DATA_GET_VC10 pulls data from 0FIGL_C10 to load 0FIGL_V10... Logic for cumulative balances considering carry forward balances from previous year for Balance sheet & P/L reports, is maintained in that function module.
(So, make sure this FM got installed while u installed standard content for GL model
Do the following, to see weather FM got installed.
Go to change mode of virtual provider (ZFIGL_V10). click information icon (blue 'i')
click 'Type/ Namespace'.. Then click 'details' button
If there is no FM, we won't get any data to 0FIGL_V10 and related queries.
And also 0FIGL_V10 uses Financial Statement Version object (0GLACCEXT), which is not available in 0FIGL_C10.
For Balance sheet & P/L reports, 0GLACCEXT is key object, so 0FIGL_VC10 is must.
We should install & load master (attr, hier, text) data for 0GLACCEXT.
How the data extraction happens?
In FI extraction 0FI_AR_4 and 0FI_AP_4 are linked with 0FI_GL_4 in order to maintain consistent data transfer from OLTP system (it is called coupled data extraction, Ref OSS notes 428571).
Note: Uncoupled" extraction possible with Plug-In PI 2002.2, see OSS note 551044
0FI_GL_4 writes the entries into the time stamp table BWOM2_TIMEST in the SAP R/3 System with a new upper limit for the time stamp selection.
And now, 0FI_AP_4 and 0FI_AR_4 will copy this new upper limit for the time stamp selection during the next data extraction in the SAP R/3 System. This ensures the proper synchronization of accounts payable and accounts receivable accounting with respect to G/L accounting.
Full load: Not a valid choice because of large volumes of detailed R/3 transaction data.
Delta load:
Note: Here the delta identification process works differently for new financial records and for changed financial records.
New Financial accounting line items which are posted in SAP R/3 sytem will be identified by the extractor using the time stamp in the document header (Table BKPF-(field) CPUDT).
By scheduling an initialization IP all the historical data can be loaded into BW from the application tables and it also sets "X" indicator in field LAST_TS (Flag: 'X' = Last time stamp interval of the delta extraction).That means after the last delta, initialization was done.
After this, daily delta loads can be carried out depending on timestamp by scheduling delta info packages.
During the delta load , the SAP R/3 system logs two time stamps that delimit a selection interval for a Data Source in table BWOM2_TIMEST(fields TS_LOW and TS_HIGH).
In case of changed FI documents, selections will be based on tables:
BWFI_AEDAT and (timestamp table) BWOM2_TIMEST (See OSS note 401646 for more details).
Delta extraction using delta queue method can also be possible incase if we want,
- Serialization of the records
- To distribute delta records to multiple BW systems.
A time stamp on the line items serves to identify the status of the delta. Time stamp intervals that have already been read are then stored in a time stamp table (BWOM2_TIMEST).
(Info object 0Recordmode plays vital role deciding delta's .Check the field "delta "in ROOSOURCE /RODELTAM table to identify the image)
The Financial Accounting line items are extracted from the SAP R/3 system in their most recent status (after-image delta method).
AIE: This delta method is not suitable for filling Info Cubes directly in the BW system. To start with therefore, the line items must be loaded in the BW system in an ODS object that identifies the changes made to individual characteristics and key figures within a delta data record. Other data destinations (Info Cubes) can be provided with data from this ODS object.
It uses delta type E(pull) means the delta data records are determined during the delta update by the data source extractor, updated to the delta queue and passed on to BI directly from there.
Comments
You can find more info Several types of extractors in my blog.
Thank you,
Subani Shaik.
Tnx
Kishore Madireddy
Thanks for such a nice post!!
Thanks,
Vandana Sagar