12 Replies Latest reply: Feb 14, 2013 6:24 AM by JeromeFr RSS

    Select Distinct and join in ODI


      I have following task to perform: I am loading metadata into Planning dimension from Oracle database. I have two tables
      1. "Sales"
      Columns: Name, Number, Value
      Sample Data:
      Product 1, 10, 200
      Product 2, 30, 100,
      Product 1, 15, 500
      2. P&R
      Name, Alias
      Product 1, SampleSoda1
      Product 2, SampleSoda2,
      Resource 1, CanForSoda,
      Resource 2, CO2
      What I need to do is: I have to select name and alias from second table of all products that were sold.
      So I need to select distinct Name from Table 1, naxt join it with Table 2 (so I have Name and Alias) and load it to planning.
      I am a little confused how to do it.

      Any help would be great!
      Best regards,
        • 1. Re: Select Distinct and join in ODI
          Hi Greg,

          What you can do is either :

          - Create a yellow interface with your table Sales as source. Map the name column directly in the target. In the flow tab, click on your target and select "Distinct rows".
          - Create a second interface, with your first interface as source. Select the "Use Temporary Interface as Derived Table (Sub-Select)" checkbox.
          - Add your second datastore and join it. Or you can use a lookup table.


          - Create an interface with Sales and P&R as source (or set P&R as a lookup table).
          - Go on the flow tab and select "Distinct rows".

          If you've a lot a data in the first table, I would go for the first solution.

          Hope it helps.


          Edited by: JeromeFr on Feb 14, 2013 9:52 AM
          To be more clear in the first step of solution 1
          • 2. Re: Select Distinct and join in ODI

            Thank for quick answer. I have one question. I think that if I select distinct rows would it not select all rows that are distinct (P1, 30, 300| P1, 40, 100 - are different rows but product is tha same) when I need only product names? Correct me if I'm wrong.

            Best regards,
            • 3. Re: Select Distinct and join in ODI
              Sorry, I was unclear.

              In the first (yellow) interface, you should only have the Name column in the target. So if you check "Distinct rows", you will only have the distincts product names.

              To explain a bit more :
              Creating an yellow interface and using it as source with the "Use Temporary Interface as Derived Table (Sub-Select)" option will just use it as a subquery. You can check the generated code of your second interface to understand it.
              The first solution explained in my first post will thus use a subquery like "SELECT DISTINCT name FROM Sales".

              The second solution will join retrieve Alias for each row of the Sales table and then select the distinct pairs of name-alias. So if you've a lot of rows in Sales, I would avoid this one.
              • 4. Re: Select Distinct and join in ODI
                I have one more problem with yellow interface.
                In target datastore (PROD_DISTINCT) i created one VARCHAR column. In target options I select:
                Temporary Datastore Location to Work Schema.
                However ODI still tries to create temporary datastore in my source where I do not have crete view permission.
                Should I change this somewhere else?

                Best regards!
                • 5. Re: Select Distinct and join in ODI
                  You actually don't need to run the first interface. If you try to run it, it will try to materialize PROD_DISTINCT in the target schema.

                  If you just run the second interface AND you've selected the "Use Temporary Interface as Derived Table (Sub-Select)" option, it will just include the SQL code from the first interface as a subquery, and thus only the second interface will materialize a table.

                  I suggest you tu build the second interface as well and Execute it with the "Simulate" checkbox selected, you will see how it works.
                  • 6. Re: Select Distinct and join in ODI
                    Ok! One more question, is it possible to create yellow interface, make it as a source in another yellow interface, and use the second one in normal interface?
                    Best regards and big thanks for your help!
                    • 7. Re: Select Distinct and join in ODI
                      Yes it is.
                      And if you use twice the Derived Table option, you only need to execute the last interface.

                      Best regards and you're welcome !
                      • 8. Re: Select Distinct and join in ODI
                        Hi Jerome,

                        Sorry for being such a noob but this is first time when I use yellow interface.

                        Use Temporary Interface as Derived Table (Sub-Select)" checkbox. is not active. I read that I can use that when:
                        "source datastore of your yellow interface is on the same database than the staging area of your blue interface."

                        My first yellow interface has: sorcue: PROD; stage:ODI_STAGE.
                        My second (which uses first as source): source: PROD, ODI_STAGE; stage:ODI_STAGE
                        My blue (which uses second yellow as source) source: Source: ODI_STAGE, Hyperion Planning (one more join); stage: ODI_STAGE.

                        Is it possible to do what we just talked about?

                        Best regards,
                        • 9. Re: Select Distinct and join in ODI
                          Don't worry, it's not a very intuitive option.

                          I think it should work with what you explained.

                          Could you check two things ?

                          - In the topology, under Physical Architecture, double click on Oracle technology. If "Support Derived Table" box is unchecked, check it. (found in the last two comments here : http://askankit.blogspot.be/2011/04/when-temporary-is-not-temporary.html?showComment=1308729768829#c4073543968078580502)
                          - in the overview tab of each interface, check that you select Oracle: ODI_STAGE
                          • 10. Re: Select Distinct and join in ODI
                            Unfortunatly both of those thigs are checked and I still cannot use it :/
                            • 11. Re: Select Distinct and join in ODI
                              Then I don't see what causes this problem.

                              You dragged and dropped the yellow interface in the source canvas of the following interface, right ?

                              Are you working with journalized table in your first interface? Or do you use a native sequence in one of the two yellow interface?
                              A limitation I forgot to mention is that you can't use journalized datastore and native sequence with derived tables.
                              • 12. Re: Select Distinct and join in ODI
                                Maybe just try to close ODI and start it again.

                                As a temporary workaround, you can create a database view for the distinct and use it as source.