Databondsnew

Informatica Testing

2012-08-01T18:32:00.001-07:00

Unit Test Plan(UTP)

Testing regimens:

1. Unit Testing

2. Functional Testing

3. System Integration Testing

4. User Acceptance Testing

Unit testing: The testing, by development, of the application modules to verify each unit (module) itself meets the accepted user requirements and design and development standards

Functional Testing: The testing of all the applications modules individually to ensure the modules, as released from development to QA, work together as designed and meet the accepted user requirements and system standards

System Integration Testing: Testing of all of the application modules in the same environment, database instance, network and inter-related applications, as it would function in production. This includes security, volume and stress testing.

User Acceptance Testing(UAT): The testing of the entire application by the end-users ensuring the application functions as set forth in the system requirements documents and that the system meets the business needs.

UTP Template:

Step #	Description	Test Conditions	Expected Results	Actual Results,	Pass or Fail (P or F)	Tested By
SAP- CMS Interfaces
1	Check for the total count of records in source tables that is fetched and the total records in the PRCHG table for a perticular session timestamp	SOURCE: SELECT count() FROM XST_PRCHG_STG TARGET:* Select count(*) from _PRCHG	Both the source and target table load record count should match.	Should be same as the expected	Pass	Stev
2	Check for all the target columns whether they are getting populated correctly with source data.	select PRCHG_ID, PRCHG_DESC, DEPT_NBR, EVNT_CTG_CDE, PRCHG_TYP_CDE, PRCHG_ST_CDE, from T_PRCHG MINUS select PRCHG_ID, PRCHG_DESC, DEPT_NBR, EVNT_CTG_CDE, PRCHG_TYP_CDE, PRCHG_ST_CDE, from PRCHG	Both the source and target table record values should return zero records	Should be same as the expected	Pass	Stev
3	Check for Insert strategy to load records into target table.	Identify a one record from the source which is not in target table. Then run the session	It should insert a record into target table with source data	Should be same as the expected	Pass	Stev
4	Check for Update strategy to load records into target table.	Identify a one Record from the source which is already present in the target table with different PRCHG_ST_CDE or PRCHG_TYP_CDE values Then run the session

What are the differences between stored procedures and triggers ?

2012-08-01T04:29:00.000-07:00

Stored procedure normally used for performing tasks But the Trigger normally used for tracing and auditing logs.

Stored procedures should be called explicitly by the user in order to execute But the Trigger should be called implicitly based on the events defined in the table.

Stored Procedure can run independently But the Trigger should be part of any DML events on the table.

Stored procedure can be executed from the Trigger But the Trigger cannot be executed from the Stored procedures.

Stored Procedures can have parameters.But the Trigger cannot have any parameters.

Stored procedures are compiled collection of programs or SQL statements in the database.

Using stored procedure we can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object.

But triggers are event-driven special procedures which are attached to a specific database object say a table.

Stored procedures are not automatically run and they have to be called explicitly by the user. But triggers get executed when the particular event associated with the event gets fired.

Snapshot Facts

2012-07-29T21:46:00.001-07:00

The snapshot fact contains a reflection of the state of an entity at a given point in time. A classic example of this would be a "daily_balance" fact in a banking system. This would, on a daily basis record the balance of each account, it would NOT list the individual transactions that happened on the account.

The differences between a logical data model and physical data model

2012-07-29T21:46:00.000-07:00

Logical vs Physical Data Modeling

Logical Data Model	Physical Data Model
Represents business information and defines business rules	Represents the physical implementation of the model in a database.
Entity	Table
Attribute	Column
Primary Key	Primary Key Constraint
Alternate Key	Unique Constraint or Unique Index
Inversion Key Entry	Non Unique Index
Rule	Check Constraint, Default Value
Relationship	Foreign Key
Definition	Comment

Factless Facts

2012-07-29T21:45:00.000-07:00

A factless fact is where the fact does not store an actual numerical measure, the mere existance of a fact record indicates that an event has happened that you wish to track. The classic example of this would be an "Attendance" fact. If you had dimensions to record date, scheduled_course, instructor and delegate then you could create a fact table that held the permutations of these dimensions. From this you could evaluate the number of courses you run, the number of delegates, the number of courses by instructor etc.

