Generic Extractor based on Function Module

[Loed]

Hi guyz,

I still didn't get the solution in my problem below..

As an alternative, I'm planning to do a full load based on system date using the FM /POSDW/SEARCH_TLOG..I saw several documents but all of them are telling me to use the FM RSAX_BIW_GET_DATA_SIMPLE or RSAX_BIW_GET_DATA..

Some documents:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a0f46157-e1c4-2910-27aa-e3f4a9c8d...

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/30f1a423-02ae-2e10-bd85-bde64e61f...

Khoder Elzein's Blog: Create Generic Datasource using Function Module and Timestamps

I just want to ask if using the mentioned FM is the only way to do a generic extractor based on FM? I tried to use directly the FM /POSDW/SEARCH_TLOG and in the extract structure I used the ET_TRANSACTION in the EXPORT part of the FM..

But I got the error below:

I also tried to use the structure /POSDW/TRANSACTION_INT of the table above but also got this error:

I searched for some threads to solve the error above and got this..

Create Datasource with RSO2 & Function Module | SCN

But after copying the FM and creating my own table or structure to be included in the TABLES tab, I still encountered the same errors above..

Any idea how can I use the FM /POSDW/SEARCH_TLOG to create a GENERIC EXTRACTOR?

Thank you..

Loed

[Former Member]

Loed,

Please check following link.

I am assuming that your data source is working fine in RSA3.

[Loed]

Hi Fahim,

Sorry but the answer to questions 2 and 4 are both NO..Do you have other way?

Thank you..

Loed

[former_member186445]

after reading the complete thread, my suggestion.

create a program that uses FM /POSDW/SEARCH_TLOG.Put the export of the Fm in a Z table.

use the Z table as source for a generic datasource. simplest way to get it right.

[Loed]

Hi M,

Thanks for your suggestion..I will try it..

Loed

[Loed]

Hi guys,

I was able to make the FM work using the RSAX_BIW_GET_DATA_SIMPLE..

So I tried to extract data in RSA3 and I got the needed number of data records..

However, after replicating the datasource and extracting the same number of records in the PSA, the request is stuck in YELLOW status..It's always 0 of 965 records instead of 965 of 965 records and with GREEN status..Any idea why? Do I still need to setup something?

Thank you..

Loed

[former_member185132]

You need to use the "RAISE no_more_data" command in the FM. Usually this is put at the point in the code after the FETCH - so if the fetch results in a sy-subrc <> 0, that means there isn't any more data to be pulled. At this point the FM should raise the no_more_data exception.

The reason is that the datasource will keep calling the FM indefinitely until the no_more_data exception is thrown. RSA3's behaviour is different - it will only call the FM 10 times or until no_more_data, whichever comes earlier.

So enter that command at the appropriate point and the DS will complete execution.

[Loed]

Hi Suhas,

Thanks for your suggestion..I tried to use the "RAISE no_more_data" command but I got ZERO record instead..

FUNCTION zrsax_biw_get_data_simple.

*"----------------------------------------------------------------------

*"*"Local Interface:

*"  IMPORTING

*"     VALUE(I_REQUNR) TYPE  SRSC_S_IF_SIMPLE-REQUNR

*"     VALUE(I_DSOURCE) TYPE  SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL

*"     VALUE(I_MAXSIZE) TYPE  SRSC_S_IF_SIMPLE-MAXSIZE OPTIONAL

*"     VALUE(I_INITFLAG) TYPE  SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL

*"     VALUE(I_READ_ONLY) TYPE  SRSC_S_IF_SIMPLE-READONLY OPTIONAL

*"     VALUE(I_REMOTE_CALL) TYPE  SBIWA_FLAG DEFAULT SBIWA_C_FLAG_OFF

*"  TABLES

*"      I_T_SELECT TYPE  SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL

*"      I_T_FIELDS TYPE  SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL

*"      E_T_DATA STRUCTURE  YTEST_POSDM OPTIONAL

*"  EXCEPTIONS

*"      NO_MORE_DATA

*"      ERROR_PASSED_TO_MESS_HANDLER

