How-To Guide: How to create an InfoSet that supports multi-value Segmentation for High Volume

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 1 of 16

®

SAP How-To Guide

1

Overview .......................................................................................... 3

2

Problem description ....................................................................... 3

3

Prerequisites ................................................................................... 4

4

Creating the InfoSet ....................................................................... 4

5

Data Source and Attribute List creation ...................................... 8

5.1 5.2

Data Source creation ........................................................................................ 8 Attribute List creation ........................................................................................ 9

6

Segmentation ................................................................................ 10

7

Delta Handling .................................................................................. 11

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 2 of 16

®

SAP How-To Guide

1 Overview This document describes the steps necessary to create an InfoSet that can be used to segment multi-value attributes of business partners in the high-volume segmentation scenario. An attribute is multi-value if there can be more than one value for a single Business Partner. Chapter 2 describes the problem in detail. Chapter 3 describes the necessary coding prerequisites. Chapter 4 describes the creation of an example InfoSet that solves this problem. Chapter 5 describes how to setup the segmentation customizing (Data Source and an Attribute List) to use this InfoSet in segmentation. Chapter 6 shows how to perform segmentation upon this InfoSet. Chapter 7 finally shows how to enable delta handling for this example.

2 Problem description The main problem is that all values for a specific business partner have to be loaded to the high-volume engine in one function call; each subsequent call deletes the values loaded up before and replaces them with the new ones. Therefore the InfoSet has to guarantee that each package of data that is returned contains all possible values for every business partner that is contained in this package. Suppose the following database setup for your multi-value attribute: Partner

Activity

Priority

Status

180

1

high

planned

180

2

medium

planned

180

3

low

visited

181

4

high

visited

181

5

low

planned

181

6

high

cancelled

182

7

high

cancelled

183

8

low

planned

So you have multi-value attributes for your business partners (i.e. there is more than one value of an attribute for each business partner key) and you want them to be dependent, i.e. “select all Partners that have an activity with priority “high” and status “visited”, you will get “181” as result and not “180” and “181”. Suppose furthermore that the chosen load package size is 2 (which does not make any sense in the real world, but is good to explain the problem here).

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 3 of 16

®

SAP How-To Guide If you read this table via a standard table access InfoSet and load the result to the high volume engine, you will get Partner

Activity

Priority

Status

180

3

low

visited

181

5

low

planned

181

6

high

cancelled

182

7

high

cancelled

182

8

low

planned

Since (180;1), (180;2) and (181;4) are deleted during the load of the respective next package. Furthermore, you cannot specify that the InfoSet returns the objects ordered by their GUID, it can be that the result packages are completely unsorted regarding the GUID. This means you have no chance to see if a given GUID will be returned by the InfoSet in a later package. This Problem can be solved using a data reading program.

3 Prerequisites The steps described in this document are only relevant for SAP CRM 7 or a higher release. Additionally, SAP Note 1688900 must be implemented or the following Support Package must be deployed to the SAP CRM System: 

Support Package 11 for SAP CRM 7

If you want to use delta handling, this has to be switched on in transaction SPRO: SAP Reference IMG->Customer Relationship Management->Marketing->Segmentation->High Volume Segmentation->Define Delta Source Objects for High Volume Segmentation.

4 Creating the InfoSet First of all you need a structure to control the communication of the Segment Builder and the Infoset. Structure ZHOWTO_MULTIVALUE looks like this for our example:

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 4 of 16

®

SAP How-To Guide The DB table ZHOWTO_MULTIVAL where you store your multi-value data looks like this:

Now you can create your InfoSet using transaction sq02:

In your InfoSet, choose “Data reading program”

Now we describe the structure of the data reading program of the InfoSet. It consists of several sections: Section “declarations”

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 5 of 16

®

SAP How-To Guide Here you declare your local variables. * *---------------------------------------------------------------------* * declarations * (insert your declarations in this section) *---------------------------------------------------------------------* *** the output table with line type of the comm. structure DATA it_data TYPE STANDARD TABLE OF zhowto_multivalue. *** DB cursor needed to retrieve the data DATA gv_db_curr TYPE cursor. *** the last partner GUID of the previous package DATA gv_last_partner_guid TYPE bu_partner_guid. *** the number of lines in the current package DATA lv_lines TYPE sy-tabix. *** references a line in the output table FIELD-SYMBOLS: TYPE zhowto_multivalue.

