Scribd
Upload
5 views1 page

Incremental Data Extraction Techniques

Mapping parameters and variables can be used in a mapping to incrementally extract only new or updated data. For example, a parameter could save the timestamp of the last extracted row to use as the starting point for the next incremental extract. Parameters can also be used to filter data for a single customer or use the same mapping across different databases and tables by changing the parameter value.

Uploaded by

no_me_No_JOY
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views1 page

Incremental Data Extraction Techniques

Mapping parameters and variables can be used in a mapping to incrementally extract only new or updated data. For example, a parameter could save the timestamp of the last extracted row to use as the starting point for the next incremental extract. Parameters can also be used to filter data for a single customer or use the same mapping across different databases and tables by changing the parameter value.

Uploaded by

no_me_No_JOY
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Example

Incremental extraction of data

Use mapping parameters and variables in a mapping to incrementally extract data. Use mapping
parameters or variables in the source filter of a Source Qualifier transformation to determine the
beginning timestamp and end timestamp for incrementally extracting data.

For example, you can create a user-defined mapping variable $$LastUpdateDateTime that saves
the timestamp of the last row the Integration Service read in the previous session. Use $
$LastUpdateDateTime for the beginning timestamp and the built-in variable $$$SessStartTime
for the end timestamp in the source filter. Use the following filter to incrementally extract data
based on the SALES.sales_datetime column in the source:

SALES.sales_datetime > TO_DATE (‘$$LastUpdateDateTime’) AND


SALES.sales_datetime < TO_DATE (‘$$$SessStartTime’)

Data Extraction of one customer

For example, you want to use the same session to extract transaction records for each of the
customers individually. Instead of creating a separate mapping for each customer account, you
can create a mapping parameter to represent a single customer account. Then use the parameter
in a source filter to extract only data for that customer account. Before running the session, you
enter the value of the parameter in the parameter file.

To reuse the same mapping to extract records for other customer accounts, you can enter a new
value for the parameter in the parameter file and run the session. Or, you can create a parameter
file for each customer account and start the session with a different parameter file each time
using pmcmd.

Monthly gross earnings

You might use a mapping parameter instead of a database lookup. For example, you want to
perform calculations using monthly gross earnings. Instead of using a Lookup transformation to
connect to a database table for that information, you can create a gross earnings mapping
parameter and update its value in the parameter file each month to reflect current earnings.

Different databases, same table

You might also use a mapping parameter in conjunction with a session parameter to reuse a
mapping and session. For example, you have transactional data from different states stored in the
same table in different databases, and you want to perform the same calculations on all data,
while changing the state sales tax accordingly. Instead of creating a separate mapping and
session for each state, you can create one mapping with a sales tax mapping parameter and a
session using a source database connection session parameter.

You might also like