Multiples rows to single row

Can Consider both source and target as flatfiles or any relational source/target

Source

emp_name,emp_id  

siva,101 

sundar,101 

kumar,101 

satya,102 

lokesh,102

narra,102

Target

EmpID,FullName

101,Siva Sundar Kumar

102,Satya Lokesh Narra

Please share your solutions to others as comments

Cheers

-Winkey

Transform each row in to levels based on the the hierarchy id column

Consider source and target as flat files

Source:

child#  parent# hier#

1       2               10

2       3               10

3       4               10

4                       10

1       2               20

2                       20

1       2              30

2       3              30

3                       30

Target:

Lvl1   Lvl2   Lvl3   Lvl4

4       3       2       1

2       1

3       2       1

Case 1

Here maximum we have 4 levels

Case 2

We don't know the number of level(Hint:- Try to use JAVA transformation to find the Maximum level), means a for each hierarchy number(heir#) we can have n numbers of levels

Please share your solutions to others as comments

Cheers

-Winkey

A Column having multiple values to separate rows

Here consider the source and target as flatfiles

Source

id area

1 123,456-234,245,000

2 456,123,567-123,090

3 586,789,567-123,234

Target

id|area

1|"123"

2|"456"

3|"586"

1|"456-234"

2|"123"

3|"567"

1|"245"

2|"567-123"

3|"789"

1|"000"

2|"090"

3|"967-423"

Please share you solutions to others as comments

Cheers

-Winkey

What is an Inline View

The concept of using subquery in the FROM clause of SELECT statement is called inline view

Example:-
 SELECT T1.Col1, T1.Col2, T2.Col1
FROM Table1 T1,(SELECT col1,AVG(col2) FROM Table1 GROUP BY Col2) T2
WHERE T1.Col1 = T2.Col1

Informatica server start and stops immedietly

Problem 1

      Check the configured HOST NAME and PORT for the repository Server

Error Log:

ERROR : REP_12400 [Mon Mar 14 23:17:07 2011] : (2224|4204) Repository Error ([REP_51024] Failed to connect to the repository. Either the server address (<none>) or the port number (5001) is invalid.)

ERROR : REP_12400 [Mon Mar 14 23:17:07 2011] : (2224|4204) Repository Error ([REP_51337] Unable to connect to the repository server on Host <none>, Port 5001.  Please check your configuration.)

ERROR : CMN_1006 [Mon Mar 14 23:17:07 2011] : (2224|4204) Failed to connect to repository [INFA_REPO].

FATAL ERROR : SF_34004 [Mon Mar 14 23:17:07 2011] : (2224|4204) Server initialization failed.

Problem 2

    Code page was not same for Actual Server and the Workflow manger server that we created 

Error Log:

FATAL ERROR : LM_36011 [Mon Mar 14 23:20:01 2011] : (4972|6204) Codepage mismatch. Server is running in codepage [MS Windows Latin 1 (ANSI), superset of Latin1] whereas the server is configured in the Workflow Manager to run in codepage [ISO 8859-1 Western European].

FATAL ERROR : SF_34004 [Mon Mar 14 23:20:01 2011] : (4972|6204) Server initialization failed.

How to troubleshoot the Problem 

        By enabling service logs

1.Goto Programs-->Informatica PowerCenter-->Informatica Server-->Informatica Server Setup

2.In the Server Tab select the radio button Output log to a file and fill the textbox with valid file path(Text file is fine) 

Problem 1 solution

1.Goto Programs-->Informatica PowerCenter-->Informatica Server-->Informatica Server Setup

2.In the Repository Tab update the HOST NAME (Ex:Venki-PC)

Success Log:

INFO : LM_36033 [Mon Mar 14 23:20:01 2011] : (4972|6204) Connected to repository [INFA_REPO] running on server:port [Venki-PC]:[5001] user [Administrator]

INFO : LM_36008 [Mon Mar 14 23:20:01 2011] : (4972|6204) Server name is [INFA_SERVER].

Problem 2 solution

1.Open Workflow manager and login

2.Connect to the INFA_SERVER

3.Right on INFA_SERVER and click on Edit

4.Change the codepage to the MS Windows Latin 1 (ANSI), superset of Latin1

Now restart Repository Server first and then start Informatica Server

Success Log:

INFO : SF_34082 [Mon Mar 14 23:35:10 2011] : (9100|5684) Server starting up.

INFO : LM_36005 [Mon Mar 14 23:35:10 2011] : (9100|5684) Platform validation successful.

INFO : CMN_1053 [Mon Mar 14 23:35:10 2011] : (9100|5684) PowerCenter

INFO : CMN_1053 [Mon Mar 14 23:35:10 2011] : (9100|5684) Copyright (c) 1998-2004 Informatica Corporation

