Friday, 11 January 2019

How to transport Calculation views with HTA with all Dependent objects in “One Click”

This blog is to explain our finding / solution to transport Hana Calculation views with dependent objects object with HTA – with classical transport.

Our main challenge was to move our Hana Development using HTA as we decided to apply LSA++ concept on our Hana modelling, by using Harmonization layers, Transformation Layers and Data Access Layer.

Our Solution – still in change:


We used layers for this solution:

◈ Hana layer to retrieve all dependent objects for a calculation view, already used for us to have an understanding of all objects used in a model.
◈ BW ABAP layer for transport management and HTA


Hana layer:


To display dependency,   following calculation view has been created:

This view is used, among others, to identify from master view all dependent views and tables

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

Semantic Layer:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

Calculated column:

on AO projection: AO_DEPENDENT_OBJECT_NAME

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

Filter on AO Projection:
("PACKAGE_ID" ='$$Package_Name$$') AND ("OBJECT_NAME" ='$$CV_Name$$') AND ("OBJECT_SUFFIX" ='calculationview')

Filter on OD Projection:
("DEPENDENT_SCHEMA_NAME" ='_SYS_BIC') AND ("DEPENDENT_OBJECT_TYPE" ='VIEW')

Variables:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

Result:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

BW Layer:


External view based on Calculation view Dependency

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

We decided to use the standard program SCTS_HOTA_ORGANIZER and add an “automatic” selection of all dependent objects.

Standard program was not good enough for us as if a model consume 10 views from different package views need to be pick one by one.

Standard program has been duplicated and following change added (Lines where code has beed added is in the code bellow):

Line 6:
      gv_depl      TYPE c,
      gv_pack(255) TYPE c.
***** START ADD fields 
DATA: lt_tab2 TYPE TABLE OF ZTCT_DEP,
      lt_pack TYPE TABLE OF ZTCT_DEP,
      lt_table TYPE TABLE OF ZTCT_DEP,
      lt_views TYPE TABLE OF ZTCT_DEP.
DATA: lv_sql TYPE string.
DATA: lo_sql_stmt   TYPE REF TO cl_sql_statement,
      lo_result    TYPE REF TO cl_sql_result_set,
      o_ref_kna1  TYPE REF TO data,
      o_ref_mandt TYPE REF TO data,
      o_ref_name1 TYPE REF TO data,
      lr_data         TYPE REF TO data,
      lx_sql_exc TYPE REF TO cx_sql_exception,
      lpv_dep     type abap_bool,
      lpv_sync     type abap_bool,
      l_tab type abap_bool .
***** END ADD fields

INTERFACE lif_data_provider.

Line 2336:
*... preselect packages

  data: l_tabix like sy-tabix.      "Add DDU
    LOOP AT mt_master REFERENCE INTO DATA(lr_master).
**** Begin Add/Change - Look for Dependencies
     if lpv_dep = 'X'.     "Check dependencies
      If  lpv_sync = 'X'.  "Check if already sync - assign to transport
        IF lr_master->sync_deploy_state = icon_led_yellow.
           l_tabix = sy-tabix.
           READ TABLE LT_VIEWS   WITH KEY PACKAGE_ID = lr_master->PACKAGE_ID INTO DATA(l_pack).
           if sy-subrc = 0.
             APPEND l_tabix TO lt_rows.
           endif.
        ENDIF.
      else.
         l_tabix = sy-tabix.
         READ TABLE LT_VIEWS   WITH KEY PACKAGE_ID = lr_master->PACKAGE_ID INTO DATA(l_pack2).
         if sy-subrc = 0.
           APPEND l_tabix TO lt_rows.
         endif.
      ENDIF.
    else.

**** End Add/Change - Look for Dependencies

     IF lr_master->sync_deploy_state = icon_led_yellow.
        APPEND sy-tabix TO lt_rows.
      ENDIF.
    endif.

Line 2379

*... preselect objects
    CLEAR lt_rows.
***** list of all CV for package
      LOOP AT mt_slave REFERENCE INTO DATA(lr_slave).
**** Begin

     if lpv_dep = 'X'.     "Check dependencies
      If  lpv_sync = 'X'.  "Check if already sync
        IF lr_slave->sync_deploy_state = icon_led_yellow.
           l_tabix = sy-tabix.
            READ TABLE LT_VIEWS  WITH   KEY   BASE_OBJECT_NAME = lr_slave->OBJECT_NAME PACKAGE_ID = lr_slave->HANA_PACKAGE_ID INTO DATA(l_pack3).
           if sy-subrc = 0.
             APPEND l_tabix TO lt_rows.
           endif.
        ENDIF.
      else.
         l_tabix = sy-tabix.
          READ TABLE LT_VIEWS  WITH   KEY   BASE_OBJECT_NAME = lr_slave->OBJECT_NAME PACKAGE_ID = lr_slave->HANA_PACKAGE_ID INTO DATA(l_pack4).
         if sy-subrc = 0.
           APPEND l_tabix TO lt_rows.
         endif.
      ENDIF.
    else.

      IF lr_slave->sync_deploy_state = icon_led_yellow.
        APPEND sy-tabix TO lt_rows.
      ENDIF.
    endif.
**** End Add/Change - Look for Dependencies
     ENDLOOP.

***** Begin Add
TYPES: BEGIN OF S_ITAB,
  LINE(255),
END OF S_ITAB.