I would never simply leave a factess fact as a bare collection of foreign key columns I would always add a dummy measure column which would be set to 1 which you would then sum.

Conformed dimensions

2012-07-29T21:44:00.001-07:00

Conformed dimensions can be used to analyze facts from two or more data marts. Suppose you have a “shipping” data mart (telling you what you’ve shipped to whom and when) and a “sales” data mart (telling you who has purchased what and when). Both marts require a “customer” dimension and a “time” dimension. If they’re the samedimension, then you have conforming dimensions, allowing you to extract and manipulate facts relating to a particular customer from both marts, answering questions such as whether late shipments have affected sales to that customer.

Suppose now that you add a “marketing” data mart to help you analyze product promotions. Again, with conformed customer and time dimensions, you’re able to analyze the effects of a particular product promotion on sales. (Analyzing facts from more than one fact table in this way is termed “drilling across.” My previous article,“Thinking dimensionally aids business intelligence design and use,” explains the function of facts and dimensions.)

As this example shows, the very same conformed dimensions—in this case, time and customer dimensions—have meaning in the context of three independentlydevelopeddata marts. These dimensions become enterprise property and can be used later in other marts as you evolve the enterprise data warehouse.

What is Junk Dimension?

2012-07-29T21:44:00.000-07:00

A "junk" dimension is a collection of random transactional codes, flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes. A good example would be a trade fact in a company that brokers equity trades.

When you consolidate lots of small dimensions and instead of having 100s of small dimensions, that will have few records in them, cluttering your database with these mini ‘identifier’ tables, all records from all these small dimension tables are loaded into ONE dimension table and we call this dimension table Junk dimension table. (Since we are storing all the junk in this one table) For example: a company might have handful of manufacture plants, handful of order types, and so on, so forth, and we can consolidate them in one dimension table called junked dimension table

It’s a dimension table which is used to keep junk attributes

Informatica PowerCenter Repository tables

2012-07-29T21:29:00.000-07:00

every PowerCenter developer either has an intention or necessity to know about the Informatica metadata tables and where information is stored etc. For the starters, all the objects that we create in Informatica PowerCenter - let them be sources, targets, mappings, workflows, sessions, expressions, be it anything related to PowerCenter, will get stored in a set of database tables (call them as metadata tables or OPB tables or repository tables).

* I want to know all the sessions in my folder that are calling some shell script/command in the Post-Session command task.
* I want to know how many mappings have transformations that contain "STOCK_CODE" defined as a port.
* I want to know all unused ports in my repository of 100 folders.

In repositories where you have many number of sessions or workflows or mappings, it gets difficult to achieve this with the help of Informatica PowerCenter client tools. After all, whole of this data is stored in some form in the metadata tables. So if you know the data model of these repository tables, you will be in a better position to answer these questions.

Before we proceed further, let me clearly urge for something very important. Data in the repository/metadata/OPB tables is very sensitive and that the modifications like insert or updates are to be made using the PowerCenter tools ONLY. DO NOT DIRECTLY USE UPDATE OR INSERT COMMANDS AGAINST THESE TABLES.

Please also note that there is no official documentation from Informatica Corporation on how these tables act. It is purely based on my assumption, research and experience that I am providing these details. I will not be responsible to any of the damages caused if you use any statement other than the SELECT, knowing the details from this blog article. This is my disclaimer. Let us move on to the contents now.
There around a couple of hundred OPB tables in 7.x version of PowerCenter, but in 8.x, this number crosses 400. In this regard, I am going to talk about few important tables in this articles. As such, this is not a small topic to cover in one article. I shall write few more to cover other important tables like OPB_TDS, OPB_SESSLOG etc.
We shall start with OPB_SUBJECT now.

OPB_SUBJECT - PowerCenter folders table

This table stores the name of each PowerCenter repository folder.

Usage: Join any of the repository tables that have SUBJECT_ID as column with that of SUBJ_ID in this table to know the folder name.

OPB_MAPPING - Mappings table

This table stores the name and ID of each mapping and its corresponding folder.

