Read on SAP BI InfoCubes or InfoProvider via RSDRI_INFOPROV_READ or class CL_RSDRI_INFOPROV

As you SAP Gurus out there knew, it is quite hard to read directly from InfoProviders. One of the reasons why you create DatastorageObjects is to have the possibility to read on it via SELECT.

The nice people of SAP has produced a function method which allows you, with proper use – to read directly in your InfoProviders and select as simple as you like. You may wonder what i’m talking about. I talk about the function RSDRI_INFOPROV_READ or the class cl_rsdri_infoprov. Many good men and women had try to solve the use of this freakin‘ module and has suffered from it.

I hope that the followin code examples will light your days as it has mine. Thanks, rewards, updates, comments are very welcome.
Download a TXT:read_from_infoprovider_template.abap.txt
New version with Class is here: cl_rsdri_infoprov.abap.template

  • Update 06.2012: it runs some time – so now i ‚d come to update this template. I know you are using it out there so – here is an update to object oriented abap with class cl_rsdri_infoprov rather than the function-module.

Be honest – if we had encountered in the pub – you had sponsored me a beer for this, right?








*Copyright (c) 2012 J.Rumpf, www.web-dreamer.de / BSD Licence
*All rights reserved.
*
*Redistribution and use in source and binary forms, with or without modification
*are permitted provided that the following conditions are met:
*
* * Redistributions of source code must retain the above copyright notice, th
* is list of conditions and the following disclaimer.
* * Redistributions in binary form must reproduce the above copyright notice,
* this list of conditions and the following disclaimer in the documentation
* and/or other materials provided with the distribution.
* * Neither the name of the owner nor the names of its contributors may be used
* to endorse or promote products derived from this software without specific
* prior written permission.
*
*THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
*ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
*WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
*DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
*FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
*DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
*SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
*CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
*OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
*OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
TYPE-POOLS: rsdrc.
CONSTANTS: cube TYPE rsinfoprov VALUE 'ZYOURINFOPROVIDER'.
DATA: cube_struct TYPE string,
fact_struct TYPE string.
"generic structure with ALL Characteristics and Keyfigures of the Cube
CONCATENATE '/bic/v' cube 'j' INTO cube_struct.
* cube_struct = 'YOUR_STRUCTURE'.
"Facttable of the Cube - be carfull in Big Cubes - there is an 'f' and an 'g'
CONCATENATE '/bic/v' cube 'f' INTO fact_struct.

TYPES:
t_cube_struct TYPE TABLE OF /bic/vzyourinfoproviderj „== Set on YOUR Target Structure or the content of cube_struct !!!
WITH DEFAULT KEY INITIAL SIZE 0.

DATA:
e_th_sfc TYPE rsdri_th_sfc, „Characteristics of the Cube
e_th_sfk TYPE rsdri_th_sfk, „Keyfigures of the cube

line_of_e_th_sfc TYPE rsdri_s_sfc,
line_of_e_th_sfk TYPE rsdri_s_sfk,

ddic TYPE dd_x031l_table, „Datadictonary for later use
ldic LIKE LINE OF ddic, „Ddic row
fact_ddic TYPE dd_x031l_table, „fact_ddic

my_structure TYPE REF TO data, „Structure of DDIC

g_t_data TYPE t_cube_struct, „table-typ of return-table
t_all_data TYPE t_cube_struct, „Table-type of return-table
c_first_call TYPE rs_bool, “ First-call
r_seloption TYPE rsdri_t_range, „Selection-Table
w_seloption TYPE LINE OF rsdri_t_range, „Selection-Row
end_of_data TYPE rs_bool, „Last Call
infoprov TYPE rsinfoprov “ Infoprovider = cube
.
**********************************************************************
„Sometimes your Structure has Columns you don’t have in your Cube
“ Here you can add this columns to be ignored by the cube.
“ !!! Please note – the function will aggregate on it’s standard key figure behaviour
“ if you don’t add all relevant primary keys!

