CDS Views Of SAP HANA For Beginners With Example.
Creating HANA views (Attribute, Analytic and Calculation) involve creating HANA artifacts in the HANA database layer (from SAP HANA Modeler Perspective). These views are then consumed in our ABAP reports either through ADBC, or external views. Hence it is a BOTTOM-UP approach.
Such Bottom-Up approach is not recommended because of the difficulty in creating and transporting the HANA artifacts (through Delivery Unites). In case any underlying HANA artifact is not included in the transport (via Delivery Unit and Transport Container), then there will not be any errors at the time of releasing the Transport.
For this reason, SAP recommended TOP-DOWN approach. The TOP-DOWN approach is equivalent to creation of HANA views, is CDS (Core Data Services) Views. These are created from ABAP perspective, can be saved directly in a TR and consumed in ABAP reports through open SQL.
Creating CDS Views:
In the following example, we will create a CDS view that takes as input the Client, a Target Currency, and a Date, and returns the list of all Customers who have booked flights before the input Date and their Booking Amounts converted to the Target Currency.
[Note: the following are not yet supported in CDS views:
- Aggregation (SUM, AVG, etc) of some currency_conversion or unit_conversion
- Select Top N
- Order By
- Nested use of Cast]
Step 1: Ensure that you are in ABAP perspective (if not already). Right click on your Package and follow the Menu path New -> other ABAP Repository Object -> Dictionary-> DDL Source. Press Next.
Step 3: Select a TR to save the CDS View. Press NEXT.
Step 4: In the next screen, Several predefined templates of CDS views are provided. In this particular example, we will select "Define Views With Parameters". Press FINISH.
Step 5: The following template CDS view will be displayed. In this window, we will have to write our code. Note that this code is neither in Open SQL, nor in Native SQL. It is in a language that is specific to CDS only.It dose not support all the Native SQL features either.
Step 6: First, we need to specify a SQL_VIEW_NAME (as highlighted in the above screen). Note that whenever we activate a CDS view, a DDIC view is created in the ABAP Data dictionary. Hence this SQL view Name must follow all the naming standards and conventions of ABAP DDIC Views.
Step 7: Write the code as shown below. Note how the input parameters (and their data-types) are declared.
Step 8: Save and Activate the CDS View. This will also create a DDIC View with the specified name.
Step 9: To test the CDS view, right click on the CDS view and select "Open Data Preview". Since this view has input parameters, it will prompt for the inputs.
Consuming CDS View:
CDS view can be consumed from the ABAP reports using the new open SQL with comma-separated Input Parameters (if any) specified within parenthesis immediately after the CDS view name.
In the following example, we will consume the CDS view created above to list the Top N Customers having highest sum total of Booking Amounts for flights before an input Date.
The following is the source code of this report.
REPORT zpus_consume_cds_demo.
* Selection-Screen Definition
PARAMETERS: p_to_c TYPE s_curr, "Target Currency
p_num TYPE int4, " No. of Customers
p_datum TYPE sydatum. "Flights Before
* Database Access
START-OF-SELECTION.
* First Check if Views With Parameters is supported
IF cl_abap_dbfeatures=>use_features(
EXPORTING
requested_features = VALUE #( (
cl_abap_dbfeatures=>views_with_parameters ) )
).
* Consume CDS View
SELECT id,
name,
postcode,
city,
country,
SUM( amt_to_curr ) AS amt_tot,
to_curr
UP TO @p_num ROWS
FROM zpus_cds_ddic( p_mandt = @sy-mandt,
p_flights_before = @p_datum,
p_to_curr = @p_to_c )
INTO TABLE @DATA(lt_customers)
GROUP BY id, name, postcode, city, country, to_curr
ORDER BY amt_tot DESCENDING.
ENDIF.
* Output Display
END-OF-SELECTION.
IF NOT lt_customers IS INITIAL.
* Display Output
cl_demo_output=>display_data( value = lt_customers
name = 'Consume CDS View with Input Parameters' ).
ENDIF.