Joining BI Publisher-based augmentation with a dimension using effective dates

Martina Iovino-Oracle

Hi everyone,
I'm trying to create a data augmentation based on a BI Publisher report, in order to add some fields to a dimension.

In my report, I’m using the table that holds Enrollment ID, Effective Start Date, and End Date as primary keys. These two date fields are timestamps in the original HCM table.

Now, after building the augmentation, I want to extend the Learning Enrollment dimension with a new field from this data. To do that, I need to join using all three primary keys.

If I join only on Enrollment ID, it works.
But when I add the dates, the join fails.

I checked and noticed that the Learning Enrollment dimension stores Effective Start Date and End Date as dates, not timestamps. I have tried several things:

• Using the raw timestamps as-is
• Casting the timestamps to dates in the BI Publisher report using something like:
  TO_CHAR(en.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AS EFFECTIVE_START_DATE
• Using TO_DATE or formatting in the data model

None of these approaches worked, even if when comparing the new effective dates with the effective dates of the dimension they look the same.

This is the Configuration Connection:

Has anyone faced this before? Do you know how to correctly do the join between a BI Publisher-based augmentation and a dimension using effective dates?

Thanks in advance.

Martina Iovino-Oracle

Solved this with Complex Join, using the dimOnly table.