DATA: ignore_list TYPE STANDARD TABLE OF fieldname.
ldic-fieldname = ‚MY_IGNORE_COLUMN_NAME‘. APPEND ldic-fieldname TO ignore_list.

**********************************************************************
“ From here you go with your selection on your cube – for each selection
“ you need to copy the following lines
DATA: z_infoobjekt TYPE /bic/oiz_infoobjekt. “ == Important because of the selection-type

“ Selection-Condition:
z_infoobjekt = ‚my_selection‘.
w_seloption-chanm = ‚Z_INFOOBJEKT‘. „technical name of InfoObjekt
w_seloption-sign = ‚I‘. “ Include
w_seloption-compop = ‚EQ‘. “ EQUAL
w_seloption-low = z_infoobjekt. „

APPEND w_seloption TO r_seloption.

**********************************************************************
* No change from here necessary.

FIELD-SYMBOLS: TYPE ANY.
DATA: fact_ref TYPE REF TO cl_abap_structdescr.
CREATE DATA my_structure TYPE (fact_struct). „Struktur-Objekt der Faktentabelle lesen
fact_ref ?= cl_abap_tabledescr=>describe_by_data_ref( my_structure ).
CALL METHOD fact_ref->get_ddic_object „DDIC Beschreibung auslesen
RECEIVING
p_object = fact_ddic
EXCEPTIONS
not_found = 1
no_ddic_type = 2
OTHERS = 3.

DATA: descr_ref TYPE REF TO cl_abap_structdescr.
CREATE DATA my_structure TYPE (cube_struct).
descr_ref ?= cl_abap_tabledescr=>describe_by_data_ref( my_structure ). „Struktur-Objekt der zu lesenden Objekte lesen
CALL METHOD descr_ref->get_ddic_object “ DDic Beschreibung auslesen
RECEIVING
p_object = ddic
EXCEPTIONS
not_found = 1
no_ddic_type = 2
OTHERS = 3.

DATA: wa_rol LIKE ldic-fieldname.
LOOP AT ddic INTO ldic. “ Iterate over all relevant Keyfigures
READ TABLE ignore_list FROM ldic-fieldname INTO wa_rol.
IF sy-subrc NE 0.
READ TABLE fact_ddic WITH KEY rollname = ldic-rollname ASSIGNING . „Compare if rollname of fact-table is in Cube-structure – if it is ne 0;
IF sy-subrc NE 0. “ Key-Figure
line_of_e_th_sfc-chanm = ldic-fieldname.
INSERT line_of_e_th_sfc INTO TABLE e_th_sfc.
ELSE. “ must be a characteristic
line_of_e_th_sfk-kyfnm = ldic-fieldname. „
INSERT line_of_e_th_sfk INTO TABLE e_th_sfk. “ !! Be carefull by Cubes with many keyfigures see above !!
ENDIF.
ENDIF.
ENDLOOP.

infoprov = cube. “ Just for type-matching.
DATA: s_r_infoprov TYPE REF TO cl_rsdri_infoprov.

WHILE ( end_of_data NE ‚X‘). “ As long as not end_of_data

CREATE OBJECT s_r_infoprov
EXPORTING
i_infoprov = infoprov
EXCEPTIONS
illegal_input = 1
OTHERS = 2.

