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

Semantic Layer:

Calculated column:

on AO projection: AO_DEPENDENT_OBJECT_NAME

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:

Result:

BW Layer:

External view based on Calculation view Dependency

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:

Execution:

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.