*"----------------------------------------------------------------------

   DATA:

     test_table  TYPE  /posdw/tt_transaction_int,

     test_wa     LIKE LINE OF  test_table,

     test_result TYPE /posdw/tt_transaction_int.

   DATA: temp_result TYPE /posdw/tt_transaction_int.

   DATA: wa_e_t_data LIKE LINE OF e_t_data.

   DATA: lv_count TYPE i.

* Example: DataSource for table SFLIGHT

   TABLES: ytest_posdm.

* Auxiliary Selection criteria structure

   DATA: l_s_select TYPE srsc_s_select.

* Maximum number of lines for DB table

   STATICS: s_s_if              TYPE srsc_s_if_simple,

* counter

            s_counter_datapakid LIKE sy-tabix,

* cursor

            s_cursor            TYPE cursor.

*  IMPORT temp_result FROM MEMORY ID 'TAB'.

* Initialization mode (first call by SAPI) or data transfer mode

* (following calls) ?

   IF i_initflag = sbiwa_c_flag_on.

************************************************************************

* Initialization: check input parameters

*                 buffer input parameters

*                 prepare data selection

************************************************************************

* Check DataSource validity

     CASE i_dsource.

       WHEN 'YTEST'.

       WHEN OTHERS.

         IF 1 = 2. MESSAGE e009(r3). ENDIF.

* this is a typical log call. Please write every error message like this

         log_write 'E'                  "message type

                   'R3'                 "message class

                   '009'                "message number

                   i_dsource   "message variable 1

                   ' '.                 "message variable 2

         RAISE error_passed_to_mess_handler.

     ENDCASE.

     APPEND LINES OF i_t_select TO s_s_if-t_select.

* Fill parameter buffer for data extraction calls

     s_s_if-requnr    = i_requnr.

     s_s_if-dsource = i_dsource.

     s_s_if-maxsize   = i_maxsize.

* Fill field list table for an optimized select statement

* (in case that there is no 1:1 relation between InfoSource fields

* and database table fields this may be far from beeing trivial)

     APPEND LINES OF i_t_fields TO s_s_if-t_fields.

   ELSE.     

************************************************************************

* Data transfer: First Call      OPEN CURSOR + FETCH

*                Following Calls FETCH only

************************************************************************

     IF s_counter_datapakid = 0.

     ENDIF.                          

     CALL FUNCTION '/POSDW/SEARCH_TLOG'

* EXPORTING

*   I_READTRANSDETAILS            = 'X'

*   I_RETAILSTOREID               =

*   I_BUSINESSDAYDATE             =

*   I_NO_SORTING                  =

*   IS_SELECTION                  =

*   I_USE_TREX                    = '1'

*   I_ONLY_GET_KEY                =

       IMPORTING

         et_transaction = test_result

* CHANGING

*       C_SOURCE       =

       .

     IF  test_table[] IS INITIAL.

       APPEND LINES OF test_result TO test_table.

     ENDIF.

     READ TABLE i_t_select WITH KEY fieldnm = 'BUSINESSDAYDATE'.

     IF sy-subrc EQ 0.

       IF i_t_select-low IS NOT INITIAL AND i_t_select-high IS INITIAL .

         LOOP AT test_table INTO test_wa WHERE businessdaydate EQ i_t_select-low.

           MOVE-CORRESPONDING test_wa TO wa_e_t_data.

           APPEND wa_e_t_data TO e_t_data.

         ENDLOOP.

       ELSEIF i_t_select-low IS NOT INITIAL AND i_t_select-high IS NOT INITIAL .

         LOOP AT test_table INTO test_wa WHERE businessdaydate GE i_t_select-low

                                           AND businessdaydate LE i_t_select-high.

           MOVE-CORRESPONDING test_wa TO wa_e_t_data.

           APPEND wa_e_t_data TO e_t_data.

         ENDLOOP.

       ENDIF.

     ELSE.

       LOOP AT test_table INTO test_wa.

         MOVE-CORRESPONDING test_wa TO wa_e_t_data.

         APPEND wa_e_t_data TO e_t_data.

       ENDLOOP.

     ENDIF.

     IF sy-subrc = 0.

       RAISE no_more_data.

     ENDIF.

     s_counter_datapakid = s_counter_datapakid + 1.

   ENDIF.