Usage: Join any of the repository tables that have MAPPING_ID as column with that of MAPPING_ID in this table to know the mapping name.

OPB_TASK - Tasks table like sessions, workflow etc

This table stores the name and ID of each task like session, workflow and its corresponding folder.

Usage: Join any of the repository tables that have TASK_ID as column with that of TASK_ID/SESSION_ID in this table to know the task name. Observe that the session and also workflow are stored as tasks in the repository. TASK_TYPE for session is 68 and that of the workflow is 71.

OPB_SESSION - Session & Mapping linkage table

This table stores the linkage between the session and the corresponding mapping. As informed in the earlier paragraph, you can use the SESSION_ID in this table to join with TASK_ID of OPB_TASK table.

OPB_TASK_ATTR - Task attributes tables

This is the table that stores the attribute values (like Session log name etc) for tasks.

Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this table means. You can know more about OPB_ATTR table in the next paragraphs.

OPB_WIDGET - Transformations table

This table stores the names and IDs of all the transformations with their folder details.

Usage: Use WIDGET_ID from this table to that of the WIDGET_ID of any of the tables to know the transformation name and the folder details. Use this table in conjunction with OPB_WIDGET_ATTR or OPB_WIDGET_EXPR to know more about each transformation etc.

OPB_WIDGET_FIELD - Transformation ports table

This table stores the names and IDs of all the transformation fields for each of the transformations.

Usage: Take the FIELD_ID from this table and match it against the FIELD_ID of any of the tables like OPB_WIDGET_DEP and you can get the corresponding information.

OPB_WIDGET_ATTR - Transformation properties table

This table stores all the properties details about each of the transformations.

Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this transformation means.

OPB_EXPRESSION - Expressions table

This table stores the details of the expressions used anywhere in PowerCenter.

Usage: Use this table in conjunction with OPB_WIDGET/OPB_WIDGET_INST and OPB_WIDGET_EXPR to get the expressions in the Expression transformation for a particular, mapping or a set.

OPB_ATTR - Attributes

This table has a list of attributes and their default values if any. You can get the ATTR_ID from this table and look it up against any of the tables where you can get the attribute value. You should also make a note of the ATTR_TYPE, OBJECT_TYPE_ID before you pick up the ATTR_ID. You can find the same ATTR_ID in the table, but with different ATTR_TYPE or OBJECT_TYPE_ID.

OPB_COMPONENT - Session Component

This table stores the component details like Post-Session-Success-Email, commands in Post-Session/pre-Session etc.

Usage: Match the TASK_ID with that of the SESSION_ID in OPB_SESSION table to get the SESSION_NAME and to get the shell command or batch command that is there for the session, join this table with OPB_TASK_VAL_LIST table on TASK_ID.

OPB_CFG_ATTR - Session Configuration Attributes

This table stores the attribute values for Session Object configuration like "Save Session log by", Session log path etc.

Optimizing Oracle Optimizer Statistics

2012-07-29T21:23:00.000-07:00

Optimizing Oracle Optimizer Statistics

Top 5 MS Power Point Tips

2012-07-29T21:13:00.000-07:00

Power point has become so common a tool that every Windows user clicks on MS Power point icon when asked to research, create a presentation, draw a flowchart or even just take notes.

However, there is so much of power under the hood that hardly gets utilized. So here are the top 5 power point tips to upgrade your power point experience

1. Stop Ctrl+C (Copy) and Ctrl+V (Paste). Select an object and do a Ctrl+D (Duplicate). Not only are you reducing the number of key presses, the Duplicate shortcut also aligns every newly duplicated object to create a pattern of equidistant objects

2. Pasted a image and it has an irritating white background?. Relax. You can clear the white background by double-clicking on the image, go to Colors on the menu bar, select "Set Transparent Color" and click anywhere on the white background of the image

3. Do you have a table that does not fit into a single slide? Do not fret. You can divide the table across two slides and use a cool slide transition to let the audience know that the table is continuing over two slides. Select the first slide and Go to Transitions on the menu bar and select "Push" as the transition.

4. Need to fit the letters of a word onto a curve? Type your word. Select the text box. Go to Format on the menu bar. Select Text Effects --> Transform --> Follow Path and the needed layout.

