Tuesday, July 28, 2009

How to improve FI_GL_4 data extract

When a Delta InfoPackage for the DataSource 0FI_GL_4 is executed in SAP NetWeaverBI (BI), the extraction process in the ECC source system mainly consists of two activities:- First the FI extractor calls a FI specific function module which reads the new andchanged FI documents since the last delta request from the application tablesand writes them into the Delta Queue.- Secondly, the Service API reads the delta from the Delta Queue and sends the FIdocuments to BI.





The time consuming step is the first part. This step might take a long time to collect allthe delta information, if the FI application tables in the ECC system contain many entriesor when parallel running processes insert changed FI documents frequently.

A solution might be to execute the Delta InfoPackage to BI more frequently to processsmaller sets of delta records. However, this might not be feasible for several reasons:First, it is not recommended to load data with a high frequency using the normalextraction process into BI. Second, the new Real-Time Data Acquisition (RDA)functionality delivered with SAP NetWeaver 7.0 can only be used within the newDataflow. This would make a complete migration of the Dataflow necessary. Third, as ofnow the DataSource 0FI_GL_4 is not officially released for RDA.To be able to process the time consuming first step without executing the deltaInfoPackage the ABAP report attached to this document will execute the first step of theextraction process encapsulated. The ABAP report reads all the new and changeddocuments from the FI tables and writes them into the BI delta queue. This report can bescheduled to run frequently, e.g. every 30 minutes.The Delta InfoPackage can be scheduled independently of this report. Most of the deltainformation will be read from the delta queue then. This will greatly reduce the number ofrecords the time consuming step (First part of the extraction) has to process from the FIapplication as shown in the picture below.






The Step By Step Solution4.
1 Implementation DetailsTo achieve an encapsulated first part of the original process, the attached ABAP report iscreating a faked delta initialization for the logical system 'DUMMY_BW'. (This system can be named anything as long as it does not exist.) This will create two delta queues for the0FI_GL_4 extractor in the SAP ERP ECC system: One for the ‘DUMMY_BW’ and theother for the 'real' BI system.The second part of the report is executing a delta request for the ‘DUMMY_BW’ logicalsystem. This request will read any new or changed records since the previous deltarequest and writes them into the delta queues of all connected BI systems.The reason for the logical BI system ‘DUMMY_BW’ is that the function module used inthe report writes the data into the Delta Queue and marks the delta as already sent tothe ‘DUMMY_BW’ BI system.This is the reason why the data in the delta queue of the ‘DUMMY_BW’ system is notneeded for further processing. The data gets deleted in the last part of the report.The different delta levels for different BI systems are handled by the delta queue and areindependent from the logical system.Thus, the delta is available in the queue of the 'real' BI system, ready to be sent duringthe next Delta InfoPackage execution.This methodology can be applied to any BI extractors that use the delta queuefunctionality.As this report is using standard functionality of the Plug-In component, the handling ofdata request for BI has not changed. If the second part fails, it can be repeated. Thecreation & deletion of delta-initializations is unchanged also.The ABAP and the normal FI extractor activity reads delta sequential. The data is sentto BI parallel.If the report is scheduled to be executed every 30 minutes, it might happen that itcoincides with the BI Delta InfoPackage execution. In that case some records will bewritten to the delta queues twice from both processes.This is not an issue, as further processing in the BI system using a DataStore Object withdelta handling capabilities will automatically filter out the duplicated records during thedata activation. Therefore the parallel execution of this encapsulated report with the BIdelta InfoPackage does not cause any data inconsistencies in BI. (Please refer also toSAP Note 844222.)

4.2 Step by Step Guide1. Create a new Logical System usingthe transaction BD54.This Logical System name is used inthe report as a constant:c_dlogsys TYPE logsys VALUE 'DUMMY_BW'In this example, the name of theLogical System is ‘DUMMY_BW’.The constant in the report needs tobe changed accordingly to thedefined Logical System name in thisStep.