CALL METHOD s_r_infoprov->read
EXPORTING
i_th_sfc = e_th_sfc
i_th_sfk = e_th_sfk
i_t_range = r_seloption
* i_th_tablesel = i_th_tablesel
* i_t_rtime = i_t_rtime
* i_reference_date = i_reference_date
* i_t_requid = i_t_requid
* i_save_in_table = i_save_in_table
* i_tablename = i_tablename
* i_save_in_file = i_save_in_file
* i_filename = i_filename
i_packagesize = 999999 „set to a maxvalue – try for your system
* i_maxrows = i_maxrows
i_authority_check = rsdrc_c_authchk-none „use if you want to check auth
* i_currency_conversion = i_currency_conversion
* i_use_db_aggregation = i_use_db_aggregation
* i_use_aggregates = i_use_aggregates
* i_rollup_only = i_rollup_only
* i_read_ods_delta = i_read_ods_delta
* i_caller = i_caller
* i_debug = i_debug
IMPORTING
e_t_data = g_t_data
e_end_of_data = end_of_data
* e_aggregate = e_aggregate
* e_split_occurred = e_split_occurred
* e_t_msg = e_t_msg
* e_stepuid = e_stepuid
EXCEPTIONS
illegal_download = 1
illegal_input = 2
illegal_input_range = 3
illegal_input_sfc = 4
illegal_input_sfk = 5
illegal_input_tablesel = 6
illegal_tablename = 7
inherited_error = 8
no_authorization = 9
trans_no_write_mode = 10
x_message = 11.

IF sy-subrc NE 0.
RAISE EXCEPTION TYPE cx_static_check.
ENDIF.


APPEND LINES OF g_t_data TO t_all_data.
ENDWHILE.


Beitrag veröffentlicht

in

, ,

von

Kommentare

