How to… Download a Hierarchy to a Flat File

How to…
Download a Hierarchy
to a Flat File
B USINESS I NFORMATION W AREHOUSE
ASAP “How to…” Paper
Applicable Releases: BW 3.0 or greater
March 2004
SAP (SAP America, Inc. and SAP AG) assumes no responsibility for errors or omissions in these materials.
These materials are provided “as is” without a warranty of any kind, either express or implied, including but not limited to, the
implied warranties of merchantability, fitness for a particular purpose, or non-infringement.
SAP shall not be liable for damages of any kind including without limitation direct, special, indirect, or consequential damages
that may result from the use of these materials.
SAP does not warrant the accuracy or completeness of the information, text, graphics, links or other items contained within
these materials. SAP has no control over the information that you may access through the use of hot links contained in these
materials and does not endorse your use of third party web pages nor provide any warranty whatsoever relating to third
party web pages.
mySAP BI “How -To” papers are intended to simplify the product implementation. While specific product features and
procedures typically are explained in a practical business context, it is not implied that those features and procedures are
the only approach in solving a specific business problem using mySAP BI. Should you wish to receive additional information,
clarification or support, please refer to SAP Professional Services (Consulting/Remote Consulting).
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
1 Business Scenario
This document describes the how to download a master data hierarchy to a flat file. The file format
matches the hierarchy upload format for sorted and time-depended hierarchies (IDOC).
2 Solution
A custom program is being provided that can be used to easily download any master data hierarchy.
2004 SAP AMERICA , I NC. AND SAP AG
1
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
3 The Step-By-Step Solution
3.1
Implement Program
Using transaction SE38 implement ABAP program Z_SAP_HIERARCHY_DOWNLOAD (see Appendix).
You can find the text elements for the selection screen in the coding.
3.2
Execute Program
Start the program. On the selection screen, first enter or pick the InfoObject. Then you can select the
hierarchy that you want to download.
Optionally, you can enter which language of hierarchy node descriptions should be downloaded. As a
default, the logon language is being used.
Last but not least you enter or select the file name for the download.
Note: If the file exists already, it will be overwritten with the current hierarchy.
If the download was successful, then a popup is displayed:
2004 SAP AMERICA , I NC. AND SAP AG
2
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
3.3
Hierarchy Upload
In order to upload the file into BW, go to the Administrator Workbench (transaction RSA1) and create a
hierarchy InfoSource. To match the download file format, you should use the IDOC transfer method:
2004 SAP AMERICA , I NC. AND SAP AG
3
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
Create a new hierarchy structure:
Select the options “Sorted hierarchy”, “Interval”, and “Time-dependent”. The structure will then match the
download format.
2004 SAP AMERICA , I NC. AND SAP AG
4
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
Create an InfoPackage. The file type is “ASCII”.
Now you can schedule the file upload.
2004 SAP AMERICA , I NC. AND SAP AG
5
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
4 Comments
•
Please note that Open Hub licenses are required if data is to be extracted and transferred from
mySAP BI to third party target systems.
2004 SAP AMERICA , I NC. AND SAP AG
6
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
5 Appendix
************************************************************************
* SAP Consulting BW Tools:
* -----------------------* Download hierarchy into a flat file. The file has the correct format
* for uploading into BW via a hierarchy InfoSource (IDOC).
*
* (c) SAP AG 2003-2004
MFB, SAP Labs LLC
* created:
2003-07-23
* last update: 2004-05-02
************************************************************************
* Text elements:
* P_DATES
Include from/to dates
* P_DATETO
Valid-to date
* P_FNAME
File name
* P_HIENM
Hierarchy name
* P_INTER
Include from/to leaves
* P_IOBJNM
InfoObject
* P_LANGU
Language
* P_VERS
Hierarchy version
************************************************************************
REPORT z_sap_hierarchy_download.
TYPE-POOLS: rs, rsdm, rrh1.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.
PARAMETERS:
p_iobjnm TYPE rsdiobjnm MEMORY ID rsc.
SELECTION-SCREEN END OF BLOCK b1.
SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME.
PARAMETERS:
p_hienm TYPE rshiedir-hienm,
p_vers
TYPE rshiedir-version,
p_dateto TYPE rshiedir-dateto,
p_langu TYPE rshiedirt-langu.
SELECTION-SCREEN END OF BLOCK b2.
SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME.
PARAMETERS:
p_fname LIKE rlgrap-filename,
p_dates AS CHECKBOX DEFAULT 'X',
p_inter AS CHECKBOX DEFAULT 'X'.
SELECTION-SCREEN END
OF BLOCK b3.
* File structure
TYPES:
* No dates/intervals
BEGIN OF y_s_hierfile_1,
nodeid
TYPE rshienodid,
iobjnm
TYPE rsiobjnm,
nodename TYPE rsnodename,
*
tlevel
TYPE rstlevel,
link
TYPE rslink,
parentid TYPE rsparent,
childid TYPE rschild,
nextid
TYPE rsnext,
langu
TYPE langu,
txtsh
TYPE rstxtsh,
txtmd
TYPE rstxtmd,
2004 SAP AMERICA , I NC. AND SAP AG
7
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
*
*
*
*
*
*
txtlg
TYPE rstxtlg,
END OF y_s_hierfile_1,
y_t_hierfile_1 TYPE STANDARD
With dates
BEGIN OF y_s_hierfile_2,
nodeid
TYPE rshienodid,
iobjnm
TYPE rsiobjnm,
nodename TYPE rsnodename,
tlevel
TYPE rstlevel,
link
TYPE rslink,
parentid TYPE rsparent,
childid TYPE rschild,
nextid
TYPE rsnext,
dateto
TYPE rsdateto,
datefrom TYPE rsdatefrom,
langu
TYPE langu,
txtsh
TYPE rstxtsh,
txtmd
TYPE rstxtmd,
txtlg
TYPE rstxtlg,
END OF y_s_hierfile_2,
y_t_hierfile_2 TYPE STANDARD
With intervals
BEGIN OF y_s_hierfile_3,
nodeid
TYPE rshienodid,
iobjnm
TYPE rsiobjnm,
nodename TYPE rsnodename,
tlevel
TYPE rstlevel,
link
TYPE rslink,
parentid TYPE rsparent,
childid TYPE rschild,
nextid
TYPE rsnext,
leafto
TYPE rsleafto,
leaffrom TYPE rsleaffrom,
langu
TYPE langu,
txtsh
TYPE rstxtsh,
txtmd
TYPE rstxtmd,
txtlg
TYPE rstxtlg,
END OF y_s_hierfile_3,
y_t_hierfile_3 TYPE STANDARD
With dates/intervals
BEGIN OF y_s_hierfile_4,
nodeid
TYPE rshienodid,
iobjnm
TYPE rsiobjnm,
nodename TYPE rsnodename,
tlevel
TYPE rstlevel,
link
TYPE rslink,
parentid TYPE rsparent,
childid TYPE rschild,
nextid
TYPE rsnext,
dateto
TYPE rsdateto,
datefrom TYPE rsdatefrom,
leafto
TYPE rsleafto,
leaffrom TYPE rsleaffrom,
langu
TYPE langu,
txtsh
TYPE rstxtsh,
txtmd
TYPE rstxtmd,
txtlg
TYPE rstxtlg,
END OF y_s_hierfile_4,
y_t_hierfile_4 TYPE STANDARD
* Hierarchy definition
DATA:
g_s_hiesel
TYPE
g_s_hiedir
TYPE
g_subrc
TYPE
g_t_hiedirt
TYPE
TABLE OF y_s_hierfile_1,
TABLE OF y_s_hierfile_2,
TABLE OF y_s_hierfile_3,
TABLE OF y_s_hierfile_4.
rsndi_s_hiesel,
rsndi_s_hiedir,
sy-subrc,
TABLE OF rshiedirt,
2004 SAP AMERICA , I NC. AND SAP AG
8
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
g_s_hierstruc
g_t_hierstruc
g_s_thiernode
g_t_thiernode
g_s_hierintvl
g_t_hierintvl
g_s_message
g_t_message
g_s_chavlinfo
g_t_chavlinfo
TYPE
TYPE
TYPE
TYPE
TYPE
TYPE
TYPE
TYPE
TYPE
TYPE
rssh_s_htab,
TABLE OF rssh_s_htab,
rsthiernode,
TABLE OF rsthiernode WITH KEY langu hieid objvers nodename,
rssh_s_jtab,
TABLE OF rssh_s_jtab WITH KEY hieid objvers nodeid,
rsndi_s_message,
TABLE OF rsndi_s_message,
rsdm_s_chavlinfo,
rsdm_t_chavlinfo.
* File
DATA:
g_fname
g_struct_s
g_struct_t
gr_s_file
gr_t_file
TYPE
TYPE
TYPE
TYPE
TYPE
string,
string,
string,
REF TO data,
REF TO data.
FIELD-SYMBOLS:
<g_langu>
<g_s_file>
<g_t_file>
TYPE ANY,
TYPE ANY,
TYPE STANDARD TABLE.
*----------------------------------------------------------------------AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_iobjnm.
CALL FUNCTION 'RSD_IOBJ_F4'
EXPORTING
i_show_cha
= rs_c_true
i_objvers
= rs_c_objvers-active
i_hietabfl
= rs_c_true
CHANGING
c_iobjnm
= p_iobjnm
EXCEPTIONS
illegal_input = 1.
CHECK sy-subrc = 0.
*----------------------------------------------------------------------AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_hienm.
DATA:
l_s_hiertxt
l_t_hiertxt
TYPE rrh1_s_hiertxt,
TYPE rrh1_t_hiertxt.
CALL FUNCTION 'RRH1_HIERARCHY_HELP_VALUES_GET'
EXPORTING
i_iobjnm
= p_iobjnm
i_dateto
= p_dateto
i_hienm
= p_hienm
i_version
= p_vers
IMPORTING
e_t_hiertxt
= l_t_hiertxt
EXCEPTIONS
no_f4_available
= 1
dialogue_canceled = 2
OTHERS
= 3.
CHECK sy-subrc = 0.
READ TABLE l_t_hiertxt INTO l_s_hiertxt INDEX 1.
CHECK NOT l_s_hiertxt IS INITIAL.
p_hienm
= l_s_hiertxt-hienm.
DATA:
l_s_dynpfields TYPE dynpread,
2004 SAP AMERICA , I NC. AND SAP AG
9
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
l_t_dynpfields TYPE STANDARD TABLE OF dynpread.
CLEAR: l_t_dynpfields, l_s_dynpfields.
l_s_dynpfields-fieldname = 'P_VERS'.
WRITE l_s_hiertxt-version TO l_s_dynpfields-fieldvalue.
APPEND l_s_dynpfields TO l_t_dynpfields.
l_s_dynpfields-fieldname = 'P_DATETO'.
WRITE l_s_hiertxt-dateto TO l_s_dynpfields-fieldvalue.
APPEND l_s_dynpfields TO l_t_dynpfields.
CALL FUNCTION 'DYNP_VALUES_UPDATE'
EXPORTING
dyname
= sy-repid
dynumb
= sy-dynnr
TABLES
dynpfields = l_t_dynpfields.
*----------------------------------------------------------------------AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.
DATA:
l_filename1
l_filename2
l_path
l_fullpath
l_action
TYPE
TYPE
TYPE
TYPE
TYPE
string,
string,
string,
string,
i.
l_filename1 = p_fname.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title
= 'Select Download File'
default_extension = 'txt'
default_file_name = l_filename1
file_filter
= 'All Files (*.*)|*.*|Text files (*.txt)|*.txt'
CHANGING
filename
= l_filename2
path
= l_path
fullpath
= l_fullpath
user_action
= l_action
EXCEPTIONS
cntl_error
= 1
OTHERS
= 2.
"#EC NOTEXT
CHECK sy-subrc = 0.
CALL METHOD cl_gui_cfw=>flush.
IF l_action = 0.
p_fname = l_fullpath.
ENDIF.
*----------------------------------------------------------------------INITIALIZATION.
GET PARAMETER ID 'RSC' FIELD p_iobjnm.
*----------------------------------------------------------------------START-OF-SELECTION.
* Check input
CHECK NOT p_fname IS INITIAL.
IF p_langu IS INITIAL.
p_langu = sy-langu.
ENDIF.
IF p_dateto IS INITIAL.
p_dateto = '99991231'.
2004 SAP AMERICA , I NC. AND SAP AG
10
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
ENDIF.
* Read hierarchy
CLEAR g_s_hiesel.
g_s_hiesel-objvers
g_s_hiesel-hienm
g_s_hiesel-version
g_s_hiesel-iobjnm
g_s_hiesel-dateto
=
=
=
=
=
rs_c_objvers-active.
p_hienm.
p_vers.
p_iobjnm.
p_dateto.
CALL FUNCTION 'RSNDI_SHIE_STRUCTURE_GET'
EXPORTING
i_s_hiesel
= g_s_hiesel
i_no_nodenm_table = rs_c_true
IMPORTING
e_s_hiedir
= g_s_hiedir
e_subrc
= g_subrc
TABLES
e_t_hiedirt
= g_t_hiedirt
e_t_hierstruc
= g_t_hierstruc
e_t_thiernode
= g_t_thiernode
e_t_hierintvl
= g_t_hierintvl
e_t_message
= g_t_message.
IF g_subrc <> 0.
READ TABLE g_t_message INTO g_s_message INDEX 1.
IF sy-subrc = 0.
MESSAGE ID g_s_message-msgid TYPE 'I' NUMBER g_s_message-msgno
WITH g_s_message-msgv1 g_s_message-msgv2
g_s_message-msgv3 g_s_message-msgv4.
ELSE.
MESSAGE ID 'RSBO' TYPE 'I' NUMBER 899
WITH 'Hierarchy read error'.
ENDIF.
EXIT.
ENDIF.
* Defined output structures
IF p_dates IS INITIAL AND p_inter IS INITIAL.
g_struct_s = 'Y_S_HIERFILE_1'.
g_struct_t = 'Y_T_HIERFILE_1'.
ELSEIF p_dates = 'X' AND p_inter IS INITIAL.
g_struct_s = 'Y_S_HIERFILE_2'.
g_struct_t = 'Y_T_HIERFILE_2'.
ELSEIF p_dates IS INITIAL AND p_inter = 'X'.
g_struct_s = 'Y_S_HIERFILE_3'.
g_struct_t = 'Y_T_HIERFILE_3'.
ELSE.
g_struct_s = 'Y_S_HIERFILE_4'.
g_struct_t = 'Y_T_HIERFILE_4'.
ENDIF.
CREATE DATA gr_s_file TYPE (g_struct_s).
ASSIGN gr_s_file->* TO <g_s_file>.
CREATE DATA gr_t_file TYPE (g_struct_t).
ASSIGN gr_t_file->* TO <g_t_file>.
* Nodes
REFRESH <g_t_file>.
LOOP AT g_t_hierstruc INTO g_s_hierstruc.
CLEAR <g_s_file>.
MOVE-CORRESPONDING g_s_hierstruc TO <g_s_file>.
*
Texts for nodes
READ TABLE g_t_thiernode INTO g_s_thiernode WITH TABLE KEY
langu
= p_langu
2004 SAP AMERICA , I NC. AND SAP AG
11
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
*
hieid
= g_s_hierstruc-hieid
objvers = rs_c_objvers-active
nodename = g_s_hierstruc-nodename.
IF sy-subrc = 0.
MOVE-CORRESPONDING g_s_thiernode TO <g_s_file>.
ELSE.
Texts for characteristic values
REFRESH g_t_chavlinfo.
CLEAR
g_s_chavlinfo.
g_s_chavlinfo-c_chavl = g_s_hierstruc-nodename.
APPEND g_s_chavlinfo TO g_t_chavlinfo.
CALL FUNCTION 'RSD_CHAVL_READ_ALL'
EXPORTING
i_iobjnm
= g_s_hierstruc-iobjnm
i_langu
= p_langu
i_dateto
= p_dateto
i_check_value
= space
i_sid_in
= space
i_hieid
= g_s_hiedir-hieid
i_objvers
= g_s_hiedir-objvers
CHANGING
c_t_chavlinfo
= g_t_chavlinfo
EXCEPTIONS
info_object_not_found
= 1
routines_generation_error = 2
check_table_not_existing = 3
text_table_not_existing
= 4
OTHERS
= 5.
IF sy-subrc = 0.
READ TABLE g_t_chavlinfo INTO g_s_chavlinfo INDEX 1.
IF sy-subrc = 0.
MOVE-CORRESPONDING g_s_chavlinfo-e_chatexts TO <g_s_file>.
ASSIGN COMPONENT 'LANGU' OF STRUCTURE <g_s_file> TO <g_langu>.
IF sy-subrc = 0.
<g_langu> = p_langu.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
*
Intervals
IF g_s_hierstruc-intervl = 'X' AND p_inter = 'X'.
READ TABLE g_t_hierintvl INTO g_s_hierintvl WITH TABLE KEY
hieid
= g_s_hierstruc-hieid
objvers = rs_c_objvers-active
nodeid
= g_s_hierstruc-nodeid.
IF sy-subrc = 0.
MOVE-CORRESPONDING g_s_hierintvl TO <g_s_file>.
ENDIF.
ENDIF.
APPEND <g_s_file> TO <g_t_file>.
ENDLOOP.
* Download output table
g_fname = p_fname.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename
=
write_field_separator
=
TABLES
data_tab
=
EXCEPTIONS
file_write_error
=
no_batch
=
g_fname
space
<g_t_file>
1
2
2004 SAP AMERICA , I NC. AND SAP AG
12
HOW TO … DOWNLOAD A HIERARCHY TO A FLAT FILE
gui_refuse_filetransfer = 3
invalid_type
= 4
no_authority
= 5
unknown_error
= 6
header_not_allowed
= 7
separator_not_allowed
= 8
filesize_not_allowed
= 9
header_too_long
= 10
dp_error_create
= 11
dp_error_send
= 12
dp_error_write
= 13
unknown_dp_error
= 14
access_denied
= 15
dp_out_of_memory
= 16
disk_full
= 17
dp_timeout
= 18
file_not_found
= 19
dataprovider_exception = 20
control_flush_error
= 21
OTHERS
= 22.
IF sy-subrc = 0.
MESSAGE ID 'RSBO' TYPE 'I' NUMBER 899
WITH 'Hierarchy download successful!'.
ELSE.
MESSAGE ID sy-msgid TYPE 'I' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
2004 SAP AMERICA , I NC. AND SAP AG
13