Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
tobias_koebler
Advisor
Advisor

Hi SLT community,

This How To describe a new expert function released with DMIS 2011 SP6 wthat allows to filter directly in the source for the initial load to reduce the transfer volume (if you have anyway in mind to filter on the SLT system) and also the option for the definition of a parallelized replication.

     

Important: DMIS 2011 SP06 or higher

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-r...

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

ACS_PLAN_ID

FIELD

CURRNUM

LOGICAL_OPERATOR

VALUE

GATING_OPERATOR

001

BKPF

2

00000

BUKRS

0001

=

'0010'

OR

001

BKPF

2

00000

BUKRS

0002

>=

'1000'

AND

001

BKPF

2

00000

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

ACS_PLAN_ID

FIELD

CURRNUM

LOGICAL_OPERATOR

VALUE

GATING_OPERATOR

001

BKPF

2

00000

GJAHR

0001

>=

2013

001

BKPF

2

00000

BUKRS

0002

=

'0010'

OR

001

BKPF

2

00000

BUKRS

0003

>=

'1000'

AND

001

BKPF

2

00000

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

47 Comments