SAP BW/BI Knowledge Sharing: Generic Extraction in SAP BI

SAP BW/BI Knowledge Sharing: Generic Extraction in SAP BI

What will happend if a request in Green is deleted?

Deleting green request is no harm. if you are loading via psa, you can go to tab 'reconstruction' and select the request and 'insert/reconstruct' to have them back.But,
For example you will need to repeat this delta load from the source system. If you delete the green request then you will not get these delta records from the source system.

Explanation :
when the request is green, the source system gets the message that the data sent was loaded successfully, so the next time the load (delta) is triggered, new records are sent.
If for some reason you need to repeat the same delta load from the source, then making the request red sends the message that the load was not successful, so do not discard these delta records.Delta queue in r/3 will keep until the next upload successfully performed in bw. The same records are then extracted into BW in the next requested delta load.

SAP Tables in Material Managetment Module

Listed below are SAP standard tables in Material Management Module - Part 1:
S170–>Payment document data
S170E–>S170 - Structural Information
S171–>Payment item data
S171E–>S171 - Structural Information
S172–>Vendor billing document data
S172E–>S172 - Structural Information
S173–>Vendor billing document item
S173E–>S173 - Structural Information
TWEA–>IS-R Labeling: Check table material groups
TWEAT–>IS-R Labeling: text table material groups
TWEK–>IS-R Labeling: Check table customer/plant groups
TWEKT–>IS-R Labeling: text table customer/plant groups
TWEP–>IS-R labeling: check table label categories
TWEPT–>IS-R labeling: text table label categories
TWET–>IS-R: check table label types
TWETT–>IS-R Labeling: text table label types
TWEZ–>IS-R Labeling: check table labeling party
TWEZT–>IS-R Labeling: text table labeling party
TWTCTRL–>Control table for procedures for additionals
TWTFM–>Procedure for additionals
TWTFMA–>Additionals: Material group attributes
TWTFMP–>Additionals: procedure for additionals per material group
TWTFMT–>Description (name) for procedures for additionals
WEST–>IS-R labeling: master data for control
WTADAB–>Additionals: Documents
WTADABT–>Additionals: Documents: Supplementary texts
WTADDI–>Additionals for a material
MCHP–>Batch record for a batch
T685H–>Conditions: Batches => Default Values
TCHW1–>Proportion units of measure / product units of measure
TCHW2–>Units of measurement for prop./prod. units of measure
TCUWS–>Customizing: Batch Configuration for Rel.3.0
TFSBD–>Sequence of Procedure for Batch Determination
WBEW–>Act. ingredient val.
AEDT–>Engineering Change Management: Alternative Dates
AEEF–>ECM: validity
AEHI–>Change hierarchy
AENR–>Change Master
AENV–>Object Types for Change Master
AEOI–>Object Management Records for Change Master
T419S–>Change Master Status
T419T–>Change Master Status Texts
TCC01–>Object Types
TCC02–>Object Type Texts
TCC03–>Engineering Change Management Object Types
TCC04–>Engineering Change Management Object Type Texts
TCC05–>Transaction-Specific Initial Values for EC Management
TCC07–>Field Selection for Engineering Change Management
TCC08–>Engineering Change Management Control Data
TCC09–>Revision Level Sequence
TCC10–>Engineering change management: release key
TCC11–>Change Types
TCC12–>Change Type Texts
TCC13–>Object Change Types
TCC14–>Object Change Type Texts
TCC15–>ECM profile
TCC16–>Profile description
TCC17–>Object type profile data
TCC18–>Table for setting of flags for object types
TCC19–>Object type description
TCCPR–>User-specific values for engineering change management
TECN–>Validity object: change numbers
TECS–>Master Validity Object
DGTMD–>DG-MAT: Dangerous goods - material master
DGTMD_LKEY–>DG-MAT: Dummy table for lock object on log. key for DGTMD
LIPSDG–>Appendix table for dangerous goods data
TDG03–>DG: Specify Transport Category
TDG04–>DG: Label for transport category
TDG09–>DG: Define dangerous goods classes
TDG10–>DG: Label for dangerous goods classes
TDG11–>DG: Define DG letters (level of danger)
TDG12–>DG: Label for dangerous goods letter
TDG15–>Water pollution classes
TDG16–>DG: Label for water pollution class
TDG17–>DG: Define danger label
TDG18–>DG: Description for danger label number
TDG21–>DG: Link table to text IDs
TDG22–>DG: Description for user-defined texts
TDG27–>DG: Hazard Notes
TDG28–>Descriptions for hazard notes
TDG29–>DG: Labels for Printing
TDG30–>DG: Text table for printing labels
TDG31–>DG: Hazard Identification Numbers
TDG32–>Text table for hazard identification numbers
TDG33–>DG: Hazard-inducing substances
TDG34–>DG: Description for hazard-inducing substances
TDG35–>DG: VbF Classes
TDG36–>DG: Description for VbF classes
TDG37–>DG: Dangerous Goods Storage Class
TDG38–>DG: Description for dangerous goods storage classes
TDG39–>DG: Processing status for DG master data
TDG40–>DG: Description for processing status
TDG41–>Maintain Profiles
TDG42–>DG-Mat: Maintain profile
TDG43–>DG: Date determination for printed texts
TDG47–>DG: Text names for DG material-master independent texts
TDG48–>DG: Description for DG material-independent texts
TDG49–>DG: Permitted Primary Languages
TDG51–>DG: Derivation of Secondary Language
TDG61–>DG: Regulations