5. You are projecting your slides and you suddenly need the audience to stop looking at the slides and focus on you the speaker. You can press "B" or "W" on the keyboard to produce a Black or White background respectively. This works only in Presentation mode.

What are theTypes of Triggers ?

2012-07-29T09:28:00.000-07:00

This section describes the different types of triggers:

Row Triggers

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

BEFORE and AFTER Triggers

When defining a trigger, you can specify the trigger timing--whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.

BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views.

Difference between Trigger and Procedure

Triggers	Stored Procedures
In trigger no need to execute manually. Triggers will be fired automatically. Triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table.	Where as in procedure we need to execute manually.

Differences between stored procedure and functions

Stored Procedure	Functions
Stored procedure may or may not return values.	Function should return at least one output parameter. Can return more than one parameter using OUT argument.
Stored procedure can be used to solve the business logic.	Function can be used to calculations
Stored procedure is a pre-compiled statement.	But function is not a pre-compiled statement.
Stored procedure accepts more than one argument.	Whereas function does not accept arguments.
Stored procedures are mainly used to process the tasks.	Functions are mainly used to compute values
Cannot be invoked from SQL statements. E.g. SELECT	Can be invoked form SQL statements e.g. SELECT
Can affect the state of database using commit.	Cannot affect the state of database.
Stored as a pseudo-code in database i.e. compiled form.	Parsed and compiled at runtime.

Explain about Triggers ?

2012-07-29T09:27:00.000-07:00

Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL

Type 3 Slowly Changing Dimension

2012-07-29T09:22:00.000-07:00

In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.

In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Christina	Illinois

To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:

Customer Key
Name
Original State
Current State
Effective Date

After Christina moved from Illinois to California, the original information gets updated, and we have the following table (assuming the effective date of change is January 15, 2003):

Customer Key	Name	Original State	Current State	Effective Date
1001	Christina	Illinois	California	15-JAN-2003

Advantages:

- This does not increase the size of the table, since new information is updated.

- This allows us to keep some part of history.

Disadvantages:

- Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Christina later moves to Texas on December 15, 2003, the California information will be lost.

Usage:

Type 3 is rarely used in actual practice.

When to use Type 3:

Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.

Type 2 Slowly Changing Dimension

2012-07-29T09:21:00.000-07:00

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The newe record gets its own primary key.

In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Christina	Illinois

After Christina moved from Illinois to California, we add the new information as a new row into the table:

Customer Key	Name	State
1001	Christina	Illinois
1005	Christina	California

Advantages:

- This allows us to accurately keep all historical information.

Disadvantages:

- This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.

- This necessarily complicates the ETL process.

Usage:

About 50% of the time.

When to use Type 2:

Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Type 1 Slowly Changing Dimension

2012-07-29T09:20:00.000-07:00

In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.

In our example, recall we originally have the following table:

Customer Key	Name	State
1001	Christina	Illinois

After Christina moved from Illinois to California, the new information replaces the new record, and we have the following table:

Customer Key	Name	State
1001	Christina	California

Advantages:

- This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.

Disadvantages:

- All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Christina lived in Illinois before.

- Usage:

About 50% of the time.

When to use Type 1:

Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.

Difference between Data cleansing and Data scrubbing ?

2012-07-28T10:08:00.000-07:00

Data cleansing is a process of removing errors and resolving inconsistencies in source data before loading data into targets.

Data scrubbing is a process of filtering, merging, decoding and translating the source data to create the validation data for data warehouse.

What does #! /bin/sh mean in a shell script?

2012-07-28T10:02:00.000-07:00

It actually tells the script to which interpreter to refer. As you know, bash shell has some specific functions that other shell does not have and vice-versa. Same way is for perl, python and other languages.

It's to tell your shell what shell to you in executing the following statements in your shell script.

Informatica Scheduler

2012-07-27T17:10:00.000-07:00

Informatica Scheduler for your Production Jobs. Check out the informatica scheduler options available in the workflow.

Retain Values from a previously processed records for future calculations

2012-07-27T09:17:00.000-07:00

There can be many scenarios where you need the previous record's data to do some calculation, while the current record is processed in an informatica mapping. For example you need the previous days closing balance to calculate the loss or gain while processing the current day's data. Here in this tutorial we will discuss some techniques to get the previous record values while we are processing current record in a mapping.

Here let's take the sample data, which we get from the Stock Exchange for the Daily closing price of the socks. In our mapping we need to build a logic to find the daily loss or gain on the stock and for this calculation we need the data from the previous day when we are processing the current day's data.

Lets see how we are going to do this in the mapping. Start the mapping creation and add an EXPRESSION transformation.

Note : If your data source is not in Sorted Order, add a SORTER Transformation to get the data sorted.

Add the Ports in the Expression.

V_GAIN_LOSS as Variable Port.
V_PRIOR_CLOSING_PRICE as Variable Port.
GAIN_LOSS as Output Port.

Note : Add the ports in the same order in the expression

Add the expression as follows.

V_GAIN_LOSS :- CLOSING_PRICE - V_PRIOR_CLOSING_PRICE
V_PRIOR_CLOSING_PRICE :- CLOSING_PRICE
GAIN_LOSS :- V_GAIN_LOSS

Now map the columns to the Target and we have the mapping as we have it in the below image.

We are all done... except build and run the workflow.

Key points behind this Mapping Logic

Now we have the mapping ready, lets see what is the key logic behind the mapping. This logic is build based on the way the ports are evaluated in the EXPRESSION transformation.

In an EXPRESSION Transformation.

Ports are evaluated in the order

INPUT
VARIABLE
OUTPUT

Ports are evaluated from Top to Button

Please look at the below chart to understand how the data is being processed through each ports for each row.

Try it Yourself.

The mapping we build right now can handle only file with single company stock prices, this logic breaks with multiple. Modify this mapping to handle files with multiple company stock price.

Informatica PowerCenter Client Tools Review

2012-07-27T09:11:00.000-07:00

The PowerCenter Client consists of applications that developers use to design mappings and mapplets, create sessions and workflows to load the data, and monitor workflow progress. In this article, you use the following applications and tools, which is used by developers during the development process.

PowerCenter Designer.
Workflow Manager.
Workflow Monitor.
Repository Manager.

PowerCenter Designer

The Designer has tools to help you build mappings and mapplets so you can specify how to move and transform data between sources and targets. The Designer helps you create source definitions, target definitions, and transformations to build the mappings. The Designer lets you work with multiple tools at one time and to work in multiple folders and repositories at the same time. It also includes windows so you can view folders, repository objects, and tasks. You can configure general Designer settings, such as background color and font. You can also configure specific tool settings for each Designer tool.

Designer Tools

The Designer provides the following tools:

Source Analyzer. Use to import or create source definitions for flat file, XML, COBOL, Application, and relational sources.
Target Designer. Use to import or create target definitions.
Transformation Developer. Use to create reusable transformations.
Mapplet Designer. Use to create mapplets.
Mapping Designer. Use to create mappings.

Designer Windows

The Designer consists of the following windows:

Navigator. Use to connect to and work in multiple repositories and folders. You can also copy and delete objects and create shortcuts using the Navigator.
Workspace. Use to view or edit sources, targets, mapplets, transformations, and mappings. You work with a single tool at a time in the workspace, which has two formats: default and workbook. You can view multiple versions of an object in the workspace.
Status bar. Displays the status of the operation you perform.
Output. Provides details when you perform certain tasks, such as saving work or validating a mapping.

Workflow 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 run 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. A worklet is similar to a workflow, but without scheduling information. You can run a batch of worklets inside a workflow.

After you create a workflow, you run the workflow in the Workflow Manager and monitor it in the Workflow Monitor.

Workflow Manager Tools

To create a workflow, you first create tasks such as a session, which contains the mapping you build in the Designer. You then connect tasks with conditional links to specify the order of execution for the tasks you created. The Workflow Manager consists of three tools to help you develop a workflow:

Task Developer. Use the Task Developer to create tasks you want to run in the workflow.
Workflow Designer. Use the Workflow Designer to create a workflow by connecting tasks with links. You can also create tasks in the Workflow Designer as you develop the workflow.
Worklet Designer. Use the Worklet Designer to create a worklet.

