SAP BW/4 HANA: ODP maintaining data source entries in the table ROOSATTR using custom ABAP program
About myself:
This is Lakshmikanth Adharapurapu Venkata is working with Bouvet since 2016 with overall 12+ years of experience. My expertise includes SAP BI HANA/BW4HANA with Buisness objects.Objective of this post is to help scn community with my technical expertise to propose best possible solutions for the SAP BI architects. I will try to write and share further blogs specially on BW/4 hana, BO design studio and Lumira designer.
Introduction:
As we know currently there is limitation on GRC data sources and other data sources which enable and support for ODP replication, hence there are lot of SAP standard extractors whose entries does not exist in table ROOSATTR without a possibility to release the data source using Se38 program RODPS_OS_EXPOSE.
Objective:
Objective of this blog is to explain how to maintain the entries in the se11 table ‘roosattr’ using custom program to enable the ODP data source which can be replicated in BW/4 HANA studio or BW hana studio.
Steps followed to achieve the requirement
Step1: Write below custom ABAP code in SE38 abap editor
REPORT ZLAKSHMIKANTH_ODP.
TYPES: gt_char30(30) TYPE c.
DATA: g_s_roosattr TYPE roosattr,
g_object TYPE roosattr-OLTPSOURCE,
g_value TYPE roosattr-EXPOSE_EXTERNAL,
g_subrc TYPE i.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(60) t_header.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: object TYPE gt_char30.
SELECTION-SCREEN COMMENT 33(40) t_object FOR FIELD object.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: value TYPE gt_char30 LOWER CASE.
SELECTION-SCREEN COMMENT 33(40) t_value FOR FIELD value.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: insert RADIOBUTTON GROUP rad1 DEFAULT ‘X’.
SELECTION-SCREEN COMMENT 5(40) t_insert FOR FIELD insert.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: update RADIOBUTTON GROUP rad1.
SELECTION-SCREEN COMMENT 5(40) t_update FOR FIELD update.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: delete RADIOBUTTON GROUP rad1.
SELECTION-SCREEN COMMENT 5(40) t_delete FOR FIELD delete.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: ul_case AS CHECKBOX DEFAULT ‘ ‘.
SELECTION-SCREEN COMMENT 5(40) t_ulcase FOR FIELD ul_case.
“upper-lowe case
SELECTION-SCREEN END OF LINE.
*———————————————————————–
INITIALIZATION.
t_header = ‘Maintain table roosattr:'(010).
t_object = ‘OBJECT’.
t_value = ‘VALUE’.
t_insert = ‘INSERT’.
t_update = ‘UPDATE’.
t_delete = ‘DELETE’.
t_ulcase = ‘CASE-SENSITIVE’.
*———————————————————————–
AT SELECTION-SCREEN.
IF ul_case is initial.
TRANSLATE value to UPPER CASE.
ENDIF.
*———————————————————————–
START-OF-SELECTION.
* OBJECT is the key column -> cannot be initial
CHECK NOT object IS INITIAL.
* authority check S_TABU_DIS
CALL FUNCTION ‘VIEW_AUTHORITY_CHECK’
EXPORTING
view_name = ‘roosattr’
NO_WARNING_FOR_CLIENTINDEP = ‘X’
EXCEPTIONS
OTHERS = 1.
**** sy-subrc <> 0 raise no authority
IF sy-subrc NE 0. “no authority
message e417(MO) raising no_permission.
ENDIF.
* use variables to avoid misunderstandings
g_object = object.
g_value = value.
* get current setting
SELECT SINGLE OLTPSOURCE EXPOSE_EXTERNAL
INTO g_s_roosattr
FROM roosattr
WHERE OLTPSOURCE = g_object.
WRITE: / ‘OLD SETTING:’.
WRITE: / ‘OBJECT =’, g_s_roosattr-OLTPSOURCE, ‘VALUE =’,
g_s_roosattr-EXPOSE_EXTERNAL.
* set new values
g_s_roosattr-OLTPSOURCE = g_object.
g_s_roosattr-EXPOSE_EXTERNAL = g_value.
* INSERT
IF insert = ‘X’.
INSERT INTO roosattr VALUES g_s_roosattr.
g_subrc = sy-subrc.
IF g_subrc <> 0.
WRITE: / ‘INSERT failed because of existing record.'(020).
ENDIF.
* UPDATE
ELSEIF update = ‘X’.
UPDATE roosattr
SET EXPOSE_EXTERNAL = g_value
WHERE OLTPSOURCE = g_object.
g_subrc = sy-subrc.
IF g_subrc <> 0.
WRITE: / ‘UPDATE failed because there is no record with'(030).
WRITE: / ‘OBJECT =’, g_object.
ENDIF.
* DELETE
ELSEIF delete = ‘X’.
DELETE FROM roosattr
WHERE OLTPSOURCE = g_object.
g_subrc = sy-subrc.
IF g_subrc <> 0.
WRITE: / ‘DELETE failed because there is no record with'(040).
WRITE: / ‘OBJECT =’, g_object.
ENDIF.
ENDIF.
* in case of success:
IF g_subrc = 0.
* — COMMIT
CALL FUNCTION ‘DB_COMMIT’.
* — reset table buffer
PERFORM reset_buffer.
* — new settings:
IF delete = ‘ ‘.
WRITE: / ‘NEW SETTING:’.
WRITE: / ‘OBJECT =’, g_object, ‘VALUE =’, g_value.
ENDIF.
ENDIF.
*&——————————————————————–*
*& Form reset_buffer
*&——————————————————————–*
* text
*———————————————————————*
FORM reset_buffer.
CALL FUNCTION ‘SBUF_GENERIC_RESET’
EXPORTING
sync = ‘X’
tabname = ‘roosattr’
EXCEPTIONS
c_func_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
WRITE: /
‘Fehler beim Invalidieren des Puffers für Tabelle roosattr.'(050).
WRITE: /
‘Setzen Sie den Puffer manuell zurück!'(060).
ENDIF.
ENDFORM. “reset_buffer
Step2: execute the program and provide the data source wish to convert to ODP and provide with X and execute
Step3: Once executed, please check the table ROOSATTR , entry is registered as shown below.
Step4: Login to hana studio, go to BW/4 hana orBW hana modelling, right click the appropriate node and replicate—select the data source from list and finish.
Hi, thank you for sharing. Do you know about reasons of mentioned limitations? Why some datasources are hidden anr developer can't just use RODPS_OS_EXPOSE?
Maksim
Hi,
SAP one of the sap documenation, there are multiple issues in the past, like a delta extractor not using the RECMODE field which is actually mandatory according to the BW definitions. Hence RODPS_OS_EXPOSE is not supporting for all the list of extractors to enable ODP supported extractors. We have contacted SAP for one of the GRC related data source which was not supported for ODP extractions ofcourse our mode of extraction should be full and SAP recommended to maintain entries manually in the table ROOSATTR using abap debugger or in client 000 with super user acess.
Please refer the blog further on this topic.
https://wiki.scn.sap.com/wiki/display/EIM/Releasing+Extractors+for+use+by+the+ODP+API
Please refer SAP note 2232584 to get the list of 2232584 - Release of SAP extractors for ODP replication
Br,
Lakshmikanth
Hi,
two more remarks from my side to avoid misunderstandings:
Best regards,
Andreas
Do we need to do the same if we have created a SAP HANA cal view and need to expose that cal view as a datasource ? Please advise. Appreciate it. Cheers.