. Implement an executable ABAPreportYBW_FI_GL_4_DELTA_COLLECTin transaction SE38.The code for this ABAP report canbe found it the appendix.

3. Maintain the selection texts of thereport.In the ABAP editorIn the menu, choose Goto 􀃆 TextElements 􀃆 Selection Texts

. Maintain the text symbols of thereport.In the ABAP editorIn the menu, choose Goto 􀃆 TextElements 􀃆 Text Symbols

5. Create a variant for the report. The"Target BW System" has to be anexisting BI system for which a deltainitialization exists.In transaction SE38, click Variants6. Schedule the report via transactionSM36 to be executed every 30minutes, using the variant created instep 5.

Code
This report collects new and changed documents for the 0FI_GL_4 from*& the FI application tables and writes them to the delta queues of all*& connected BW system.*&*& The BW extractor itself therefore needs only to process a small*& amount of records from the application tables to the delta queue,*& before the content of the delta queue is sent to the BW system.


*&---------------------------------------------------------------------*REPORT ybw_fi_gl_4_delta_collect.TYPE-POOLS: sbiw.* Constants* The 'DUMMY_BW' constant is the same as defined in Step 1 of the How to guideCONSTANTS: c_dlogsys TYPE logsys VALUE 'DUMMY_BW',c_oltpsource TYPE roosourcer VALUE '0FI_GL_4'.* Filed symbols.

FIELD-SYMBOLS:

DATA: l_slogsys TYPE logsys,
l_tfstruc TYPE rotfstruc,
l_lines_read TYPE sy-tabix,
l_subrc TYPE sy-subrc
,l_s_rsbasidoc TYPE rsbasidoc,
l_s_roosgen TYPE roosgen,
l_s_parameters TYPE roidocprms,
l_t_fields TYPE TABLE OF rsfieldsel,
l_t_roosprmsc TYPE TABLE OF roosprmsc,
l_t_roosprmsf TYPE TABLE OF roosprmsf.
* Selection parameters

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECTION-SCREEN SKIP
1.PARAMETER prlogsys LIKE tbdls-logsys OBLIGATORY.SELECTION-SCREEN:
END OF BLOCK b1.AT SELECTION-SCREEN.

* Check logical systemSELECT COUNT * FROM tbdls BYPASSING BUFFERWHERE logsys = prlogsys.IF sy-subrc <> 0.MESSAGE e454(b1) WITH prlogsys.* The logical system & has not yet been definedENDIF.



* Get own logical systemCALL FUNCTION 'RSAN_LOGSYS_DETERMINE'EXPORTINGi_client = sy-mandtIMPORTINGe_logsys = l_slogsys.* Check if transfer rules exist for this extractor in BWSELECT SINGLE * FROM roosgen INTO l_s_roosgenWHERE oltpsource = c_oltpsourceAND rlogsys = prlogsysAND slogsys = l_slogsys.IF sy-subrc <> 0.

MESSAGE e025(rj) WITH prlogsys.* No transfer rules for target system &ENDIF.* Copy record for dummy BW systeml_s_roosgen-rlogsys = c_dlogsys.MODIFY roosgen FROM l_s_roosgen.IF sy-subrc <> 0.MESSAGE e053(rj) WITH text-002.* Update of table ROOSGEN failedENDIF.


* Assignment of source system to BW systemSELECT SINGLE * FROM rsbasidoc INTO l_s_rsbasidocWHERE slogsys = l_slogsysAND rlogsys = prlogsys.IF sy-subrc <> 0 OR( l_s_rsbasidoc-objstat = sbiw_c_objstat-inactive ).MESSAGE e053(rj) WITH text-003.* Remote destination not validENDIF.


* Copy record for dummy BW systeml_s_rsbasidoc-rlogsys = c_dlogsys.MODIFY rsbasidoc FROM l_s_rsbasidoc.IF sy-subrc <> 0.MESSAGE e053(rj) WITH text-004.* Update of table RSBASIDOC failedENDIF.


