12 Replies Latest reply on Jan 18, 2010 5:02 PM by 733651

    Help Required

    Varun Kaushal
      How to map two different sources to one Target Datastore using single interface.

      Thankx in advance
      Nick
        • 1. Re: Help Required
          JohnGoodwin
          Drag the two sources on to the source window, create a join between the tables by dragging the column off one source table on to a column on the other source table.
          You can then change the join details in the mapping window if required.

          Cheers

          John
          http://john-goodwin.blogspot.com/
          1 person found this helpful
          • 2. Re: Help Required
            Varun Kaushal
            Dear John

            I am trying to merge two files into one file. Both the data sources have different records. How can i merge the record from 2 different sources into a target datastore. According to me Joins can be put when we hav some similarity between the records of the sources.

            Thankx in advance
            Nick
            • 3. Re: Help Required
              Varun Kaushal
              Thankx John

              Your answer helpful in solving some ther issue.

              Nick
              • 4. Re: Help Required
                733651
                To use two different sources, you need to have a column joining these two tables.

                Thanks.
                • 5. Re: Help Required
                  Varun Kaushal
                  Dear Nithesh,

                  I have an Financial Application as a target in which Accounts is one of the dimensions. The data for this financial application data comes from 2 different sources both having financial data for different accounts.

                  Now i want to create a single interface to load data from both the applications into the target.

                  For this in the mapping I have to map two account dimensions from source 1 and source 2 to the accounts dimension of the target.

                  Kindly provide the steps required to do so.

                  Thankx in advance
                  Nick
                  • 6. Re: Help Required
                    733651
                    Hi,

                    As both the sources have the data related to the finance for a particular applicant. The following approach would be suitable:

                    1. Drag the two tables that you have mentioned earlier.
                    2. As the tables are holding the financial information about a customer, so there should be a mapping with the customer in some way, so get that link and establish the same with the above two tables in the interface created. Drag the appropriate tables needed and link the columns.
                    3. Map the needed source columns to the target table

                    Hope this helps.

                    Thanks.
                    • 7. Re: Help Required
                      Varun Kaushal
                      Hi

                      I have already put a join on the customer ID which is same for the 2 source tables and mapped one of then to the target. But how to map account code which is different for the two sources.

                      Thankx
                      Nick
                      • 8. Re: Help Required
                        733651
                        Do you mean that both the tables have customer-ids with different account codes?

                        If it so, write it as an outer join when you are mapping the customer-ids in one table. This will retrieve all the data needed and insert into the target table.

                        Hope this helps.

                        Thanks.
                        1 person found this helpful
                        • 9. Re: Help Required
                          Varun Kaushal
                          I tried putting a cross join i get the following error message.

                          -16 : 37000 : java.sql.SQLException: Wrong data type: in statement [create table "C$_0File" (       )]
                          java.sql.SQLException: Wrong data type: in statement [create table "C$_0File" (       )]

                               at org.hsqldb.jdbc.jdbcUtil.throwError(jdbcUtil.java:62)

                               at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(jdbcPreparedStatement.java:412)

                               at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)

                               at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)

                               at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

                               at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)

                               at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)

                               at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)

                               at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)

                               at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)

                               at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

                               at com.sunopsis.dwg.cmd.e.i(e.java)

                               at com.sunopsis.dwg.cmd.g.y(g.java)

                               at com.sunopsis.dwg.cmd.e.run(e.java)

                               at java.lang.Thread.run(Unknown Source)



                          When i put either left or right join the traget file shows data from the source which i map with the target.
                          • 10. Re: Help Required
                            733651
                            Can you please provide the sql command and also the data types of the columns for which you have cross-joined.

                            Try using the LEFT OUTER JOIN.

                            Also if there are any answers helpful, do mark them as helpful.

                            Thanks.
                            • 11. Re: Help Required
                              Varun Kaushal
                              I am testing with 2 columns i each source (Entity and Amount) and I am putting cross join on both the columns.
                              Entity - varchar
                              Amount - Number

                              the error comes at create work table

                              create table "C$_1File"
                              (
                                   
                              )

                              If you give me your mail id I can forward you all the screen shots.

                              Thankx
                              Nick
                              • 12. Re: Help Required
                                733651
                                Do a cross-join only on one column, in this case, the entity column. I believe this should solve your problem.

                                You can reach me on nithesh.baheti@gmail.com.

                                Thanks.