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 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. 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. 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(). " First Level -> Window if sy-tabix = 1. lr_rv = lr_window->get_subcontroller_by_viewname( ). else. " Next Level -> View or Viewset if lr_rv is bound. lr_rv = lr_rv->get_subcontroller_by_viewname( ). 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() where name cp 'STRUCT.*' and hidden = abap_false. replace first occurrence of 'STRUCT.' in -name with space. condense -name no-gaps. append value #( name = 'SEL_FIELD' value = -name ) to et_fields[]. endloop. endif. " if lr_config is bound. endif. " if lr_rv is bound. endif. " if lr_window is bound endmethod.