Use Case

Use Case: Filter Initial Load

You don’t want to load the entire source table but a filter on the SLT is not the optimal solution as most of the data is transferred to the SLT system and skipped here. In case you filter a significant part of a table it’s better to already limit the reader module of the initial load accordingly

Ensure that you use a reading type 4/5. Without it will not work.

Use Case: Parallelize Replication

You need to parallelize the replication of a table as there are high change rates and one parallel task can not catch up delta data within expected latency time.

Use Case: Parallelize Initial Load

In order to parallelize the initial load (more precisely,the access plan calculation which is required before the real data transfer can be done – the actual transfer will be automatically parallelized), there is another option availabe, which automatically determines the subsets to be processed by each of the parallel-running jobs. For details, see http://scn.sap.com/community/replication-server/blog/2013/09/26/how-to-improve-the-initial-load-by-row-id-approach

However, if you can define such subsets yourself, this might be more efficient, firstly because you save the first step to determine the subsets, and secondly because the access might be more efficient, if you define the subsets by only giving values and / or intervals for the first key fields rather than for all the key fields of the table.

Process

With DMIS 2011 SP6 a new table is available where Filter for the Initial Load and / or settings to parallelize the replication can be defined. The eintries in table DMC_ACSPL_SELECT have to be defined on the SLT systems

Fieldname	Description
MT_ID	Mass Transfer ID
MIGR_OBJ_ALIAS	Table Name Attention: in case of cluster tables, you need to specify: the name of the logical table(s) in case of the initial load the name of the physical table in case of the replication So you need to maintain two records if the setting should be valid for both initial load and replication in case of clusters!
VALIDITY	Validity of this setting 1 = use for initial load and replication (filter load and parallelize replication) 2 = use for initial load only (filter load) 3 = use for replication only (parallelize replication)
ACS_PLAN_ID	Access Plan ID Use ID 00000 for filter criteria of initial load and / or replication use access plan ID 00001 – 99999 for parallelization settings
FIELD	Fieldname
CURRNUM	Line Number (0001 – 9999) to define multiple records for the same access plan ID
LOGICAL_OPERATOR	Logical Operator (allowed in Open SQL where clause)
VALUE	Filter / parallelization value for current field
GATING_OPERATOR	required if several records for the same field are defined multiple fields are concatenated with AND automatically

The value has to be defined properly based on the internal format of the corresponding data type. Character-like field values have to be defined with single quotes. NUMC fields have to be defined with single quotes and leading zeros.

Process: Filter Initial Load & Replication

You can enter filter criteria for any field of the source table. If you enter mutliple filter criteria for one field you have to define a gating operator (AND / OR). If you define filter on multiple fields the filter from the indiviual fields will be concatenated with gating operator AND.
If you define a filter for the replication also a filter on the DB trigger is recommended as otherwise the changes that don’t fit to the filter criteria will not be handled and remain in the logging table.

Example 1: Filter on one field for a single value
Define filter criteria for table BKPF in mass transfer ID 001. Only those records should be loaded where GJAHR is greater or equal 2013. The filter criteria shall be relevant for initial load and replication

MT_ID	MIGR_OBJ_ALIAS	VALIDITY	ACS_PLAN_ID	FIELD	CURRNUM	LOGICAL_OPERATOR	VALUE	GATING_OPERATOR
001	BKPF	1	00000	GJAHR	0001	>=	2013

The following where clause will be used for the select:

SELECT FROM BKPF WHERE GJAHR >= 2013.

However, this won’t work for the initial load if you are using reading type 3 (“primary key order”). Also it does not work for replication unless you also parallelize the replication (see below: “Parallelize Replication”). Note that filters for replication should rather be set using corresponding trigger filters.

Example 2: Filter on one fieldfor mutliple values
Define filter criteria for table BKPF in mass transfer ID 001. Only those records should be loaded where BUKRS is equal 0010 or between 1000 and 3000. The filter criteria shall be relevant for the initial load only

