GS dot net

What is Python?

2022-09-18T22:27:00.001+05:30

Python was created by Guido van Rossum, and released in 1991.

Python is a popular programming language. It is an interpreted, interactive, object-oriented programming language. It incorporates modules, exceptions, dynamic typing, very high level dynamic data types, and classes. It supports multiple programming paradigms beyond object-oriented programming, such as procedural and functional programming. Python combines remarkable power with very clear syntax. It has interfaces to many system calls and libraries, as well as to various window systems, and is extensible in C or C++. It is also usable as an extension language for applications that need a programmable interface. Finally, Python is portable: it runs on many Unix variants including Linux and macOS, and on Windows

It is used for:

web development (server-side),
software development,
mathematics,
system scripting.

Convert number of month to name of month in Excel

2013-09-07T23:27:00.003+05:30

Lets assume Date in A1 cell then use the follwing formulas

=TEXT(A1,”MMMMM”)
for the first letter of the month
=TEXT(A1,”MMMM”)
for the month spelled out completely
=TEXT(A1,”MMM”)
for the 3 letter abbreviation of month

Informatica Scenario 1

2013-05-29T07:38:00.000+05:30

We have a source table as follows:

We have to move the into the target tables such that one

MY SOURCE

MY TARGET

SNo	Name
1	A
2	B
1	C
3	D
4	F
1	E
2	G

SNo	Name
1	A
2	B
1	C
3	D
4	F
1	E
2	G

After importing Source and Target, create a Sorter transformation to sort the data.
Then create an Expression transformation with 3 ports named say v_Name, v_SNo (2 variable ports) and o_Name (output port).
Give an expression as follows
v_Name -> IIF(SNo = v_SNo, v_Name||Name, Name)
v_SNO -> SNo
o_Name -> v_Name
Finally connect the ports to Target, create and execute the session and workflow.

How to Copy and Paste values without Formulas in Excel?

2012-12-12T12:09:00.004+05:30

