Make foreign key in the physical layer:
In the BMM put in the dim org logical table source/content tab/where part:
DIM_ORG.DT_End IS NULL
In the Physical Diagram, while creating the Join,1 person found this helpful
Select 'New complex join' instead of 'New foreign key'
This allows you to specify non equi-joins
Hope this helps.
Hello Goran & Raghu,
Thanks for your answers, both suggestions are accepted by the Administration tool and also pass the Contingency check. I'll have to do some testing to check if they both actually solve my case.
I do have one additional question: What are the (dis)advantages of these two solutions?
Thanks again for your feedback.
To my knowledge,1 person found this helpful
With the Complex join at Physical Layer, only the data that satisfies the Join will be processed and propagated to Business Layer and so on..
With the CONTENT FILTER on Source at BMM.,
Initially at the Physical Layer, total data will be processed,
Then at the Business Layer, only the data that meets the FILTER condition will be propagated to the next Layer or it is visible to the user.
I can't say this is the disadvantage.
This is just my view.
In OBIEE 11G as we don't have the option to select the join type ('New complex join' or 'New foreign key join') apply the join condition DIM_ORG.Id_Org =FACT_REV.Id_Org AND DIM_ORG.DT_End IS NULL directly on the Physical table but not on the Alias table!
doing so will not throw the [nQSError: 30001] Only columns, designated predicates and operators are allowed error..! this worked in my case for a similar requirement.