top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

SCD TYPE2 in informatica [CLOSED]

0 votes
80 views

Can anyone of you please elaborate on how to map the informatica for the inserts and updates to the target from source table?

I appreciate it, if you explain with example.

posted Nov 3, 2014 by Sunil

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

TYPE2 Only INSERTS(New Rows as well as Updated Rows)

Version Data Mapping:

The Type 2 Dimension/Version Data mapping filters source rows based on user-defined comparisons and inserts both new and changed dimensions into the target. Changes are tracked in the target table by versioning the primary key and creating a version number for each dimension in the table. In the Type 2 Dimension/Version Data target, the current version of a dimension has the highest version number and the highest incremented primary key of the dimension.

Use the Type 2 Dimension/Version Data mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table. Version numbers and versioned primary keys track the order of changes to each dimension.

When you use this option, the Designer creates two additional fields in the target:

PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target. PM_VERSION_NUMBER. The Integration Service generates a version number for each row written to the target.

Creating a Type 2 Dimension/Effective Date Range Mapping

The Type 2 Dimension/Effective Date Range mapping filters source rows based on user-defined comparisons and inserts both new and changed dimensions into the target. Changes are tracked in the target table by maintaining an effective date range for each version of each dimension in the target. In the Type 2 Dimension/Effective Date Range target, the current version of a dimension has a begin date with no corresponding end date.

Use the Type 2 Dimension/Effective Date Range mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table. An effective date range tracks the chronological history of changes for each dimension.

When you use this option, the Designer creates the following additional fields in the target:

PM_BEGIN_DATE. For each new and changed dimension written to the target, the Integration Service uses the system date to indicate the start of the effective date range for the dimension. PM_END_DATE. For each dimension being updated, the Integration Service uses the system date to indicate the end of the effective date range for the dimension.

PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target.

The Type 2 Dimension/Flag Current mapping

The Type 2 Dimension/Flag Current mapping filters source rows based on user-defined comparisons and inserts both new and changed dimensions into the target. Changes are tracked in the target table by flagging the current version of each dimension and versioning the primary key. In the Type 2 Dimension/Flag Current target, the current version of a dimension has a current flag set to 1 and the highest incremented primary key.

Use the Type 2 Dimension/Flag Current mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table, with the most current data flagged. Versioned primary keys track the order of changes to each dimension.

When you use this option, the Designer creates two additional fields in the target:

PM_CURRENT_FLAG. The Integration Service flags the current row “1” and all previous versions “0.” PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target.

answer Nov 6, 2014 by Shweta Singh
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...