In Microsoft Excel, when you perform a normal copy and paste on a cell that has been calculated by a formula, the formula gets pasted as well. Sometimes, when you copy the contents of a cell, you want to paste just the value and not the underlying formula that is displayed in the formula bar like copy the resulting value of a formula to a cell on another worksheet. You can get an invalid cell reference error (#REF!) error.

You can avoid this error by pasting the resulting values of formulas without the formula in destination cells.

Select the cells or ranges you wish to copy.
On the Home tab, under Clipboard, select Copy.
Select the cell you wish to paste your values to.
Click the lower half of the large Paste button; from the extended menu that appears, select Values. Then click OK.

Or use a Shortcut Menu

An even quicker way to paste values is by using a mouse shortcut:
Point to the border of the selected range, and the pointer should change to a four-headed arrow.
Press the right mouse button, and drag the cells slightly to the right.
Keep pressing the right mouse button, and drag the cells back to their original location.
Release the right mouse button and a shortcut menu will appear.
Click on Copy Here as Values Only.
The formulas are replaced by the values of their results.

How to skip Windows 7 login screen?

2012-12-08T20:16:00.005+05:30

If you have been running Windows 7 on your personal computer, chances are high that you haven’t assigned a password for your user account. Whenever you start your computer / laptop, the boot process will stop at the Login screen and you need to hit enter or click on your user account picture to see the desktop.

As you need to hit enter each time you start your your computer / laptop, here is quick solution to skip the Login screen.

Click on Start and then enter the following command in the search box, then "netplwiz" and press the ENTER key. Or Open Run dialog box by using Windows + R keyboard shortcut and type "control userpasswords2" in Run dialog box and hit enter. This command will load the Advanced User Accounts Control Panel applet.

Under User Accounts tab, uncheck the box named Users must enter a user name and password to use this computer.
Click Apply button to see Automatically Log On dialog box. If you have a password for your user account, simply enter the password and click ok. If you don’t have a password for your account, simply click Ok.
You are done. Reboot your Windows 7 to see the change.

How to calculate person's age in Excel

2012-11-17T14:55:00.006+05:30

There are many ways to calculate a person's age in Excel.

To calculate the age of a person on date since their birthdate:

=INT((date-birthdate)/365.25)

To figure their age as of today, date can be replaced with a function like TODAY() or NOW(). The INT() function is used to return only the number of complete years.

Let's say suppose Cell A1 contains "13-11-1984", then you can find person's age by enterring the following formula as

=INT((A1-birthdate)/365.25)

To calculate age in terms of years, months, and days, DATEDIF is a handy function. The following formula is similar to the one above.

=DATEDIF(A1,TODAY(),"Y") & " years, " & DATEDIF(A1,TODAY(),"YM") & " months, " & DATEDIF(A1,TODAY(),"MD") & " days"

Convert the case of text (Upper, Lower or Proper) in Excel

2012-11-13T17:18:00.003+05:30

If you want to convert text from uppercase to lowercase or from lowercase to proper case to make it more readable. To change the case of text, you can use the UPPER, LOWER, or PROPER functions as shown below:

Let say cell A1 contains "suneel gudipati"
To convert the text into upper case enter the formula =UPPER(A1)
It changes text to all Upper case as "SUNEEL GUDIPATI"
Similarly to convert into lower case enter the formula =LOWER(A1)
It changes text to all lowercase as "suneel gudipati"
Similarly to convert into Title case enter the formula =PROPER(A1)
It changes text to all lowercase as "Suneel Gudipati"

How to extract Year, Month, or Day from the Date in Excel?

2012-11-05T08:30:00.002+05:30

If you need to use the year, month or day referred to in a date cell in a separate cell follow the step:

Lets say that cell A1 contains the date November 13, 2007 (entered as 13/11/2007 or 11/13/2007 depending on your date system).
Now you want to extract the year i.e., 2007 from the date you entered. Simply enter the formula =YEAR(A1).
Similarly you can extract the month and day from the date using the formulas =MONTH(A1) and =DAY(A1).

Working with Headers and Footers in Excel

2012-11-05T07:27:00.002+05:30

In Excel, you can add or change headers or footers to provide useful information in worksheet printouts. For example, you can add predefined header and footer information or insert elements in the report, such as page numbers, print dates, filenames, etc.,. Headers print on the top of each page. Footers print on the bottom of each page. To define where in the printout the headers or footers should appear and how they should be scaled and aligned, you can choose from several header and footer options. These are different from column headings, which you can print at the top of each page.

For the Excel Spreadsheets, you can work with headers and footers in Page Layout view, or use the Page Setup dialog box to specify the same headers or footers for more than one worksheet at the same time. For other sheet types, such as chart sheets, or for embedded charts, you can only work with headers and footers in the Page Setup dialog box.

Microsoft Office Excel offers the most commonly used headers as presets that you can choose from a dropdown list. Select File, Page Setup, Header/Footer, click on the Header or theFooter dropdown and choose one of the preset options. The preset options can be placed either on the top of the report, on the bottom of the report, or both.

You also have the option to create your own custom headers and footers. Select File, Page Setup, Header/Footer, click on the Custom Header or the Custom Footerbuttons. Decide if you want your header or footer on the left, right, or centered. Click in the appropriate box and type in what you want printed as part of your header or footer.

Follow the steps to add/change the Headers and Footers:

Click the worksheet to which you want to add headers or footers (or that contains headers or footers that you want to change).
Click on the Insert tab then click on Header & Footer in the Text group.
Do one of the following:

To add a header or footer, click the left, center, or right header or footer text box at the top or the bottom of the worksheet page.
To change a header or footer, click the header or footer text box at the top or the bottom of the worksheet page respectively, and then select the text that you want to change.

Type the new header or footer text.

Tip: If you work with headers and footers in Page Layout view, you can close the headers and footers by returning to Normal view.

How to show the Day of the week in Excel?

2012-11-04T06:24:00.001+05:30

Sometimes you may be using dates in your worksheet and want the cell to show the day of the week like Sunday, Monday, etc., In such cases follow the step:

Select the cell containing the date you want displayed as a weekday.
Right click on the cell and select Format Cells and click on the Number tab.
In the Category box select Custom from the list.
Type dddd in the Type field on the right side of the dialog box
Select OK.

You can use this technique to display the day of the week in four different formats:

digit number (d),
digit number (dd),
digit text (ddd),
full-word text (dddd).

Sequence Generator transformation:

2012-10-14T19:42:00.000+05:30

Sequence Generator transformation is Passive and Connected Transformation.
The Sequence Generator transformation generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
We use it to generate Surrogate Key in Data Ware House environment mostly. When we want to maintain history, then we need a key other than Primary Key to uniquely identify the record. So we create a Sequence 1,2,3,4 and so on. We use this sequence as the key.

For example: If EMPNO is the key, we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key and not EMPNO.

Sequence Generator Ports:

The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.
We cannot edit or delete these ports.
Likewise, we cannot add ports to the transformation.
NEXTVAL: Use the NEXTVAL port to generate sequence numbers by connecting it to a transformation or target.

For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.

CURRVAL: CURRVAL is NEXTVAL plus the Increment By value.

We typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation.

If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
When we connect the CURRVAL port in a Sequence Generator transformation, the Integration Service processes one row in each block. We can optimize performance by connecting only the NEXTVAL port in a mapping.

Points to remember:

If Current value is 1 and end value 10, no cycle option. There are 17 records in source. In this case session will fail.
If we connect just CURR_VAL only, the value will be same for all records.
If Current value is 1 and end value 10, cycle option there. Start value is 0. There are 17 records in source. Sequence: 1 2 – 10. 0 1 2 3 –
To make above sequence as 1-10 1-20, give Start Value as 1. Start value is used along with Cycle option only.
If Current value is 1 and end value 10, cycle option there. Start value is 1. There are 17 records in source. Session runs. 1-10 1-7. 7 will be saved in repository. If we run session again, sequence will start from 8.
Use reset option if you want to start sequence from CURR_VAL every time.

Stored Procedure transformation

2012-10-07T20:24:00.000+05:30

Stored Procedure transformation is a passive transformation. Stored Procedure transformation is a Connected and Unconnected Transformation
Stored procedures are stored and run within the database.
A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

Use of Stored Procedure in mapping:

Check the status of a target database before loading data into it.
Determine if enough space exists in a database.
Perform a specialized calculation.
Drop and recreate indexes. Mostly used for this in projects.

Data Passes Between IS and Stored Procedure

One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure. There are three types of data that pass between the Integration Service and the stored procedure:
Input/output parameters: Parameters we give as input and the parameters returned from Stored Procedure.
Return values: Value returned by Stored Procedure if any.
Status codes: Status codes provide error handling for the IS during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. We cannot see this value. The IS uses it to determine whether to continue running the session or stop.

Specifying when the Stored Procedure Runs

Normal: The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. We pass some input to procedure and it returns some calculated values. Connected stored procedures run only in normal mode.
Pre-load of the Source: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.
Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.
Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for dropping indexes or disabling constraints.
Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

Using a Stored Procedure in a Mapping

Create the stored procedure in the database.
Import or create the Stored Procedure transformation.
Determine whether to use the transformation as connected or unconnected.
If connected, map the appropriate input and output ports.
If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
Configure the session.

Findind sum of the salary

2012-07-15T20:41:00.000+05:30

Drag source and target tables into Mapping Designer.
Design the mapping as shown below.

Connect all ports from Source Qualifier transformation to Expression transformation.
Create an output variable in Expression transformation as shown below:

o_Sum_Sal = CUME(sal)

Connect all ports fromm Expression transformation to target.

What is DNS (Domain Name System)?

2012-07-09T09:58:00.000+05:30

DNS stands for Domain Name System (or Service or Server), an Internet service that translates domain names into IP addresses. Because domain names are alphabetic, they're easier to remember. So, each time you browse a Web site or you send an e-mail, you’re using a domain name.

The DNS automatically converts the names we type in our Web browser address bar to the IP addresses of Web servers hosting those sites.

DNS implements a distributed database to store this name and address information for all public hosts on the Internet. DNS assumes IP addresses do not change (are statically assigned rather than dynamically assigned). Every time you use a domain name, therefore, a DNS service must translate the name into the corresponding IP address. For example, the domain name www.mytraveltalk.com might translate to 196.105.22.4.

The DNS database resides on a hierarchy of special database servers. When clients like Web browsers issue requests involving Internet host names, a piece of software called the DNS resolver (usually built into the network operating system) first contacts a DNS server to determine the server's IP address. If the DNS server does not contain the needed mapping, it will in turn forward the request to a different DNS server at the next higher level in the hierarchy. After potentially several forwarding and delegation messages are sent within the DNS hierarchy, the IP address for the given host eventually arrives at the resolver, that in turn completes the request over Internet Protocol.

Simply, the DNS system is, in fact, its own network. If one DNS server doesn't know how to translate a particular domain name, it asks another one, and so on, until the correct IP address is returned.

Generating sequence numbers without using Sequence Generator transformation

2012-07-08T04:15:00.000+05:30

Create a target table with a field name SEQ_NO for sequence number.
Design the Mapping as shown above.
In the Expression transformation create a variable port and increment it by 1.

V_Count=V_Count+1

In the Expression transformation create a output port and assign variable port to it.

O_Count=V_Count

Connect O_Count to SEQ_NO.

July 9 could be 'Internet doomsday' for some (so check your PC or Mac)

2012-07-08T04:10:00.000+05:30

July 9 might be "Internet doomsday" for PC and Mac users who haven't taken steps to make sure their systems are not infected with what's being called DNSChanger malware.

The warnings about the Internet problem have been splashed across Facebook and Google. Internet service providers have sent notices, and the FBI set up a special website.

Hundreds of thousands may lose Internet in July.

But people around the world may still lose their Internet service unless they do a quick check of their computers for malware that could have taken over their machines more than a year ago.

Despite repeated alerts, the number of computers that probably are infected is more than 277,000 worldwide, down from about 360,000 in April.

Users whose computers are still infected Monday will lose their ability to go online, and they will have to call their service providers for help deleting the malware and reconnecting to the Internet.

The problem began when international hackers ran an online advertising scam to take control of more than 570,000 infected computers around the world. When the FBI went in to take down the hackers late last year, agents realized that if they turned off the malicious servers being used to control the computers, all the victims would lose their Internet service.

In a highly unusual move, the FBI set up a safety net. They brought in a private company to install two clean Internet servers to take over for the malicious servers so that people would not suddenly lose their Internet.

And while it was the first time they'd done something like that, FBI officials acknowledged that it may not be the last, since authorities are taking on more of these types of investigations.

The temporary Internet system they set up, however, will be shut down at 12:01 a.m. EDT Monday, July 9.

Most victims don't even know their computers have been infected, although the malicious software probably has slowed their Web surfing and disabled their antivirus software, making their machines more vulnerable to other problems.

Popular social networking sites and Internet providers have gotten more involved, reaching out to computer users to warn of the problem.

Both Facebook and Google created their own warning messages that showed up if someone using either site appeared to have an infected computer. Facebook users would get a message that says, "Your computer or network might be infected," along with a link that users can click for more information.

Google users got a similar message, displayed at the top of a Google search results page. It also provides information on correcting the problem.

To check whether a computer is infected, users can visit a website run by the group brought in by the FBI: http://www.dcwg.org.

The site includes links to respected commercial sites that will run a quick check on the computer, and it also lays out detailed instructions if users want to actually check the computer themselves.

How To Burn an ISO Image File to a CD, DVD?

2012-07-02T08:49:00.000+05:30

An ISO file is an image of a CD, DVD, or BD so to use it, you first need to burn it to a disc. You must have a CD or DVD or BD burner to write ISO files to a disc in the respective format. You will not be able to burn ISO files if you only have a standard CD, DVD, or BD drive.

Follow the steps below to burn an ISO image file to a disc.

Download Free ISO Burner, a completely free program that only burns ISO files, making it really easy to use. For a free ISO Burner software follow this link http://www.freeisoburner.com
Free ISO Burner is a standalone program, meaning it doesn't install, it just runs. So double-click on the FreeISOBurner file that you just downloaded. The Free ISO Burner program will start.
Insert a blank disc in your drive. As long as your optical drive supports it, this disc can be a CD, DVD, or BD since Free ISO Burner supports them all.
Click the Open button next to the empty space within the ISO File section, near the top of the program window. When the Open window appears, locate and select the ISO file you wish to burn to the empty disc.
Once you've selected the ISO file, click the Open button at the bottom of the window to confirm your selection.
If you have more than one optical drive, you may have more than one option to choose here.
Skip the customizations in the Options area unless you know what you're doing.
Click the Burn button to start the ISO file burn.
Depending on how large the ISO file is, and how fast your disc burner is, the ISO burning process could take anywhere from several seconds, to several minutes, to complete.
When the burning is complete, the disc will automatically eject from the drive. You then can remove the disc and close Free ISO Burner.
Now you can use the ISO-file-turned-disc for whatever you needed it for.

Understanding file sizes (Bits, Bytes, KB, MB, GB, TB)

2012-06-24T20:58:00.000+05:30

Have you ever been confused about the number of kilobytes in a megabyte, or in a gigabyte or vice-versa?

These terms are those used most commonly and is the binary system, and you can be comfortable using it. The binary system is a base 2 system in which a bit is a binary digit, the smallest increment of data on a computer. A bit can hold only one of two values: 0 or 1. Put another way, a bit is either an 'on' or an 'off' which is processed by a computer processor, we represent 'on' as '1' and 'off' as '0'. 8 bits are known as a byte, and it is bytes which are used to pass our information in it's basic form - characters.

An alphanumeric character (e.g. a letter or number such as 'A', 'B' or '9') is stored as 1 byte. For example, to store the letter 'R' uses 1 byte, which is stored by the computer as 8 bits, '01010010'. Note that many non-alphanumeric characters such as symbols and foreign language characters use multiple bytes

A kilobyte (KB) is 1,024 bytes, not one thousand bytes as might be expected, because computers use binary (base 2) math, instead of a decimal (base 10) system.

Computer storage and memory is often measured in megabytes (MB) and gigabytes (GB). Where 1 MB is 1,024 kilobytes, or 1,048,576 (1024x1024) bytes.

Similarly, one 1 GB is 1,024 MB, or 1,073,741,824 (1024x1024x1024) bytes. A terabyte (TB) is 1,024 GB; 1 TB is about the same amount of information as all of the books in a large library, or roughly 1,610 CDs worth of data. A petabyte (PB) is 1,024 TB. Indiana University is now building storage systems capable of holding petabytes of data. An exabyte (EB) is 1,024 PB. A zettabyte (ZB) is 1,024 EB. Finally, a yottabyte (YB) is 1,024 ZB.

So in computer jargon, the following units are used:

UNIT	EQUIVALENT
1 kilobyte (KB)	1,024 bytes
1 megabyte (MB)	1,048,576 bytes
1 gigabyte (GB)	1,073,741,824 bytes
1 terabyte (TB)	1,099,511,627,776 bytes
1 petabyte (PB)	1,125,899,906,842,624 bytes

Since your computer uses a binary system as mentioned above, you may notice a discrepancy between your hard drive's published capacity and the capacity acknowledged by your computer. For example, a hard drive that is said to contain 10 GB of storage space using a decimal system is actually capable of storing 10,000,000,000 bytes. However, in a binary system, 10 GB is 10,737,418,240 bytes. As a result, instead of acknowledging 10 GB, your computer will acknowledge 9.31 GB. This is not a malfunction but a matter of different definitions.

What is InPrivate Browsing in Internet Explorer?

2012-06-23T21:48:00.000+05:30

A feature of the Internet Explorer 8 Web browser that lets users surf the Web without browsing history, temporary Internet files, form data, cookies, and usernames and passwords being retained by the browser. This is a more private browsing option that, according to Microsoft, leaves no evidence of your browsing or search history.

To open an InPrivate Browsing window, you can either use the Ctrl+Shift+P shortcut key or opening a new tab and selecting Browse with InPrivate or selecting it from the Safety button on the top right corner of the browser window. Internet Explorer 8 will launch a new browser session that won't record any information. To end your InPrivate Browsing session, simply close the browser window.

You can create shortcut for Internet Explorer with InPrivate Browsing in just a few easy steps.

Right click on the Desktop and select New and then Shortcut
You may have 32-bit or 64-bit Windows 7 thus type the following text in the text box of Create Shortcut window :
For 32-bit windows:

"%ProgramFiles%\Internet Explorer\iexplore.exe" –private

For 64-bit windows:

"%ProgramFiles(x86)%\Internet Explorer\iexplore.exe" –private

Click Next button
Type Internet Explorer (InPrivate Browsing) or anything of your choice as the name of the shortcut.
You will then notice that a shortcut is formed on the desktop with the name of your choice.

NOTE:

While you are surfing the web using InPrivate Browsing, Internet Explorer stores some information such as cookies and temporary Internet files - so the webpages you visit will work correctly. However, at the end of your InPrivate Browsing session, this information is discarded.

TIP:

The InPrivate browsing is useful for is checking your email on somebody else’s computer – no danger of cookies lying around letting them back into your email account over the net through Internet Explorer. Thus anyone else who accesses your computer will not be able to know about your activities over the internet.

Finding Maximum value in the column

2012-05-28T19:09:00.000+05:30

Description:
My source and target tables as shown below:

MY SOURCE

MY TARGET

Col_1	Col_2	Col_3
A	0	0
0	B	0
0	0	C

Col_1	Col_2	Col_3
A	B	C

Solution:

Drag source and taget tables in mapping
Create an Aggregator t/f and connect all ports from Source Qualifier t/f to Aggregator.
In Aggregator t/f, create 3 output ports as shown below:

out_Col1=MAX (Col1)
out_Col2 = MAX (Col2)
out_Col3 =MAX (Col3)

Connect all output ports to target table.

The mapping pipeline as shown above.

Stored Procedures

2012-05-24T19:48:00.000+05:30

Stored procedures provide a powerful way to code application logic that can be stored on the server. Stored procedures reduce network traffic, improve performance, and improving productivity (statements in a stored procedure only need to be written one time). Additionally, stored procedures can be used to help ensure the integrity of the database (information is entered in a consistent manner).

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Join the data of two tables which does not have common columns

2012-05-24T00:23:00.000+05:30

Description:

Lets assume I have two source files as shown below:

SOURCE 1

SOURCE 2

EmpNo	Name
100	Sivan
200	Uday
300	Chandu
400	Satish

Sal	Location
2000	Noida
3000	Banglore
1800	Hyderabad
1500	Chennai

I would like to load the target a shown below:

SNo	Name	Sal	Location
100	Sivan	2000	Noida
200	Uday	3000	Banglore
300	Chandu	1800	Hyderabad
400	Satish	1500	Chennai

Solution:

Drag Source1, Source2, Target tables.
Create a Expression t/f and connect SQ_Sorce1 to Expression t/f.
Create a variable port say v_Dummy and assign default value as 1.
Create a Expression t/f and connect SQ_Sorce2 to Expression t/f.
Create a variable port say v_Dummy (with same name and data type used in previous pipeline) and assign default value as 1.
Join two piplines to a Joiner t/f. The Joinner condition v_dummy=v_dummy.
Connect all ports to the target.

Update Strategy transformation

2012-05-17T16:17:00.000+05:30

Update Strategy transformation Active and Connected Transformation. The Update Strategy transformation to update, delete or reject rows coming from source based on some condition.

For example; if Address of a CUSTOMER changes, we can update the old address or keep both old and new address. One row is for old and one for new. This way we maintain the historical data.

Update Strategy is used with Lookup Transformation. In Data warehouse, we create a Lookup on target table to determine whether a row already exists or not. Then we insert, update, delete or reject the source record as per business need.

In PowerCenter, we set the update strategy at two different levels:

Within a session
Within a Mapping

Update Strategy within a session:

When we configure a session, we can instruct the IS to either treat all rows in the same way or use instructions coded into the session mapping to flag rows for different database operations.

Session Configuration:

Edit Session -> Properties -> Treat Source Rows as: (Insert, Update, Delete, and Data Driven). Insert is default.

Specifying Operations for Individual Target Tables:

You can set the following update strategy options:

Insert: Select this option to insert a row into a target table.
Delete: Select this option to delete a row from a table.
Update: We have the following options in this situation:

Update as Update. Update each row flagged for update if it exists in the target table.
Update as Insert. Inset each row flagged for update.
Update else Insert. Update the row if it exists. Otherwise, insert it.

Truncate: Select this option to truncate the target table before loading data.

Flagging Rows within a Mapping:

Within a mapping, we use the Update Strategy transformation to flag rows for insert, delete, update, or reject.

Operation	Constant	Numeric Value
INSERT	DD_INSERT	0
INSERT	DD_INSERT	0
UPDATE	DD_UPDATE	1
DELETE	DD_DELETE	2
REJECT	DD_REJECT	3

Update Strategy Expressions:

Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition. You can write these expression in Properties Tab of Update Strategy transformation.

IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )

IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )

Note: We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them. To do, see the Properties Tab for the Option.

Interview questions on Lookup transformatioin?

2012-05-15T22:53:00.000+05:30

Q. What is a lookup transformation?

Ans: The Lookup transformation is passive transformation. Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.

There are two types of Lookups in Powercenter Designer, namely:

Connected Lookup
Unconnected Lookup .

Q. What are the tasks of a lookup transformation?

Ans: We can use the Lookup transformation to perform following:

Get a related value: EMP has DEPTNO but DNAME is not there. We use Lookup to get DNAME from DEPT table based on Lookup Condition.
Perform a calculation: We want only those Employees who’s SAL > Average (SAL). We will write Lookup Override query.
Update slowly changing dimension tables: Most important use. We can use a Lookup transformation to determine whether rows already exist in the target.

Q. How do you configure a lookup transformation?

Ans: Configure the lookup transformation to perform the following types of lookups:

Relational or flat file lookup
Pipeline lookup
Connected or unconnected lookup
Cached or uncached lookup

Q. What is a pipeline lookup transformation?

Ans: A pipeline lookup transformation is used to perform lookup on application sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source qualifier as the lookups source.

Q. What is connected and unconnected lookup transformation?

Ans: A connected lookup transformation is connected the transformations in the mapping pipeline. It receives source data, performs a lookup and returns data to the pipeline.