* Delta initializationsSELECT * FROM roosprmsc INTO TABLE l_t_roosprmscWHERE oltpsource = c_oltpsourceAND rlogsys = prlogsysAND slogsys = l_slogsys.IF sy-subrc <> 0.MESSAGE e020(rsqu).* Some of the initialization requirements have not been completedENDIF.

LOOP AT l_t_roosprmsc ASSIGNING .IF -initstate = ' '.MESSAGE e020(rsqu).* Some of the initialization requirements have not been completedENDIF.-rlogsys = c_dlogsys.-gottid = ''.-gotvers = '0'.-gettid = ''.-getvers = '0'.ENDLOOP.

* Delete old records for dummy BW systemDELETE FROM roosprmscWHERE oltpsource = c_oltpsourceAND rlogsys = c_dlogsysAND slogsys = l_slogsys.

* Copy records for dummy BW systemMODIFY roosprmsc FROM TABLE l_t_roosprmsc.IF sy-subrc <> 0.MESSAGE e053(rj) WITH text-005.* Update of table ROOSPRMSC failedENDIF.* Filter values for delta initializationsSELECT * FROM roosprmsf INTO TABLE l_t_roosprmsfWHERE oltpsource = c_oltpsourceAND rlogsys = prlogsysAND slogsys = l_slogsys.IF sy-subrc <> 0.MESSAGE e020(rsqu).


* Some of the initialization requirements have not been completedENDIF.LOOP AT l_t_roosprmsf ASSIGNING .-rlogsys = c_dlogsys.ENDLOOP.* Delete old records for dummy BW systemDELETE FROM roosprmsfWHERE oltpsource = c_oltpsourceAND rlogsys = c_dlogsysAND slogsys = l_slogsys.* Copy records for dummy BW systemMODIFY roosprmsf FROM TABLE l_t_roosprmsf.IF sy-subrc <> 0.MESSAGE e053(rj) WITH text-006.* Update of table ROOSPRMSF failedENDIF.


**************************************
COMMIT WORK for changed meta data
**************************************
COMMIT WORK.* Delete RFC queue of dummy BW system* (Just in case entries of other delta requests exist)CALL FUNCTION 'RSC1_TRFC_QUEUE_DELETE_DATA'
EXPORTING
i_osource = c_oltpsource
i_rlogsys =
c_dlogsysi_all = 'X'
EXCEPTIONS
tid_not_executed = 1
tid_not_executed = 1
client_not_found = 3

error_reading_queue = 4

OTHERS = 5.

IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgnoWITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.ENDIF.

********************************************
COMMIT WORK for deletion of delta queue
********************************************
COMMIT WORK.
* Get MAXLINES for data package
CALL FUNCTION 'RSAP_IDOC_DETERMINE_PARAMETERS'
EXPORTINGi_oltpsource = c_oltpsourcei_slogsys = l_slogsysi_rlogsys = prlogsysi_updmode = 'D 'IMPORTINGe_s_parameters = l_s_parameterse_subrc = l_subrc.

.IF l_subrc <> 0.MESSAGE e053(rj) WITH text-007.* Error in function module RSAP_IDOC_DETERMINE_PARAMETERSENDIF.* Transfer structure depends on transfer methodCASE l_s_roosgen-tfmethode.WHEN 'I'.

l_tfstruc = l_s_roosgen-tfstridoc.WHEN 'T'.l_tfstruc = l_s_roosgen-tfstruc.ENDCASE.* Determine transfer structure field listPERFORM fill_field_list(saplrsap) TABLES l_t_fieldsUSING l_tfstruc.* Start the delta extraction for the dummy BW systemCALL FUNCTION 'RSFH_GET_DATA_SIMPLE'

