Do we need to install sap hana studio to create a simple abap report in hana database when hana is connected as a secondry database
Do we need to install sap hana studio to create a simple abap report in hana database when hana is connected as a secondry database or can we write it in ECC which is primary(local databse) and execute in hana database?
this is the simple program :
REPORT YHANA_P3.
TABLES : mara.
DATA : LT_T1 TYPE STANDARD TABLE OF mara,
LWA_T1 LIKE LINE OF LT_T1 .
SELECT * FROM mara CONNECTION (‘SAP_HANA_CONNECTION’) INTO TABLE LT_T1 .
if sy-subrc = 0.
message ‘DATA FETCH FROM marA SUCCESSFUL’ TYPE ‘I’.
endif.
LOOP AT LT_T1 INTO LWA_T1.
WRITE:/ LWA_T1-matnr.
ENDLOterminates the program saying the referred table dosn’t exist.
Hi,
No you do not need to install the HANA studio.
The report can also be coded on the ABAP application server directly.
Regards.
YS
Thanks for the help!
I wrote a simple abap report in ecc and tried to run the same report extracting data from ORACLE(primary) and HANA(secondary) by adding CONNECTION(CONNECTION_NAME) while accessing HANA.I can't find any time difference when data is fetched from HANA.Ideally it hould be very fast,but it's not so.Why is that so?
The code is given below:
DATA : lv_count_hana TYPE sy-dbcnt,
lv_lines_hana TYPE i,
lv_timestamp_hana TYPE p,
lv_timestamp_1_hana TYPE p,
lv_total_time_hana TYPE sy-tabix,
lv_total_mins_hana TYPE string,
lv_total_hrs_hana TYPE string,
lv_date_hana TYPE sy-datum,
lv_date_p_hana TYPE sy-datum,
lv_time_hana TYPE sy-uzeit,
lv_time_p_hana TYPE sy-uzeit,
lv_seconds_hana TYPE tzntstmpl,
it_dd03l_hana TYPE TABLE OF dd03l,
lv_count TYPE sy-dbcnt,
lv_lines TYPE i,
lv_timestamp TYPE p,
lv_timestamp_1 TYPE p,
lv_total_time TYPE sy-tabix,
lv_total_mins TYPE string,
lv_total_hrs TYPE string,
lv_date TYPE sy-datum,
lv_date_p TYPE sy-datum,
lv_time TYPE sy-uzeit,
lv_time_p TYPE sy-uzeit,
lv_seconds TYPE tzntstmpl,
it_dd03l TYPE TABLE OF dd03l.
DATA :
lv_string_hana TYPE string,
lv_dbcnt_string_hana TYPE string,
lv_string TYPE string,
lv_dbcnt_string TYPE string.
START-OF-SELECTION.
SELECT COUNT(*) FROM dd03l CONNECTION ('SAP_HANA_CONNECTION') BYPASSING BUFFER.
lv_count_hana = sy-dbcnt.
SELECT COUNT(*) FROM dd03l BYPASSING BUFFER.
lv_count = sy-dbcnt.
WHILE lv_count_hana > 0.
CLEAR : lv_seconds_hana.
GET TIME STAMP FIELD lv_timestamp_hana.
SELECT * FROM dd03l CONNECTION ('SAP_HANA_CONNECTION') INTO TABLE it_dd03l_hana PACKAGE SIZE 100000.
GET TIME STAMP FIELD lv_timestamp_1_hana.
TRY.
CALL METHOD cl_abap_tstmp=>subtract
EXPORTING
tstmp1 = lv_timestamp_1_hana
tstmp2 = lv_timestamp_hana
RECEIVING
r_secs = lv_seconds_hana.
CATCH cx_parameter_invalid_range .
CATCH cx_parameter_invalid_type .
ENDTRY.
lv_total_time_hana = lv_seconds_hana + lv_total_time_hana.
DESCRIBE TABLE it_dd03l_hana LINES lv_lines_hana.
lv_count_hana = lv_count_hana - lv_lines_hana.
CLEAR : it_dd03l_hana.
REFRESH : it_dd03l_hana.
FREE : it_dd03l_hana.
GET TIME STAMP FIELD lv_timestamp_hana.
ENDSELECT.
ENDWHILE.
DATA : lv_output_hana TYPE swl_pm_cvh-duration.
DATA : lv_str_hana TYPE string,
lv_str1_hana TYPE string,
lv_str2_hana TYPE string.
CALL FUNCTION 'MONI_TIME_CONVERT'
EXPORTING
ld_duration = lv_total_time_hana
IMPORTING
lt_output_duration = lv_output_hana.
CONDENSE lv_output_hana.
SPLIT lv_output_hana AT ':' INTO lv_str2_hana lv_str1_hana lv_str_hana.
WHILE lv_count > 0.
CLEAR : lv_seconds.
GET TIME STAMP FIELD lv_timestamp.
SELECT * FROM dd03l INTO TABLE it_dd03l PACKAGE SIZE 100000.
GET TIME STAMP FIELD lv_timestamp_1.
TRY.
CALL METHOD cl_abap_tstmp=>subtract
EXPORTING
tstmp1 = lv_timestamp_1
tstmp2 = lv_timestamp
RECEIVING
r_secs = lv_seconds.
CATCH cx_parameter_invalid_range .
CATCH cx_parameter_invalid_type .
ENDTRY.
lv_total_time = lv_seconds + lv_total_time.
DESCRIBE TABLE it_dd03l LINES lv_lines.
lv_count = lv_count - lv_lines.
CLEAR : it_dd03l.
REFRESH : it_dd03l.
FREE : it_dd03l.
GET TIME STAMP FIELD lv_timestamp.
ENDSELECT.
ENDWHILE.
DATA : lv_output TYPE swl_pm_cvh-duration.
DATA : lv_str TYPE string,
lv_str1 TYPE string,
lv_str2 TYPE string.
CALL FUNCTION 'MONI_TIME_CONVERT'
EXPORTING
ld_duration = lv_total_time
IMPORTING
lt_output_duration = lv_output.
CONDENSE lv_output.
SPLIT lv_output AT ':' INTO lv_str2 lv_str1 lv_str.
CLEAR : sy-dbcnt.
CLEAR : lv_count_hana.
SELECT COUNT(*) FROM dd03l CONNECTION ('SAP_HANA_CONNECTION') BYPASSING BUFFER.
lv_dbcnt_string_hana = sy-dbcnt.
CLEAR : sy-dbcnt.
CLEAR : lv_count.
SELECT COUNT(*) FROM dd03l BYPASSING BUFFER.
lv_dbcnt_string = sy-dbcnt.
CONCATENATE 'The Execution Time is :' lv_str2_hana 'hours' lv_str1_hana 'minutes' lv_str_hana 'seconds for retrieving' lv_dbcnt_string_hana 'records from SAP HANA database' INTO lv_string_hana SEPARATED BY space.
CONCATENATE 'The Execution Time is :' lv_str2 'hours' lv_str1 'minutes' lv_str 'seconds for retrieving' lv_dbcnt_string 'records from Oracle database' INTO lv_string SEPARATED BY space.
WRITE:/ lv_string_hana.
WRITE:/ lv_string.
Thanks for the help!
I wrote a simple abap report in ecc and tried to run the same report extracting data from ORACLE(primary) and HANA(secondary) by adding CONNECTION(CONNECTION_NAME) while accessing HANA.I can't find any time difference when data is fetched from HANA.Ideally it hould be very fast,but it's not so.Why is that so?
The code is given below:
DATA : lv_count_hana TYPE sy-dbcnt,
lv_lines_hana TYPE i,
lv_timestamp_hana TYPE p,
lv_timestamp_1_hana TYPE p,
lv_total_time_hana TYPE sy-tabix,
lv_total_mins_hana TYPE string,
lv_total_hrs_hana TYPE string,
lv_date_hana TYPE sy-datum,
lv_date_p_hana TYPE sy-datum,
lv_time_hana TYPE sy-uzeit,
lv_time_p_hana TYPE sy-uzeit,
lv_seconds_hana TYPE tzntstmpl,
it_dd03l_hana TYPE TABLE OF dd03l,
lv_count TYPE sy-dbcnt,
lv_lines TYPE i,
lv_timestamp TYPE p,
lv_timestamp_1 TYPE p,
lv_total_time TYPE sy-tabix,
lv_total_mins TYPE string,
lv_total_hrs TYPE string,
lv_date TYPE sy-datum,
lv_date_p TYPE sy-datum,
lv_time TYPE sy-uzeit,
lv_time_p TYPE sy-uzeit,
lv_seconds TYPE tzntstmpl,
it_dd03l TYPE TABLE OF dd03l.
DATA :
lv_string_hana TYPE string,
lv_dbcnt_string_hana TYPE string,
lv_string TYPE string,
lv_dbcnt_string TYPE string.
START-OF-SELECTION.
SELECT COUNT(*) FROM dd03l CONNECTION ('SAP_HANA_CONNECTION') BYPASSING BUFFER.
lv_count_hana = sy-dbcnt.
SELECT COUNT(*) FROM dd03l BYPASSING BUFFER.
lv_count = sy-dbcnt.
WHILE lv_count_hana > 0.
CLEAR : lv_seconds_hana.
GET TIME STAMP FIELD lv_timestamp_hana.
SELECT * FROM dd03l CONNECTION ('SAP_HANA_CONNECTION') INTO TABLE it_dd03l_hana PACKAGE SIZE 100000.
GET TIME STAMP FIELD lv_timestamp_1_hana.
TRY.
CALL METHOD cl_abap_tstmp=>subtract
EXPORTING
tstmp1 = lv_timestamp_1_hana
tstmp2 = lv_timestamp_hana
RECEIVING
r_secs = lv_seconds_hana.
CATCH cx_parameter_invalid_range .
CATCH cx_parameter_invalid_type .
ENDTRY.
lv_total_time_hana = lv_seconds_hana + lv_total_time_hana.
DESCRIBE TABLE it_dd03l_hana LINES lv_lines_hana.
lv_count_hana = lv_count_hana - lv_lines_hana.
CLEAR : it_dd03l_hana.
REFRESH : it_dd03l_hana.
FREE : it_dd03l_hana.
GET TIME STAMP FIELD lv_timestamp_hana.
ENDSELECT.
ENDWHILE.
DATA : lv_output_hana TYPE swl_pm_cvh-duration.
DATA : lv_str_hana TYPE string,
lv_str1_hana TYPE string,
lv_str2_hana TYPE string.
CALL FUNCTION 'MONI_TIME_CONVERT'
EXPORTING
ld_duration = lv_total_time_hana
IMPORTING
lt_output_duration = lv_output_hana.
CONDENSE lv_output_hana.
SPLIT lv_output_hana AT ':' INTO lv_str2_hana lv_str1_hana lv_str_hana.
WHILE lv_count > 0.
CLEAR : lv_seconds.
GET TIME STAMP FIELD lv_timestamp.
SELECT * FROM dd03l INTO TABLE it_dd03l PACKAGE SIZE 100000.
GET TIME STAMP FIELD lv_timestamp_1.
TRY.
CALL METHOD cl_abap_tstmp=>subtract
EXPORTING
tstmp1 = lv_timestamp_1
tstmp2 = lv_timestamp
RECEIVING
r_secs = lv_seconds.
CATCH cx_parameter_invalid_range .
CATCH cx_parameter_invalid_type .
ENDTRY.
lv_total_time = lv_seconds + lv_total_time.
DESCRIBE TABLE it_dd03l LINES lv_lines.
lv_count = lv_count - lv_lines.
CLEAR : it_dd03l.
REFRESH : it_dd03l.
FREE : it_dd03l.
GET TIME STAMP FIELD lv_timestamp.
ENDSELECT.
ENDWHILE.
DATA : lv_output TYPE swl_pm_cvh-duration.
DATA : lv_str TYPE string,
lv_str1 TYPE string,
lv_str2 TYPE string.
CALL FUNCTION 'MONI_TIME_CONVERT'
EXPORTING
ld_duration = lv_total_time
IMPORTING
lt_output_duration = lv_output.
CONDENSE lv_output.
SPLIT lv_output AT ':' INTO lv_str2 lv_str1 lv_str.
CLEAR : sy-dbcnt.
CLEAR : lv_count_hana.
SELECT COUNT(*) FROM dd03l CONNECTION ('SAP_HANA_CONNECTION') BYPASSING BUFFER.
lv_dbcnt_string_hana = sy-dbcnt.
CLEAR : sy-dbcnt.
CLEAR : lv_count.
SELECT COUNT(*) FROM dd03l BYPASSING BUFFER.
lv_dbcnt_string = sy-dbcnt.
CONCATENATE 'The Execution Time is :' lv_str2_hana 'hours' lv_str1_hana 'minutes' lv_str_hana 'seconds for retrieving' lv_dbcnt_string_hana 'records from SAP HANA database' INTO lv_string_hana SEPARATED BY space.
CONCATENATE 'The Execution Time is :' lv_str2 'hours' lv_str1 'minutes' lv_str 'seconds for retrieving' lv_dbcnt_string 'records from Oracle database' INTO lv_string SEPARATED BY space.
WRITE:/ lv_string_hana.
WRITE:/ lv_string.
Hi Abhishekh,
Can you please create a discussion thread instead of Blog post. Ideally the questions should be asked as discussion thread and not posted as a blog.
Secondly, instead of count(*), can you please try sum(KF) type query which has aggregation. HANA is more efficient on column based operations than a row based operation. It is faster in row based operations, but you will see a considerable performance improvement in column based operations.
Regards,
Ravi
hi ravindra,
Thanks for the help!
i will surely create a discussion on this.
can you please elaborate on use of SUM(KF).how can we use ths in in a query with a example?
I know the aggregation concept and as it is column based storage in HANA it would be faster when compared to traditional database.
Can you please explain theusage of sum(kf).
Regards
AB.