OBIEE 12c - Reflecting changes in hierarchies over time

[Deleted User]

No worries. Was just too tempting not to respond.

Mark.Thompson

Thank you, everyone, for your responses.  I think I understand now.  Thank you especially to Robert Angel who did his best to get the answer through my thick skull.

Correct Answer awarded to Martin.  The join he described was the missing link in my understanding of how to proceed.  Once I saw that, I understood.

Robert Angel

Hi Mark,

Thank you - I have a thick head myself some days, so I try to be understanding when the penny does not fall for others....

On the join my preference would be using a single key for the join, sure you need the dates to generate the new key, but the join itself will work fastest just based on fk = pk and doing this is part of the ETL is not difficult.

Martin van Donselaar

Agreed, the join on dates is not necessary here...Just make sure the new dimension key is used in the fact.

Mark.Thompson

I was curious to test where I could use that join between the dates (which is indeed more inefficient, I understand, but just from a point of view of experimenting with the possibilities), and I found that I cannot create that type of join between the dimension table and the fact table in the Physical layer of the repository.  Replacing my original example in this post with the names of the physical columns that I am testing, here is the attempt at creating that join in the Physical layer:  

"ORCL".""."CALTECH"."SALES_REP_F"."REP_ID" = "ORCL".""."CALTECH"."SALES_REP_D"."REP_ID" AND

"ORCL".""."CALTECH"."SALES_REP_F"."MONTH"  between "ORCL".""."CALTECH"."SALES_REP_D"."EFF_START_DATE" and "ORCL".""."CALTECH"."SALES_REP_D"."EFF_END_DATE"

Error dialog: Only columns, designated predicates, and operators are allowed.  (i think It doesn't like the between as an operator).

OK, so how about this:

"ORCL".""."CALTECH"."SALES_REP_D"."REP_ID" = "ORCL".""."CALTECH"."SALES_REP_F"."REP_ID" AND

"ORCL".""."CALTECH"."SALES_REP_F"."MONTH" >= "ORCL".""."CALTECH"."SALES_REP_D"."EFF_START_END" AND

"ORCL".""."CALTECH"."SALES_REP_F"."MONTH" <= "ORCL".""."CALTECH"."SALES_REP_D"."EFF_END_DATE"

Error dialog: All operators must be equality.  (Oops, no greater than or less than allowed here.)

Thus, it appears that we can only use = in our Physical layer joins, so this join would have to happen in the database.  Is that correct?

[Deleted User]

Mark.Thompson wrote:Thus, it appears that we can only use = in our Physical layer joins, so this join would have to happen in the database. Is that correct?

No. That's why we have complex joins in the physical layer.

Mark.Thompson

Got it.  After I had manually typed the join condition, the little operator field still showed =.  I removed everything from the join field, removed the keys from the dimension table in the physical layer, then rebuilt the join by copying/pasting what I had there before, and now the operator shows COMPLEX, and it was accepted.  That's all I can test for the moment, but at least that's one more hurdle jumped.  Thanks, Christian.