21 Antworten zu „Read on SAP BI InfoCubes or InfoProvider via RSDRI_INFOPROV_READ or class CL_RSDRI_INFOPROV“

  1. Avatar von Joe

    Updated template available in the article. Btw. The code is still valid for HANA powered BW systems – and works very well 🙂

  2. Avatar von Joe
    Joe

    Just a little update:

    I used this function to be in a virtual infoprovider and used the same function to read on the virtual infoprovider. This does not work! It raises an x_message.

    So just use the following instead of the RSDRI_INFOPROV_READ:


    data: infoprov type rsinfoprov " Infoprovider = cube
    infoprov = cube. " Just for type-matching.

    data: s_r_infoprov type ref to cl_rsdri_infoprov.

    create object s_r_infoprov
    exporting
    i_infoprov = infoprov
    exceptions
    illegal_input = 1
    others = 2.

    call method s_r_infoprov->read
    exporting
    i_th_sfc = e_th_sfc
    i_th_sfk = e_th_sfk
    i_t_range = r_seloption
    * i_th_tablesel = i_th_tablesel
    * i_t_rtime = i_t_rtime
    * i_reference_date = i_reference_date
    * i_t_requid = i_t_requid
    * i_save_in_table = i_save_in_table
    * i_tablename = i_tablename
    * i_save_in_file = i_save_in_file
    * i_filename = i_filename
    i_packagesize = 99999
    * i_maxrows = i_maxrows
    i_authority_check = rsdrc_c_authchk-none "use if you want to check auth
    * i_currency_conversion = i_currency_conversion
    * i_use_db_aggregation = i_use_db_aggregation
    * i_use_aggregates = i_use_aggregates
    * i_rollup_only = i_rollup_only
    * i_read_ods_delta = i_read_ods_delta
    * i_caller = i_caller
    * i_debug = i_debug
    importing
    e_t_data = g_t_data
    e_end_of_data = end_of_data
    * e_aggregate = e_aggregate
    * e_split_occurred = e_split_occurred
    * e_t_msg = e_t_msg
    * e_stepuid = e_stepuid
    exceptions
    illegal_download = 1
    illegal_input = 2
    illegal_input_range = 3
    illegal_input_sfc = 4
    illegal_input_sfk = 5
    illegal_input_tablesel = 6
    illegal_tablename = 7
    inherited_error = 8
    no_authorization = 9
    trans_no_write_mode = 10
    x_message = 11.

    if sy-subrc ne 0.
    raise exception type cx_static_check.
    endif.

    This will work even if you call it on a virtual infoprovider as described above.

  3. Avatar von Pedro
    Pedro

    Hi Joe, thanks for the post, helped me a lot.

    I got a performance issue here. Im using the FM RSDRI_INFOPROV_READ to read an infocube that has a lot of data and the FM is taking too long – about 10 minutes – to return the values to my main program.

    I’m trying to use aggregates setting both I_ROLLUP_ONLY and I_USE_AGGREGATES as rs_c_true, but I’m not sure if the FM is actually using them.

    Do you know if BW consider those access when it shows the usage statistics of the aggregates?

    1. Avatar von Joe

      Hey,
      i think it uses aggregates – but had never tried it myself. You can test it – when monitoring transaction SM50 by looking at the tables the process read. It should read then on the tables of the aggregate.

      If it takes that long – can you try to run it as background job? Another way is to split the read into one ore more background jobs with part-selections.

      A Background-Read looks like this:


      data: con_task(8) value '00009999',
      lv_info like rfcsi.
      call function
      'RSDRI_INFOPROV_READ_RFC'
      starting new task con_task
      performing result on end of task
      *** xxx ***
      form result using con_task.

      receive results from function 'RSDRI_INFOPROV_READ_RFC'
      importing
      e_end_of_data = lv_info
      * E_RFCDATA_UC = g_uc_string
      tables
      i_t_sfc = g_t_mak_sfc
      i_t_sfk = g_t_mak_sfk
      i_t_range = g_t_mak_range
      e_t_rfcdata = g_t_rfcdata
      * E_T_RFCDATAV =
      e_t_field = g_t_field

      exceptions
      communication_failure = 1
      system_failure = 2.
      endform.

  4. Avatar von Joe
    Joe

    Hi Jorge,
    i don’t understand your question. You can use the code as you please – as long as you respect the bsd-license (which effectivly means to keep the comments about the bsd license and the orignal source owner).
    Or do you mean something else?

  5. Avatar von Jorge
    Jorge

    Hi Joe,

    Can I use your code in BW infocubes?

    Kind regards

  6. Avatar von Joe
    Joe

    Dear Ashir,
    try this please:
    [cc lang=“ABAP“]DATA: z_infoobjekt TYPE /bic/oiz_infoobjekt. “ == Important because of the selection-type[/cc]
    it is possible that the range doesn’t have the right format so:
    [cc lang=“ABAP“]
    LOOP AT s_plant.
    g_s_range-chanm = ‘GCSLOCT’. „This looks wrong – your infoobject should start with ‚0‘ or with ‚Z‘
    g_s_range-sign = ‘I’.
    g_s_range-compop = ‘EQ’.

    clear z_infoobjekt. z_infoobjekt = s_plant-low.
    g_s_range-low = z_infoobjekt. “changes for looping sceanrio

    APPEND g_s_range TO g_t_range.
    ENDLOOP.
    [/cc]
    Also look in the example where this was in the selection-tab:
    [cc lang=“ABAP“]
    “ Selection-Condition:
    z_infoobjekt = ‚my_selection‘.
    w_seloption-chanm = ‚Z_INFOOBJEKT‘. „technical name of InfoObjekt
    w_seloption-sign = ‚I‘. “ Include
    w_seloption-compop = ‚EQ‘. “ EQUAL
    w_seloption-low = z_infoobjekt. “

    APPEND w_seloption TO r_seloption.[/cc]

  7. Avatar von ashir
    ashir

    Hi all,
    The example you’d given for info_reader is very helpful and i thank you all for that
    Here i am facing a problem with the parameter ‚i_t_range‘ – select options, I have filled elements of this parameter as shown but regarless of my selection it brings some more data from cube. It would be very much helpful for me if somebody can resolve my issue. The value apppending to i_t_range is as shown below,
    [cc lang=“ABAP“]LOOP AT s_plant.
    g_s_range-chanm = ‚GCSLOCT‘.
    g_s_range-sign = ‚I‘.
    g_s_range-compop = ‚EQ‘.
    g_s_range-low = s_plant-low. „changes for looping sceanrio
    APPEND g_s_range TO g_t_range.
    ENDLOOP. [/cc]
    It should fetch data for the plant which i given in s_plant-low(select option) only but it returns other plants too in the output internal table e_t_data.
    This is for plant, and I am appending some material,version and period into the i_t_range table and for which it returns the corrct values only.The problem’s with the plant input selection only.
    so can anybody help me with this,,
    thanks in advance.

    Ashir ck

  8. Avatar von Joe

    Nice one – thank you very much for sharing this peace of information. But in order to use the aggregation-behaviour of the cube you should use the function [cc lang=“ABAP“]RSDRI_AGGREGATE_DATA[/cc] – if i may suggest…

  9. Avatar von Nastya
    Nastya

    Got it!
    In order to reach data in „yellow“ requests one should run RSDRI_INFOPROV_READ with following parameters:
    [cc lang=“ABAP“] I_ROLLUP_ONLY = rs_c_false[/cc]
    together with (! necessary!)
    [cc lang=“ABAP“]I_T_REQUID = lt_reqrange[/cc]
    where lt_reqrange is filled exactly with the „yellow“ request only.
    This sample code will let you fill „lt_reqrange“ correctly:
    [cc lang=“ABAP“]
    DATA:
    ls_reqrange TYPE RSDR0_S_REQUID ,
    lt_reqrange TYPE RSDR0_T_REQUID .
    * Forming restrictions by request
    CLEAR lt_reqrange.
    DATA: is_request type mch_s_part ,
    it_request type standard table of mch_s_part .
    * Get a list of all requests in your info-cube
    CALL FUNCTION ‚RSSM_ICUBE_REQUESTS_GET‘
    EXPORTING
    I_INFOCUBE = ‚YOURINFOCUBE‘
    * I_RSMDATASTATE_UPDATE = ‚ ‚
    * I_DATE_FROM = ‚00000000‘
    I_DATE_TO = ‚99991231‘
    * I_WITHOUT_TEXTS = ‚ ‚
    IMPORTING
    E_T_REQUEST = it_request
    * EXCEPTIONS
    * WRONG_INFOCUBE = 1
    * INTERNAL_ERROR = 2
    * OTHERS = 3
    .
    IF SY-SUBRC 0.
    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
    * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.
    * Select from the list the yellow one and insert it into lt_reqrange
    LOOP AT it_request into is_request WHERE report IS INITIAL .
    ls_reqrange-OPT = ‚EQ‘.
    ls_reqrange-REQLOW = is_request-partnr .
    ls_reqrange-REQHIGH = “ .
    INSERT ls_reqrange INTO TABLE lt_reqrange.
    ENDLOOP .
    [/cc]
    In order to get ALL data from the infocube you just need to call RSDRI_INFOPROV_READ twice, second time without any restrictions by request and rollup mode but then don’t forget to COLLECT keyfigures.
    All the best!

  10. Avatar von Joe

    Hi Nastya,
    are you aware that the Request-ID have – in fact – two kinds of keys – and the question is which one of this keys the parameter wants?

    The Request-IDs can have a ID that looks like:
    [cc lang=“ABAP“]17131[/cc]
    or
    [cc lang=“ABAP“]DTPR_7D9W4BR1JWCD7FKQLJU4RWAJN[/cc]

    Just because you quoted that you got also a zero for sending a „green“ Request in – that is not what i would expect.

  11. Avatar von Nastya
    Nastya

    Thank you for reply, Joe!
    So far there’s not much to report – I_T_REQUID seems to be of no use as well.
    When limited to „yellow“ REQID it returns zero values for both – new and old (=“green“) records. That meaning that „most recent“data can’t be reached yet.
    I’m aware about a 3.5 variable, but when looked at a generated query programm in RSRT, I didn’t find any *INFOPROV*READing FM there – it seems that BEx queries amazingly use another mechanism of reading data..
    Anyway, thank you again for taking time to answer!

  12. Avatar von Joe

    Hi Nastya,
    that is in fact a good question. I haven’t tried this yet for myself. [cc lang=“ABAP“]I_ROLLUP_ONLY[/cc] and [cc lang=“ABAP“]I_USE_AGGREGATES[/cc] should not work from their description.

    I would suggest to use [cc lang=“ABAP“]I_T_REQUID [/cc]with the Request-ID(s) of the „yellow“ request. I don’t know if you have to use also all other requests of the Cube…
    In old BW3.5 times there was a variable to set in the planing-query to get the „yellow“ requests…

    I knew you know got the Problem of „where the hell shall i got the Requ.Ids“ – but i can’t help you on that for fast. Maybe you just try if this work. I would be happy if you share the result here 🙂

  13. Avatar von Nastya
    Nastya

    Hi Joe! Thank you for a very helpful piece – I’m sure this will avoid many problems that many of us have.
    However, I personally didn’t succeed in making use of RSDRI_INFOPROV_READ when getting „most recent data“ from InfoCube (=“yellow“ request containing some new data inserted by planning via APO interface).
    Perhaps, you could advise some parameter in this Function Module that can fix it? (I tried I_USE_AGGREGATES and I_ROLLUP_ONLY – all in vain :((
    Best regards,
    Nastya

  14. Avatar von Hauke
    Hauke

    Hi Joe,
    thanks for your fast reply!
    a) or b) do not apply.
    I also changed your program to prevent the InfoObjekt from being inserted into e_th_sfc and addet 0Currency instead. Now the error message states correctly, that 0Currency is not part of the cube…

    If I find reason and solution, I will let you know
    Hauke

  15. Avatar von Joe

    Hi Hauke,
    hmm – the error-message seems to say that this infoobject is not present in the cube. This can be a result of
    a) Missing spelling of the InfoObject
    b) it is not present in the cube

    so i suggest have a look into the /BIC/V*yourcubetechname*J structure and copy the name of the 0CURRENCY into the program, if it exists.

    Maybe i’m wrong – than the ST22-Dump message would be helpfull.

  16. Avatar von Hauke
    Hauke

    Hi Joe,

    I would like to use your programm, but unfortunately face some difficulties.

    The relevant key figure in my cube is compunded to a currency. This currency refers to 0Currency. When I execute the programm I get the following error message:

    Inkonsistent Entry Parameter (Parameter: sfc-sidreturnnm or keyreturnnm, Value „INFOOBJECT“)

    When debugging it seems that there is an unsuccessful change fromt DDIC to ABAP type.

    Any idea how i can solve this problem?

    Thanks in advance!

  17. Avatar von Joe

    And to mention it again : be carefull when using your own structure – the aggregation behaviour of the cubes take place.

  18. Avatar von Joe

    1: You should look in the transaction SE11 under „Structures/Dataelements“ as the „j“ Structure is no table.
    It is generated so it should exists on any BI-system and for any Cube.

    You can of course use your own structure this is why the commented line is declared:
    * cube_struct = ‘YOUR_STRUCTURE’.

    2: yes – this is wordpress syntax highlight-module – i corrected the missing field-symbol. If any-how an Field-symbol goes missing. quote a comment again please.

  19. Avatar von Ken
    Ken

    Hi, Your program is very helpful and I really appreciate you putting out there.

    I am going through the code and see how to utilize the logic. I got 2 questions:

    Question 1:
    When I look at my system, I don’t find the
    „generic structure with ALL Characteristics and Keyfigures of the Cube, the system doesn’t not have those ‚J‘ table created.

    CONCATENATE ‚/bic/v‘ cube ‚j‘ INTO cube_struct.
    * cube_struct = ‚YOUR_STRUCTURE‘.

    Just wonder is this automatic system generate? or need some steps to activate them? Or I can just create my own base on the characterisitc and keyfigures I need to be returned??

    Question 2:

    In the follow ling, the field symbol is missing variable is missing, does it really required?
    FIELD-SYMBOLS: TYPE ANY.

    Thank You very much and greatly appreciate you putting such helpful information to share.

    Best Regards
    Ken

  20. Avatar von Joe

    It’s quite funny who reads this site: From all over the world – china, usa, germany – all big companys 😀
    Have fun – leave a comment to all the other BI Developers around