SEQUENCE GENERATOR TRANSFORMATION

1. SEQUENCE GENERATOR TRANSFORMATION

2. Topics to discuss… Sequence Generator Transformation Overview Common Uses for Sequence Generator Sequence Generator Transformation Properties

3. When we created a centralized group, we focused first on Defined Benefit, Health and Welfare, and common/shared data management. The Defined Contribution and HRO data management were to be considered in a subsequent phaseWhen we created a centralized group, we focused first on Defined Benefit, Health and Welfare, and common/shared data management. The Defined Contribution and HRO data management were to be considered in a subsequent phase

4. When we created a centralized group, we focused first on Defined Benefit, Health and Welfare, and common/shared data management. The Defined Contribution and HRM/BPO (formerly SHRO) data management were to be considered in a subsequent phaseWhen we created a centralized group, we focused first on Defined Benefit, Health and Welfare, and common/shared data management. The Defined Contribution and HRM/BPO (formerly SHRO) data management were to be considered in a subsequent phase

5. Creating Keys  You can create primary or foreign key values with the Sequence Generator transformation by connecting the NEXTVAL port to the desired transformation or target . When creating primary or foreign keys, only use the Cycle option to prevent the Power Center Server from creating duplicate primary keys. Discuss HRODiscuss HRO

6. Replacing Missing Values Use the Sequence Generator transformation to replace missing keys by using NEXTVAL with the IIF and ISNULL functions. To replace null values in the ORDER_NO column, for example, you create a Sequence Generator transformation with the desired properties and drag the NEXTVAL port to an Expression transformation. In the Expression transformation, drag the ORDER_NO port into the transformation (along with any other necessary ports). Then create a new output port, ALL_ORDERS. In ALL_ORDERS, you can then enter the following expression to replace null orders: IIF( ISNULL( ORDER_NO ), NEXTVAL, ORDER_NO ) Example - report into Chris Crawford Chevron has an acquisition of new unit (large group 10,000 lives) Implementation reviews Business Requirements, layouts, File frequency Make suggestions on how to structure Sets up hand off and structure Technology group completes programming. Example - report into Chris Crawford Chevron has an acquisition of new unit (large group 10,000 lives) Implementation reviews Business Requirements, layouts, File frequency Make suggestions on how to structure Sets up hand off and structure Technology group completes programming.

7. Sequence Generator Ports  NEXTVAL The NEXTVAL port can be used to generate sequence numbers by connecting it to a transformation or target. The NEXTVAL port when connected to a downstream transformation, it generates the sequence based on the Current Value and Increment By properties. The NEXTVAL port can be connected to more than one target table in a mapping to generate unique primary key values for each target table. The column of unique primary key values is sent to one target table as a block of sequence numbers. The second targets receives a block of sequence numbers from the Sequence Generator transformation only after the first target table receives the block of sequence numbers Chevron – Inbound weekly census file MSDW – Outbound Pension Payroll, gives pension checks to retiree, scheduled process H&W Vendor Files - CobraChevron – Inbound weekly census file MSDW – Outbound Pension Payroll, gives pension checks to retiree, scheduled process H&W Vendor Files - Cobra

8. Connecting NEXTVAL to Two Target Tables in a Mapping  MSDW monthly census file and wants to change frequency to monthly to daily Analyst adjusts BRD Prod Operations executes files MSDW monthly census file and wants to change frequency to monthly to daily Analyst adjusts BRD Prod Operations executes files

9. Senior level implementation folks – Art DB, Shari HR , Lori HR, Ray H&WSenior level implementation folks – Art DB, Shari HR , Lori HR, Ray H&W

10. CURRVAL CURRVAL is NEXTVAL plus the Increment By value We will connect the CURRVAL port only when the NEXTVAL port is already connected to a downstream transformation. When a row enters the transformation connected to the CURRVAL port, the Power Center Server passes the last-created NEXTVAL value plus one. Mention that ACS other groups have similar functions. For example, H&W, DB, DC implementation function DC processes payrollMention that ACS other groups have similar functions. For example, H&W, DB, DC implementation function DC processes payroll

12. Sequence Generator Transformation Properties  Start Value Increment By End Value Current Value Cycle Number of Cached Values Reset Tracing Level 1) Walk through with Census File. Chart details inbound file 2) Parameters based on client functionality and needs1) Walk through with Census File. Chart details inbound file 2) Parameters based on client functionality and needs

13. Start Value This value denotes the value to start the sequence with. By default it is zero. Cycle Cycle option is used to generate a repeating sequence. If selected, the Power Center Server cycles through the sequence range. Otherwise, the Power Center Server stops the sequence at the configured end value. If disabled, the Power Center Server fails the session with overflow errors if it reaches the end value and still has rows to process. As it cycles, the Power Center Server reaches the configured end value for the sequence, it wraps around and starts the cycle again, beginning with the configured Start Value.

15. Current Value  The Power Center Server uses the current value as the basis for generated values for each session. To indicate which value we want the Power Center Server to use the first time it uses the Sequence Generator transformation, we must enter that value as the current value. If we want to use the Sequence Generator transformation to cycle through a series of values, the current value must be greater than or equal to Start Value and less than the end value. At the end of each session, the Power Center Server updates the current value to the last value generated for the session plus one if the Sequence Generator Number of Cached Values is 0. Since the Power Center Server uses the current value to determine the first value for each session, you should only edit the current value when you want to reset the sequence.

16.  Number of Cached Values Number of Cached Values determines the number of values the Power Center Server caches at one time. When Number of Cached Values is greater than zero, the Power Center Server caches the configured number of values and updates the current value each time it caches values. Reset If you select Reset for a non-reusable Sequence Generator transformation, the Power Center Server generates values based on the original current value each time it starts the session. Otherwise, the Power Center Server updates the current value to reflect the last-generated value plus one, and then uses the updated value the next time it uses the Sequence Generator transformation.

17. Thank you !!!