Slowly Changing Dimensions Using Effective To and From Dating
Type 2 Slowly changing dimension. This dimension will keep a full history of changes in the target by date range.
The mapping reads the incoming source and compares the source records to the warehouse table active records. Flags are set to determine if the incoming record is a new record or if it already exists in the warehouse. If the record exists in the warehouse, then the fields from the source record and the warehouse record are compared to see if there are any differences. If differences exist, then a new record is added and the old record is end-dated. If no changes exist between the two records, then the existing warehouse record remains as-is.
In a typical PowerCenter mapping for a dimension that keeps an entire history, the Informatica server must read the new dimension file from the source system and compare it to the existing records in the dimension table of the warehouse. Flagging a record as new or as a changed record needs to be addressed by comparing the incoming record to the records in the warehouse. If a record is new it needs to be added to the warehouse dimension table. If a record is unchanged, no updates or inserts should occur. If a record is changed, the existing record needs to be effectively end-dated and a new record containing the changes should be inserted.
- The entire history of a dimension remains intact in the warehouse. So, ‘Point-in-Time’ reports are available in the warehouse.
- Can be used with relational and file-based sources.
- Large dimension tables will require enough lookup cache to handle the data returned from the warehouse or a partitioning strategy or non-cached lookup may be required.
- Large dimension tables will require extra time for the database to perform the updates that end-date a record. Inserts will also be affected if numerous indexes exist on the warehouse table. Archiving the old records may be a requirement for performance.
- XML file for PowerCenter 9.6.x Mapping (See Attachments section below)
- XML file for PowerCenter 9.1.0 Mapping (See Attachments section below)
- XML file for PowerCenter 8.6.1 Mapping (See Attachments section below)
The source file is a comma delimited flat file which contains Customer dimension attributes. One transformation that occurs is deriving the First Name and Last Name from the Full Name attribute. The main functionality of this mapping is to perform a comparison of incoming data to existing data by using a Dynamically Cached lookup and using the built in flag ‘NewLookupRow’ to determine the appropriate load strategy. The Dynamic Cache lookup has a filter to only cache ‘Active’ records based on EFFECT_TO_DATE.
Please refer to the mapping’s metadata descriptions for more information on this mapping template’s functionality.
- The Mappings were originally built using PowerCenter Designer. If you are using PowerCenter Express (PCX), all the mappings cannot be imported as PCX includes only the Informatica Developer tool and not PowerCenter Designer tool. For example, Informatica Developer doesn’t support mappings that use “Sequence Generator” transformation.
- The objects are based on “UTF-8” codepage. If you have changed you codepage, XML may need to be edited.