SAP BW
Extraction &
Modelling ABAP Reporting
Functional
1.LO EXTRACTION 1. BW on HANA 1.Types of Internal
Features Table
2.GENERIC EXTRACTION
2. LSA++ Architecture 2.Optimization of 1.Customer Exit
3.GENERIC DELTA
Code variable
3..ADSO
4.Take care lists when 2.CKF and RKF
Moving transports in 4.Composite Provider 3.Move
Production corresponding and 3.Structure
5.Currency and Unit direct assignments
5.Enhancement Steps Conversion difference
6.PO Extractor 6.DSO Overwrite and
Additive Property
7.FI Extractors
7.Record Modes
Functional
8.Direct Update DSO
1.PO Tcodes
9.Conversion routine
2.FI Tcodes
10.Transfer Routine
3..PO Tables(Keys of Tables)
11.Non Cumulative KF
4..FI tables
12.Virtual
5.Account Assignment Characteristics & KF
6.Request to Receipt and 13.IP(Integrated
Invoice to Pay Cycle Planning)
7.Three Way Invoice 14.Lookup on DSO and
Verfication handle delta in that
scenario
13.Exceptional
Aggregation
Extraction Part
1. LO Extraction: Link:- http://easy-learn-bw.blogspot.com/2013/08/lo-extraction.html
2. GENERIC EXTRACTION:-
https://blogs.sap.com/2012/09/26/generic-extraction-based-on-a-view-part-1/
3. GENERIC DELTA:-
Based on
Cal day,
Time Stamp,
Numeric Pointer
Two Safety Intervals:-
Upper Safety Interval
Lower Safety Interval
https://blogs.sap.com/2013/08/23/delta-mechanism-for-generic-extractors/
4. Take Care List when moving Objects in Production(when done some
enhancements):-
Setup table should be delete
Extraction Queue should be blank for this first we need to Push data in RSA7 and then fetch
data in BI ,so that both Extraction Queue (LBWQ) and RSA7 can be blank
Make sure no posting during transportation ,so that extraction queue(LBWQ) should be
blank
In BI side PSA should be blank
5. Enhancement of Data Source:-
https://archive.sap.com/kmuuid2/00c1f726-1dc2-2c10-f891-
ddfbffdb1a46/Step%20by%20Step%20Guide%20to%20Enhance%20a%20Datasource.pdf
6. PO Extractor:-
Application Area:-02
Setup table Tcode :-OLI3BW
S.NO. Extractor Comments
1. 2lis_02_hdr Header-EKKO
2. 2lis_02_itm Item-EKPO
3. 2lis_02_scl Schedule Line-EKET
4. 2lis_02_sgr Schedule line with Goods Receive EKET,EKBE,EKPO
5. 2lis_02_acc Account Assignment-EKKN
7.PO tables:-
Main tables:-
Table Desc Keys
EKKO PO Header EBELN
EKPO PO Item EBELN,EBELP
EKET PO Schedule Line Item EBELN,EBELP,ETENR
EKBE PO History Data EBELN,EBELP,ZEKKN,VGABE,GJAHR,BELNR,BUZEI
EKKN PO account Assignment EBELN,EBELP,ZEKKN
EBELN:PO DOC No, EBELP:-Item, ETENR:-Delivery Schedule line, ZEKKN:-Sequential Number of
account assignment, VGABE:-Transaction Type, GJAHR:-Mat Doc Year, BELNR:-Mat Doc No., BUZEI:-
Item
https://sap4tech.net/sap-purchase-order-tables/
8. PO TCodes :-
Main Tcodes for Purchase Order
S.No. Tcode Desc
1. ME23N Create
2. ME22N Change
3. ME23N Display
4. ME2N Purchase Order History
5. ME2L PO by Vendor
6. ME2M PO by Material
7. MIGO Goods Receive
8. MIRO Invoice Enter
http://www.tcodesearch.com/tcodes/search?q=purchase+order
9. FI Extractor:-
S.NO. Extractor Comments
1. 0fi_gl_14 General Line Item-leading ledger-faglflexa ,bkpf, bseg
2. 0fi_gl_10 Balances-FAGLFLEXT
3. 0fi_ap_4 Account Payable :Line Item-BSIK,BSAK
4. 0fi_ar_4 Account Receivable: Line Items-BSID,BSAD
10. FI Important Tables:-
S.NO. Table Desc
1. BKPF Accounting Document
2. BSEG Item Level
3. FAGLFLEXA General Ledger: Actual
Line Items Table
4. FAGLFLEXT General Ledger: Totals
5. BSIK Accounting: Secondary
index for vendors
6. BSAK Accounting: Index for
vendors (cleared items)
7. BSID Accounting: Secondary
index for customers
8. BSAD Accounting: Index for
customers (cleared
items)
9. SKA1 G/L Accounts (Chart of
Accounts)
10. SKB1 G/L Accounts (Company
Code)
11. SKAT G/L Accounts (Chart of
Accounts: Description)
11. FI Important Tcodes:-
S.No. Tcode Desc
1. FB03 Display Document
2. FBV0 Display Parked Document
3. FB11 Display Hold Document
5. F.01 To check FSV
6. FAGLL03 General Ledger Report
https://wiki.scn.sap.com/wiki/display/ERPFI/SAP+FICO+Transaction+Codes
Modelling Part:-
1.BW on HANA Features:-
2. LSA & LSA++ Architecture:-
LSA++ Having only three layer architecture:-
Acquisition
DWH
Virtual
3. ADSO :
Advanced DSO have persistent object is combination of below objects
1. Field based structure of PSA ( filed based modeling)
2. No activation required for like WDSO (update property)
3. 3 tables have (active, new, change log table ) like standard DSO (over write property)
4. Info cube model – select setting – all char are keys ( addition property)
Link:-
https://blogs.sap.com/2016/05/22/sap-bw-on-hana-advanced-dso/
https://blogs.sap.com/2018/03/13/bw-on-hana-adso-type/
4. Composite Provider:-
A Composite Provider is an Info Provider, which combines data from several analytic indexes
or from other Info Providers (by Join or Union), and makes this data available for reporting
and analysis.
UNION and JOIN operations are executed in HANA and not the application server.
5. DSO Overwrite and Summation Property:-
DSO - Over write or summation option you will see at transformations --> rule details---> aggregation
option. This option will come for only key figures.
In Summation mode: Key figures of same key will be added while in Overwrite mode it will be
adding up.
6. Record Modes:-
After-image ‘ ‘ :
The record provides an after image, i.e. how does the record look like after the change.
The status of the record after it has been changed, or after data has been added is
transferred.
Before-image ‘X’:
The record provides a before image, i.e. what did the record look like before the change.
The status of the record before the change is transferred.
Add ‘A’:
The record provides an additive image. Only the differences for all the numeric values are
available.
Delete ‘D’:
This record mode type signifies that the record must be deleted.
New Image ‘N’:
The record provides a new image, i.e. when a new record is created; a new image is set for
it.
Reverse Image ‘R’:
The record provides a reverse image. The content of this record is equivalent to a before
image. The only difference occurs when updating a Data Store object: An existing record
with the same key is deleted.
Delta Process:-
Delta Process Support Data Load into DSO or Cube
ABR (After Image, Before Image) DSO and Cube
ADD (Additive Image) DSO and Cube
AIE (After Image) DSO Only
R (Reverse Image DSO and Cube
D (Delete Image) DSO Only
Question:-
If in Start routine code mentioned below is written then what will happen?
Delete Source_package where recordmode = ‘R’.
7. Direct Update DSO:-
It is used to fetch data from external system
e.g. from APDs, XI or third party tool.
Data loading process in not same like Standard DSO.
Having single table only active table(activation not required)
SID not generated
http://garudatrainings.com/wp-content/uploads/2014/03/Direct-Update-DSO.pdf
https://blogs.sap.com/2013/08/22/direct-update-dso-in-sap-bw/
https://blogs.sap.com/2012/09/20/data-store-object-dso/
8. Conversion Routine:-
Conversion routines are used to convert the fields either from display format to SAP internal format
or from SAP internal format to display format.
e.g. ALPHA conversion in infoobject.
https://blogs.sap.com/2013/10/25/conversion-routine-in-sap-bw/
https://blogs.sap.com/2012/04/23/conversion-rouitnes-alpha/
Customize Conversion Routine:-
Need to create FM output or Input then assign it to domain in se11
http://saptechnical.com/Tutorials/ABAP/Conversion/Custom.htm
https://archive.sap.com/discussions/thread/146553
9. Transfer Routine:-
Each InfoObject can hold a global transfer routine which always gets executed after the
transformation or the communication structure. No matter if the updaterule. Using global
“transfer routine exists” makes sense if an InfoObjet gets transformed with an ABAP routine
always on the same way.
https://blogs.sap.com/2013/10/21/transfer-routine-exists-on-an-infoobject/
10. Virtual Characteristics and Key figures:-
Infoobject don’t have any physical data stored ,value of the object calculate at run time.
https://danielnurindra.wordpress.com/2009/03/30/creating-virtual-characteristic-using-badi/
https://blogs.sap.com/2014/03/28/virtual-characteristics-without-the-use-of-badi/
11. Currency Conversion and Unit Conversion:-
Tables: TCURR & TCURV
Note: Before currency conversion and Unit conversion be sure exchange rate and units of
source systems are transferred into BW system via Transfer Global
Currency Conversion:-
Steps:-
Goto RSCUR
Create Currency translation type
Pass Exchage Rate Type
Select Source and Target Currency (can be use fixed or dynamic based on requirement)
Then go to time ref and pass info-object name
https://archive.sap.com/kmuuid2/2056ef93-2004-2d10-21ae-
f973bb48d7a1/Currency%20Conversion%20using%20Variables%20in%20SAP%20BI%20-
%20Reporting.pdf
Unit Conversion:-
https://archive.sap.com/kmuuid2/00944f2f-c3e8-2c10-ccb5-
a6e4cc882db4/Reporting%20Data%20in%20Alternate%20Unit%20of%20Measure%20in%20BI%
207.0
https://wiki.scn.sap.com/wiki/display/BI/Define+unit+of+measure+conversions+in+0MATERIAL+
definition
12. Non Cumulative KF:-
A non-cumulative is a non-aggregating key figure on the level of one or more objects, which is
always displayed in relation to time. Examples of non-cumulatives include headcount, account
balance and material inventory.
http://easy-learn-bw.blogspot.com/2013/07/non-cumulative-key-figures.html
13. Case Study of delta Scenario :-
What issue can come in below scenario and what will be your suggestion for below
design
Looking Up on FI
DSO(routine)
PO DSO2
e.g. Payment terms
PO DSO1 FI DSO1
Data Source-PO Data Source-FI
ABAP
1. Types of Internal Table :-
Standard
Sorted
Hashed
https://www.sapnuts.com/courses/core-abap/internal-table-work-area/internal-
tables-types.html
2. Optimization of Code:-
Minimum use of delet statement
Avoid to use loop inside loop and use read statement
Use binary search in read statement
Use SELECT INTO TABLE rather than SELECT INTO CORRESPONDING FIELDS OF TABLE
Delete adjacent duplicate entries from internal table before selection from database
table using “ FOR ALL ENTRIES” statement
SORT inside a LOOP is not used
After the APPEND statement inside a loop, the work area that has been appended is
cleared
Internal tables, Work areas & Global Variables are freed when no longer needed
(e.g. using the FREE / REFRESH command), especially when the tables are large or
the program is a batch program
Do not delete the records of internal table inside the Loop – End loop.
For more link:-
https://archive.sap.com/discussions/thread/832175
Reporting
1. Customer Exit Variable:-
Abap code is used for retrieving variable value
There are i_steps method to populate variable values:-
I_steps=0,1,2,3
https://blogs.sap.com/2013/12/13/all-about-istep-variable/
2. Maximum two structure we can use in BEX QUERY Designer
3. CKF and RKF:-
Restricted keyfigure is to create a keyfigure values based on some value of
Charecteristic.
Ex. No of employees in south region..
Calculated keyfigure own formulas can be created based on existing keyfigure with
the available functions at formula or Caculated keyfigures. percentage fuctions,
mathe matical functions, boolean fucntions are available and other functions too.
once you save this Restricted and Calculated keyfigures they are saved globally and
available in all quries on that infoprovider.
New formula is similar function to Calculated keyfigure, but that is local formula.
New Selection is also local and similar to Restricted keyfigure.
https://wiki.scn.sap.com/wiki/display/BI/Formulas%2C+Calculated+Key+Figures+and+Restri
cted+key+Figures