SAP development function modules

ABAP4_CALL_TRANSACTION
Call transaction code, also see SAPGUI_SET_FUNCTIONCODE

ADDR_PERS_COMP_COMM_GET
Get additional user address details not retrived by SUSR_USER_ADDRESS_READ (i.e. email address)

ADDR_PERS_COMP_COMM_MAINTAIN
Update additional user address details (i.e. email address)

BAPI_COMPANYCODE_GET_PERIOD
Retrieve fiscal year and payrole period (based on date and company code)

CALCULATE_DATE
Increase/decrease DATE by a specific number of Days/Months

CONVERT_DATE_TO_EXTERNAL
Converts date from system storage format to users specified display format

ADDR_PERS_COMP_COMM_GET
Get additional user address details not retrived by SUSR_USER_ADDRESS_READ (i.e. email address)

BAPI_COMPANYCODE_GET_PERIOD
Retrieve fiscal year and payrole period (based on date and company code)

CREATE_TEXT
Create standard texts (i.e. PO headers texts)

CURRENCY_AMOUNT_SAP_TO_DISPLAY
Convert currency value from value stored in SAP to displayed currency

CURRENCY_AMOUNT_DISPLAY_TO_SAP
Convert currency value from displayed currency value to value stored in SAP

CLPB_EXPORT
Export files to clipboard

CLPB_IMPORT
Copies clipboard into table

DATE_COMPUTE_DAY
Returns day of the week for a particular date(1=Monday, 5=Friday etc.)

DATE_TO_DAY
Returns day of the week for a particular date('Monday', 'Friday', 'Sat.')

ENQUEUE_READ
Returns list if active lock objects

DETERMINE_PERIOD
Retrieve fiscal year and payrole period (Note: verision parameter is fiscal year varient from table T009)

DYNP_VALUES_UPDATE
Return values (i.e. from an F4 value request) into respective fields on a dynpro

F4IF_FIELD_VALUE_REQUEST
Call search help manually i.e. from report selection screen

F4IF_INT_TABLE_VALUE_REQUEST
Display internal table as search help (documented in SAP)

FILE_GET_NAME
Retrieve Logical file path. Use Transaction 'FILE' to view/create logical file paths

FTI_FISCAL_YEAR_MONTH_GET
Returns fiscal year for specific date

GUI_DOWNLOAD, WS_DOWNLOAD, DOWNLOAD
Download file to PC

GUI_UPLOAD, WS_UPLOAD, UPLOAD
Upload file from PC