MT_ID	MIGR_OBJ_ALIAS	VALIDITY	FIELD	CURRNUM	LOGICAL_OPERATOR	VALUE	GATING_OPERATOR
001	BKPF	2	BUKRS	0001	=	‘0010’	OR
001	BKPF	2	BUKRS	0002	>=	‘1000’	AND
001	BKPF	2	BUKRS	0003	<=	‘3000’

The following where clause will be used for the select:

SELECT FROM BKPF WHERE BUKRS = '0010' OR BUKRS >= '1000' AND BUKRS <= '3000'.

Example 3: Filter on multiple fields
Define filter criteria for table BKPF in mass transfer ID 001. Only those records should be loaded where GKAHR is greater or equal 2013 and where BUKRS is equal 0010 or between 1000 and 3000. The filter criteria shall be relevant for the initial load only

MT_ID	MIGR_OBJ_ALIAS	VALIDITY	FIELD	CURRNUM	LOGICAL_OPERATOR	VALUE	GATING_OPERATOR
001	BKPF	2	GJAHR	0001	>=	2013
001	BKPF	2	BUKRS	0002	=	‘0010’	OR
001	BKPF	2	BUKRS	0003	>=	‘1000’	AND
001	BKPF	2	BUKRS	0004	<=	‘3000’

The following where clause will be used for the select:

SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS = '0010' OR BUKRS >= '1000' AND BUKRS <= '3000'.

Process: Parallelize Replication

You can enter ranges to split the replication into parallel tasks. The number of defined ranges also defines the maximum parallel processing of the current table. How many jobs will finally procssing a table is defined by the overall number of jobs and the maximum number of parllel jobs defined for a table (can be defined in LTRC -> Data Transfer Monitor). If you define range criteria for the parallelization of the replication you need to define disjount subsets of the data to be processed by each of the replication jobs.

Example 1: Parallelize Replication
Split replication of table BKPF in mass transfer ID 001 into 4 ranges. The ranges are defined based on field:
Range 1: 0000 -> 2000
Range 2: 2000 -> 5000
Range 3: 5000 -> 8000
Range 4: 8000 -> 9999

MT_ID	MIGR_OBJ_ALIAS	VALIDITY	ACS_PLAN_ID	FIELD	CURRNUM	LOGICAL_OPERATOR	VALUE	GATING_OPERATOR
001	BKPF	3	00001	BUKRS	0001	<=	‘2000’
001	BKPF	3	00002	BUKRS	0001	>	‘2000’	AND
001	BKPF	3	00002	BUKRS	0002	<=	‘5000’
001	BKPF	3	00003	BUKRS	0001	>	‘5000’	AND
001	BKPF	3	00003	BUKRS	0002	<=	‘8000’
001	BKPF	3	00004	BUKRS	0001	>	‘8000’

The following where clause will be used for the select:

Range 1:

SELECT FROM BKPF WHERE BUKRS <= '2000'.

Range 2:

SELECT FROM BKPF WHERE BUKRS > '2000' AND BUKRS <= '5000'.

Range 3:

SELECT FROM BKPF WHERE BUKRS > '5000' AND BUKRS <= '8000'.

Range 4:

SELECT FROM BKPF WHERE BUKRS > '8000'.

Example 2: Parallelize Replication & Filter
Split replication of table BKPF in mass transfer ID 001 into 4 ranges as defined in example 1. In addition only those records should be loaded where GJAHR is greater or equal 2013.

MT_ID	MIGR_OBJ_ALIAS	VALIDITY	ACS_PLAN_ID	FIELD	CURRNUM	LOGICAL_OPERATOR	VALUE	GATING_OPERATOR
001	BKPF	1	00000	GJAHR	0001	>=	2013
001	BKPF	3	00001	BUKRS	0001	<=	‘2000’
001	BKPF	3	00002	BUKRS	0001	>	‘2000’	AND
001	BKPF	3	00002	BUKRS	0002	<=	‘5000’
001	BKPF	3	00003	BUKRS	0001	>	‘5000’	AND
001	BKPF	3	00003	BUKRS	0002	<=	‘8000’
001	BKPF	3	00004	BUKRS	0001	>	‘8000’

The following where clause will be used for the select:

