How to Consume HANA Model Views in ADBC?
While practicing the different HANA Modelling Views, I had some interesting questions.
Can we ONLY consume data from tables which are present in HANA DB and not in SAP through ADBC?
What about HANA Views like Attribute, Analytic or Calculation View? Can we also get data from HANA Views using ADBC?
Before we check the answers, let us know the definition of the different Views.
1) Attribute Views: In the language of SAP Netweaver Business Warehouse Consultant, Attribute Views are like Dimensions. The noticeable difference is that Attribute Views are fully reusable throughout the system and not stuck up to a single model (like the Dimensions). In simple terms Attribute Views are views on one or more table that can be reused for multiple purpose.
Example: An Attribute View “FLIGHT” can show together Airline Code, Airline Connection Number and Flight Date into one Object. This Attribute View can be used in Analytical and Calculation Views where entities are relevant. Generally Attribute Views represent master data. But, however technically there is no restriction and its possible to make Attribute Views on transaction data.
2) Analytical Views: Again, in the language on SAP BW, Analytical Views can be roughly compared with Info Cubes or Info Sets in SAP ERP. As same it joins together one central Fact table which contains transaction data to report on, with number of tables or Attribute Views. You can create variables in an Analytical View.
3) Calculation Views: Calculation Views can be referred as combination of tables, Attributes views and Analytical Views to deliver a complex business requirement. They offer to combine different Analytical Views into one source of data for reporting.
Let me try to explain the Steps I followed to get the answers.
Consuming Attribute View
1. Select any package in content where Attribute is created.
2. Double click on it and see the view data.
This view is not display in SAP as view are directly create in HANA for BI and other analytical reporting purpose.
3. To check data click on Data Preview, right click on view name and check the Data Preview.
4. Data like below should display in report.
5. Click generate SQL to get the SQL statement for ADBC.
Below SQL statement is used for the ADBC.
Sample Program to Consume the Attribute View using ADBC. (Please pardon the simplicity in the code. This is just for demonstration)
*&----------------------------------------------------------------------------*
*& Report to demonstrate “How to Consume Attribute View
*& using ADBC from a Program”
*&----------------------------------------------------------------------------*
REPORT ZCONSUME_ATTRI_VIEW_ADBC.
TYPES: BEGIN OF ty_result,
Id TYPE STRING,
FirstName TYPE STRING,
City TYPE STRING,
Country TYPE STRING,
END OF ty_result.
* Data declaration
DATA:
lr_SQL_connection TYPE REF TO cl_SQL_connection,
lr_SQL_statement TYPE REF TO cl_SQL_statement,
lr_SQL_result_set TYPE REF TO cl_SQL_result_set,
lr_SQL_exception TYPE REF TO cx_SQL_exception,
lr_SQL_parameter_invalid TYPE REF TO cx_parameter_invalid,
lr_parameter_invalid_type TYPE REF TO cx_parameter_invalid_type, lr_salv_exception TYPE REF TO cx_salv_msg, lr_salv_alv TYPE REF TO cl_salv_table,
lt_result TYPE STANDARD TABLE OF TY_RESULT, ls_result TYPE ty_result,
lr_data TYPE REF TO data,
lv_where_clause_statement TYPE string,
lv_error_text TYPE string,
lv_where_mandt TYPE string,
lv_where_spras TYPE string.
Connect to dabatabse (HANA or Non-HANA)
* 1 Set the database connection
* If we do not pass the DB name, it would pull the default database
TRY.
CALL METHOD cl_SQL_connection=>get_connection
EXPORTING
con_name = SPACE
sharable = SPACE RECEIVING
con_ref = lr_SQL_connection.
CATCH cx_SQL_exception .
ENDTRY.
CALL METHOD lr_SQL_connection->create_statement
EXPORTING
tab_name_for_trace =
RECEIVING
stmt_ref = lr_SQL_statement.
data:
lv_statement type string.
* This is the SQL Statement which we generated in HANA
lv_statement =
|SELECT "Id", "FirstName","City","Country" FROM
"_SYS_BIC"."A_DEMO/AT_CUSTOMERS"|. “A_demo-package name AT_CUSTOMER-view name
* Execute the generated Stateme here
lr_SQL_result_set = lr_SQL_statement->execute_query( lv_statement ).
GET REFERENCE OF lt_result INTO lr_data.
lr_SQL_result_set->set_param_table( lr_data ).
lr_SQL_result_set->next_package( ).
cl_salv_table=>factory(
IMPORTING
r_salv_table = lr_salv_alv " Basic Class Simple ALV Tables
CHANGING
t_table = lt_result ).
* Show the output
lr_salv_alv->display( ).
Output:
Consuming Calculation View
In the above scenario, we checked with Attribute View. Let’s take another example. This time with Calculation View. Fundamentally, there is no difference. But still we want to show it.
We follow the same steps to get Calculation View data and Analytical View data.
Please check the example below.
From data preview (by right clicking on Calculation View CV_PRACT1) in Raw Data section we can see the data.
This example has Employee data with salary details.
To get the SQL statement which will be used in ADBC, we will get from SQL generate by right clicking the view CV_PRACT1 (select generate SQL). Below is screen shot of generated SQL statement.
Just use this SQL statement in ADBC while passing the SQL statement and you will get the output.
Similar Program to show how to use the generated SQL statement in ADBC.
*&------------------------------------------------------------------------*
*& Report to demonstrate “How to Consume Calculation View
*& using ADBC from a Program”
*&-----------------------------------------------------------------------*
REPORT ZCONSUME_CAL_VIEW_ADBC.
TYPES:
BEGIN OF ty_result,
EMP_ID TYPE STRING,
EMPNAME TYPE STRING,
CALDATE TYPE STRING,
CALMONTH TYPE STRING,
CALYEAR TYPE STRING,
DEPTNAME TYPE STRING,
BONUS TYPE STRING,
SAL TYPE STRING,
END OF ty_result.
* Data declaration
DATA:
lr_SQL_connection TYPE REF TO cl_SQL_connection,
lr_SQL_statement TYPE REF TO cl_SQL_statement,
lr_SQL_result_set TYPE REF TO cl_SQL_result_set,
lr_SQL_exception TYPE REF TO cx_SQL_exception,
lr_SQL_parameter_invalid TYPE REF TO cx_parameter_invalid,
lr_parameter_invalid_type TYPE REF TO cx_parameter_invalid_type,
lr_salv_exception TYPE REF TO cx_salv_msg,
lr_salv_alv TYPE REF TO cl_salv_table,
lt_result TYPE STANDARD TABLE OF TY_RESULT,
ls_result TYPE ty_result,
lr_data TYPE REF TO data,
lv_where_clause_statement TYPE string,
lv_error_text TYPE string,
lv_where_mandt TYPE string,
lv_where_spras TYPE string.
* Connect to dabatabse (HANA or Non-HANA)
* 1 Set the database connection
* If we do not pass the DB name, it would pull the default database
TRY.
CALL METHOD cl_SQL_connection=>get_connection
* EXPORTING
* con_name = SPACE
* sharable = SPACE
RECEIVING
con_ref = lr_SQL_connection.
CATCH cx_SQL_exception .
ENDTRY.
CALL METHOD lr_SQL_connection->create_statement
* EXPORTING
* tab_name_for_trace =
RECEIVING
stmt_ref = lr_SQL_statement.
data: lv_statement type string.
* This is the SQL Statement which we generated in HANA
lv_statement =
|SELECT "EMPID","EMPNAME","CALDATE","CALMONTH","CALYEAR","DEPTNAME",sum("BONUS") AS "BONUS",sum("SAL") AS "SAL" FROM "_SYS_BIC"."_A_DEMO/CV_PRACT1"|
&& |GROUP BY "EMPID","EMPNAME","CALDATE","CALMONTH","CALYEAR","DEPTNAME"|.
* Execute the generated Statement here
lr_SQL_result_set = lr_SQL_statement->execute_query( lv_statement ).
GET REFERENCE OF lt_result INTO lr_data.
lr_SQL_result_set->set_param_table( lr_data ).
lr_SQL_result_set->next_package( ).
cl_salv_table=>factory(
IMPORTING
r_salv_table = lr_salv_alv " Basic Class Simple ALV Tables
CHANGING
t_table = lt_result ).
* Show the output
lr_salv_alv->display( ).
lr_SQL_result_set->close( ).
Output