An unconnected Lookup transformation is separate from the pipeline in the mapping. We write an expression using the :LKP reference qualifier to call the lookup within another transformation. Unconnected lookup does not participate in mapping data flow. In fact it is not connected to the other transformation so Informatica server creates a separate cache for unconnected and processing takes place in parallel. That’s why performance increases.

The major advantage of unconnected lookup is its reusability. We can call an unconnected lookup multiple times in the mapping unlike connected lookup.

Q. What are the differences between connected and unconnected lookup transformation?

Connected lookup transformation receives input values directly from the pipeline. Unconnected lookup transformation receives input values from the result of a :LKP expression in another transformation.
Connected lookup transformation can be configured as dynamic or static cache. Unconnected lookup transformation can be configured only as static cache.
Connected lookup transformation can return multiple columns from the same row or insert into the dynamic lookup cache. Unconnected lookup transformation can return one column from each row.
If there is no match for the lookup condition, connected lookup transformation returns default value for all output ports. If you configure dynamic caching, the Integration Service inserts rows into the cache or leaves it unchanged. If there is no match for the lookup condition, the unconnected lookup transformation returns null.
In a connected lookup transformation, the cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports. In an unconnected lookup transformation, the cache includes all lookup/output ports in the lookup condition and the lookup/return port.
Connected lookup transformation passes multiple output values to another transformation. Unconnected lookup transformation passes one output value to another transformation.
Connected lookup transformation supports user-defined values. Unconnected lookup transformation does not support user-defined default values.

