Learning Informatica PowerCenter 9.x (2014)

Chapter 2. Using the Designer Screen – Advanced

In our first chapter, we developed our skills in order to use the PowerCenter Designer screen; you also learned how to create mappings using sources, targets, and transformations.

Before you read this chapter, make sure that you have a complete understanding of the various components of the PowerCenter Designer screen that we discussed in Chapter 1, Starting the Development Phase – Using the Designer Screen Basics. You should be clear on how to use sources and targets and how to create mappings.

In this chapter, we will talk about the high-level aspects of the PowerCenter Designer screen. Once we are clear with the basics of the designer screen, we are all set to work on the advanced concepts of PowerCenter Designer.

Apart from the basic functionalities of creating mappings, the designer screen offers multiple utilities that assist you in smoother execution of the ETL processing. Some of these functionalities will be regularly used, and some will be rare for you. It is always good to have an understanding of these functionalities in order to have an upper hand while using the tool.

The topics that will be covered in this chapter are as follows:

· Debugger

· Reusable transformations

· Mapplets

· Target load plans

· Parameters and variables

· Comparing objects

Debug me please – the debugger

Informatica PowerCenter provides a utility called debugger that debugs the mapping so that you can easily find issues with the mapping that you created. Using the debugger, you can see the flow of every record across the transformations.

As you are aware, Informatica PowerCenter is not a data storage tool, it is a data manipulation tool that helps you manipulate the data. This point is important to the debugger, as once you finish the process, you only have either the source or the target to check the result and compare it in order to verify the data. Debugger jumps in with a functionality that provides you with the option to actually see the data flow from each and every transformation in your mapping.

When you execute the mapping through the session task, the data automatically starts flowing from the source to the target through transformations. You manually execute the same process using the debugger.

Take a look at the upcoming example to understand the debugger functionality.

You created a mapping with 100 transformations, and you have 1,000 records present in your source. You executed the mapping and got the results. When you started analyzing the output in your target table, you found that the data in few columns was incorrect. Now, the problem is that you are not sure where out of the 100 transformations the issue actually started— debugger is your solution. It will allow you to see the movement of each record from every transformation so that you can catch the origin of the error and rectify it accordingly.

To set up and execute the debugger, perform the following steps:

1. In the designer, navigate to Tools | Mapping Designer | Mapping | Debugger | Start Debugger, as shown in the following screenshot:

2. A new window will pop up mentioning the prechecks for the debugger as follows:

Read the points clearly before you proceed and click on Next.

3. The next screen will ask you to select the session task for the mapping you wish to debug. Select the session task you created in order to execute the mapping.

Even though running a debugger is a manual process, you still need a session task because Integration Service needs to get the path to extract and load the data that is provided in the session task.

Note that you can use an existing session or create a new debug session to run the debugger. If you use an existing session, the debugger will use the properties mentioned in the session. You need to mention all the details if you create a new debug session.

After selecting the session, click on Next.

4. Select the appropriate session in the next window and click on Next.

5. In the next screen, you will see the option to discard the target data. This option indicates what you wish to do with the data you loaded in the target using the debugger. As you run the debugger for testing purposes, you basically wish to discard the target data.

Checking this option will discard the data in the target. Now, click on Finish.

The output panel of your screen will get divided into three sections, as shown in the following screenshot:

The output panel gets divided into the following sections:

· Notification: This section will show you general information about the debugger execution.

· Target instance: This section will show you the data as it reaches the target of your mapping. If your mapping contains multiple targets, all the targets will be displayed in this section in the drop-down list, as shown in the preceding screenshot. You can select the target for which you wish to see the data from the dropdown.

· Instance: This section will show you the data when it reaches different transformations of your mapping. If your mapping contains multiple transformations, all the transformations will be displayed in this section in the dropdown, as shown in the preceding screenshot. You can select the transformation for which you wish to see the data from the dropdown. The debugger shows you the data in each transformation as per the logic you coded in the mapping.

We have completed the setup for the debugger; now click on Next Instance to start the debugger.

The debugger will start showing you the movement of the data from each transformation under the instance, and then in the target under the target instance at the end.

Keep pressing Next Instance (F10) to move the data toward the next instance in your mapping flow.

Once all the data reaches the target, the debugger will automatically shut down.

If you have very big mapping, and traversing through every transformation is a tough task, you can actually select particular transformations at a particular interval to understand and narrow the issue down. This can be achieved using breakpoints.

To set a breakpoint, click on Edit Breakpoint, as shown in the following screenshot:

A new window will let you add breakpoints. Click on Add to add a new breakpoint to the debugger. You can set few more properties in this window.

As you must have noticed, the debugger is the best way to find the issues in the data movement across the mapping. So, whenever you face some issues related to data loading in the target or mismatches in the targets, start a debugger and solve your issue.

Reuse me please – reusable transformation

As you are aware, source and targets are reusable components, that is, you work on sources in the Source Analyzer and targets in the Target Designer, respectively. Also, you must be aware that you cannot edit sources and targets in the Mapping Designer, and they can be edited in Source Analyzer and Target Designer, respectively.