Section “selection screen statements” not needed in our cases; just leave it unchanged. Section “read data into IT_DATA” In this section you retrieve the data from the data base *-------------------------------------------------------------------* * read data into IT_DATA *-------------------------------------------------------------------* * (select your data here into internal table IT_DATA) *** initialize variables CLEAR it_data. UNASSIGN . IF %rtmode-acc_check IS NOT INITIAL. *** %rtmode-acc_check is set if you want to read the first *** package of this InfoSet CLEAR gv_last_partner_guid. ENDIF. *** open a cursor to select the data OPEN CURSOR WITH HOLD gv_db_curr FOR SELECT but~partner_guid howto~partner howto~priority howto~status FROM but000 AS but INNER JOIN zhowto_multival AS howto ON but~partner = howto~partner WHERE but~partner_guid > gv_last_partner_guid ORDER BY but~partner_guid. *** fetch the next package. *** %rtmode-pack_size holds the customized package size FETCH NEXT CURSOR gv_db_curr INTO CORRESPONDING FIELDS OF TABLE it_data PACKAGE SIZE %rtmode-pack_size.

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 6 of 16

®

SAP How-To Guide *** close the cursor CLOSE CURSOR gv_db_curr. *** check how many lines we have read DESCRIBE TABLE it_data LINES lv_lines. *** read the last line of the package. READ TABLE it_data ASSIGNING INDEX lv_lines. *** We want to ensure that values for a partner GUID *** is not spread across several packages. We achieve this by *** a) return the records sorted by GUID *** b) always return all value records for the last GUID *** in a package, even if we return slightly more records *** as given in %rtmode-pack_size IF sy-subrc EQ 0. *** store the last GUID in this package. We need this in the *** next package run. gv_last_partner_guid = -partner_guid. *** delete all value records for this GUID *** from the output table DELETE it_data WHERE partner_guid = -partner_guid. *** manually select all value records for this GUID from DB *** and append them to the output table. SELECT but~partner_guid howto~partner howto~priority howto~status FROM but000 AS but INNER JOIN zhowto_multival AS howto ON but~partner = howto~partner APPENDING CORRESPONDING FIELDS OF TABLE it_data WHERE but~partner_guid = gv_last_partner_guid.

ENDIF.

Section “output of the data” In this section you prepare the output table of the data reading program. *------------------------------------------------------------* * output of the data * (this section can be left unchanged) *------------------------------------------------------------* *** name of the comm. structure DATA r_tabname TYPE string VALUE '%dtab'. *** reference to the output table FIELD-SYMBOLS TYPE table. *** reference to a line of the output table FIELD-SYMBOLS TYPE ANY. *** %dtab holds an internal table with header line at runtime. *** assign the table and the header line to the *** respective field symbols ASSIGN (r_tabname) TO . CONCATENATE r_tabname '[]' INTO r_tabname. ASSIGN (r_tabname) TO . *** this loop fills the output table

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 7 of 16

®

SAP How-To Guide LOOP AT it_data ASSIGNING . MOVE-CORRESPONDING TO . APPEND TO . *** this line suppresses the generated check routines. *** It's not needed for correctness, but for performance CHECK 1 = 2. * !! the following comment MUST NOT BE CHANGED !! * ENDLOOP.

Now you can check, save and generate your InfoSet.

Finally, you will have to assign your InfoSet to the correct user groups to make it visible for Segmentation. Navigate to the overview of all InfoSets, select your InfoSet and choose Goto -> Assignment to UG from the menu. The correct UG is “CRM_MKTHV”.

5 Data Source and Attribute List creation In order to use this InfoSet, you will have to create corresponding Data Sources and Attribute Lists.

5.1 Data Source creation Start transaction CRMD_MKTDS and create a new Data Source for High-Volume Segmentation and choose “InfoSet” as “Origin Type”. The following screenshot display the creation of the Data Source directly on the InfoSet ZHOWTO_MULTIVALUE.

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 8 of 16

®

SAP How-To Guide

5.2 Attribute List creation Start transaction CRMD_MKTDS and create a new Attribute List for a High-Volume Usage, assign your data source and activate the attributes you want to use.

Set the “Multiple Values” flag for your attributes

Now save your attribute list and load it to the high volume engine (use the check button):

Check your jobs to see when the upload finished (sm37)

You can monitor the attributes using TA CRMD_MKTSEG_FF_MON: © 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 9 of 16

®

SAP How-To Guide

Doubleclick on a line to see the details:

6 Segmentation Enter the CRM UI using transaction CRM_UI_START and choose a role where high volume segmentation is customized. Create a new high volume segment. Select your attribute list “zhowto_multivalue”. Select some filters using the double right arrow.

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 10 of 16