Q. How do you handle multiple matches in lookup transformation? or what is "Lookup Policy on Multiple Match"?

Ans: "Lookup Policy on Multiple Match" option is used to determine which rows that the lookup transformation returns when it finds multiple rows that match the lookup condition. You can select lookup to return first or last row or any matching row or to report an error.

Q. What is "Output Old Value on Update"?

Ans: This option is used when dynamic cache is enabled. When this option is enabled, the integration service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a new row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.

Q. What is "Insert Else Update" and "Update Else Insert"?

Ans: These options are used when dynamic cache is enabled.

Insert Else Update option applies to rows entering the lookup transformation with the row type of insert. When this option is enabled the integration service inserts new rows in the cache and updates existing rows when disabled, the Integration Service does not update existing rows.
Update Else Insert option applies to rows entering the lookup transformation with the row type of update. When this option is enabled, the Integration Service updates existing rows, and inserts a new row if it is new. When disabled, the Integration Service does not insert new rows.

Q. What are the options available to configure a lookup cache?

Ans: The following options can be used to configure a lookup cache:

Static cache
Dynamic cache
Persistent cache
Recache
Shared Cache

Q. What is a cached lookup transformation and uncached lookup transformation?

Ans:

Cached lookup transformation: The Integration Service builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service stores condition values in the index cache and output values in the data cache. The Integration Service queries the cache for each row that enters the transformation.
Uncached lookup transformation: For each row that enters the lookup transformation, the Integration Service queries the lookup source and returns a value. The integration service does not build a cache.