Sources and targets are called reusable components because you can use the same source and target in multiple mappings. We can reuse the source or target across multiple mappings only if the metadata requirement of both the mappings is exactly the same. In this case, metadata means the number of columns, their data type, their data size, indexes, constraints, and so on. Even if there are small changes, you cannot reuse the components.

On the same lines, if we have the same logic to implement across multiple mappings, we can use the reusable transformations, which allow us to reuse the same transformation across mappings. You can only reuse the transformation if the metadata requirements in the mappings are exactly the same. Even if there is a small difference, the reusable transformations will not work as there can be mismatch in the processing.

There are two ways in which you can create reusable transformations:

· Using the Transformation Developer

· Making existing transformations reusable

Using Transformation Developer

To use Transformation Developer, perform the following steps:

1. In the designer, navigate to Tools | Transformation.

2. Click on Transformation | Create.

3. Select the transformation type you wish to create from the list of transformations. We are creating an expression transformation as an example. Click on Create and then click on Done.

The transformation will appear on the screen. Also, you will see the transformation added to your navigator under Transformations, as shown in the following screenshot:

Now, you can drag-and-drop the transformation in the mapping in the Mapping Designer in order to use it as a reusable component.

You will see that you cannot edit the reusable transformation in the Mapping Designer. To edit the reusable transformation, you need to use Transformation Developer.

Making existing transformation reusable

In the earlier section, we saw how to use a new transformation as a reusable transformation. Consider that you already have a transformation that you wish to use in another mapping, but as it is nonreusable, you are not able to do so. To solve this issue, we have an option using which you can make an existing nonreusable transformation reusable. Perform the following steps to do this:

1. Open the mapping containing the transformation you wish to make reusable in the Mapping Designer. We are using an existing mapping containing an expression transformation in order to make it reusable.

2. Double-click on the transformation in order to view the properties of the transformation. You will see an option, Make reusable. Click on the box, as shown in the upcoming screenshot.

A warning message will pop up mentioning that the process is irreversible.

You can see that the transformation is now added under Transformation in the navigator, which indicates that the transformation can be reused; you can reuse this in other mappings too.

Mapplet

In the previous section, we saw how to make a transformation reusable. Going further, you might also like to reuse logic that was implemented using multiple transformations. The group of transformations that can be reused is called as mapplets.

Mapplets can be created in the Mapplet Designer in Informatica PowerCenter Designer. Mapplets allow you to reuse groups of transformations in multiple mappings. As is the case with reusable transformations, in order to use mapplets, the metadata requirements of the mappings should be exactly the same.

You can use the Mapplet Designer to create mapplets with new transformations or use existing transformations.

To create a new mapplet, perform the following steps:

1. In the designer, navigate to Tools | Mapplet Designer, as shown in the following screenshot. Then, click on Create.

2. Specify the name of new mapplet as MPLT_REUSABLE.

3. Add the transformations in the mapplet as per your logic. We are using filter and expression transformation to create sample logic.

4. Click on Transformations and create mapplet input and mapplet output and name them MPLT_INPUT and MPLT_OUTPUT, respectively.

5. Place the mapplet input before the filter and the mapplet output after the expression. These will act as the source and target for the mapplet. Drag-and-drop the columns from the expression transformation to MPLT_OUTPUT and drag-and-drop the columns from filter transformation back toMPLT_INPUT, as shown in the following screenshot:

6. Press Ctrl + S to save the mapplet, and you will be able to see the mapplet added under mapplets in the navigator.

7. You can now drag-and-drop the mapplet in the Mapping Designer in order to use it as reusable components. Drag-and-drop the mapplet into the Mapping Designer and link the columns from the source qualifier to input ports of the mapplet and drag-and-drop the output ports from the mapplet to the Target Designer, as shown in the following screenshot:

If you wish to reuse the logic that was implemented using multiple transformations in an existing mapping, you can simply copy the existing transformations from the Mapping Designer and paste it in the Mapplet Designer. This is the easiest way to use the existing group of transformations.

As you might have noticed, mapplet serves two purposes—first, it allows you to reuse your existing transformation and second, it makes your mapping look simpler by replacing multiple components with a single component.

Managing constraints – the target load plan

While you work on multiple mappings in a complex scenario, situations might demand that you put multiple data flows in a single mapping in order to justify the performance and the complexity. A sample mapping depicting the preceding statement is shown in the following screenshot:

If you execute the mapping, the session might fail because of the primary key and foreign key violation. As the Employee and Department tables have dependencies on each other, when you run the mapping containing both the flows, the process might fail if the data violates the dependency.

To avoid the issue, Informatica PowerCenter contains a utility called the target load plan—how do you plan to load the data into multiple targets in a mapping? We can load the data in a particular sequence in multiple targets in a mapping in order to avoid failure due to constraints.