®

SAP How-To Guide

Now build your segments using the filters.

Dragging and Dropping “High” and “Visited” yields the correct result 1.

7 Delta Handling This section describes how to modify the above steps to enable delta support for the upload. Suppose you want to create a new business partner “183” with the following activities: Partner

Activity

Priority

Status

183

9

low

Planned

183

10

high

Cancelled

We assume that saving the BP will also insert the above entries into our table ZHOWTO_MULTIVAL. You will have to do it manually if you follow this example. When you save the BP and delta handling is switched on, the system writes a change pointer into table CRMD_MKTHV_DTA. If a data source shall support delta handling (how to do that is described below), the delta source object and the timestamp of the latest delta record to be returned are passed as query input parameters to the InfoSet in the delta case. Then the © 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 11 of 16

®

SAP How-To Guide records that are returned by the InfoSet have to be filtered according to these parameters. The following pages show how to do that. First of all, the communication structure ZHOWTO_MULTIVALUE has to be enhanced by the two fields:

The new fields must also be included in the field catalog of the Infoset:

Now you will need to enter the delta data reading program. The changes to the “normal” data reading program are highlighted. Section “declarations” *---------------------------------------------------------------------* * declarations * (insert your declarations in this section) *---------------------------------------------------------------------* *** the output table with line type of the comm. structure DATA it_data TYPE STANDARD TABLE OF zhowto_multivalue. *** DB cursor needed to retrieve the data DATA gv_db_curr TYPE cursor. *** the last partner GUID of the previous package DATA gv_last_partner_guid TYPE bu_partner_guid. *** the number of lines in the current package DATA lv_lines TYPE sy-tabix. *** table to read query input parameters DATA lt_selections TYPE TABLE OF rsparams. *** table for fieldinfos of the selections DATA lt_fieldinfo TYPE TABLE OF rsel_info. *** Delta Source ID of the delta records to be selected *** (e.g. BP BUS type) DATA lv_src_id TYPE crm_mkthv_seg_dta_src_id. *** latest date for a delta record to be selected DATA lv_created_at TYPE crm_mkthv_seg_dta_created_at. *** decides whether or not we run in delta mode DATA lv_full_load TYPE c. *** references a line in the output table FIELD-SYMBOLS TYPE zhowto_multivalue. *** references a line in the selection table FIELD-SYMBOLS TYPE rsparams. *** references a line in the fieldinfo table

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 12 of 16

®

SAP How-To Guide FIELD-SYMBOLS



TYPE rsel_info.

Section “selection screen statements”

As in the “normal” data reading program, this section stays unchanged. Section “read data into IT_DATA” In this section you retrieve the data from the data base. Here we have to distinguish the full load case and the delta case. *-------------------------------------------------------------------* * read data into IT_DATA *-------------------------------------------------------------------* * (select your data here into internal table IT_DATA) *** initialize variables CLEAR it_data. CLEAR lt_selections. CLEAR lt_fieldinfo. CLEAR lv_src_id. CLEAR lv_created_at. UNASSIGN . UNASSIGN . UNASSIGN . *** read selections of query CALL FUNCTION 'RS_REFRESH_FROM_SELECTOPTIONS' EXPORTING curr_report = sy-repid TABLES selection_table = lt_selections. *** get fiedinfos for selections CALL FUNCTION 'RS_REPORTSELECTIONS_INFO' EXPORTING report = sy-repid TABLES field_info = lt_fieldinfo. *** get delta source ID from query input READ TABLE lt_fieldinfo ASSIGNING WITH KEY dbfield = 'ZHOWTO_MULTIVALUE_D-SRC_ID'. *** we assume that we run in delta mode at first. *** If we cannot read the necessary delate information *** from the query input parameters, we switch to full load lv_full_load = space. IF sy-subrc = 0. READ TABLE lt_selections ASSIGNING WITH KEY selname = -name. IF sy-subrc = 0. lv_src_id = -low. ELSE. lv_full_load = 'X'. ENDIF. ELSE. lv_full_load = 'X'.

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 13 of 16

®