Q. How the integration service builds the caches for connected lookup transformation?

Ans: The Integration Service builds the lookup caches for connected lookup transformation in the following ways:

Sequential cache: The Integration Service builds lookup caches sequentially. The Integration Service builds the cache in memory when it processes the first row of the data in a cached lookup transformation.
Concurrent caches: The Integration Service builds lookup caches concurrently. It does not need to wait for data to reach the Lookup transformation.

Q. How the integration service builds the caches for unconnected lookup transformation?

Ans: The Integration Service builds caches for unconnected Lookup transformations as sequentially.

Q. What is a dynamic cache?

Ans: To cache a target table or flat file source and insert new rows or update existing rows in the cache, use a Lookup transformation with a dynamic cache. The IS dynamically inserts or updates data in the lookup cache and passes data to the target. Target table is also our lookup table. No good for performance if table is huge.

Q. When you use a dynamic cache, do you need to associate each lookup port with the input port?

Ans: Yes. You need to associate each lookup/output port with the input/output port or a sequence ID. The Integration Service uses the data in the associated port to insert or update rows in the lookup cache.

Q. What are the different values returned by NewLookupRow port?

Ans: The different values are

0 - Integration Service does not update or insert the row in the cache.
1 - Integration Service inserts the row into the cache.
2 - Integration Service updates the row in the cache.

Q. What is a persistent cache?

Ans: If the lookup table does not change between sessions, we can configure the Lookup transformation to use a persistent lookup cache. The IS saves and reuses cache files from session to session, eliminating the time required to read the lookup table.

Q. What is Recache?

Ans: If the persistent cache is not synchronized with the lookup table, we can configure the Lookup transformation to rebuild the lookup cache. If Lookup table has changed, we can use this to rebuild the lookup cache.

Q. What is a shared cache?

Ans: You can configure multiple Lookup transformations in a mapping to share a single lookup cache. The Integration Service builds the cache when it processes the first Lookup transformation. It uses the same cache to perform lookups for subsequent Lookup transformations that share the cache. There are two types of Shared Cache. They are...

Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the IS shares the cache by default. You can only share static unnamed caches.
Named cache: Use a persistent named cache when we want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

Q. How do you improve the performance of lookup transformation?

Ans:

Create an index on the columns used in the lookup condition
Place conditions with equality operator first
Cache small lookup tables.
Join tables in the database: If the source and the lookup table are in the same database, join the tables in the database rather than using a lookup transformation.
Use persistent cache for static lookups.
Avoid ORDER BY on all columns in the lookup source. Specify explicitly the ORDER By clause on the required columns.
For flat file lookups, provide Sorted files as lookup source.

How to return muliple values using Unconnected lookup?

2012-05-12T12:06:00.000+05:30

Today we discuss how to return muliple values using Unconnected lookup. First of all, the UnConnected lookup has one return port and returns one column from each row. If the interviewer asked the above scenario, follow the below solution.

Let us assume EMP will be source table and DEPT will be LOOKUP table.
Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
Go to the Mapping Designer and create the mapping with source, target, lookup and expression transformations as shown below:

Then concatnate multiple ports with the separator '&' into single port in Lookup transformation.

To do edit Lookup transformation and create a return port (DNAME).
Then go to Properties tab, there write the following expression in Lookup SQL Over.

SELECT dname||'&'||loc FROM dept

In expression transformation spilt it into multiple ports as follow.
Declare 2 output variables with

o_DName = SUBSTR (dname, 1, (INSTR(dname, '&', 1))
o_Loc = SUBSTR(dname, INSTR (dname, '&'))

Connect all ports from Expression transformation to target table.