HR_SEN_CRULE_0100_DATE
Increase/decrease DATE by a specific number of Days/Months/Years

JOB_OPEN, JOB_SUBMIT, JOB_CLOSE
Create and submit background jobs

MESSAGE_TEXT_BUILD
Builds actual message based on info returned from Call transaction

NUMBER_GET_NEXT
Get the next unique number in a number range. Use tcode SNRO for maintaining number ranges

NUMBER_CHECK
Check if number is within a number range (see tcode SNRO)

NUMBER_GET_INFO
Get info about number range (see tcode SNRO)

POPUP*
Display Pop-up Screen(s)

READ_TEXTS
Retrive standard texts (i.e. PO headers texts)

READ_EXCHANGE_RATE
Retrieve exchange rate on a particular date

RS_CREATE_VARIANT
Creating a Report Variant

RS_VARIANT_CONTENTS
Returns contents of a variant, is also useful as the result lists all variable names that could be set on screen

RZL_READ_DIR_LOCAL
Get list of files within specific directory(Application Server)

SAPGUI_SET_FUNCTIONCODE
Execute SAP function code. Can also be used to execute transaction code by using '/o' i.e. '/OSE80'.

SO_SPLIT_FILE_AND_PATH
Splits the filename and its path i.e. c:\temp\hello.txt would return 'hello.txt' & 'c:\temp\'

SPLIT_FILENAME
Splits the file name and its extension i.e. c:\temp\hello.txt would return 'hello' & 'txt'





STUM_WP_TOTAL_ACTIVITY
Retrieves list of all process from all servers (i.e. SM50, SM66). Also returns a second table, containing a list of all the servers

STUM_WP_SERVER_ACTIVITYS & EW_TH_WPINFO
Retrieves list of processes on current server

SUSR_USER_ADDRESS_READ
Get user address details stored under 'Own data'. Also see ADDR_PERS_COMP_COMM_GET

SUSR_USER_PARAMETERS_GET
Get user parameter details stored under 'Own data'

SUSR_USER_DISPLAY_WITH_AUTHS
Displays user authorisation objects (Note: may have to debug around authority checks)

SUSR_USER_AGR_ACTIVITYGR_GET
Get users activity group details

SUSR_SYNC_USER_TABLES
Sync USR tables

SXPG_COMMAND_EXECUTE
Execute external command(FTP data in & out of SAP)

SX_INTERNET_ADDRESS_TO_NORMAL
Check email address has been entered in correct format

TH_POPUP
Display Windows Message on Users Screen

WS_EXECUTE
Execute External Program

Useful SAP programs

Program Description
BALVBT01 Example SAP program for displying multiple ALV reports on one page
BCALV_GRID_DEMO ALV Dialog grid demo (4.6)
SHOW_COLO Displays all colours available
SHOW_ICON Displays all icon available
RGUGBR00 Substitution/Validation and rules utility
RKCTSEAR Search source code of various programs for up to two strings. Also see RPR_ABAP_SOURCE_SCAN or use search in source functionality via SE80
RPCIFU01 Display File
RPCIFU03 Download Unix File to PC
RPCIFU04 Upload PC File to Unix File
RPR_ABAP_SOURCE_SCAN Search ABAP code for a string. Much more flexible than RSRSCAN1 or RKCTSEAR
RSBDCBTC Submit a BDC job with an internal batch number and wait for the end of the batch input session
RSBDCDRU Prints the contents of a Batch Input session. No options for error transactions only
RSBDCOS0 Execute OS Command (Logged in SYSLOG and Trace Files)
RSBDCSUB Process batch input sessions automatically
RSBTCDEL Delete batch jobs
RSCONN01 SAPconnect: Start Email Send Process
RSCSAUTH Maintain/Restore Authorization Groups
RSINCL00 Extended ABAP Program Reference List
RSMODRES Restore enhancement projects after upgarde
RSORAREL Check Oracle Version
RSPARAM Display all instance parameters
RSPO0041 Delete Old Spool Requests
RSSNAPDL Reorganization Program for Table SNAP of Short Dumps
RSTRANSP Transport Report Variants
RSTXFCON SAPscript: Conversion of Page Format for Forms
RSTXPDFT4 Convert spool request to PDF document
RSTXPDFT5 GUI download of a spool request
RSTXSCRP SAPscript Export to Dataset / SAPscript Import from Dataset (Upload and download SAPScript layout sets)
RSTXTRAN Transfer of SAPscript Texts(standard texts) to a transport
RSUSR003 Check the Passwords of Users SAP* and DDIC in All Clients
RSUSR006 List of Users with Incorrect Logons
RSVARFIT Adjust Variants to Modified Selections
RSVTPROT Evaluation of change logs
RSWBO052 Change Object Directory Entries
RSWBO060 Include Objects in a Transport Request
SAPMSUU0 Program for user maintenance(SU01), Maybe useful if you do not have access to the actual SU01 transaction code.

