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 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.
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.
Follow what Ebin said as this approach worked for me sometime back.
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'
After this, I didnt do any changes in BMMI Layer. My BMMI layer with these three objects look like below.
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.
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.
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.
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.
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?
You need to map the existing columns in the BMM layer to the new LTS. Don't create different logical columns for different LTS.