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.

 

 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply