RPD - Conditional Join implementation due to null values

Question

Hi Experts,

Issue:

I already have current RPD build in the way the figure below illustrates (Arrow joins). Looking for an easier fix where RPD BMM layer do not need many changes.

Current RPD logic:

PS and F are main tables where I expect to see all records of WRno from PS mandatorily that looks related information in F table.

(PS is left outer joined currently to F on secondary join condition, i.e. PS.ps_prop_id = F.id and PS.ps_type = f.f_type.

F table has 3 alias tables to bring out different columns data.Mostly F.id from F table is left outer join to dimension tables such as C1,C2 and C3.

Requirement:

As mentioned in the figure below,

Firstly, PS had to left outer join on F by PS.ps_id = F.f_id for all conditions where ps_id is not null.

secondly, when ps_id is null, PS should be left outer joined on F by PS.ps_prop_id = F.f_prop_id and PS.ps_type = F.f_type.

How can i implement this solution in OBIEE? Columns pulled on report would be Wr_no,ps_prop_id,to_prop_id,f_prop_id only.

Sasha2020 · Answer

Hi Chris,

Unfortunately, the data issues are under the maintenance of a third-party vendor, and the management doesn't have time to correct data as this issue can take almost a month or two to discuss with management and DBAs to identify corrections everywhere. From development, they expect quick remedies so that business continues on temporary base till main problems resolve. That is the reason why i mentioned that RPD joins shouldn't go much change from earlier scenario.

And yes, every record should be tested on first condition and if it fails should go for second conditional join.

Thank you.

[Deleted User] · Answer

Let me ask a counter question:

Do you think it is an optimal approach to have every single query that runs is forced through this logic for every single row on the database every single time it runs?

Instead of loading he data correctly ONCE and everything else then follows and uses it transparently?

Just a thought, but I am well aware than almost nobody thinks of performance or good design these days anymore and tries to wiggle their way around logically or even in the frontend.