Hi All,
This blog describes a way, to find out where a calculation view is used within BW. In our case such a where-used was needed, since we wanted to get rid of 'old' calculation views, but wheren't sure if they might still be in use somewhere.
To start off, in the HANA Modeler of the HANA Studio the WhereUsed function shows which native HANA Objects make use of the particular CalculationView. This WhereUsed is discussed in detail here:
https://blogs.sap.com/2013/03/05/extended-where-used-search-for-hana-information-model-objects/
However what is not shown is if this Object is used inside of BW. For example in the definition of a CompositeProvider. Since the CompositeProvider is not a native HANA Object, it is not possible to get the connection between the CalculationView and the CompositeProvider inside of the HANA Modeler.
So how to get this information? We found a workaround solution, by going in the BW and reading information of all Composite Providers there looking for our particular Calculation View. This turned out to be a little tricky, since only the XML definition of the single Composite Providers is known in BW and there's no easy to read table. So we wrote a small ABAP Programm that did tthis for us.
I hope it helps some of you and please feel free to comment and/or enhance the code!
Best regards,
Pelle
Remark: This Where-Used does not scan the SQL Code in your HANA Expertscripts, don't forget to check these too before you start deleting 'old' calculation views..
😉
REPORT zbi_calcview_whereused.
************************************************************************
* INITIALIZATION
***********************************************************************
TYPES: BEGIN OF ty_output,
hcpr TYPE rsohcprnm,
alias TYPE rsohcprcolnm,
calcview TYPE rsohcprcolnm,
bwobject TYPE rsdodsobject,
prov_str TYPE rsohcprcolnm,
cvalue TYPE rsohcprcolnm,
END OF ty_output.
DATA: lt_xml_info TYPE TABLE OF smum_xmltb INITIAL SIZE 0,
lt_return TYPE STANDARD TABLE OF bapiret2,
lt_output TYPE STANDARD TABLE OF ty_output,
lv_offset TYPE i,
lv_offset2 TYPE i,
lv_alias TYPE rsohcprcolnm,
ls_output TYPE ty_output,
lv_txt_s TYPE scrtext_s,
lv_txt_m TYPE scrtext_m,
lv_txt_l TYPE scrtext_l.
DATA: lo_alv TYPE REF TO cl_salv_table,
lo_columns TYPE REF TO cl_salv_columns,
lo_column TYPE REF TO cl_salv_column,
lo_funcs TYPE REF TO cl_salv_functions,
lo_msg TYPE REF TO cx_root.
FIELD-SYMBOLS: <fs_xml_info> TYPE smum_xmltb,
<fs_any_tab> TYPE any.
************************************************************************
* SELECTION SCREEN DETAILS
************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001.
PARAMETERS: p_hcpr TYPE rsohcprnm.
PARAMETERS: p_calc TYPE rsohcprcolnm.
PARAMETERS: p_bwpr TYPE rsdodsobject.
SELECTION-SCREEN END OF BLOCK blk1.
************************************************************************
* LOGIC
************************************************************************
* Select XML definition of CompositeProvider
IF p_hcpr IS INITIAL.
SELECT hcprnm, xml_ui
FROM rsohcpr
INTO TABLE @DATA(lt_hcpr_xml)
WHERE objvers = 'A'.
ELSE.
SELECT hcprnm, xml_ui
FROM rsohcpr
INTO TABLE @lt_hcpr_xml
WHERE hcprnm = @p_hcpr
AND objvers = 'A'.
ENDIF.
IF lt_hcpr_xml IS INITIAL.
MESSAGE 'No active CompositeProvider found' TYPE 'E'.
ENDIF.
* Loop over all active CompositeProviders
LOOP AT lt_hcpr_xml ASSIGNING FIELD-SYMBOL(<ls_hcpr_xml>).
CLEAR: lt_xml_info.
* Parse XML string to XML table
CALL FUNCTION 'SMUM_XML_PARSE'
EXPORTING
xml_input = <ls_hcpr_xml>-xml_ui
TABLES
xml_table = lt_xml_info
return = lt_return.
*Loop over all Providers of current CompositeProvider
*-alias gives information to providertype
*-entity gives information to provider
LOOP AT lt_xml_info ASSIGNING <fs_xml_info>
WHERE ( cname = 'entity' OR cname = 'alias' ).
CLEAR: lv_offset, lv_offset2, ls_output.
"Prefill alias information (used in next line)
IF <fs_xml_info>-cname = 'alias'.
FIND ALL OCCURRENCES OF '.' IN <fs_xml_info>-cvalue RESULTS DATA(lt_find_res).
IF sy-subrc <> 0 OR lines( lt_find_res ) < 2. CONTINUE. ENDIF.
SORT lt_find_res BY offset ASCENDING.
lv_offset = lt_find_res[ 1 ]-offset + 1.
lv_offset2 = lt_find_res[ lines( lt_find_res ) ]-offset.
DATA(lv_length) = lv_offset2 - lv_offset.
lv_alias = <fs_xml_info>-cvalue+lv_offset(lv_length).
CONTINUE.
ENDIF.
"Write output information
ls_output-hcpr = <ls_hcpr_xml>-hcprnm.
ls_output-cvalue = <fs_xml_info>-cvalue.
ls_output-alias = lv_alias.
"Write output information depending on type
DO.
TRY.
CASE sy-index.
WHEN 1.
FIND 'composite' IN <fs_xml_info>-cvalue MATCH OFFSET lv_offset.
IF sy-subrc <> 0. CONTINUE. ENDIF.
lv_offset = lv_offset - 1.
"provider is a BW Object->write output information
ls_output-prov_str = <fs_xml_info>-cvalue(lv_offset).
ls_output-bwobject = ls_output-prov_str.
WHEN 2.
FIND 'calculationview' IN <fs_xml_info>-cvalue MATCH OFFSET lv_offset.
IF sy-subrc <> 0. CONTINUE. ENDIF.
lv_offset = lv_offset - 1.
"provider is a HANA Object->write output information
ls_output-prov_str = <fs_xml_info>-cvalue(lv_offset).
FIND ALL OCCURRENCES OF '/' IN ls_output-prov_str MATCH OFFSET lv_offset2.
IF sy-subrc <> 0. CONTINUE. ENDIF.
lv_offset2 = lv_offset2 + 1.
ls_output-calcview = ls_output-prov_str+lv_offset2.
WHEN OTHERS.
"No pattern found
EXIT.
ENDCASE.
CATCH cx_sy_range_out_of_bounds.
"No action
ENDTRY.
EXIT.
ENDDO.
"Write back result
APPEND ls_output TO lt_output.
ENDLOOP.
ENDLOOP.
* Apply filter on calculation view
IF p_calc IS NOT INITIAL.
DELETE lt_output WHERE calcview <> p_calc.
ENDIF.
* Apply filter on BW Provider (ODSO, ADSO, HCPR, FBPA)
IF p_bwpr IS NOT INITIAL.
DELETE lt_output WHERE bwobject <> p_bwpr.
ENDIF.
**********************************************************************
* Output
**********************************************************************
ASSIGN lt_output TO <fs_any_tab> .
TRY.
cl_salv_table=>factory(
IMPORTING
r_salv_table = lo_alv
CHANGING
t_table = <fs_any_tab> ).
"Set column optimized
lo_columns = lo_alv->get_columns( ).
lo_columns->set_optimize( ).
"Set column title
DATA(lt_colums) = lo_columns->get( ).
LOOP AT lo_columns->get( ) ASSIGNING FIELD-SYMBOL(<lv_column>).
lo_column = lo_columns->get_column( <lv_column>-columnname ).
lv_txt_s = <lv_column>-columnname.
lv_txt_m = <lv_column>-columnname.
lv_txt_l = <lv_column>-columnname.
lo_column->set_short_text( lv_txt_s ).
lo_column->set_medium_text( lv_txt_m ).
lo_column->set_long_text( lv_txt_l ).
ENDLOOP.
"Set functions
lo_funcs = lo_alv->get_functions( ).
lo_funcs->set_all( ).
lo_alv->display( ).
"Error handling
CATCH cx_salv_msg INTO lo_msg.
MESSAGE lo_msg TYPE 'E'.
CATCH cx_salv_not_found INTO lo_msg.
MESSAGE lo_msg TYPE 'E'.
ENDTRY.
Bluesky