Workflow Manager Windows

The Workflow Manager displays the following windows to help you create and organize workflows:

Navigator. You can connect to and work in multiple repositories and folders. In the Navigator, the Workflow Manager displays a red icon over invalid objects.
Workspace. You can create, edit, and view tasks, workflows, and worklets.
Output. Provides details when you perform certain tasks, such as saving work or validating a mapping.

Workflow Monitor

You can monitor workflows and tasks in the Workflow Monitor. With the Workflow Monitor, you can view details about a workflow or task in Gantt Chart view or Task view. The Workflow Monitor displays workflows that have run at least once. You can run, stop, abort, and resume workflows from the Workflow Monitor. The Workflow Monitor continuously receives information from the Integration Service and Repository Service. It also fetches information from the repository to display historic information.

The Workflow Monitor consists of the following windows:

Navigator window. Displays monitored repositories, Integration Services, and repository objects.
Output window. Displays messages from the Integration Service and the Repository Service.
Properties window. Displays details about services, workflows, worklets, and tasks.
Time window. Displays progress of workflow runs.
Gantt Chart view. Displays details about workflow runs in chronological (Gantt Chart) format.
Task view. Displays details about workflow runs in a report format, organized by workflow run.

You can Toggle between Gantt Chart view and Task view by clicking the tabs on the bottom of the Workflow Monitor. You can also view and hide the Output and Properties windows in the Workflow Monitor.

Repository Manager

This is more of an object management or administrative tool, where the developers spent the least amount of time. You can navigate through multiple folders and repositories and perform basic repository tasks like moving mappings, sessions, workflows or any other objects between folders or repositories with the Repository Manager. Menu items in the Repository Manager are enabled or disabled according to the privileges and permissions you have.

The Repository Manager can display four main windows: the Navigator window, the Main window, the Dependency window, and the Output window.

Get top 5 records to target without using rank

2012-07-26T22:27:00.000-07:00

How to get top 5 records to target without using rank ?

Solution:

Drag the source to mapping and connect it to sorter transformation.

Arrange the salary in descending order in sorter as follows and send the record to expression.

Add the next value of sequence generator to expression.(start the value from 1 in sequence generator).

Connect the expression transformation to a filter or router. In the property set the condition as follows

Finally connect to the target.

Change your Target Flat File name Dynamically

2012-07-24T22:27:00.000-07:00

When ever you create a flat file using a PowerCenter Workflow, the name of the file will be static, which is specified in the Session 'Output Filename' property. There can be business cases where you need to generate flat files with dynamic file name, like add Timestamp with the file name . Here in this article we will discuss how we can generate flat files with dynamically changing name.

As the first step lets create the flat file definition using Target Designer

Now lets add one new column 'FileName' using the 'Add File Name Column' button which is highlighted at the top right corner as shown in the below image. Dont get confused... this column is not going to be in the target file, this is the column based on the file name is dynamically changed.

Next step will be; create the mapping to generate the flat file as shown below.

We wont worry about any transformation in the mapping except EXP_FILE_NAME Transformation. This expression transformation is responsible for generating the target file name dynamically.

Lets look at the Expression Transformation below.

Here in this transformation, we have to create an output port 'FileName' and give the expression value as 'Customer_Master_' || TO_CHAR(SYSDATE,'YYYY-MM-DD') || '.csv'. Using this expression we are dynamically attaching the Date along with the file name. You can customize this expression as your need to change the file name.

Now from the expression transformation, map the 'FileName' port to the target table and all the remaining ports.

We are all done... Now create and run the workflow; you will see the file is generated in your Target File directory with the date as part of the file name. There is not any specific setting required at the session level.

Error Handling made easy using informatica Row Error logging

2012-07-24T22:24:00.000-07:00

Every time we start with a Data Warehouse or Data Integration project we spent lot of time defining our error handling approach. Still there are good chances that we might miss some scenarios because of unexpected data issues. Here in this article, lets us discuss an approach to handle the unexpected error.

Error handling Configuration.

We do not have any error configuration required during the mapping development. So after the mapping is created, during the session configuration set the session properties as shown in below image.

Here is the details on what each property means.

