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.
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
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
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.
Please provide table definition for:
ReplyDeleteZTCT_DEP
Program cannot be activated without it!