ENDFUNCTION.

Screenshots:

Did I use the RAISE no_more_data correctly?

Thank you..

Loed

[former_member185132]

So the logic seems to be that the POSDW FM is called just once and it gives you all the results required. In other words the POSDW FM (and therefore the extractor FM itself) needs to be called just once.

If that understanding of mine is correct, then we need a bit of a change in the code. You can remove the  current no_more_data statement and the IF statement around it as well.

Then, go to the IF statement just before the POSDW FM call and change it to this:

 IF s_counter_datapakid > 0.
      RAISE no_more_data.
ENDIF.                     

[Loed]

Hi Suhas,

Yes you are right, the POSDW FM should only be called once..

After doing your suggested changes, I tried to re-extract data but request is still stuck in yellow..

Do I still need to do some changes in the code?

Thank you so much..

Loed

[Former Member]

Hi Loed,

I think you can try to remove the IF-Statement around RAISE no_more_data.

[Loed]

Hi Peter,

I already tried it but it also gave me ZERO record..

Thank you..

Loed

[Former Member]

Hi Loed,

I can imagine, that at the fist call you have to select/send the data, and at the second call of FM you don't send the data but generate RAISE no_more_data.

That is the common way for use of the FM DataSource.

Peter

[Loed]

Hi Peter,

Yes, I think the code is already functioning like it? The selection first of data then RAISE no_more_data afterwards? I just don't know why request is always stuck in YELLOW..

Is the RAISE no_more_data the only way to make the request auto-GREEN?

Thank you..

Loed

[Former Member]

Hi Loed, the no_more_data is to mark the completion of extraction. Which turns the signal to green.

I would suggest to change the code in one block as the all the data is supplied in one package.

if sy-subrc is INITIAL.

        RAISE no_more_data.

        s_counter_datapakid = s_counter_datapakid + 1.

ENDIF.

Also please check the SPRO->business wearhouse->automated process->set traffic light

Thanks

[former_member185132]

Hi Loed,

Can you post the code as it was after making the changes I suggested? It should work as-is, but please post it so I can see if there's been any mistake from your end or mine.

The Raise no more data is mandatory - without that BW will think that ECC still has still more data to send. That is why the load remains in yellow, as BW is expecting data. Also, if the exception is raised in a particular call of the FM, BW does not take any data for that particular call. So the extraction (filling up e_t_data) must happen in one call and the raise in the next call. If you post your code I can go through and let you know if that's happening.

Regards,

Suhas

[Loed]

Hi Suhas,

Please see codes below:

Thank you..

Loed

[Loed]

Hi Jugal,

I tried your suggestion but it also gives me ZERO record..

This is the traffic light in our SPRO..

Thank you..

Loed

[Former Member]

Hi Loed,

I would expect if no data is available .. status as green instead of yellow.

Also please check if any  delta is enable in the source system  ?

Are you passing the value of BUSINESSDAYDATE in your info package selection.. else pl remove them if not required at all.

Ideally if you are able to see the results in RSA3 you should get data in BW with the same selection.

If you try preview data in your DS what do you get ? does it gives any error ?

Thanks,

[former_member185132]

Please put a breakpoint at the line near the end where it increments s_counter_datapakid and execute it in RSA3. I want to understand if it is indeed hitting that line and incrementing the field. Can you do that please?

If it is incrementing, also put a breakpoint at the IF statement just before the NO_MORE_DATA. Need to check the s_counter_datapakid at each call at this statement too.

[Former Member]

Hi Loed,

could you try to make the folloing changes:

1. Change "ENDIF" on the line 244 to the ELSE

2. put ENDIF at the line 285.

I hope it help you

Peter

[Loed]

Hi Jugal,

I'm using this datasource for FULL loading only..This is just an alternative since I still can't fix the standard datasource related to this..

Yes, I'm using the BUSINESSDAYDATE to filter in infopackage level..