Error Log Type :- Specifies the type of error log to create. It can be relational database or flat file.
Error Log DB Connection :- Database connection for a relational log.
Error Log Table Name Prefix :- Specifies the table name prefix for relational logs.
Log Row Data :- Specifies whether or not to log transformation row data.
Log Source Row Data :- Specifies whether or not to log source row data.
Data Column Delimiter :- Data will be delimited by the specified character in DB column.

With this configuration we specified, Informatica PowerCenter will create four different tables for error logging and the table details as below.

PMERR_DATA :- Stores data about a transformation row error and its corresponding source row.
PMERR_MSG :- Stores metadata about an error and the error message.
PMERR_SESS :- Stores metadata about the session.
PMERR_TRANS :- Stores metadata about the source and transformation ports, when error occurs.

With this configuration, we are done with the setting required to capture any error during the session execution. Now lets see how do we retrieve the data from the error log tables and report it to Business Users or IT Department.

Report the Error Data.

Now we have the error data stored in the error table, it is important to share the error data to the Business Users or to IT Department. Lets see how we can pull data from these tables.

We can pull the basic error report using the SQL. We can be more fancy with the SQL and get more information from the error tables.

select
sess.FOLDER_NAME as 'Folder Name',
sess.WORKFLOW_NAME as 'WorkFlow Name',
sess.TASK_INST_PATH as 'Session Name',
data.SOURCE_ROW_DATA as 'Source Data',
msg.ERROR_MSG as 'Error MSG'
from
ETL_PMERR_SESS sess
left outer join ETL_PMERR_DATA data
on data.WORKFLOW_RUN_ID = sess.WORKFLOW_RUN_ID and
data.SESS_INST_ID = sess.SESS_INST_ID
left outer join ETL_PMERR_MSG msg
on msg.WORKFLOW_RUN_ID = sess.WORKFLOW_RUN_ID and
msg.SESS_INST_ID = sess.SESS_INST_ID
where
sess.FOLDER_NAME = <Project Folder Name> and
sess.WORKFLOW_NAME = <Workflow Name> and
sess.TASK_INST_PATH = <Session Name> and
sess.SESS_START_TIME = <Session Run Time>

The above SQL can be converted to an Informatica mapping and can be scheduled to run after all your ETL jobs are completed to get a daily error report and the same can be scheduled for an email delivery to the data owners to take relevant action on the errors.

Pros and Cons of this Approach.

We should know the Pros and Cons of this approach before applying this to your project.

Pros.

Out of the box Solution Provided by Informatica.
Captures all the Error, Including the unthought error.
Less Coding and Testing efforts required by the development team.

Cons.

If an error occurs after an active transformation, Integration service will not be able to capture source data into the erro table.
In sessions with multiple non-pass through partitions, Integration service can not capture source data into error table.
Added overhead to the Session performance (Which is expected and acceptable).

Please leave us a connemt below, if you have any difficulties implementing this error handling approach. I will be more than happy to help you.

All DW Basics and Concepts

2012-07-24T20:49:00.000-07:00

All DW Basics and Concepts

Design Approach to update Bigger tables

2012-07-24T14:33:00.000-07:00

One of the issue we come across during the ETL design is "Update Large Tables". This is a very common ETL scenarion especially when you treat with large volume of data like loading an SCD Type 2 dimension table. Here in this article lets discuss an approach to update Larger tables using Informatica Mapping.

High level Design Approach.

Use Database JOIN to identify the records to be updated.
Insert the records into TEMP table, which is identified for UPDATE.
Use post session SQL to update the target table.

Design Assumption.

Source and Target tables are relational table.
Both source and target table is on the same schema.
Tables are accessible using a single database user.

Informatica Implementation.

For the demonstration purpose lets consider the Customer Dimension table load T_DIM_CUST, which has 100 M records. Each load we are expecting to update 100 K Records records in the Dimension table.

Lets start with the mapping building. As the first step, lets OUTER Join the source table CUST_STAGE and target table T_DIM_CUST. Use the SQL below as the SQL override in source qualifier.