Locate Function Exit within program

For this example I am using transaction MR1M

Step 1
Execute transaction MR1M and display program name by selecting 'Status...' from the 'System'menu

Step 2
Double click on the program (screen) name to enter source code, or access code via SE80.

Step 3
Search source code for the 'Customer-Function' string using the find button.
Remember to select 'In main program'.

Step 4
A list of search results should be displayed indicating where all function exits can be found.
You can now double click on each of them to go to its position in the source code. This also allows for the insertion of breakpoints so that you can test if the exits are called in the appropriate place.

SAP database tables

DBCON : Description of database connections (including password)

DD02V : Date dictionary tables

DD03L : Date dictionary tables and fields

DD07T : Texts for Domain Fixed Values

T100 : Table of message ( mesage class and message number )

TRDIR : Table of programs

TRMAC : Table of macros

TSTC : Table of transaction codes

TADIR : Directory of R/3 repository objects

TFACD :Factory calender definition

THOCI : Public holiday calendar index

TPARA : Table of PID's

DEVACCESS : Table of development users Including registered Developer access key

USR02 : Logon data

USR04 : User master authorization (one row per user)

UST04 : User profiles (multiple rows per user)

USR10 : Authorisation profiles (i.e. &_SAP_ALL)

UST10C : Composit profiles (i.e. profile has sub profile)

USR11 : Text for authorisation profiles

USR12 : Authorisation values

USR13 : Short text for authorisation

USR40 : Table for illegal passwords

OBJT : Authorisation objetc table

SAP BW Query Exit to restrict users output

Please note this article has been written from an ABAPers point of view and some knowledge of creating BW queries will be required.

When using an authorisation object to restrict a query report it determines if the user can see everything in a range. If they cant see them all then an authorisation error will be displayed. You may have a requirement to still allow the user to see the report but to remove values they are not authorised to see.

This would be done by having 2 variables on the report selection screen, one for the user to enter their selection and a hidden one which will do the actual report restriction. The code for this will look something like this, where 'ZGMGTNOI' is the hidden field and '0S_GRANT' is the visible user input field.

**** ZGMGTNOI variable derives input from variable OS_GRANT
WHEN 'ZGMGTNOI'.
if i_step = 2.

* gets selection values entered by user (0S_GRANT)
loop at I_T_VAR_RANGE into wa_range where VNAM eq '0S_GRANT'
and IOBJNM eq '0GRANT_NBR'.
wa_grant-low = wa_range-low.
wa_grant-high = wa_range-high.
wa_grant-option = wa_range-opt.
wa_grant-sign = wa_range-sign.
append wa_grant to r_grant.
endloop.

* selects all grants user is authorised to see within entered
* selection values
select grant_nbr
from ZGMUSERGRANTS
into corresponding fields of table it_ZGMUSERGRANTS
where UNAME eq sy-uname
and grant_nbr in r_grant.