SAP How-To Guide ENDIF. *** get lates creation date from query input READ TABLE lt_fieldinfo ASSIGNING WITH KEY dbfield = 'ZHOWTO_MULTIVALUE_D-CREATED_AT'. IF sy-subrc = 0. READ TABLE lt_selections ASSIGNING WITH KEY selname = -name. IF sy-subrc = 0. lv_created_at = -low. ELSE. lv_full_load = 'X'. ENDIF. ELSE. lv_full_load = 'X'. ENDIF. IF %rtmode-acc_check IS NOT INITIAL. *** %rtmode-acc_check is set if you want to read the first *** package of this InfoSet CLEAR gv_last_partner_guid. ENDIF. IF lv_full_load = 'X'. *** open a cursor to select the data OPEN CURSOR WITH HOLD gv_db_curr FOR SELECT but~partner_guid howto~partner howto~priority howto~status FROM but000 AS but INNER JOIN zhowto_multival AS howto ON but~partner = howto~partner WHERE but~partner_guid > gv_last_partner_guid ORDER BY but~partner_guid. ELSE. OPEN CURSOR WITH HOLD gv_db_curr FOR SELECT but~partner_guid howto~partner howto~priority howto~status FROM crmd_mkthv_dta AS delta INNER JOIN but000 AS but ON delta~obj_guid = but~partner_guid INNER JOIN zhowto_multival AS howto ON but~partner = howto~partner WHERE but~partner_guid > gv_last_partner_guid AND delta~src_id = lv_src_id AND delta~created_at < lv_created_at ORDER BY but~partner_guid. ENDIF. *** fetch the next package. *** %rtmode-pack_size holds the customized package size FETCH NEXT CURSOR gv_db_curr INTO CORRESPONDING FIELDS OF TABLE it_data PACKAGE SIZE %rtmode-pack_size. *** close the cursor

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 14 of 16

®

SAP How-To Guide CLOSE CURSOR gv_db_curr. *** check how many lines we have read DESCRIBE TABLE it_data LINES lv_lines. *** read the last line of the package. READ TABLE it_data ASSIGNING INDEX lv_lines. *** We want to ensure that values for a partner GUID *** is not spread across several packages. We achieve this by *** a) return the records sorted by GUID *** b) always return all value records for the last GUID *** in a package, even if we return slightly more records *** as given in %rtmode-pack_size IF sy-subrc EQ 0. *** store the last GUID in this package. We need this in the *** next package run. gv_last_partner_guid = -partner_guid. *** delete all value records for this GUID *** from the output table DELETE it_data WHERE partner_guid = -partner_guid. IF lv_full_load = 'X'. *** manually select all value records for this GUID from DB *** and append them to the output table. SELECT but~partner_guid howto~partner howto~priority howto~status FROM but000 AS but INNER JOIN zhowto_multival AS howto ON but~partner = howto~partner APPENDING CORRESPONDING FIELDS OF TABLE it_data WHERE but~partner_guid = gv_last_partner_guid. ELSE. SELECT

but~partner_guid howto~partner howto~priority howto~status FROM crmd_mkthv_dta AS delta INNER JOIN but000 AS but ON delta~obj_guid = but~partner_guid INNER JOIN zhowto_multival AS howto ON but~partner = howto~partner APPENDING CORRESPONDING FIELDS OF TABLE it_data WHERE but~partner_guid = gv_last_partner_guid ORDER BY but~partner_guid. ENDIF. ENDIF.

Section “output of the data” This section looks exactly the same as in the “normal” data reading program.

© 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 15 of 16

®

SAP How-To Guide *------------------------------------------------------------* * output of the data * (this section can be left unchanged) *------------------------------------------------------------* *** name of the comm. structure DATA r_tabname TYPE string VALUE '%dtab'. *** reference to the output table FIELD-SYMBOLS TYPE table. *** reference to a line of the output table FIELD-SYMBOLS TYPE ANY. *** %dtab holds an internal table with header line at runtime. *** assign the table and the header line to the *** respective field symbols ASSIGN (r_tabname) TO . CONCATENATE r_tabname '[]' INTO r_tabname. ASSIGN (r_tabname) TO . *** this loop fills the output table LOOP AT it_data ASSIGNING . MOVE-CORRESPONDING TO . APPEND TO . *** this line suppresses the generated check routines. *** It's not needed for correctness, but for performance CHECK 1 = 2. * !! the following comment MUST NOT BE CHANGED !! * ENDLOOP. Again, you can check, save and generate your InfoSet now. Assign it to the user group for delta InfoSets (Goto -> Assignment to UG from the menu in the entry screen).

Finally you will have to update your data source with the delta information. Enter transaction CRMD_MKTDS and edit your data source:

This tells the upload job that this data source supports delta handling for object BUS1006005 (Business Partner). © 2012 SAP AG Dietmar-Hopp-Allee 16 69190 Walldorf

How-To Guide: How to create an InfoSet that supports Multivalue Segmentation for High Volume

Page 16 of 16