Index tables for IC Inbox acceleration

[dmitry_sharshatkin]

1. Think of your business requirements and realization

First of all you need to understand what you want to achieve. Sounds too general and standard? Yes, but if you do any mistakes already here, it will be hard to change anything in the future…

1.1. Formulate your business requirements

You need to have a clear understanding what you want to achieve with the inbox. Any of the index tables: CRMD_ORDER_INDEX, CRMD_AUI_BTIDX or custom-ones, is just a flat container of data, which helps you to:

1.1.1. Improve the search

You need to have a clear understanding what you want to achieve with the inbox. Any of the index

- You can reach it over if the majority of the search-enabled fields will be placed in the index table.

- Normally in the index you are using only Business Partner, Transaction Type and the Inbox Status.

- You can always use any other standard fields or custom-defined fields.

- In the standard the authorization checks will not be supported by the inbox index table. After the list of transactions is found, it's sent to the CRM Reporting Framework (CL_CRM_QUERYINDEXAUI_RUN_BTIL=>CHECK_AUTHORIZATION).

If the list of the documents is huge, this puts additional performance overhead.

- You can also put a filter on the transactions. For instance you can store only not-completed transactions. In this case, the table should always be small enough.

- You can set up different tables to support different searches. The split can be done according to any need, - based on business role, search criteria, etc.

1.1.2. Improve the reading of data and population of the screen fields

- You can reach it over if the majority of the fields on the screen will be placed into the index table.

- You can place standard and the custom fields into your index table.

- You can place not only the identifications (Transaction ID, GUID, Codes), but also the data behind (Descriptions, Texts, Concatenated Texts, etc.)

- You can set up different tables to support even different views.

Hint: Remember that by means of DO_CONFIG_DETERMINATION method of the view controller, you can dynamically switch the configuration of the result table:

http://scn.sap.com/thread/2131172

1.2. Build you strategy

Once you know what exactly you want to achieve you need to know how you want to achieve this. Basically you need to answer the following questions:

1). What will be the index table(s)?

You need to define how many tables you want to create and what for.
Hint: In fact nothing is blocking you from developing several tables and building a database view on them.

2). Which fields will be in the table(s)?

You need to define which fields you would like to see in the tables.
Which of them you would like to use for the search, representation and the authorization checks.

3). How the tables will be populated?

You need to consider initial load and the regular updates. Initial load is normally performed by automatically generated report, which can be adjusted. The regular delta updates are normally done from BADI implementation ORDER_SAVE, which can also be generated automatically. If you chose this option, you'd better use update function module. You can also develop a report for that, but this option can be recommended only if the save performance overhead is significant or not possible due to application issues.

4). How the tables will be deleted?

You should not forget about how the table(s) will be deleted. You need to consider both: full deletion of the table and regular or periodic deletion. For both you need to develop custom-reports. If you wish to implement online deletion, you can use standard BADIs:
- ORDER_SAVE BADI can be used for online manipulation over the existing documents, for instance, when the status is changed.
- ARC_CRM_ORDER_DELETE can be used to update the index table when the transaction is deleted.

1.3. Example

In my example the table ZDMSH_ICSRV_IDX will contain only not confirmed (status <> I1005) service-related transactions (object type = BUS2000116).

Table ZDMSH_ICSRV_IDX will contain the same fields as the standard index table CRMD_AUI_BTIDX, plus:
DIS_CHANNEL (Distribution Channel),
SALES_ORG (Sales Organization ID),
SALES_ORG_RESP (Organizational Unit (Sales)),
SERVICE_ORG (Service Organization) and
SERVICE_ORG_RESP (Organizational Unit (Service)), which can be used for authorization checks and
ZZ_BILL_TO (Bill to Party),
ZZ_PAYER (Payer),
ZZ_SERV_RCPNT (Service Recipient), which supposed to be shown on the screen.