* If no values are found! Populates hidden restriction variable with
* user input
if sy-subrc ne 0.
loop at r_grant into wa_grant.
move-corresponding wa_grant to wa_ETRANGE.
wa_ETRANGE-opt = wa_grant-option.
append wa_ETRANGE to E_T_RANGE.
endloop.
* If values are found! Populates hidden restriction variable with
* all values user is authorised to see
else.
clear: wa_range.
loop at it_ZGMUSERGRANTS into wa_ZGMUSERGRANTS.
wa_ETRANGE-sign = 'I'.
wa_ETRANGE-opt = 'EQ'.
wa_ETRANGE-low = wa_ZGMUSERGRANTS-grant_nbr.
append wa_ETRANGE to E_T_RANGE.
endloop.
endif.
endif.

What is the BW user exit RSR00001, and How does it work?

The SAP BW query exit RSR00001 is an Enhancement for Global Variables in Reporting. It is called up several times during the execution of a report. The parameter I_STEP is populated with a number from 0 to 3 to specify at what point the enhancement is being called.

i.e. If I_STEP = 1, then Call has taken place directly before any variable variable entry has been made. This can be used to pre populate selection variables

If I_STEP = 2, then then call has taken place directly after variable entry. This step is only started up when a variable is not input ready and could not be filled when I_STEP was equal to 1.

If I_STEP = 3, you can check the values of the variables. Triggering an exception (RAISE) causes the variable screen to appear once more. Afterwhich, I_STEP=2 is also called again. If I_STEP = 0, then the enhancement is not called from the variable screen. The call can come from the authorization check or from the Monitor.

Implementing Business Add-Ins (BADI)

The use of object orientated code within SAP has lead to new method of enhancing standard SAP code called Business Add-Ins or BADI's for short. Although the implementation concept is based on classes, methods and inheritance you do not really have to understand this fully to implement a BADI. Simply think of methods as a function module with the same import and export parameters and follow the simple instructions below.


Steps:
1.    Execute Business Add-In(BADI) transaction SE18
2.    Enter BADI name i.e. HRPBSGB_HESA_NISR and press the display
        button
3.    Select menu option Implementation->Create
4.    Give implementation a name such as Z_HRPBSGB_HESA_NISR
5.      You can now make any changes you require to the BADI within this
        implementation, for example choose the Interface tab
6.    Double click on the method you want to change, you can now enter
        any code you require.
7.      Please note to find out what import and export parameters a
        method has got return the original BADI definition
        (i.e. HRPBSGB_HESA_NISR) and double click on the method name
        for example within HRPBSGB_HESA_NISR contract is a method
8.      When changes have been made activate the implementation

Enhance the Extractors (ABAP perpective)

Procedure to enhance the Extractors or Datasources from ABAP perspective :
1. Check the fields which we need to enhance already exist as part of  the extraction structure or we need to enhance the structure too.

2. If it is enhancing existing extractor , do the following steps:

    a.  Check whether the extractor is active (RSA6)

    b.  If not active check  in Repository (RSA5 ) & activate the same

    c.   Once the extractor is active , we can enhance the extractor with the Z fields :

          i. Use RSA6 transaction ,select the extractor to be enhanced and use the "Enhance Extraction structure" option

          ii. Provide the name to appending structure and give the fields to  be appended (The procedure is same as appending any other structure)

          iii.Once the extraction structure of the extractor is enhanced ,we need to uncheck the hide fields option as follows :

                1. Select the datasource and use   "Datasource->Change Datasource" from the menu.

                2. Uncheck the Hide Field Option for the zfields we appended and save the extractor .

    d.  Now the extraction structure is enhanced with the z fields

    e.  We can fill the data in the zfields using the following ways

          i.  User Exit  RSAP0001->EXIT_SAPLRSAP_001 (Not ideal)

          ii.  Using Custom BADI

    f.  We can use  BADI (RSU5),

3. RSA3 is used to test the Extractor (Simulation the extractor )

Note : Use field Symbols for better performance .

To check whether your query is migrated to SAP BW7 version already

Check the Table RSZCOMPDIR:
Enter your Query Technical Name as input to the field COMPID and execute. If Field VERSION in the Table RSZCOMPDIR has the value less than 100 that means Query is in 3.x version. If it is more than 100 means, it is already migrated.