INFO : LM_36009 [Mon Mar 14 23:35:10 2011] : (9100|5684) Informatica PowerCenter Server, version [7.1.1], build [0811], 32-bit.

INFO : LM_36063 [Mon Mar 14 23:35:10 2011] : (9100|5684) (Powered by ZL Engine)

INFO : LM_36069 [Mon Mar 14 23:35:10 2011] : (9100|5684) All Rights Reserved.

INFO : LM_36068 [Mon Mar 14 23:35:10 2011] : (9100|5684) This Software is protected by U.S. Patent Numbers 6,208,990; 6,044,374; 6,014,670; 6,032,158; 5,794,246; 6,339,775 and other U.S. Patents Pending.

INFO : SF_34058 [Mon Mar 14 23:35:10 2011] : (9100|2332) Service started.

INFO : SF_34002 [Mon Mar 14 23:35:10 2011] : (9100|2332) Server is initializing.

INFO : LM_36033 [Mon Mar 14 23:35:13 2011] : (9100|2332) Connected to repository [INFA_REPO] running on server:port [Venki-PC]:[5001] user [Administrator]

INFO : LM_36008 [Mon Mar 14 23:35:13 2011] : (9100|2332) Server name is [INFA_SERVER].

INFO : SF_34012 [Mon Mar 14 23:35:13 2011] : (9100|2332) Opened Server Port [4002] to listen for client connections.

INFO : CMN_1570 [Mon Mar 14 23:35:13 2011] : (9100|2332) Server Codepage: [MS Windows Latin 1 (ANSI), superset of Latin1]

INFO : CMN_1569 [Mon Mar 14 23:35:13 2011] : (9100|2332) Server Mode: [ASCII]

INFO : LM_36070 [Mon Mar 14 23:35:13 2011] : (9100|2332) The server is running on a host with 2 logical processors.

INFO : LM_36039 [Mon Mar 14 23:35:13 2011] : (9100|2332) The maximum number of sessions that can run simultaneously is [10].

INFO : CMN_1010 [Mon Mar 14 23:35:13 2011] : (9100|2332) Allocated system shared memory [id = 2700923] of [2000000] bytes for [Load Manager Shared Memory].

INFO : LM_36095 [Mon Mar 14 23:35:14 2011] : (9100|2332) Persistent session cache file cleanup is scheduled to run on [Tue Mar 15 23:35:14 2011].

INFO : SF_34003 [Mon Mar 14 23:35:14 2011] : (9100|2332) Server initialization completed.

Cheers

Winkey

unexpected condition at:icetss.hp:261 when tries to open designer of Informatica Powercenter 7.1.1 in windows 7

When tried to open designer after installing Informatica 7.1.1 in windows 7


warning:unexpected condition at:icetss.hp:261 
contact informatica technical support for asistence 
continuing may result in damage to your repository. 
either continue if informatica tech support advises


Solution 
     We have to install the a patch  EBF92770
Just drop your E-mail in the comments..so that I can send you the path


Comment if it helps you


Cheers
-Winkey

TOAD for oracle 11g 64 Bit Installation in Windows 7

While Installing oracle 11g R2 in windows 7 64 bit Operating system
better to go for Oracle 11g 32 bit only so that TOAD will support for 32bit
Oracle only

Note:While installing Oracle change the path from  Program files (86x) to Program files

Thanks & Cheers
-Winkey

How to Install Informatica 7.1.1 in Windows 7/Vista

Problems
    1.You will not find the Informatica Server& Repository Server if try to Install in windows Vista/7
Solution
  1.Right click on the power center setup & click on properties
  2.Go to compatability Tab
  3.Check windows XP compatability mode
  4.Apply & Exit
Now try to install you will find all the components(Client,Repository Server,Informatica Server,ODBC)

Plz comment if it useful for U

Thanks & Cheers
-Winkey

When trying to deploy a report Iam geting an error in SQL Server 2008 "The Specified targetServerURL is not valid. specify a valid URL for a Report Server in the Deployment settings"

Solution
1.Check whether reportServer is running or not
2.If not configured properly the Goto Start->Programs->SQLServer Configuration Tool-->ReportServer Configuration
  and configure all the required fields
3.If 1 and 2 are options  fine
4.RightClick on the ReportProject
5.Click Properties and fill the TargetServerURL with proper URL

Appreciate your comment if it helps your

Cheers
-Winkey

There is no data tab in report designer while using SQL Server 2008 R2

In SQL Server 2005 there is Data tab beside Design & Preivew Tabs
Where as in SQL Server 2008 you have do like this:

   View-->Report data or Alt+Ctrl+D

You might face this problem when you try to create a report and if you want drag and drop from the dataset

Plz comment if it helps you

Thanks & Cheers
Winkey