8 Replies Latest reply on Jul 1, 2015 2:38 PM by Ebin

    Reg. nQSError: 15009 : Multiple paths exist to table

    Prasanna M

      Hi all,

       

      I have below three logical tables which are mapped to each other as attached

      "Branch" "Accounts" "Customers"

       

      Circular Mapping Issue.JPG

       

      A 'Customer' can have '0..n' 'Accounts'. When I try to get the count of 'Customers' by 'Branch', the actual result is less than the expected result. On further digging to the physical query, I found out that, the customers count based on branch goes via 'Accounts'. since, Branch is Linked to 'Accounts' which is further linked to 'Customers'. so, the customers with no accounts are missed out from the count.

       

      I would like to design the model in such a way that if 'Accounts' is not selected, the 'Accounts' table should be ignored and only 'Branch' and 'Customers' should be considered. Also, in my Presentation layer, i would like to have only 1 'Branch' table and not seperate tables for 'Accounts' and 'Customers'

       

      Please suggest if this is feasible.

        • 1. Re: Reg. nQSError: 15009 : Multiple paths exist to table
          Ebin

          Hi Prasanna,

           

          Is it possible to join the Branch and Customers directly.. Is there any direct relation between these 2 tables ? If you have direct relation between Branch and Customer, you could create an alias of Customers and join it to the Branch tables, so that if 'Accounts' is not selected, the 'Accounts' table will be ignored and only 'Branch' and 'Customers' wil be considered.

           

          Thanks !

          • 2. Re: Reg. nQSError: 15009 : Multiple paths exist to table
            Prasanna M

            Hi Ebin,

             

            Thanks for the response.

             

            Do u mean to create an alias of Customers and join in the physical layer? if I do so, I dont need to do any changes in BMMI layer? I am using OBIEE 10g here.

             

            Thanks

            • 3. Re: Reg. nQSError: 15009 : Multiple paths exist to table
              Ebin

              Yes, you need to create the Customer alias in physical layer and then you need to bring this as LTS to the BMM layer. There is some difference in the query generation mechanism for 10g compared to 11g. So test the solution after adding new LTS in BM Layer.

              • 4. Re: Reg. nQSError: 15009 : Multiple paths exist to table
                Ibag

                Yes Prasanna,

                 

                Follow what Ebin said as this approach worked for me sometime back.

                • 5. Re: Reg. nQSError: 15009 : Multiple paths exist to table
                  Prasanna M

                  Hi Ebin,

                   

                  I already have an alias for 'Customers'. Now, I have physically joined 'Customer' and 'Branch' as attached. Please note that, already there is a join between 'Branch' and 'Account'

                   

                  customer_account_branch.JPG

                  After this, I didnt do any changes in BMMI Layer. My BMMI layer with these three objects look like below.

                  cust_acc_branch_bmmi.JPG

                  After the above change, I still get the same issue (count of customers not reflecting properly). I couldn't get if I need to do additional change in BMMI layer.

                   

                  Please help in this regard.

                   

                  Thanks.

                  • 6. Re: Reg. nQSError: 15009 : Multiple paths exist to table
                    Ebin

                    Hi Prasanna,

                     

                    You may need to remodel your physical layer. Currently I could see 2 issues in it.

                     

                    1) It has a circular join

                    2) 2 fact tables are joined to each other.

                     

                    My proposed solution was to create an alias for Customer table (Dont use existing alias Fact_SGTM_CUSTOMER_DETAILS) and join this to the Dim - SGTM - Branch. After that bring the new alias as LTS to the customer dimension.

                     

                    Please test the solution after making the change. I don't have access to any 10g environments to attach the generated query here.

                    • 7. Re: Reg. nQSError: 15009 : Multiple paths exist to table
                      Prasanna M

                      Thanks Ebin,

                       

                      The circular Join was made just to check whether it fetches the results properly.

                       

                      Also, The two aliases starting with 'Fact' are actually not Facts. They are converted into Dimensions once I have brought another Table 'Account Balances' which stores day-wise

                      Below is the snapshot of Customers logical table with two LTS.

                      account balances. So, My BMM layer still has only one Fact. BMM_Customers.JPG

                       

                      Below is the Physical Layer Joins between the discussed tables. Whereas Fact_SGTM_CUSTOMER_DETAILS_1 is the newly created alias.And it is just dragged and dropped to Customers Logical Table.

                       

                      Physical_Joins.JPG

                       

                      Even after the above modifications, my Request provides me the same result. And the Physical SQL issued to the database still uses the 'Accounts' table.

                       

                      Am I missing any step here?

                       

                      Thanks

                      • 8. Re: Reg. nQSError: 15009 : Multiple paths exist to table
                        Ebin

                        Prasanna,

                         

                        You need to map the existing columns in the BMM layer to the new LTS. Don't create different logical columns for different LTS.