8 Replies Latest reply on Nov 22, 2017 1:26 AM by Nico Heinze

    Connect By Root In Informatica implimentation ?

    chaitanya mannem Active Member

      How to implement connect by root in informatica with out sql over ride.

        • 1. Re: Connect By Root In Informatica implimentation ?
          Nico Heinze Guru

          Could you please explain with a small example? I don't know this CONNECT BY ROOT clause.

           

          In general there are ways to implement recursion in PowerCenter, though - depending on your setup - these may become a little tricky. You can find, for example, a Velocity article about Best Practice SQL Override where the last use case covers resolving recursive data structures.

          Be warned that this article is LONG (20 pages) and this one use case alone takes 10 pages for all the necessary explanations.

           

          Regards,

          Nico

          • 2. Re: Connect By Root In Informatica implimentation ?
            chaitanya mannem Active Member

            Hi Nico,

             

            Connect by root gives hierarchy level data.

            My requirement is I have source column ID need to pass this as input into table to get Latest ID.

            Lets say I/p is ID =A in table latest value for A could be C we get that based on other column from table seq as below. my o/p should be 'C'.this is my req.

             

            ID SEQ
            A  1
            B  2

             

            Thanks,

            Chaitanya

            • 3. Re: Connect By Root In Informatica implimentation ?
              chaitanya mannem Active Member

              Hi Nico,

               

               

               

              Connect by root gives hierarchy level data.

               

              My requirement is I have source column ID need to pass this as input into table to get Latest ID.

               

              Lets say I/p is ID =A in table latest value for A could be C we get that based on other column from table seq as below. my o/p should be 'C'.this is my req.

               

               

               

              ID SEQ

              A  1

              B  2

              C

               

               

               

              Thanks,

               

              Chaitanya

              • 4. Re: Connect By Root In Informatica implimentation ?
                Nico Heinze Guru

                Typical use case for the Loop Transformations that I've developed a while ago for PowerCenter.

                Please take a look here:    Loop Transformations for PowerCenter – preliminary version

                 

                The only disadvantage (a solution will hopefully be provided next week or so) is that this version only runs on Windows; under Unix/Linux the Java Virtual Machine works a bit different than on Windows, and one of those subtle differences require a little rework.

                But for Windows you can use this technique to resolve hierarchies in the data.

                Take a look at the sample workflows delivered as part of the package; the use case with the flat file lookup will provide you with an example how your case can be remedied easily.

                 

                If you don't want (or are not allowed) to use the Loop Transformations, the last Use Case in the following Velocity article will help:

                https://velocity.informatica.com/index.php/best-practices-all/149-development-techniques/639-guidelines-sql-overrides

                Please note that the explanations are LONG, the whole article takes app. 20 pages. Don't print it all if you can avoid it.

                 

                Regards,

                Nico

                • 5. Re: Connect By Root In Informatica implimentation ?
                  chaitanya mannem Active Member

                  Hi Nico,

                   

                  Typical use case for the Loop Transformations that I've developed a while ago for PowerCenter.

                  Please take a look here:    Loop Transformations for PowerCenter – preliminary version

                   

                  Could you provide Java code you are using inside the java transformation.

                   

                   

                  Thanks,

                  Chaitanya

                  • 6. Re: Connect By Root In Informatica implimentation ?
                    Nico Heinze Guru

                    It is stored inside the JTX. You can copy it from there wherever you like. :-)

                     

                    The JAR file just contains a couple of variables, no methods.

                     

                    Regards,

                    Nico

                    • 7. Re: Connect By Root In Informatica implimentation ?
                      chaitanya mannem Active Member

                      Hi Nico,

                       

                      I have other completely different issue.

                       

                      I am using VBS script to convert csv to excel in post session command.

                       

                      session having long run with below messages. can you help to resolve. you can check my script I have copied below.

                       

                      Severity Timestamp Node Thread Message Code Message

                      INFO 11/22/2017 10:50:53 AM SHINFADEV01 POST-SESS CMN_1946 [Pre/Post Session Command] Process id 7960. Waited 182 seconds for child process of the shell command to exit.

                       

                      rem  CSV_to_Excel.vbs
                      rem =============================================================
                      rem  convert all NON-empty worksheets in CSV file to xlsx
                      rem  Excel file names will default to Sheet names
                      rem  output folder defaults to the folder where the script resides or
                      rem  if path is specified with the input file, that path is used
                      rem
                      rem  input parameter 1:  Excel path\file in argument 1
                      rem                     (if path is not specified, the current path is defaulted)
                      rem
                      rem ============================================================

                      Dim strExcelFileName
                      Dim strCSVFileName

                      strExcelFileName = WScript.Arguments.Item(0)  'file name to parse
                      strStandardFileName = WScript.Arguments.Item(1)  'file name after conversion

                      rem get path where script is running
                      Set fso = CreateObject ("Scripting.FileSystemObject")  'use this to find current path
                      strScript = Wscript.ScriptFullName
                      strScriptPath = fso.GetAbsolutePathName(strScript & "\..")

                      rem If the Input file is NOT qualified with a path, default the current path
                      LPosition = InStrRev(strExcelFileName, "\")
                      if LPosition = 0 Then 'no folder path
                          strExcelFileName = strScriptPath & "\" & strExcelFileName
                      strScriptPath = strScriptPath & "\"
                      else                 'there is a folder path, use it for the output folder path also
                      strScriptPath = Mid(strExcelFileName, 1, LPosition)
                      End If
                      rem msgbox LPosition & " - " & strExcelFileName & " - " & strScriptPath  ' use this for debugging

                      Set objXL = CreateObject("Excel.Application")
                      Set objWorkBook = objXL.Workbooks.Open(strExcelFileName)
                      objXL.DisplayAlerts = False

                      rem loop over worksheets
                        For Each sheet In objWorkBook.Sheets
                            'only saveAS sheets that are NOT empty
                      if objXL.Application.WorksheetFunction.CountA(sheet.Cells) <> 0 Then
                      rem             sheet.Rows(1).delete  ' this will remove Row 1 or the header Row
                      sheet.SaveAs strScriptPath & strStandardFileName & ".xlsx", 6  'XLSX
                         End If
                        Next

                      rem clean up
                      objWorkBook.Close
                      objXL.quit
                      Set objXL = Nothing
                      Set objWorkBook = Nothing
                      Set fso = Nothing

                      rem end script

                      • 8. Re: Connect By Root In Informatica implimentation ?
                        Nico Heinze Guru

                        Nothing to do about it from the PowerCenter side. The workflow log will contain one message every 2 seconds if a shell command / batch file runs longer than 2 seconds. This is normal behaviour and should - if necessary - be used as an indicator that the respective OS command might be taking too long to execute.

                         

                        So the only thing you can do about it is to make sure that the Excel script run faster, then you will get less of these messages. There is no way (which I know of) to get rid of these messages completely.

                         

                        Regards,

                        Nico