The preview of datasource is the same with my screenshot above..

Thank you..

Loed

[Loed]

Hi Peter,

Same result..I was able to extract the needed number of records but request is still stuck in yellow..

Thank you..

Loed

[Loed]

Hi Suhas,

Please see screenshots below..

I think the FM is working fine based on the result when I DEBUGGED it..The code is indeed hitting the RAISE no_more_data..I just don't know why request is always stuck in YELLOW..

Thank you..

Loed

[former_member185132]

OK, when you run the InfoPackage from BW, it would create a job in ECC. The job name would be "BIREQU_XXX", where REQU_XXX is the request ID shown in the Infopackage monitor.

Please check if the job in ECC is completed, and if not please post the job log.

[Loed]

Hi Suhas,

This is a POSDM extractor..POSDM is located inside the BW server..POSDM and BW resides in a single box..

The request is finished but the request is in YELLOW status..This is the log:

Thank you..

Loed

[former_member185132]

I don't see a log. Did you attach it?

Anyhow, if the job has successfully completed, it means that the no_more_data was indeed raised and the datasource finished executing. So the problem is likely in the IDOCs/qRFCs on the Infopackage side. Just check on SCN about those issues.

[Loed]

Hi Suhas,

Sorry the picture disappeared after posting..Here it is..

Thank you.

Loed

[former_member185132]

So the job finished successfully here and returned 6 records as well, which means the FM and Datasource are working fine. The problem is in the IDOCs/qRFCs as I said in the previous reply. So please have a look at that.

[Former Member]

Hi Loed, if you debug your code via RSA3, do you see the NO_MORE_DATA exception is triggered and s_counter_datapakid is not zero. (the internal table E_T_DATA also should contain the value).

Thanks

[Loed]

Hi Suhas,

Ok I will search for a note related to it..

Thank you so much for your time..

Loed

[Loed]

Hi Jugal,

Please see screenshots below..

I think the FM is working fine based on the result when I DEBUGGED it..The code is indeed hitting the RAISE no_more_data..I just don't know why request is always stuck in YELLOW..

Also, E_T_DATA has values..

Thank you..

Loed

[anshu_lilhori]

Please try adding these lines above S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.

IF S_COUNTER_DATAPAKID = 1.

RAISE NO_MORE_DATA.

endif.

I hope this works.

Regards,

AL

[Loed]

Hi Anshu,

After adding the lines below, the datasource gives me ZERO record..

IF S_COUNTER_DATAPAKID = 1.

RAISE NO_MORE_DATA.

endif.

Thank you..

Loed

[Former Member]

Hi Loed,

The  counter is incremented in the end and the raise with if condition to counter is in the beginning.

Please try with comment the raise clause  in  line 98.

In line number 193, you can try to put this code instead of existing code.

s_counter_datapakid  = 1 . " Alwasys it ends in 1st data package .

RAISE no_more_data.

Thanks

[Loed]

Hi Suhas,

Is this IDOC/RFC problem for BASIS team? I mean, how will I explain to BASIS team this scenario so they can fix it? By the way, POSDM (this extractor) and BW resides in the same server..

Thank you..

Loed

[former_member185132]

Not necessarily a basis issue. Sometimes the data gets stuck in the IDOC/qRFC stage. Just search on the forums, its quite a common problem in BW data loads.

[Loed]

Hi Jugal,

Same result, the status is still stuck in YELLOW..Maybe this is really related with the RFC / iDOC as what Suhas said..

I will try to search for it..

Thank you for your time..

Loed

[Former Member]

Hi Loed,

for me the best way to test the connection is to activate an "easy" ( at example a Text) DataSource from Business Context and test at first with this DataSource. If it is OK, you have a large chance that the connection is OK.

If not, please check the TA WE20 on both side of connection. Under "Partner Type LS" check for your Partner System and in the tab "Classification" the partner status must have status "A" aktive.

Peter

[Loed]

Hi Peter,

Thanks a lot for the idea..I tried to create a dummy datasource using a table and it is also stuck in YELLOW status..So idoc / rfc connection really is the problem as Suhas said..

