Reusability is a great feature in Informatica PowerCenter which can be used by developers. Its general purpose is to reduce unnecessary coding which ultimately reduces development time and increases supportability. In this article lets see how we can build mapplet in Informatica PowerCenter to make your code reusable.Lets consider a real time scenario for the demonstration.
What is Mapplet
Mapplet is a reusable object that you create in the Mapplet Designer. It contains a set of transformations and lets you reuse the transformation logic in multiple mappings. When you use a mapplet in a mapping, you use an instance of the mapplet. Any change made to the mapplet is inherited by all instances of the mapplet.
The Sales Department is interested in getting both the quarterly and yearly sales. This calculation is required in multiple ETL process. So decided to create a reusable code using Mapplet.
- Build a mapplet that uses multiple sources and aggregate functions.
- Create a variable within the mapplet for use in the aggregate functions.
I. Set the Mapplet Designer Options
- Manually create a Source Qualifier to pull in data from multiple source definitions. To build a custom Source Qualifier, you must set the Mapplet Designer Options correctly.
- Select TOOLS | OPTIONS.
- Click the Format tab.
- In the Category section, choose Mapplet Designer from the pull-down list.
- In the Tables section, uncheck the Create Source Qualifiers When Opening Sources box.
- Click OK.
II. Create a New Mapplet
- Switch to Mapplet Designer.
- Create a Mapplet, by selecting MAPPLETS | CREATE.
- Name the mapplet MPLT_QtrSales_x.
III. Analyze the source tables.
- Bring the source definitions ITEMS, ORDER_ITEMS, and ORDERS into the Mapplet Designer Workspace by dragging them from the Navigator Window into the workspace.
- Create the Source Qualifier either from TRANSFORMATIONS | CREATE, or use the Source Qualifier icon from the transformation toolbar. Name it SQ_SalesByQtr_x.
IV. Create an Aggregator transformation
- Create an Aggregator transformation and name it Agg_SalesByQtr_x
- Copy and link ITEM_ID and ITEM_NAME from the Source Qualifier (SQ_SalesByQtr_x) into the aggregator(Agg_SalesByQtr_x).
- Double-click on Agg_SalesByQtr_x to edit the Aggregator.
- On the Columns tab, add the following ports:
NOTE: Notice that you now have a new input port DATE_ENTERED in your Aggregator transformation. The local input port is automatically added when the external reference made to the SQ_SalesByQtr_x is validated.
- MONTH = GET_DATE_PART(DATE_ENTERED,'MM')
- Q1Sales = SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 1 OR MONTH = 2 OR MONTH = 3)
- Q2Sales = SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 4 OR MONTH = 5 OR MONTH = 6)
- Q3Sales = SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 7 OR MONTH = 8 OR MONTH = 9)
- Q4Sales = SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 10 OR MONTH = 11 OR MONTH = 12)
NOTE: The ports in the Source Qualifier must be in the same order as the ports in the Aggregator, in order to facilitate the correct summarization by the groupings you have specified, above.
V. Create a Mapplet Output Transformation
- Select TRANSFORMATION | CREATE or Click on the icon from the Transformations toolbar. Name it Output_SalesByQtr_x
- Select all of the output ports of the Aggregator and drag them into the mapplet Output transformation.
- Select MAPPLET | VALIDATE from the menu.
- Verify the results of the mapplet validation in the Output Window.
- Save the repository.
We are all done and below is the completed Mapplet layout.
Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise.