Introduction
ABAP CDS views allow us to build very nice and structured applications for any solution or UI.
This blog is about how to build a very nice, simple and really fast WebUI application for really crazy data model and volumes, by just using ABAP CDS.
Business Case
I had to build a search (F4 WebUI Popup) for one very “specific” application.
We are using CRM IBASE model to store certain objects, which can be grouped into several CDS Views or entities. In order to keep SAP’s secrets, I will not reveal the names and will call them: Entity1, Entity2, Entity3.
Behind each entity, we have about 8, almost always the same tables or views. Here is the list of the tables with total numbers of entries in these tables: IBIN (275.447.044), IBIB (2.271.052), ZSIB_PRD_IDX (4.418.220), COMV_PRSHTEXT (7.184.985), ZSST_SY_SMVV (72.926), IBPART_IDX (8.129.592), BUT000 (17.244.188), ZSST_SY_HEAD (3.163.696), ZSST_BUS_ADM (288.391), ZSST_BUS_SM (288.391).
So you can see the data volumes are really tremendous! But HANA can handle it. Yes, all this runs on HANA database; didn’t I tell it before? :-)))
Step 1: Define your entities and build ABAP CDS views
At the beginning, you need to define your entities (Entity1, Entity2, Entity3) and build proper ABAP CDS views. I will not describe how to do it, it was already well described million times on SCN. I will just give you some examples.
Entity1
We just join 8 tables with hundreds of millions of entries inside! Entities 2 and 3 look very similar indeed. :-)))
Step 2: Define views for the relations and the searches
If you have multiple entities, as in mine case, probably you would like to connect them together and build the search views.
In my case for one search I had to use even union all. Therefore, my search CDS layer contains, in fact, two levels, so that I will just provide two ABAP CDS definitions.
Search Level 1: Entity1_SEARCH1
The entity Entity1_SEARCH2, which will be used in UNION ALL later, looks pretty similar.
Search Level 2: Entity1_SEARCH (combination of the search CDS from the level 1)
Performance Note: when you create a view, or execute UNION ALL statement (most probably on many databases, including HANA), the search though the parts (i.e. Entity1_SEARCH1, Entity1_SEARCH2) will be executed in parallel reducing the response time! Use UNION ALL and do not call two similar selects from ABAP side.
Step 3: Create search- and search result- structures
When everything is ready on DB side, it’s a time to develop in ABAP (NetWeaver).
For our search, we need to create the search and result structures in SE11. I will skip this step, as it’s not important and well described.
Should I remind you that for simplicity you should give the names to the fields as they appear to be in the CDS view or in the corresponding generated ABAP view representation.
Step 4: Develop GENIL search classes
In my case I was dealing with IBASE component and therefore I inherited the class from the standard CL_IBCOMPTOCOMPADV_IL. To implement a search, you need to implement the method: SEARCH_DYNAMIC.
I wanted to publish a complete code of the method IF_IBASE_IL_SEARCH~SEARCH_DYNAMIC, but I need to anonymize really a lot and therefore will just post a couple of the most important things.
Important Thing 1
Performance Note: When you build your view on 10 different tables using LEFT OUTER JOIN and then during the selection you are using the fields (in SELECT … or WHERE… clauses) only from 1 table, most probably (must for HANA) the other 9 tables will be just ignored.
It’s very important to keep the selection list as small as possible, and select the only fields, which will be shown on the screen. You can pass the fields visible to the end-user via the variable IS_QUERY_PARAMS-SELECTION_HITS[].
Important Thing 2
You should build your CDS views in way that no subsequent selects are needed. All the data has to be read from DB in one call.
Always apply Max Hits parameter, which can be retrieved from IS_QUERY_PARAMS-MAX_HITS.
Step 5: Enhance BOL/GENIL model
At this step you need to enhance BOL/GENIL model (or create your own) and let the system know your objects. The way how you do it actually depends on the application. For 1Order, BUPA, CASE or IBASE applications, different approaches must be used, and we will skip it. It was also quite nicely described on SCN already.
In my case I had to put my query and result objects into the table CRMC_OBJ_IBIL and redefine object instatiation in the class CL_CRM_IBASE_IL_FACTORY.
(Some other example see here:
https://blogs.sap.com/2015/12/11/extend-1order-searches-how-to-guide-for-customers/
https://wiki.scn.sap.com/wiki/display/Snippets/How+to+Extend+Model+for+Business+Transactions+with+Ne...)
So finally you should have the standard model to be enhanced or new custom model to be created.
Step 6: Build WebUI Component and Views
Finally, you need to consume your newly created BOL / GENIL objects. For that you might need to create a custom WebUI component with respective search and search result views and use the corresponding search and search result objects.
And of course, we will skip this part as well and fill focus on the most important things.
( For more details refer to https://blogs.sap.com/2010/03/28/crm-70-how-to-5e-search-view-and-search-result/ )
Remember, we said (switch on a boring professor’s voice and your imagination J))): “ …for better performance you should provide the list of the fields visible to the end user”. Yes, it’s easy. You can get it from personalization!
Your ON_SEARCH event handler should contain the following lines:
And the method GET_RV_FIELDS (Get Result View Fields) looks as below:
Relevant Information:
Extend 1Order Searches: How to guide for customers
https://blogs.sap.com/2015/12/11/extend-1order-searches-how-to-guide-for-customers/
... to add to Michael's post - "Virtual fields in CRM reporting Framework"
https://blogs.sap.com/2017/05/04/virtual-fields-in-crm-reporting-framework/
How to Extend Model for Business Transactions with New Relationships
https://wiki.scn.sap.com/wiki/display/Snippets/How+to+Extend+Model+for+Business+Transactions+with+Ne...
Handler Classes in SAP CRM WEBUI Framework
https://blogs.sap.com/2014/09/22/handler-classes-in-sap-crm-webui-framework/
Creating a Custom Genil/Bol object Model
https://blogs.sap.com/2012/08/06/creating-a-genilbol-object-model/
CRM 7.0 How to –5E Search View and Search Result
https://blogs.sap.com/2010/03/28/crm-70-how-to-5e-search-view-and-search-result/
Performance considerations when using ABAP Core Data Services on AnyDB
https://blogs.sap.com/2016/10/27/performance-considerations-using-abap-core-data-services-anydb/
ABAP CDS views allow us to build very nice and structured applications for any solution or UI.
This blog is about how to build a very nice, simple and really fast WebUI application for really crazy data model and volumes, by just using ABAP CDS.
Business Case
I had to build a search (F4 WebUI Popup) for one very “specific” application.
We are using CRM IBASE model to store certain objects, which can be grouped into several CDS Views or entities. In order to keep SAP’s secrets, I will not reveal the names and will call them: Entity1, Entity2, Entity3.
Behind each entity, we have about 8, almost always the same tables or views. Here is the list of the tables with total numbers of entries in these tables: IBIN (275.447.044), IBIB (2.271.052), ZSIB_PRD_IDX (4.418.220), COMV_PRSHTEXT (7.184.985), ZSST_SY_SMVV (72.926), IBPART_IDX (8.129.592), BUT000 (17.244.188), ZSST_SY_HEAD (3.163.696), ZSST_BUS_ADM (288.391), ZSST_BUS_SM (288.391).
So you can see the data volumes are really tremendous! But HANA can handle it. Yes, all this runs on HANA database; didn’t I tell it before? :-)))
Step 1: Define your entities and build ABAP CDS views
At the beginning, you need to define your entities (Entity1, Entity2, Entity3) and build proper ABAP CDS views. I will not describe how to do it, it was already well described million times on SCN. I will just give you some examples.
Entity1
define view Entity1
with parameters p_valid_at : timestamp
as select
from ibin as component
inner join ibib as ibase on component.mandt = ibase.mandt
and component.ibase = ibase.ibase
inner join zsib_prd_idx as prd_idx on component.mandt = prd_idx.client
and component.objnr = prd_idx.objnr
and prd_idx.status <> 'SMTHHERE'
inner join comv_prshtext as product on prd_idx.client = product.client
and prd_idx.product_guid = product.product_guid
and product.object_family = 'SMTHHERE'
inner join zsst_sy_smvv as smvv on smvv.client = product.client
and smvv.product_guid = product.product_guid
and smvv.valid_from <= :p_valid_at
and smvv.valid_to >= :p_valid_at
left outer join ibpart_idx as partner_idx on component.mandt = partner_idx.mandt
and ibase.ib_guid_16 = partner_idx.segment_recno
and partner_idx.segment = 1
inner join but000 as bp on bp.client = partner_idx.mandt
and bp.partner = partner_idx.partner
and ( bp.type = '2' or bp.type = '3' )
left outer join zsst_sy_head as sy_head on sy_head.client = product.client
and sy_head.product_guid = product.product_guid
{
26 fields from all tables
}
where
component.valfr <= :p_valid_at
and component.valto >= :p_valid_at
and ibase.ibtyp = 'SMTHHERE'
;
We just join 8 tables with hundreds of millions of entries inside! Entities 2 and 3 look very similar indeed. :-)))
Step 2: Define views for the relations and the searches
If you have multiple entities, as in mine case, probably you would like to connect them together and build the search views.
In my case for one search I had to use even union all. Therefore, my search CDS layer contains, in fact, two levels, so that I will just provide two ABAP CDS definitions.
Search Level 1: Entity1_SEARCH1
define view Entity1_SEARCH1
with parameters p_valid_at : timestamp
as select distinct from Entity1 (p_valid_at: $parameters.p_valid_at) as Ent1
left outer join Entity2 (p_valid_at: $parameters.p_valid_at) as Ent2 on Ent2.key = Ent1.key
left outer join Entity3 (p_valid_at: $parameters.p_valid_at) as Ent3 on Ent3.key = Ent2.key
left outer join SomeView as Smth on Smth.key = Ent1.key
{ 32 fields from all entities }
where
Ent1.usage not like '%ABC%'
and Ent1.usage not like '%XYZ%'
and Ent1.deletion_flag <> 'D'
and Ent1.deletion_flag <> 'X'
and
(
Ent2.solution_id is not null
or
( Ent2.solution_id is null
and Smth.sys_product_id is null
)
)
;
The entity Entity1_SEARCH2, which will be used in UNION ALL later, looks pretty similar.
Search Level 2: Entity1_SEARCH (combination of the search CDS from the level 1)
define view : Entity1_SEARCH
with parameters p_valid_at : timestamp
as
select distinct from
Entity1_SEARCH1 (p_valid_at: $parameters.p_valid_at) as Entity1_SEARCH1
{ All 32 fields }
union all
select distinct from
Entity1_SEARCH2 (p_valid_at: $parameters.p_valid_at) as Entity1_SEARCH2
{ All 32 fields }
;
Performance Note: when you create a view, or execute UNION ALL statement (most probably on many databases, including HANA), the search though the parts (i.e. Entity1_SEARCH1, Entity1_SEARCH2) will be executed in parallel reducing the response time! Use UNION ALL and do not call two similar selects from ABAP side.
Step 3: Create search- and search result- structures
When everything is ready on DB side, it’s a time to develop in ABAP (NetWeaver).
For our search, we need to create the search and result structures in SE11. I will skip this step, as it’s not important and well described.
Should I remind you that for simplicity you should give the names to the fields as they appear to be in the CDS view or in the corresponding generated ABAP view representation.
Step 4: Develop GENIL search classes
In my case I was dealing with IBASE component and therefore I inherited the class from the standard CL_IBCOMPTOCOMPADV_IL. To implement a search, you need to implement the method: SEARCH_DYNAMIC.
I wanted to publish a complete code of the method IF_IBASE_IL_SEARCH~SEARCH_DYNAMIC, but I need to anonymize really a lot and therefore will just post a couple of the most important things.
Important Thing 1
Performance Note: When you build your view on 10 different tables using LEFT OUTER JOIN and then during the selection you are using the fields (in SELECT … or WHERE… clauses) only from 1 table, most probably (must for HANA) the other 9 tables will be just ignored.
It’s very important to keep the selection list as small as possible, and select the only fields, which will be shown on the screen. You can pass the fields visible to the end-user via the variable IS_QUERY_PARAMS-SELECTION_HITS[].
" Build Select List...
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
build_select_list( is_query_params-selection_hints[] ).
Important Thing 2
You should build your CDS views in way that no subsequent selects are needed. All the data has to be read from DB in one call.
" Trigger a normal select...
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
select distinct (gt_select_list) "#EC CI_NOORDER
from Entity1_SEARCH( p_valid_at = @lv_timestamp ) ##DB_FEATURE_MODE[VIEWS_WITH_PARAMETERS]
where
field0 in @lr_field0
and field1 in @lr_field1
and field2 in @lr_field2
and field3 in @lr_field3
and field4 in @lr_field4
and field5 in @lr_field5
and field6 in @lr_field6
and (lv_smth_else)
and (lv_auth_tail)
into corresponding fields of table @lt_result
up to @lv_max_hits_new rows.
Always apply Max Hits parameter, which can be retrieved from IS_QUERY_PARAMS-MAX_HITS.
Step 5: Enhance BOL/GENIL model
At this step you need to enhance BOL/GENIL model (or create your own) and let the system know your objects. The way how you do it actually depends on the application. For 1Order, BUPA, CASE or IBASE applications, different approaches must be used, and we will skip it. It was also quite nicely described on SCN already.
In my case I had to put my query and result objects into the table CRMC_OBJ_IBIL and redefine object instatiation in the class CL_CRM_IBASE_IL_FACTORY.
(Some other example see here:
https://blogs.sap.com/2015/12/11/extend-1order-searches-how-to-guide-for-customers/
https://wiki.scn.sap.com/wiki/display/Snippets/How+to+Extend+Model+for+Business+Transactions+with+Ne...)
So finally you should have the standard model to be enhanced or new custom model to be created.
Step 6: Build WebUI Component and Views
Finally, you need to consume your newly created BOL / GENIL objects. For that you might need to create a custom WebUI component with respective search and search result views and use the corresponding search and search result objects.
And of course, we will skip this part as well and fill focus on the most important things.
( For more details refer to https://blogs.sap.com/2010/03/28/crm-70-how-to-5e-search-view-and-search-result/ )
Remember, we said (switch on a boring professor’s voice and your imagination J))): “ …for better performance you should provide the list of the fields visible to the end user”. Yes, it’s easy. You can get it from personalization!
Your ON_SEARCH event handler should contain the following lines:
method eh_onsearch.
data: lr_query_service type ref to cl_crm_bol_dquery_service.
data: lt_sel_fields type crmt_name_value_pair_tab.
lr_query_service ?= typed_context->searchquerynode->collection_wrapper->get_current( ).
lr_query_service->get_property_as_value( exporting iv_attr_name = 'SELECTION_HINTS'
importing ev_result = lt_sel_fields ).
" Reload Selection Fields...
delete lt_sel_fields where name = 'SEL_FIELD'.
append lines of get_rv_fields( 'ZYOURCOMP/YourPage#ZYOURCOMP/YourResultView' ) to lt_sel_fields[].
lr_query_service->set_property( exporting iv_attr_name = 'SELECTION_HINTS'
iv_value = lt_sel_fields ).
And the method GET_RV_FIELDS (Get Result View Fields) looks as below:
method get_rv_fields.
data: lt_path type table of string.
data: lr_window type ref to cl_bsp_wd_window.
data: lr_rv type ref to cl_bsp_wd_view_controller.
data: lr_config type ref to if_bsp_dlc_configuration.
data: ls_config type bsp_dlc_table_descr.
split iv_view_path at '#' into table lt_path.
if lt_path[] is initial.
return.
endif.
lr_window = me->view_manager->get_window_controller( ).
if lr_window is bound.
loop at lt_path assigning field-symbol(<fs_path>).
" First Level -> Window
if sy-tabix = 1.
lr_rv = lr_window->get_subcontroller_by_viewname( <fs_path> ).
else.
" Next Level -> View or Viewset
if lr_rv is bound.
lr_rv = lr_rv->get_subcontroller_by_viewname( <fs_path> ).
endif.
endif.
endloop.
" Latest View
if lr_rv is bound.
lr_config = lr_rv->configuration_descr.
if lr_config is bound.
call method cl_bsp_dlc_table_utility=>conf_xml_to_abap
exporting
iv_xml = lr_config->get_config_data( )
receiving
rs_table_config = ls_config.
" Take Only Standard, Showed Fields
loop at ls_config-columndefinition assigning field-symbol(<fs_column>)
where name cp 'STRUCT.*' and hidden = abap_false.
replace first occurrence of 'STRUCT.' in <fs_column>-name with space.
condense <fs_column>-name no-gaps.
append value #( name = 'SEL_FIELD' value = <fs_column>-name ) to et_fields[].
endloop.
endif. " if lr_config is bound.
endif. " if lr_rv is bound.
endif. " if lr_window is bound
endmethod.
Relevant Information:
Extend 1Order Searches: How to guide for customers
https://blogs.sap.com/2015/12/11/extend-1order-searches-how-to-guide-for-customers/
... to add to Michael's post - "Virtual fields in CRM reporting Framework"
https://blogs.sap.com/2017/05/04/virtual-fields-in-crm-reporting-framework/
How to Extend Model for Business Transactions with New Relationships
https://wiki.scn.sap.com/wiki/display/Snippets/How+to+Extend+Model+for+Business+Transactions+with+Ne...
Handler Classes in SAP CRM WEBUI Framework
https://blogs.sap.com/2014/09/22/handler-classes-in-sap-crm-webui-framework/
Creating a Custom Genil/Bol object Model
https://blogs.sap.com/2012/08/06/creating-a-genilbol-object-model/
CRM 7.0 How to –5E Search View and Search Result
https://blogs.sap.com/2010/03/28/crm-70-how-to-5e-search-view-and-search-result/
Performance considerations when using ABAP Core Data Services on AnyDB
https://blogs.sap.com/2016/10/27/performance-considerations-using-abap-core-data-services-anydb/
- SAP Managed Tags:
3 Comments