About the WE20, here is the screenshot..Is the partner class required? I tried to check other servers but for some datasources the partner class is BLANK (as in space)..By the way, PMD is the server being used for BW and POSDM, they share the same server..As we can see the status is ACTIVE..What do you think is the possible cause of the YELLOW status? Can it be fixed here in WE20?

Thank you..

Loed

[Former Member]

Hi Loed,

This is the right state. I think we can't correct anything at this place.

Have you checked the system connection?

You have to get the "green" message... If not, put please the message in the thread.

Best regards

Peter

[Loed]

Hi,

Yes I already checked the SOURCE SYSTEM, it's working fine..The PMD is the system for POSDM (this extractor) and the BW..They reside in the same box..

Thank you..

Loed

[Former Member]

Hi Loed,

I think this document can help you.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70974b03-f90b-3110-60a4-c73ccda3f...

QuickLink=index&overridelayout=true&58725087840398

Best regards

Peter

[Loed]

Hi,

Thank you for the link, will have a look at it..I will also ask the BASIS team to check it..

Loed

[sander_vanwilligen]

Hi Loed,

The main reason for using a Function Module extractor is the scalability. The OPEN CURSOR and FETCH are meant to retrieve and process a data package with a certain package size.

However, it must be used in conjunction with a SELECT statement on a single table or join on multiple tables. It doesn't work directly in conjunction with Function Module /POSDW/SEARCH_TLOG.

I suggest to analyze Function Module /POSDW/SEARCH_TLOG. Maybe you can reuse the logic for the Function Module extractor. If it turns out to be difficult or cumbersome to reuse the logic, then it might be better to look for alternative solutions.

Best regards,

Sander

[Loed]

Hi Sander,

Thank you for your input..

I will also try to look for alternative or maybe do the suggestion of Raf..

Loed

[Former Member]

Hi Loed,

I don't know the FM /POSDW/SEARCH_TLOG. But I can see that it export a internal table.

At first you can put in the FB Z_POSDW_SEARCH_TLOG for the Parameter E_T_DATA the type /POSDW/TRANSACTION_INT. It need a structure type and not table type.

In the DataSource FM you call the FM /POSDW/SEARCH_TLOG and loop the result data to internal table E_T_DATA.

Cursor..fetch is very helpfull vor select of defined amount of data. It is necessary, if you

want to extract only a part of data.

best regards

Peter

[former_member185132]

Hi Loed,

The RSAX_* FMs are the right templates to use for creating a Datasource. The reason is that the Datasource expects a certain set of Importing, Exporting and Changing parameters in the FM. You can ensure that only by copying one of the RSAX* FM.

So to meet this requirement, please copy an RSAX* template and call the  /POSDW/SEARCH_TLOG in the code

Regards,

Suhas

[Loed]

Hi Suhas,

So to meet this requirement, please copy an RSAX* template and call the /POSDW/SEARCH_TLOG in the code..

Can you guide me in the CALL the /POSDW/SEARCH_TLOG part?

I already copied the RSAX_BIW_GET_DATA_SIMPLE based on this document:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a0f46157-e1c4-2910-27aa-e3f4a9c8d...

But I am stuck in PAGE 9, I already did all steps until ENDLOOP command..How can I use the /POSDW/SEARCH_TLOG in the FM RSAX_BIW_GET_DATA_SIMPLE?

Thank you..

Loed

[former_member185132]

Remove the OPEN CURSOR and FETCH statements if you don't see a use for them.

Then, at the place where FETCH was written, write the CALL FUNCTION ... command to call the /POSDW/ FM.

The basic idea in the FM is that the E_T_DATA should be filled up, as that is the data that will go to BW. So where the FETCH was, you write the CALL FUNCTION and then take the results and fill up E_T_DATA.

[RafkeMagic]

hmmm... if you remove the open cursor & fetch, then you're not really using the most important parameters of the function, are you?

there are better ways to do so then (for example: check out this link for more information)

[former_member185132]

OK, I wasn't aware that you could write a program for Infoset queries. Thanks for highlighting that, I agree that is a better approach than an FM.