The table will be built using the standard customizing: SPRO -> CRM -> Interaction Center Web Client ->Customer-Specific System Modifications -> Business Transaction Search.

The search for non-completed service transactions will be supported by the table ZDMSH_ICSRV_IDX. (As of today, the authorization checks are not considered, but in fact it's very easy for implementation).

All other searches will not be changed and will be supported by the standard index table CRMD_AUI_BTIDX.

The table ZDMSH_ICSRV_IDX will not store completed transactions. It will be populated from ORDER_SAVE BADI using the update function module. The same functionality will be used to delete the data from the table, once the document reaches the status completed - I1005.

Initial load will be performed using the automatically generated report.

2. Check standard solutions and pre-requisites

You may need to use or refer to the standard-delivered index table and therefore we start from this.

SAP delivers the index table CRMD_AUI_BTIDX out of box. Below you find what needs to be done to have this working.

2.1. Install EHP3

To be able to consume this solution, you need to have EHP3 to be installed on your SAP CRM 7.0.

2.2.  Activate relevant business functions

In transaction SFW5 the following business functions need to be activated: CRM_IC_INBOX, CRM_IC_INBOX2.

You can also activate CRM_IC_INBOX_ACC, which is for HANA acceleration.

2.3. Initial load of the table CRMD_AUI_BTIDX

In order to perform the initial load of the data the report CRM_AUI_BT_INDEX_INITIAL_LOAD has been delivered.

Hint: For performance you can run several background jobs in parallel. Also parallelization should be enabled for each background job.

2.4. Regular updates of the table CRMD_AUI_BTIDX

Regular updates to the table CRMD_AUI_BTIDX are done from the standard. The function CRM_AUI_INDEX_SAVE is called from the function CRM_ORDER_SAVE_OW.

2.5. Enhancing the table CRMD_AUI_BTIDX

The table CRMD_AUI_BTIDX can be enhanced to store the custom fields. These fields can be used for the search and representation purposes.  In order to write the custom data into this table you can enhance the standard method: CL_CRM_AUI_BTIDX => READ_HEADER.

Hint: It's worth to enhance the bottom of the method. See the following posts for more information:
How to Integrate New “Result List Attributes” into the Agent Inbox (http://scn.sap.com/docs/DOC-39631)
How to Integrate a New Search Attribute into the Agent Inbox (http://scn.sap.com/docs/DOC-39532)

2.6. How the standard switch is implemented

The switch is implemented in CL_CRM_AUI_ADVQUERY_SERVICE => GET_DQUERY_RESULT.

2.7. Define Inbox Profile

In the customizing you need to adjust your inbox profiles: SPRO -> CRM -> Interaction Center Web Client -> Agent Inbox -> Define Inbox Profiles.

Activate: Lean Search = X and Business Transaction Table = X.

2.8. Adjust further Inbox customizing

In the customizing you need to adjust further inbox customizing: SPRO -> CRM -> Interaction Center Web Client -> Agent Inbox ->  …

3. Build the custom index for IC Inbox

3.1. Build the custom index table

The process of creation of the custom index table is nicely described in the following document:

http://johanvanzijl.com/wp-content/uploads/2008/05/inbox-bt-search.pdf

3.2. Creation of custom table

You can build a custom index table for the IC inbox as per standard functionality available since CRM 5.0. Follow the implementation guide: SPRO -> CRM  Interaction center Web Client -> Customer-Specific system Modifications -> Business Transaction Search -> Define Search Index for Business Transaction Search.

There you need to specify the table fields using existing flat structures. You can use the structures: CRMT*_*_IX or any other one, e.g. CRMT_AUI_INDEX, which is used in the standard index table CRMD_AUI_BTIDX.

Hint: Later you will be using the method CL_CRM_IC_SOS_QUERY_MANAGER => EXECUTE_QUERY, which returns CRMT_REPORT_LOCATORLIST_TA. So that for your new index table you can take the fields from the structure CRMT_REPORT_LOCATORLIST. In this case you can just use MOVE-CORRESPONDING command to simplify the mapping process.

Hint: You will also need to construct BTAUISearchResult (BOL/GENIL) objects. This object has a structure CRMS_AUI_SRCH_RESULT. Usage of the fields from this structure can also simplify the mapping process.

Next we set up the fields with the filters. Normally you can set up the filters for corresponding business partner functions.

But in fact this is not necessary fields; this just will be used for automatic generation of the ORDER_SAVE BADI generation and the generation of the initial load report. If you plan to re-write the ABAP coding, you can customize only the flat structure.

You do not need to place fields CLIENT and GUID. These fields will be automatically added to the index table structure. These fields will be considered as primary database key.

Hint: The following customizing tables are involved:

CRMC_IC_SOS_TABL – contains the «header» information for the index table. Used for index table determination.

CRMC_IC_APPL_SOS – contains the fields' information. Used for building the dynamic ABAP query.
CRMC_IC_APPL_SO2 – contains the structures information. Used for building the dynamic ABAP query.

3.3. Building the query class

The QUERYCLASS from CRMC_IC_SOS_TABL (ZL_DMSH_IC_SOS_QUERY) is used for building and executing the SQL query based on the selection criteria provided from IC.

This class needs to be inherited or copied from the standard class CL_CRM_IC_SOS_QUERY. In my case I had to change core methods and thus it was copied. If the custom class is not mentioned in the table, the standard class CL_CRM_IC_SOS_QUERY will be used.

Hint: The global variable GR_RESULT_LIST will contain the reference to result of the selection from your index table. This table will be of type:  TYPE STANDARD TABLE OF <YOUR INDEX TABLE>.

3.3.1. Manage authorizations in custom-table

As mentioned in the chapter 1.1.1. «Improve the search», in the standard the authorization checks are addressed to CRM Reporting Framework. This means that the guids of the identified transactions are passed as search criteria to the RFW. In case the number of transactions is huge, the number of accesses to CRMD_ORDER_INDEX table will also be considerable. And this can significantly slow down the performance.

Instead you can store the required authorization fields inside the index table and build the query in a way that the authorization fields are correctly mentioned in the where clause, which is dynamically generated. You can use the following standard functions for these purposes.

Function	Authorization Object	Description
CRM_REPORT_RF_CHECK_AUTHORITY	All below	Considers all standard checks as CRM RFW
CRM_REPORT_RF_AUTH_OBJ_ORD_LP	CRM_ORD_LP	Visibility in Org. Mode (Dynamic)
CRM_REPORT_RF_AUTH_OBJ_ORD_PR	CRM_ORD_PR	Transaction type (Static)
CRM_REPORT_RF_AUTH_OBJ_ORD_OE	CRM_ORD_OE	Allowed Organ. Units (Static)
CRM_REPORT_RF_AUTH_OBJ_ORD_PO	CRM_ORD_PO	Business Transaction Type
CRM_REPORT_RF_AUTH_OBJ_ORD_OP	CRM_ORD_OP	Allowed org.units: purch.
CRM_REPORT_RF_AUTH_OBJ_ORD_RS	CRM_ORD_RS	Authorization based on relationship with document partners

3.4. Activating the index table

While activating the index table from SPRO, the ORDER_SAVE BADI implementation - <YOUR_INDEX>_BADI and the initial load report <YOUR_INDEX>_ILOAD will be generated.

3.5. Writing the ORDER_SAVE BADI

In my case the implementation ZDMSH_ICSRV_IDX_BADI has been generated.  The initial code is generated automatically depending on the customizing set in the IMG.

In my case I have created a separate class (ZL_DMSH_SOS_IDX_UPDATE) which implements the BADI interface - IF_EX_ORDER_SAVE.

The method CHANGE_BEFORE_UPDATE looks as below:

CHANGE_BEFORE_UPDATE
method if_ex_order_save~change_before_update.   data: lr_instance type ref to if_ex_order_save.   lr_instance = zl_dmsh_sos_idx_update=>get_instance( ).   if lr_instance is not initial and      lr_instance is bound.     lr_instance->change_before_update( iv_guid = iv_guid ).   endif. endmethod.

3.6. Writing the initial load report

In my case the report ZDMSH_ICSRV_IDX_ILOAD has been generated. The following code has been used.

ZDMSH_ICSRV_IDX_ILOAD

data: lr_instance type ref to if_ex_order_save. * Get Index Update Entity   lr_instance = zl_dmsh_sos_idx_update=>get_instance( ). * Select the guids to be processed   select guid from crmd_orderadm_h into table lt_guid where (lv_where). * Proces the guids   loop at lt_guid into lv_guid.     if lr_instance is not initial and        lr_instance is bound.       lr_instance->change_before_update( iv_guid = lv_guid ).       commit work and wait.       select single guid into (lv_guid) from zdmsh_icsrv_idx where guid = lv_guid.         if sy-subrc = 0.           add 1 to lv_entries.         endif.       endif.     endloop.

As you can see we re-used the same functionality we used in the BADI - ZL_DMSH_SOS_IDX_UPDATE.

3.7. Change the standard execution logic

In fact to make use of the custom index table, we need to call CL_CRM_IC_SOS_QUERY_MANAGER => ECEUTE_QUERY.

If you are not using the standard BT inbox index table CRMD_AUI_BTIDX, then GET_1O_QUERY_RESULT will be called (see picture below).

In this case the functionality CL_CRM_IC_SOS_QUERY_MANAGER => ECEUTE_QUERY will be executed from the standard. Or if it's not active or index is not applicable, the standard RFW will be called.

If the index table CRMD_AUI_BTIDX is activated (as in our case), the method GET_1O_INDEX_QUERY_RESULT will be executed. In this case the functionality CL_CRM_IC_SOS_QUERY_MANAGER => ECEUTE_QUERY obviously will not be called.

At this point we need to continue with index query as we still want to use the standard index query. We need to change the logic for the index query to make sure that we still can use our own indices, even if the standard index is activated.

Search	Query Name	Standard Class
Normal (Custom Index or RFW)	BTAdvQueryAUI	CL_CRM_QUERYAUI_RUN_BTIL
Standard Index CRMD_AUI_BTIDX	BTAdvQueryIndexAUI	CL_CRM_QUERYINDEXAUI_RUN_BTIL

We do a substitution of the GENIL implementation for the query BTAdvQueryIndexAUI via the customizing table CRMC_OBJ_BTIL_C.

Our new class needs to be inherited from the standard class CL_CRM_QUERYINDEXAUI_RUN_BTIL; however the last one is marked final. We need to SAP Note: 2107260 – «Class CL_CRM_QUERYINDEXAUI_RUN_BTIL is marked as final» beforehand to overcome this problem.

Search	Query Name	Custom Class
Standard Index CRMD_AUI_BTIDX	BTAdvQueryIndexAUI	ZL_DMSH_QUERYINDEXAUI_RUN_BTIL

Should I remind you that in the table CRMC_OBJ_BTIL_C the class name should be posted without postfix _RUN and _BTIL.

We re-define the search method in a way that it distinguishes the searches that are supported by custom-developed indices, in our case it's just one index table.

ZL_DMSH_QUERYINDEXAUI_RUN_BTIL=>IF_CRM_QUERY_RUNTIME_BTIL~GET_DYNAMIC_QUERY_RESULT

method if_crm_query_runtime_btil~get_dynamic_query_result. * check if we can use the customer index table     if check_custom_index( it_selection_parameters ) = abap_true. * Read the Custom Index Table       call method me->get_from_custom_index         exporting           is_query_params         = is_query_params           it_selection_parameters = it_selection_parameters           ir_root_list            = ir_root_list.     else. * Use the standard query       call method super->if_crm_query_runtime_btil~get_dynamic_query_result         exporting           is_query_params         = is_query_params           it_selection_parameters = it_selection_parameters           ir_root_list            = ir_root_list.     endif.   endmethod.

Below method contains checks for supportability by the custom indices. In our case all queries for non-completed service documents should go into our index table.

ZL_DMSH_QUERYINDEXAUI_RUN_BTIL=>CHECK_CUSTOM_INDEX

method check_custom_index.     data: ls_selection_parameters like line of it_selection_parameters.     data: lv_process_type type crmt_process_type.     data: ls_proc_type type crmc_proc_type.    rv_code = abap_false.     read table it_selection_parameters into ls_selection_parameters                                          with key attr_name = 'MAINCATEGORY'.     lv_process_type = ls_selection_parameters-low.     call function 'CRM_ORDER_PROC_TYPE_SELECT_CB'       exporting         iv_process_type      = lv_process_type       importing         es_proc_type         = ls_proc_type       exceptions         entry_not_found      = 1         text_entry_not_found = 2         others               = 3. * we store only Service- related transactions in the table     if sy-subrc = 0 and ls_proc_type-object_type = 'BUS2000116'. * we store only non-completed transactions       read table it_selection_parameters into ls_selection_parameters                                           with key attr_name = 'STATUS'.       if sy-subrc = 0 and ls_selection_parameters-low <> '0003'.         rv_code = abap_true.       endif.     endif.   endmethod.

3.8. Implement the BADI CRM_IC_SOS_INDEX

Maybe you have noticed that CL_CRM_IC_SOS_QUERY_MANAGER => ECEUTE_QUERY is executing the BADI.

The method GET_INDEX_NAME can be used in order to get the exact index that need to work at this point in time. In the check described above we just need to distinguish if we run a standard search or via the custom tables, which can be several, e.g. one for Closed documents, one for Open documents. Such check can be done at the level of this BADI.

The BADI is specified as per IMG: SPRO -> CRM -> Interaction Center WebClient -> Customer-Specific System Modifications -> Business Transaction Search ->  BAdI: Determination of Search Index for Business Transaction Search.

The BADI CRM_IC_SOS_INDEX should return us the correct index name. Note that it could be several indices and the proper name should be retrieved by this BADI.

ZCL_IM_DMSH_ICSRV_IDX_SOS => IF_EX_CRM_IC_SOS_INDEX~GET_INDEX_NAME
method if_ex_crm_ic_sos_index~get_index_name.     if iv_caller = 'ZL_DMSH_QUERYINDEXAUI_RUN_BTIL' and " Place from where it's called        iv_profile = 'ZDMSH_IC_LI' and                   " WebUI Business Role        it_parameters[] is not initial.                  " IC Inbox search criteria       ev_indexname = zl_dmsh_sos_idx_update=>gc_index_name_srv. " ZDMSH_ICSRV_IDX     endif.   endmethod.

You might get problems passing IT_PARAMETERS into the BADI implementation. This is due to outdated BADI generation. You should re-generate the BADI following SAP Note: 893181 – «BAdI CRM_UPLOAD_CO was not generated correctly».

3.9. Getting index data line by line

The result of the method CL_CRM_IC_SOS_QUERY_MANAGER => ECEUTE_QUERY is a reference to the table CRMT_REPORT_LOCATORLIST_TA, which might have totally different fields as of your index table. But you need to make a proper mapping of your fields, meaning you need to get the data from your table. There is a functionality that can provide you the reference to the particular line of the result table GR_RESULT_LIST. It's a method CL_CRM_IC_SOS_QUERY_MANAGER => GET_INDEX_DATA_REF.

To be able to get the resulted table line by line you can use the following code:

Use of CL_CRM_IC_SOS_QUERY_MANAGER => GET_INDEX_DATA_REF

* run the query vis the standard index manager    lr_result_list ?= cl_crm_ic_sos_query_manager=>execute_query(      iv_caller               = 'ZL_DMSH_QUERYINDEXAUI_RUN_BTIL'    iv_profile              = lv_profile    iv_parameters           = lt_parameter    iv_requested_fields     = lt_parameter    it_selection_parameters = it_selection_parameters). * check the result list is not initial     check lr_result_list is not initial. * get the index line from the global buffers     data: lv_guid type crmt_object_guid.     data: lr_index_line type ref to data.     data: ls_index_line type zdmsh_icsrv_idx.     data: ls_attributes type crms_aui_srch_result_hana.     data: lt_result type crmt_aui_srch_result_hana.     data: ls_result type crms_aui_srch_result.     field-symbols: <fs_index_line>   type any,                    <fs_locator_tab>  type crmt_report_locatorlist_ta,                    <fs_locator_line> type crmt_report_locatorlist.     assign lr_result_list->* to <fs_locator_tab>.     if <fs_locator_tab> is assigned.       loop at <fs_locator_tab> assigning <fs_locator_line>.         lv_guid = <fs_locator_line>-guid.         clear: lr_index_line.         lr_index_line ?= cl_crm_ic_sos_query_manager=>get_index_data_ref( lv_guid ).         if lr_index_line is not initial.           assign lr_index_line->* to <fs_index_line>.           if sy-subrc = 0.             ls_index_line = <fs_index_line>.

3.10. Map data to BOL/GENIL structure

You also need to map the data to the BOL/GENIL object BTAUISearchResult, which is of structure CRMS_AUI_SRCH_RESULT. Here you can re-use the standrad functionality e.g. CL_CRM_QUERYINDEXAUI_RUN_BTIL => POSTPROCESSING or develop a custom one.

At WebUI level the object BTAUIView (CRMST_INBOX_RESULTLIST) is used.

Hint: If the mapping requires access to other tables, e.g. BUT000 or ADRC, it's worth to implement a mass read or pre-buffering on API level here. This will help to avoid SELECT SINGLE on the database.

Hint: You can enhance the standard objects BTAUISearchResult (CRMS_AUI_SRCH_RESULT) and BTAUIView (CRMST_INBOX_RESULTLIST) according to your needs.

Hint: You can develop your own ZZ-fields on the WebUI level. Inside the GETT'ers you can use CL_CRM_IC_SOS_QUERY_MANAGER => GET_INDEX_DATA_REF to read access the index fields.

3.11. Customer Implementation Class for the Inbox Item Type

An implementation class in the inbox contains the ABAP code used to handle events or attributes for a particular inbox item type. The inbox architecture allows you to introduce new attributes or modify existing ones.

The following table provides an overview of the standard implementation classes by inbox item type:

Object	Implementing Class
Inbox Item: Case	CL_CRM_AUI_CASE
Inbox Item: Fax	CL_CRM_AUI_FAX
Inbox Item: Letter	CL_CRM_AUI_LET
Inbox Item: ERP Sales Order	CL_CRM_AUI_ERP
Inbox Item: OneOrder	CL_CRM_AUI_ONEORDER
Inbox Item: Outbound Correspondence	CL_CRM_AUI_OUTCORRESP
Inbox Item: SAP Office – Outbound E-Mail	CL_CRM_AUI_SAPOFFICE
Inbox Item: Workflow Workitem	CL_CRM_AUI_WF_WORKITEM
Inbox Item: Workitem – Inbound Email, Fax and Letter	CL_CRM_AUI_WORKITEM

The settings are done in the customizing table CRMC_AUI_ITEMSCL or as per IMG: SPRO -> CRM -> Agent Inbox -> Basic Settings for Item Types ->  Assign Implementation Classes.

You must create a new user-defined implementation class according to the ABAP OO paradigm: A new implementation class must be inherited from the standard implementation class.

See more about enhancing the standard IC Inbox objects here: http://scn.sap.com/docs/DOC-39631