Consider that the EMPLOYEES table data depends on the DEPARTEMNTS data because of the primary key and foreign key constraints. So, to satisfy the referential integrity, the DEPARTMENTS table should be loaded first. The target load order is useful when you wish to handle referential integrity while inserting, deleting, or updating data in tables that have the primary key and foreign key relationships.

To set the target load plan in a mapping, perform the following process:

1. In the designer, navigate to Tools | Mapping Designer | Mapping | Target Load Plan, as shown in the following screenshot:

2. New window will pop up, as shown in the following screenshot:

To set the target load plan, just move the target load group using the arrow provided in the window. To load the DEPARTMENTS table before the EMPLOYEES table, select SQ_DEPARTMENTS and click on the up arrow, as shown in the preceding screenshot.

This will make the DEPARTMENTS table load before the EMPLOYEES table is loaded, and we can avoid failure.

When we use the target load plan, Informatica PowerCenter actually restricts the extraction of the data from the source qualifier. It waits for the first flow in the selected sequence to finish loading the data and then starts extracting the data from the second source qualifier for its loading.

I hate hardcoding – parameters and variables

When you work on any technology, it is always advised that your code be dynamic. This means that you should use the hardcoded values as less as possible in your code. It is always recommended that you use the parameters or the variable in your code so that you can easily pass these values and don't need to change the code frequently. We will discuss this concept in more detail in the Parameters file section in Chapter 5, Using the Workflow Manager Screen – Advanced.

In this section, we will discuss how to use parameters and variables on the PowerCenter Designer screen.

The value of a variable can change between the session run. The value of a parameter will remain constant across the session runs. The difference is very minute, so you should define parameters and variables properly, as per your requirements.

Consider a filter transformation where you have defined the filter condition as LOCATION='USA'. As you have used hardcoded values in the filter conditions, it is always recommended that you pass the value using a parameter or variable.

Follow these steps to use variables or parameters:

1. In the designer, navigate to Tools | Mapping Designer | Mapping | Parameters and Variables, as shown in the following screenshot:

2. A new window will pop up allowing you to provide parameters and variables, as shown in the following screenshot. Add a new parameter or variable depending on your requirement. For our reference, we are creating a $$LOCATION. $$ parameter that is used as a mapping level variable or parameter. From the dropdown, you can choose whether you wish to make it a parameter or a variable. Then, click on OK.

3. Once you define the parameter or variable at the mapping level, you can use them in the transformations. Take a look at the following screenshot:

With this, we have defined a parameter or a variable at the mapping level and used it in the transformations in order to avoid hardcoding.

Comparing objects

Informatica PowerCenter allows you to compare objects that are present within repository. You can compare sources, targets, transformations, mapplets and mappings in the PowerCenter Designer under Source Analyzer, Target Designer, Transformation Developer, Mapplet Designer, and Mapping Designer, respectively. You can compare the objects in the same repository or in multiple repositories.

Perform the following steps to compare two objects. We are using two sources and comparing them as an example:

1. In the designer, navigate to Tools | Source Analyzer | Sources | Compare, as shown in the following screenshot:

2. Select Source 1 and Source 2, which you wish to compare, as shown in the following screenshot:

3. Click on Compare.

4. You can check out the different types of comparison options of the two sources. The Informatica PowerCenter comparison utility gives you the option to check out the differences between the repository and versions in the General tab. The table tab allows you to check out the differences between the owner name and the database type. The Column tab allows you to see the differences in the column and other properties related to columns such as data type, precision, and other constraints. Similar to this, when you compare targets and mappings, you can check out the differences using the Compare Object functionality.

This can be helpful in the analysis of the components and to understand the difference between the newer and the existing components.

Summary

We started the chapter with the debugger, which helps you debug the errors in your mapping. Using the debugger, you can pinpoint the error in your mapping using which you can easily resolve issues. Next, we discussed the reusable transformation that allows you to reuse the transformation across mappings. Reusable transformations are very important, as they save the time and effort required to recreate the same transformations.

Moving on, we talked about mapplets, which allow you to reuse groups of transformations that make your mapping simpler by replacing multiple transformations with a single component. Also, mapplets save your time and effort by allowing you to reuse existing components. Next in the line was the target load plan using which you can set the priority of loading the target tables in a single mapping. This is useful while loading the data and maintaining the constraints. We also saw some details about the parameters and variables that we will discuss in more details in Chapter 5,Using the Workflow Manager Screen – Advanced. We finished the chapter by comparing the objects functionality, which allows you to compare two components, which is very helpful in understanding and maintaining the versions on the repository.

With this, we have seen all the basic and advanced topics on the Informatica PowerCenter Designer screen. The more you practice, the better you will get at building your logic. As you must understand by now, practice is the only option if you wish to gain expertise and understand the nuances of the Informatica PowerCenter tool.

In the next chapter, we will take our discussion to a very important data warehousing concept, that is, Slowly Changing Dimensions (SCD). We will use the wizard feature that is available on designer screens in order to understand the different types of SCDs that are available.