Mapping Wizards
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Mapping Wizards as PDF for free.
More details
- Words: 2,053
- Pages: 53
Suggested Naming Conventions Transformations • Aggregator
AGG_TransformationName
• Expression
EXP_TransformationName
• Filter
FIL_TransformationName
• Joiner
JNR_TransformationName
• Lookup
LKP_TransformationName
• Normalizer
NRM_TransformationName
• Rank
RNK_TransformationName
• Sequence Generator
SEQ_TransformationName
• Stored Procedure
SP_TransformationName
• Source Qualifier
SQ_TransformationName
• Update Strategy
UPD_TransformationName
Suggested Naming Conventions Targets • T_TARGETNAME
Mappings • m_MappingName
Mapplets • mplt_MappletName
Sessions • s_MappingName
Mapping Designer Ports • To design the basic flow of data between source and targets • Types: input, output, variable
Variable Ports For aggregator, expression and rank transformations Use variable to • simplify complex expression • store temporary data • store values from prior rows
Provides Wizards to create simple pass through mappings/Slowly changing dimensions
Mapping Creation How to create a Mapping? • Open the Mapping designer workspace • Choose Mappings > Create • Give a proper name to the mapping
• Open the Sources from the navigator which you would have created/imported using source analyzer • Drag and drop the source table from navigator into the workspace
Mapping Creation
Mapping Creation • Choose Transformation > Create to create a transformation object • Select the type of transformation object you want to create and give a proper name to it
Mapping Creation • Drag and drop the required fields(ports) from Source Qualifier to the transformation object
Mapping Creation • Add new ports in the transformation object and define them as variable ports to do complex transformation
Mapping Creation • Open the Targets from the navigator which you would have created/imported using Warehouse Designer • Select the target table, drag and drop it into the designer workspace
Mapping Creation • Drag and drop the output ports from the last transformation object to the corresponding ports in the target
• Choose Repository > Save to store the mapping • Check the output window for any errors
Mapping Wizards Wizards help to create mappings quickly and easily Wizards designed to create mappings for loading and maintaining star schemas Mappings created using wizards can be edited and customized Getting Started Wizard • Simple Pass Through • mappings to load static fact and dimensions • Use this when you want to drop all existing data from the table before loading new data
• Slowly Growing Target • mappings to load slowly growing dimension or fact tables • Use this to load new data when existing data does not require updates
Mapping Wizards Slowly Changing Dimensions Wizard • Type I Dimension Mapping - Keep most recent values in target
Target
Source Emp id
Name
Email
Emp id
Name
Email
1001
Shane
Shane @xyz.c om
1001
Shane
Shane @xyz.c om
Source
Target
Emp id
Name
Email
Emp id
Name
Email
1001
Shane
Shane@ abc.co.in
1001
Shane
Shane@ abc.co.in
Shane@xyz. com
Mapping Wizards Slowly Changing Dimensions Wizard • Type II Dimension Mapping Version Data Mapping - insert new and changed dimensions with version number and incremented primary key - full history and progress of changes
Source Emp id
Name
Email
10
Shane
Shane @xyz.c om
Target
P M_P RIMA RYKE Y
Emp id
Name
Email
PM_V ERSI ON_ NUM BER
1000
10
Shane
Shan e@xy z.co m
0
Type II Dimension/Versioning Source Emp id
Name
Email
10
Shane Shane@ abc.co.in
PM_PRI MARYKE Y
Emp id
Name
Email
PM_VERSION_N UMBER
1000
10
Shane
Shane@ xyz.com
0
1001
10
Shane
Shane@ abc.co.in
1
Target
Type II Dimension/Versioning Source Emp id
Name
10
Shane Shane@ abc.com
Target
Email
PM_PRI MARYKE Y
Emp id
Name
Email
PM_VERSION_N UMBER
1000
10
Shane
Shane@ xyz.com
0
1001
10
Shane
Shane@ abc.co.in
1
1003
10
Shane
Shane@ abc.com
2
Mapping Wizards Slowly Changing Dimensions Wizard • Type II Dimension Mapping Flag Current Mapping - insert new and changed dimensions with flags and incremented primary key - full history and flagging only current dimensions
Target
Source Emp id
Name
Email
10
Shane
Shane @xyz.c om
P M_P RIMA RYKE Y
Emp id
Name
Email
PM_CU RRENT _FLAG
1000
10
Shane
Shan e@xy z.co m
1
Type II Dimension/Flag Current Source Emp id
Name
Email
10
Shane Shane@ abc.co.in
PM_PRI MARYKE Y
Emp id
Name
Email
PM_CURRENT_F LAG
1000
10
Shane
Shane@ xyz.com
N
1001
10
Shane
Shane@ abc.co.in
Y
Target
Type II Dimension/Flag Current Source Emp id
Name
10
Shane Shane@ abc.com
Target
Email
PM_PRI MARYKE Y
Emp id
Name
Email
PM_CURRENT_F LAG
1000
10
Shane
Shane@ xyz.com
N
1001
10
Shane
Shane@ abc.co.in
N
1003
10
Shane
Shane@ abc.com
Y
Mapping Wizards Slowly Changing Dimensions Wizard • Type II Dimension Mapping • Effective Date Range Mapping - insert new and changed dimensions with date range to define current dimension data - full history and tracking changes with an exact effective date range
Source Emp id
Name
Email
10
Shane Shane @xyz.c om
Target
P M_P RIMA RYKE Y
Emp id
Name
Email
PM_B EGIN _DAT E
1000
10
Shane
Shan e@xy z.co m
01/0 1/00
PM_E ND_ DATE
Type II Dimension/Effective Date Source Emp id
Name
Email
10
Shane Shane@ abc.co.in
PM_PRIMA RYKEY
Emp id
Name
Email
1000
10
Shane
Shane 01/01/00 @xyz.c om
1001
10
Shane
Shane 03/01/00 @ abc.co. in
Target
PM_BEGIN _DATE
PM_END _DATE 03/01/00
Type II Dimension/Effective Date Source Emp id
Name
Email
10
Shane Shane@ abc.com
PM_PRI MARYKE Y
Emp id
Name
Email
PM_BEGIN _DATE
PM_END_ DATE
1000
10
Shane
Shane@ xyz.com
01/01/00
03/01/00
1001
10
Shane
Shane@ abc.co.in
03/01/00
05/02/00
1003
10
Shane
Shane@ abc.com
05/02/00
Target
Mapping Wizards Slowly Changing Dimensions Wizard • Type III Dimension Mapping - insert new and update values in existing dimensions - keep current and previous values (partial history)
Target Source Emp id
Name
Email
10
Shane Shane @xyz.c om
PM_P RIMA RYKE Y
Emp id
Name
Email
1
10
Shane
Shane @xyz. com
PM_P rev_ Colu mnN ame
PM_E FFECT _DAT E
01/01 /00
Type III Dimension Source Emp id
Name
Email
10
Shane Shane@ abc.co.in
PM_PRIMA RYKEY
Emp id
Name
Email
PM_Prev_ ColumnNa me
PM_EFFE CT_DATE
1
10
Shane
Shane Shane@xy @ z.com abc.co. in
01/02/00
Target
Type III Dimension Source Emp id
Name
Email
10
Shane Shane@ abc.com
PM_PRI MARYKE Y
Emp id
Name
Email
PM_Prev_ ColumnNa me
PM_EFFEC T_DATE
1
10
Shane
Shane@ abc.com
Shane@ abc.co.in
01/03/00
Target
Mapplet Designer Mapplet • Reusable object that reflect set of transformation logic to use in multiple mappings • Like a single transformation object • Expands at run time
Each Mapplet must include the following • Exactly one input transformation or Source Qualifier transformation • At least one output transformation
Rules for Objects in Mapplets • Use only reusable Sequence Generators • Do not use pre- or post-session stored procedures
Mapplet Designer Rules for Objects in Mapplets • Do not use the following in a mapplet Joiner Cobol Source definition Normalizer Target definitions
Mapplet Designer How to create a Mapplet? • Open the Mapplet designer workspace • Choose Mapplets > Create • Give a name to the Mapplet
• Create an input transformation to define mapplet input ports if the mapplet contains no sources
Creating a Mapplet
• Double click on the object to define the input ports
Creating a Mapplet • Create the transformation objects to be used in the mapplet • One input transformation can be connected to only one transformation, so to pass same values to two separate data flows,connect the input transformation to another transformation and then split the data flow • Use Output transformation to create output ports, creating one Output transformation for each mapplet output group
• Connect all the input ports, ports in transformation objects and the output ports to complete the data flow
Creating a Mapplet
• Choose Repository > Save to store the mapplet • Check the output window for validation status • If mapplet is not valid,correct the problem and re-save the mapplet
Replacing a relational database connection • You can replace a relational database connection with another relational database connection. • For example, you might have several sessions that you want to write to another target database. Instead of editing the properties for each session, you can replace the relational database connection for all sessions in the repository that use the connection.
Work Flow Manager • In the Workflow Manager, you define a set of instructions called a workflow to execute mappings you build in the Designer. • Generally, a workflow contains a session and any other task you may want to perform when you execute a session. Tasks can include a session, email notification, or scheduling information. You connect each task with links in the workflow. • You can also create a worklet in the Workflow Manager. A worklet is an object that groups a set of tasks
Replacing a relational database connection •Workflow manager>Connections>Replace
Working with tasks Type of Tasks • • • • • • •
Command Decision Event Email Timer Assignment Session
Command • The Command task allows you to specify one or more shell commands to run during the workflow. For example, you can specify shell commands in the Command task to delete reject files, copy a file, or archive target files. • You can use a Command task in the following ways: Standalone Command task: You can use a Command task anywhere in the workflow or worklet to run shell commands. Pre- and post-session shell command: You can call a Command task as the pre- or post-session shell command for a Session task.
Command (Cntnd…)
Decision •The Decision task allows you to enter a condition that determines the execution of the workflow, similar to a link condition.
Decision
•The Decision task has a pre-defined variable called $Decision_task_name.Condition that represents the result of the decision condition. •The Informatica Server evaluates the condition in the Decision Task and sets the pre-defined condition variable to True (1) or False (0). •You can use the Decision task instead of multiple link conditions in a workflow
Decision (Contd…)
Event •You can define events in the workflow to specify the sequence of task execution. The event is triggered based on the completion of the sequence of tasks. Use the following tasks to help you use events in the workflow: Event-Raise task. Event-Raise task represents a user-defined event. Use the Event-Raise task with the Event-Wait task to define events. Event-Wait task. The Event-Wait task waits for an event to occur. Once the event triggers, the Informatica Server continues executing the rest of the workflow. Pre-defined event. A pre-defined event is a file-watch event. For pre-defined events, use an Event-Wait task to instruct the Informatica (Indicator File) User-defined event. A user-defined event is a sequence of tasks in the workflow.
Event(Contd..)
Event (Contd…)
Email •The Workflow Manager provides an Email task that allows you to send email during a workflow. •You can create reusable Email tasks in the Task Developer for any type of email. Or, you can create non-reusable Email tasks in the Workflow and Worklet Designer. You can use Email tasks in any of the following locations: Session properties Workflow properties Workflow or worklet
Email(Contd…)
Timer •The Timer task allows you to specify the period of time to wait before the Informatica Server executes the next task in the workflow. •You can choose to start the next task in the workflow at an exact time and date. •You can also choose to wait a period of time after the start time of another task, workflow, or worklet before starting the next task. The Timer task has two types of settings: Absolute time. Relative time.
Timer(Contd..)
Assignment •The Assignment task allows you to assign a value to a user-defined workflow variable.
Session •You create a Session task for each mapping you want the Informatica Server to run. The Informatica Server uses the instructions configured in the session to move data from sources to targets.
Session
Worklet •A worklet is an object that represents a set of tasks. It can contain any task available in the Workflow Manager. •You can also nest a worklet in another worklet.
WorkFlow Monitor • The Workflow Monitor is a tool that allows you to monitor workflows and tasks. • You can view details about a workflow or task in either Gantt Chart view or Task view. • You can run, stop, abort, and resume workflows from the Workflow Monitor.
Workflow Monitor •You can configure options specific to Gantt Chart and Task view.
WorkFlow Monitor Gantt Chart View
WorkFlow Monitor • Task View
AGG_TransformationName
• Expression
EXP_TransformationName
• Filter
FIL_TransformationName
• Joiner
JNR_TransformationName
• Lookup
LKP_TransformationName
• Normalizer
NRM_TransformationName
• Rank
RNK_TransformationName
• Sequence Generator
SEQ_TransformationName
• Stored Procedure
SP_TransformationName
• Source Qualifier
SQ_TransformationName
• Update Strategy
UPD_TransformationName
Suggested Naming Conventions Targets • T_TARGETNAME
Mappings • m_MappingName
Mapplets • mplt_MappletName
Sessions • s_MappingName
Mapping Designer Ports • To design the basic flow of data between source and targets • Types: input, output, variable
Variable Ports For aggregator, expression and rank transformations Use variable to • simplify complex expression • store temporary data • store values from prior rows
Provides Wizards to create simple pass through mappings/Slowly changing dimensions
Mapping Creation How to create a Mapping? • Open the Mapping designer workspace • Choose Mappings > Create • Give a proper name to the mapping
• Open the Sources from the navigator which you would have created/imported using source analyzer • Drag and drop the source table from navigator into the workspace
Mapping Creation
Mapping Creation • Choose Transformation > Create to create a transformation object • Select the type of transformation object you want to create and give a proper name to it
Mapping Creation • Drag and drop the required fields(ports) from Source Qualifier to the transformation object
Mapping Creation • Add new ports in the transformation object and define them as variable ports to do complex transformation
Mapping Creation • Open the Targets from the navigator which you would have created/imported using Warehouse Designer • Select the target table, drag and drop it into the designer workspace
Mapping Creation • Drag and drop the output ports from the last transformation object to the corresponding ports in the target
• Choose Repository > Save to store the mapping • Check the output window for any errors
Mapping Wizards Wizards help to create mappings quickly and easily Wizards designed to create mappings for loading and maintaining star schemas Mappings created using wizards can be edited and customized Getting Started Wizard • Simple Pass Through • mappings to load static fact and dimensions • Use this when you want to drop all existing data from the table before loading new data
• Slowly Growing Target • mappings to load slowly growing dimension or fact tables • Use this to load new data when existing data does not require updates
Mapping Wizards Slowly Changing Dimensions Wizard • Type I Dimension Mapping - Keep most recent values in target
Target
Source Emp id
Name
Emp id
Name
1001
Shane
Shane @xyz.c om
1001
Shane
Shane @xyz.c om
Source
Target
Emp id
Name
Emp id
Name
1001
Shane
Shane@ abc.co.in
1001
Shane
Shane@ abc.co.in
Shane@xyz. com
Mapping Wizards Slowly Changing Dimensions Wizard • Type II Dimension Mapping Version Data Mapping - insert new and changed dimensions with version number and incremented primary key - full history and progress of changes
Source Emp id
Name
10
Shane
Shane @xyz.c om
Target
P M_P RIMA RYKE Y
Emp id
Name
PM_V ERSI ON_ NUM BER
1000
10
Shane
Shan e@xy z.co m
0
Type II Dimension/Versioning Source Emp id
Name
10
Shane Shane@ abc.co.in
PM_PRI MARYKE Y
Emp id
Name
PM_VERSION_N UMBER
1000
10
Shane
Shane@ xyz.com
0
1001
10
Shane
Shane@ abc.co.in
1
Target
Type II Dimension/Versioning Source Emp id
Name
10
Shane Shane@ abc.com
Target
PM_PRI MARYKE Y
Emp id
Name
PM_VERSION_N UMBER
1000
10
Shane
Shane@ xyz.com
0
1001
10
Shane
Shane@ abc.co.in
1
1003
10
Shane
Shane@ abc.com
2
Mapping Wizards Slowly Changing Dimensions Wizard • Type II Dimension Mapping Flag Current Mapping - insert new and changed dimensions with flags and incremented primary key - full history and flagging only current dimensions
Target
Source Emp id
Name
10
Shane
Shane @xyz.c om
P M_P RIMA RYKE Y
Emp id
Name
PM_CU RRENT _FLAG
1000
10
Shane
Shan e@xy z.co m
1
Type II Dimension/Flag Current Source Emp id
Name
10
Shane Shane@ abc.co.in
PM_PRI MARYKE Y
Emp id
Name
PM_CURRENT_F LAG
1000
10
Shane
Shane@ xyz.com
N
1001
10
Shane
Shane@ abc.co.in
Y
Target
Type II Dimension/Flag Current Source Emp id
Name
10
Shane Shane@ abc.com
Target
PM_PRI MARYKE Y
Emp id
Name
PM_CURRENT_F LAG
1000
10
Shane
Shane@ xyz.com
N
1001
10
Shane
Shane@ abc.co.in
N
1003
10
Shane
Shane@ abc.com
Y
Mapping Wizards Slowly Changing Dimensions Wizard • Type II Dimension Mapping • Effective Date Range Mapping - insert new and changed dimensions with date range to define current dimension data - full history and tracking changes with an exact effective date range
Source Emp id
Name
10
Shane Shane @xyz.c om
Target
P M_P RIMA RYKE Y
Emp id
Name
PM_B EGIN _DAT E
1000
10
Shane
Shan e@xy z.co m
01/0 1/00
PM_E ND_ DATE
Type II Dimension/Effective Date Source Emp id
Name
10
Shane Shane@ abc.co.in
PM_PRIMA RYKEY
Emp id
Name
1000
10
Shane
Shane 01/01/00 @xyz.c om
1001
10
Shane
Shane 03/01/00 @ abc.co. in
Target
PM_BEGIN _DATE
PM_END _DATE 03/01/00
Type II Dimension/Effective Date Source Emp id
Name
10
Shane Shane@ abc.com
PM_PRI MARYKE Y
Emp id
Name
PM_BEGIN _DATE
PM_END_ DATE
1000
10
Shane
Shane@ xyz.com
01/01/00
03/01/00
1001
10
Shane
Shane@ abc.co.in
03/01/00
05/02/00
1003
10
Shane
Shane@ abc.com
05/02/00
Target
Mapping Wizards Slowly Changing Dimensions Wizard • Type III Dimension Mapping - insert new and update values in existing dimensions - keep current and previous values (partial history)
Target Source Emp id
Name
10
Shane Shane @xyz.c om
PM_P RIMA RYKE Y
Emp id
Name
1
10
Shane
Shane @xyz. com
PM_P rev_ Colu mnN ame
PM_E FFECT _DAT E
01/01 /00
Type III Dimension Source Emp id
Name
10
Shane Shane@ abc.co.in
PM_PRIMA RYKEY
Emp id
Name
PM_Prev_ ColumnNa me
PM_EFFE CT_DATE
1
10
Shane
Shane Shane@xy @ z.com abc.co. in
01/02/00
Target
Type III Dimension Source Emp id
Name
10
Shane Shane@ abc.com
PM_PRI MARYKE Y
Emp id
Name
PM_Prev_ ColumnNa me
PM_EFFEC T_DATE
1
10
Shane
Shane@ abc.com
Shane@ abc.co.in
01/03/00
Target
Mapplet Designer Mapplet • Reusable object that reflect set of transformation logic to use in multiple mappings • Like a single transformation object • Expands at run time
Each Mapplet must include the following • Exactly one input transformation or Source Qualifier transformation • At least one output transformation
Rules for Objects in Mapplets • Use only reusable Sequence Generators • Do not use pre- or post-session stored procedures
Mapplet Designer Rules for Objects in Mapplets • Do not use the following in a mapplet Joiner Cobol Source definition Normalizer Target definitions
Mapplet Designer How to create a Mapplet? • Open the Mapplet designer workspace • Choose Mapplets > Create • Give a name to the Mapplet
• Create an input transformation to define mapplet input ports if the mapplet contains no sources
Creating a Mapplet
• Double click on the object to define the input ports
Creating a Mapplet • Create the transformation objects to be used in the mapplet • One input transformation can be connected to only one transformation, so to pass same values to two separate data flows,connect the input transformation to another transformation and then split the data flow • Use Output transformation to create output ports, creating one Output transformation for each mapplet output group
• Connect all the input ports, ports in transformation objects and the output ports to complete the data flow
Creating a Mapplet
• Choose Repository > Save to store the mapplet • Check the output window for validation status • If mapplet is not valid,correct the problem and re-save the mapplet
Replacing a relational database connection • You can replace a relational database connection with another relational database connection. • For example, you might have several sessions that you want to write to another target database. Instead of editing the properties for each session, you can replace the relational database connection for all sessions in the repository that use the connection.
Work Flow Manager • In the Workflow Manager, you define a set of instructions called a workflow to execute mappings you build in the Designer. • Generally, a workflow contains a session and any other task you may want to perform when you execute a session. Tasks can include a session, email notification, or scheduling information. You connect each task with links in the workflow. • You can also create a worklet in the Workflow Manager. A worklet is an object that groups a set of tasks
Replacing a relational database connection •Workflow manager>Connections>Replace
Working with tasks Type of Tasks • • • • • • •
Command Decision Event Email Timer Assignment Session
Command • The Command task allows you to specify one or more shell commands to run during the workflow. For example, you can specify shell commands in the Command task to delete reject files, copy a file, or archive target files. • You can use a Command task in the following ways: Standalone Command task: You can use a Command task anywhere in the workflow or worklet to run shell commands. Pre- and post-session shell command: You can call a Command task as the pre- or post-session shell command for a Session task.
Command (Cntnd…)
Decision •The Decision task allows you to enter a condition that determines the execution of the workflow, similar to a link condition.
Decision
•The Decision task has a pre-defined variable called $Decision_task_name.Condition that represents the result of the decision condition. •The Informatica Server evaluates the condition in the Decision Task and sets the pre-defined condition variable to True (1) or False (0). •You can use the Decision task instead of multiple link conditions in a workflow
Decision (Contd…)
Event •You can define events in the workflow to specify the sequence of task execution. The event is triggered based on the completion of the sequence of tasks. Use the following tasks to help you use events in the workflow: Event-Raise task. Event-Raise task represents a user-defined event. Use the Event-Raise task with the Event-Wait task to define events. Event-Wait task. The Event-Wait task waits for an event to occur. Once the event triggers, the Informatica Server continues executing the rest of the workflow. Pre-defined event. A pre-defined event is a file-watch event. For pre-defined events, use an Event-Wait task to instruct the Informatica (Indicator File) User-defined event. A user-defined event is a sequence of tasks in the workflow.
Event(Contd..)
Event (Contd…)
Email •The Workflow Manager provides an Email task that allows you to send email during a workflow. •You can create reusable Email tasks in the Task Developer for any type of email. Or, you can create non-reusable Email tasks in the Workflow and Worklet Designer. You can use Email tasks in any of the following locations: Session properties Workflow properties Workflow or worklet
Email(Contd…)
Timer •The Timer task allows you to specify the period of time to wait before the Informatica Server executes the next task in the workflow. •You can choose to start the next task in the workflow at an exact time and date. •You can also choose to wait a period of time after the start time of another task, workflow, or worklet before starting the next task. The Timer task has two types of settings: Absolute time. Relative time.
Timer(Contd..)
Assignment •The Assignment task allows you to assign a value to a user-defined workflow variable.
Session •You create a Session task for each mapping you want the Informatica Server to run. The Informatica Server uses the instructions configured in the session to move data from sources to targets.
Session
Worklet •A worklet is an object that represents a set of tasks. It can contain any task available in the Workflow Manager. •You can also nest a worklet in another worklet.
WorkFlow Monitor • The Workflow Monitor is a tool that allows you to monitor workflows and tasks. • You can view details about a workflow or task in either Gantt Chart view or Task view. • You can run, stop, abort, and resume workflows from the Workflow Monitor.
Workflow Monitor •You can configure options specific to Gantt Chart and Task view.
WorkFlow Monitor Gantt Chart View
WorkFlow Monitor • Task View
Related Documents
Mapping Wizards
November 2019 33
Math Wizards
June 2020 13
Witty Wizards
July 2020 10
Mapping
June 2020 30
Modern Witches And Wizards
November 2019 13