SELECT
--Columns From Source Tables
CUST_STAGE.CUST_ID,
CUST_STAGE.CUST_NAME,
CUST_STAGE.ADDRESS1,
CUST_STAGE.ADDRESS2,
CUST_STAGE.CITY,
CUST_STAGE.STATE,
CUST_STAGE.ZIP,
...
--Columns from Target Tables.
--If any column has NULL value, record to be set as INSERT else UPDATE
T_DIM_CUST.CUST_ID,
T_DIM_CUST.AS_OF_START_DT,
T_DIM_CUST.AS_OF_END_DT
FROM CUST_STAGE
--Outer Join is Used
LEFT OUTER JOIN T_DIM_CUST
ON CUST_STAGE.CUST_ID = T_DIM_CUST.CUST_ID
AND T_DIM_CUST.AS_OF_END_DT = TO_DATE('12-31-4000','MM-DD-YYYY')

Now using a Router Transformation, route the records to INSERT/UPDATE path. Records identified as INSERT will be mapped to T_DIM_CUST and identified as UPDATE will be mapped to T_DIM_CUST_TEMP. Use T_DIM_CUST_CUST_ID, which is the column from the target table to identify the records to be inserted/updated. Below is the Router Group Filter Condition and you can see how the mapping looks like in the below image.

INSERT : IIF(ISNULL( T_DIM_CUST_CUST_ID ), TRUE, FALSE)

UPDATE : IIF(NOT ISNULL( T_DIM_CUST_CUST_ID ), TRUE, FALSE)

Now the mapping development is complete, during the session configuration process, add the below SQL as part of the Post session SQL statement as shown below. This correlated update SQL will update the records in T_DIM_CUST table with the values from T_DIM_CUST_TEMP.

UPDATE T_DIM_CUST SET
(T_DIM_CUST.AS_OF_END_DT,T_DIM_CUST.UPDATE_DT) =
(SELECT T_DIM_CUST_TEMP.AS_OF_END_DT,T_DIM_CUST_TEMP.UPDATE_DT
FROM T_DIM_CUST_TEMP
WHERE T_DIM_CUST_TEMP.CUST_ID = T_DIM_CUST.CUST_ID
AND T_DIM_CUST.AS_OF_END_DT = TO_DATE('12-31-4000','MM-DD-YYYY'))
WHERE EXISTS
(SELECT 1
FROM T_DIM_CUST_TEMP
WHERE T_DIM_CUST_TEMP.CUST_ID = T_DIM_CUST.CUST_ID
AND T_DIM_CUST.AS_OF_END_DT = TO_DATE('12-31-4000','MM-DD-YYYY'))

That is all we need... Hope you enjoyed this design technique. Please let us know if you have any difficulties in implementing this technique.

Databondsnew

Informatica Testing

What are the differences between stored procedures and triggers ?

Snapshot Facts

The differences between a logical data model and physical data model

Factless Facts

Conformed dimensions

What is Junk Dimension?

Informatica PowerCenter Repository tables

Optimizing Oracle Optimizer Statistics

Top 5 MS Power Point Tips

What are theTypes of Triggers ?

Explain about Triggers ?

Type 3 Slowly Changing Dimension

Type 2 Slowly Changing Dimension

Type 1 Slowly Changing Dimension

Difference between Data cleansing and Data scrubbing ?

What does #! /bin/sh mean in a shell script?

Informatica Scheduler

Retain Values from a previously processed records for future calculations

Key points behind this Mapping Logic

Try it Yourself.

Informatica PowerCenter Client Tools Review

PowerCenter Designer. Workflow Manager. Workflow Monitor. Repository Manager.

PowerCenter Designer

Designer Tools

Designer Windows

Workflow Manager

Workflow Manager Tools

Workflow Manager Windows

Workflow Monitor

Repository Manager

Get top 5 records to target without using rank

Change your Target Flat File name Dynamically

Error Handling made easy using informatica Row Error logging

Error handling Configuration.

Report the Error Data.

Pros and Cons of this Approach.

Pros.

Cons.

All DW Basics and Concepts

Design Approach to update Bigger tables

High level Design Approach.

Design Assumption.

Informatica Implementation.

PowerCenter Designer.
Workflow Manager.
Workflow Monitor.
Repository Manager.