Query on Pulling Data in Data Lake Compass Using Effective Date and AsOfDate

FNU Nikhil Parshe

Hello everyone,

I need assistance with pulling data from the data lake using Effective Date and AsOfDate in Data Lake Compass. Specifically, I am working with the HRT system, querying the CRHH_HCM_Employee and CRHH_HCM_EmployeeBenefit business classes.

Here’s the scenario:
I have a list of 5 employees:

Employee - EmployeeName - RelationshipStatus

1. 121 - John - Active
2. 122 - Andy - Active
3. 123 - Kelly - LOA
4. 124 - Susan - Term Final (Effective 2024-09-09)
5. 125 - Nike - Term Pay Pending

Objective 1: Pulling Active, LOA, and Term Pay Pending Employees passing latest date range
I want to retrieve records for employees who are in statuses of Active, LOA, or Term Pay Pending, excluding those who are Term Final, by using the employee benefit enrollment dates.

I am applying the following condition:

FROM
CRHH_HCM_EmployeeBenefit EB
JOIN CRHH_HCM_Employee E ON EB.Employee = E.Employee
WHERE
(
E.RelationshipStatus IN ('ACTIVE', 'LOA', 'TERM PAY PENDING')
)
AND EB.DateRangeBegin <= '2024-09-11'
AND (EB.DateRangeEnd >= '2024-09-11' OR EB.DateRangeEnd IS NULL)

For this scenario, I expect to see 4 records, excluding employee 124 (Susan), who was terminated with a final status effective on 2024-09-09. (Which is working fine and I'm seeing the expected output)

Objective 2:

In another scenario, if I pass a past date range as:

FROM
CRHH_HCM_EmployeeBenefit EB
JOIN CRHH_HCM_Employee E ON EB.Employee = E.Employee
WHERE
(
E.RelationshipStatus IN ('ACTIVE', 'LOA', 'TERM PAY PENDING')
)
AND EB.DateRangeBegin <= '2024-01-01'
AND (EB.DateRangeEnd >= '2024-01-01' OR EB.DateRangeEnd IS NULL)

I expect to see all 5 employee records, including Susan (Employee 124), as she was still active on that date.(However, I'm not seeing all the 5 employees because, the query is considering the latest RelationshipStatus as TERM FINAL and excluding Employee 124)

Question:
How can I effectively pull data based on the Effective Date and AsOfDate in Compass to accommodate these scenarios? In IPD, using the Landmark node, we have user-defined fields for these dates.

Is there a similar approach or best practice in Data Lake Compass that would allow me to handle this?

For understanding please refer the snip:

Any insights or advice on structuring this query would be greatly appreciated!

Thank you!

Bojan Rafajloski

Hi Nikhil,

You may be dealing with a use case where you would like to query past versions of a record, which is something that Compass SQL with Data Lake allows you to do.

I assume this record does not contain every status change and has only the current status. In that case, you can access past version of the records in the Data Lake. ERPs publish every record change with a concept we refer to as Variations and you can easily access any of the historical data (including delete data) with Compass SQL variations handling functions: Variation handling

For example, this query will return all record variations that exist in the Data Lake:

SELECT * FROM infor.allvariations('Products')