EXPORTINGi_requnr = 'DUMMY'i_osource = c_oltpsourcei_showlist = ' 'i_maxsize = l_s_parameters-maxlinesi_maxfetch = '9999'i_updmode = 'D 'i_rlogsys = c_dlogsysi_read_only = ' 'IMPORTING

e_lines_read = l_lines_readTABLESi_t_field = l_t_fieldsEXCEPTIONSgeneration_error = 1interface_table_error = 2metadata_error = 3error_passed_to_mess_handler = 4no_authority = 5OTHERS = 6.IF sy-subrc <> 0.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgnoWITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.ENDIF.

*********************************
COMMIT WORK for delta request
**********************************
COMMIT WORK.
** Delete RFC queue of dummy BW systemCALL FUNCTION 'RSC1_TRFC_QUEUE_DELETE_DATA'EXPORTINGi_osource = c_oltpsourcei_rlogsys = c_dlogsysi_all = 'X'EXCEPTIONStid_not_executed = 1invalid_parameter = 2client_not_found = 3error_reading_queue = 4OTHERS = 5.IF sy-subrc <> 0.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgnoWITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.ENDIF.* Data collection for 0FI_GL_4 delta queue successfulMESSAGE s053(rj) WITH text-008.

Publish exceptions

Publish exceptions


1.create query with exceptions
2.use central alert framwork:

.T-code ALRTCATDEF
.create alert category
.create containor element
.create text for alert message



.Then you can enter a text and a URL for a subsequent activity (optionally). E.g. you can add a link to a BI Query which should be checked by the recipient in order to react to the alert.

.In the last step of the alert category configuration you have to assign the alert to the end users. You can enter fixed recipients or roles. If you enter a role, all users that are assigned to that role will get the alert. You can also enter roles, if you press the button "Subscription Authorization". In that case the assigned users will have the option to subscribe for the alert later.

.In the next step you have to call the BEx Broadcaster and create an Information broadcasting setting based on the query, on which the exception has been defined on. As distribution type you have to choose "Distribute according to exceptions". In the details you can either choose the distribution type "Send Email" or "Create Alert", if you want to distribute the alert via the Universal Worklist. As selection criterion you can either choose to distribute all exceptions or you can choose a specific alert level. In our example we only want to distribute alerts, which have the level "Bad 3".

.Then you have to assign the corresponding alert category you have created before to your Information broadcasting setting.

.In the next step you have to do the mapping between the BI parameters of the Query and the alert container elements. These parameters will then be passed over to the alert.

.In the last step you have to save the Information Broadcasting setting. You can execute the setting directly or you can schedule the execution e.g. periodically each week.

.As a result you will see 2 new alerts in the Universal Worklist for all users which have been assigned to the alert corresponding alert category. You can access the Universal Worklist in the Enterprise Portal: Business Intelligence 􀃆Business Explorer 􀃆 Universal Worklist.



about DTP

DTP


.default It is recommended to configure the DTP with upload mode “Delta”. The deletion of the PSA data is necessary before each data load, if a “Full” DTP is used. A Full DTP extracts all Requests from the PSA regardless if the data has been already loaded or not. This means the Delta upload via a DTP from the DataSource (PSA) in the InfoCube is necessary, even if the data is loaded via a Full upload from the Source to the DataSource (PSA) by using an InfoPackage. ( which means load from PSA via DTP will load all data from PSA no matter the data were loaded before or not,so eother PSA should delted after load ,or DTP use delta even laod form Data source to PSA use delta already)
.Only get Delta Once:
.Get Data by Request: get the oldest request
.Get runtime information of a Data Transfer Process (DTP) in a Transformation : I will give detail in another blog .
.Debug a Data Transfer Process (DTP) Request:The debugging expert mode can be started from the execute tab of the DTP. The “Expert Mode” flag appears when the Processing Mode “Serially in the Dialog Process (for Debugging)” is selected.Choose “Simulate” to start the Debugger in expert mode.The debugging for loaded data can be executed from the DTP Monitor directly.Choose “Debugging”.