Range 1:

SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS <= '2000'.

Range 2:

SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS > '2000' AND BUKRS <= '5000'.

Range 3:

SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS > '5000' AND BUKRS <= '8000'.

Range 4:

SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS > '8000'.

Enjoy this new features :smile:

Best, Tobias

Table Partitioning with Filter and Parallelism Approach Based - a.) Filter maintained under table DMC_ACSPL_SELECT for RYEAR >=2015 with Validity = 1 [= use for initial load and replication (filter load and parallelize replication)]. b.) Parallelism Criteria Set with parallel job as 5.	FICO consultant created the entries for 2014 and 2017. - Initial Load worked with filter approach for RYEAR >=2015. - Delta Replication didn't work with filter approach for RYEAR >=2015. - It replicated the delta entries to HANA for 2014 and 2017. Whereas, our aim was only to get the entries for >=2015.
Table Partitioning with Filter and Parallelism Approach Based with ABAP code- a.) Filter maintained under table DMC_ACSPL_SELECT for RYEAR >=2015 with Validity = 2 [= use for initial load only (filter load)]. b.) ABAP code included. c.) Parallelism Criteria Set with parallel job as 5.	FICO consultant created the entries for 2014 and 2017. - Initial Load worked with filter approach for RYEAR >=2015. - Delta Replication didn't work with filter approach for RYEAR >=2015. - It replicated the delta entries to HANA for 2014 and 2017. Whereas, our aim was only to get the entries for >=2015.
Table Partitioning with Filter and Parallelism Approach Based with ABAP code- a.) Filter maintained under table DMC_ACSPL_SELECT for RYEAR >=2015 with Validity = 2 [= use for initial load only (filter load)] & Validity = 3 [= use for replication only (parallelize replication)]. b.) ABAP code included. c.) Parallelism Criteria Set.	FICO consultant created the entries for 2014 and 2017. - Initial Load worked with filter approach for RYEAR >=2015. - Delta Replication didn't work with filter approach for RYEAR >=2015. - Delta replication entries were stuck in logging table and were not replication to HANA. - ECC system started dumping with SAPSQL_PARSE_ERROR
Table Partitioning with Filter and Parallelism Approach Based with ABAP code- a.) Filter maintained under table DMC_ACSPL_SELECT for RYEAR >=2015 with Validity = 1 [= use for initial load and replication (filter load and parallelize replication)] & Validity = 3 [= use for replication only (parallelize replication)]. b.) ABAP code included. c.) Parallelism Criteria Set.	FICO consultant created the entries for 2014 and 2017. - Initial Load worked with filter approach for RYEAR >=2015. - Delta Replication didn't work with filter approach for RYEAR >=2015. - ECC system started dumping with SAPSQL_PARSE_ERROR
Table Partitioning with Filter (DMC) and Parallelism Approach Based - a.) Filter maintained under table DMC_ACSPL_SELECT for RYEAR >=2015 with Validity = 2 [= use for initial load only (filter load)]. c.) Parallelism Criteria Set with parallel job as 5.	FICO consultant created the entries for 2014 and 2017. - Initial Load worked with filter approach for RYEAR >=2015. - Delta Replication didn't work with filter approach for RYEAR >=2015. - It replicated the delta entries to HANA for 2014 and 2017. Whereas, our aim was only to get the entries for >=2015.
Table Partitioning with Filter with ABAP code and Parallelism Approach Based - a.) Filter maintained using ABAP Code - Z_IMPORT_MODIFIED_ZZZA. c.) Parallelism Criteria Set with parallel job as 5.	Observations - 1.) More job Runtime for Calculation - Calculation job which were taking 60 secs to complete with DMC approach is not taking 300 to 400 secs to complete. Hence, more time for calculation. 2.) Performance Impact as per SAP.

How To filter on the initial load & parallelize replication DMIS 2011 SP06 or higher

Use Case

Use Case: Filter Initial Load

Use Case: Parallelize Replication

Use Case: Parallelize Initial Load

Process

Process: Filter Initial Load & Replication

Process: Parallelize Replication

Assigned Tags