DATA: T_ITAB TYPE TABLE OF S_ITAB.

DATA: WA_S_ITAB TYPE S_ITAB.
if l_tab = ''.
  clear WA_S_ITAB.
LOOP AT lt_table ASSIGNING FIELD-SYMBOL(<fs>).
  WA_S_ITAB-line = <fs>-BASE_OBJECT_NAME.
  APPEND WA_S_ITAB to T_ITAB.
ENDLOOP.


***** Display list of table to be replicated in SLT
    CALL FUNCTION 'POPUP_WITH_TABLE'
      EXPORTING
        ENDPOS_COL         = 100
        ENDPOS_ROW         = 100
        STARTPOS_COL       = 10
        STARTPOS_ROW       = 10
        TITLETEXT          = 'List of SLT Tables'
*     IMPORTING
*       CHOICE             =
      TABLES
        VALUETAB           =  T_ITAB
     EXCEPTIONS
       BREAK_OFF          = 1
       OTHERS             = 2
              .
    IF SY-SUBRC <> 0.
*     Implement suitable error handling here
    ENDIF.
l_tab = 'X'.
else.
  l_tab = ''.

endif.
***** End Add  
    TRY.
        lr_selections = mr_hierseq->get_selections( 2 ).
        lr_selections->set_selected_rows( lt_rows ).

Line 4919
 SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE lv_title.
  PARAMETERS:
    pv_pack TYPE cts_hot_package-hana_package_id LOWER CASE MEMORY ID hta_pack DEFAULT 'XXXXXX',               "add DEFAULT  value
    pv_sub  TYPE abap_bool AS CHECKBOX DEFAULT abap_true MEMORY ID hta_sub, "handle ALL subpackages as well  "Change to abap_true
    pv_pcv TYPE cts_hot_package-hana_package_id LOWER CASE MEMORY ID hta_packcv,                             "Add - Package of CV to Move
    pv_cv TYPE CTS_HOT_OBJECT-ABAP_HANA_OBJECT_NAME_SUFFIX LOWER CASE MEMORY ID hta_view,                    "Add - Calculation view - Father
    pv_dep   TYPE abap_bool AS CHECKBOX DEFAULT abap_true,                                                   "Add - With Dependency?
    pv_sync   TYPE abap_bool AS CHECKBOX DEFAULT abap_true .                                                 "Add - Only object not yet Sync
  SELECTION-SCREEN END OF BLOCK b1.

Line 4945
AT SELECTION-SCREEN.
*** Beging Add
  lpv_dep = pv_dep.
  lpv_sync = pv_sync.
*** End Add
  CASE sy-ucomm.
    WHEN 'AUSF'.
*** BEGIN - ADD SELECT from dependencies
      l_tab = 'X'.
      if lpv_dep = 'X'.

      TRY.
          lv_sql = | SELECT BASE_SCHEMA_NAME, BASE_OBJECT_NAME, DEPENDENT_OBJECT_NAME, VERSION_ID, ACTIVATED_BY, OBJECT_NAME, OBJECT_SUFFIX, PACKAGE_ID, BASE_OBJECT_TYPE2, ACTIVATED_AT2 |
*               use HANA built-in function
                && |   FROM _SYS_BIC."NAME OF CV_EXT_DEPENDENCY" |
                && | ('PLACEHOLDER' = ('$$CV_Name$$', '{ pv_cv }'), 'PLACEHOLDER' = ('$$Package_Name$$', '{ pv_pcv }')) |.

*         Create an SQL statement to be executed via default secondary DB connection
          CREATE OBJECT lo_sql_stmt EXPORTING con_ref = cl_sql_connection=>get_connection( ).
*         execute the native SQL query/ SQL Call
          lo_result = NEW cl_sql_statement( )->execute_query( lv_sql ).   " new syntax
*         read the result into the internal table lt_partner
          GET REFERENCE OF lt_tab2 INTO lr_data.
    lo_result->set_param_table( lr_data ).  "Retrieve result of native SQL call
    lo_result->next_package( ).
    lo_result->close( ).
        CATCH cx_sql_exception INTO lx_sql_exc.
          "lv_text = lx_sql_exc->get_text( ).
          "MESSAGE lv_text TYPE ‘E’.
      ENDTRY.
     data: l_pack(40), l_cv(40).
     LOOP AT lt_tab2 ASSIGNING FIELD-SYMBOL(<fs>).
      if <fs>-BASE_OBJECT_TYPE2 = 'VIEW'.
        SPLIT <fs>-BASE_OBJECT_NAME AT '/'   INTO l_pack l_cv.
        <fs>-PACKAGE_ID = l_pack.
        <fs>-BASE_OBJECT_NAME = l_cv.
        append <fs> to  lt_views.
        <fs>-PACKAGE_ID = pv_pcv.
        <fs>-BASE_OBJECT_NAME = pv_cv.
        append <fs> to  lt_views.
      ELSE.
        append <fs> to  lt_table.
      endif.
     endloop.
****** END - ADD SELECT from dependencies
     endif.
      IF pv_pack IS INITIAL OR pv_pack = '*'.

Text Element:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

Execution:

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

All views are selected automatically

Transport is requested, all calculation views are now assign to transport and can be moved to QA

Following list is also provide with list of table need to be replicated.

SAP HANA Tutorial and Material, SAP HANA Guides, SAP HANA Learning, SAP HANA Study Materials

No comments:

Post a Comment