When creating a PSA component, the designer is asked to assign a value to RecordDateTime. The selected value will have implications downstream on both fact and dimension components.
RecordDateTime Definition
RecordDateTime is the point in time which the record is considered effective. For example; a transaction is often considered effective at this point in time at which it occurred (transaction time).
How RecordDateTime is used when Loading a Dimension
LeapFrogBI will keep track of the RecordDateTime in PSA, Transform, Dimension, and Fact components. When loading a dimension the RecordDateTime is used to assign a value to LFBI_Start (SCD tracking field). When tracking history in at least one dimension attribute (SCD2) and a new record version is loaded, LFBI will expire the existing record and create a new version with the LFBI_Start set to the new record’s RecordDateTime. LFBI_End and LFBI_Current are also managed based during the load process.
How RecordDateTime is used when Loading a Fact Component
Fact component definitions include one or more foreign key lookups to related dimensions. The process of looking up the correct foreign key must include logic to retrieve the correct record version when the dimension tracks history (SCD2). LeapFrogBI will use the RecordDateTime to establish the value to be used when performing this lookup. The generated SQL will include a ”Between” operator such as the following where p200 is a fact table and k1 is a dimension table.
p200.[LFBI_RecordTime] Between k1.[LFBI_Start] and k1.[LFBI_End]RecordDateTime will always be included in the lookup OnClause.
General Guidance
Not Tracking History – If you do not have SCD2 attributes, then it is acceptable to set the RecordDateTime to a constant value. By doing so you can be assured that the RecordDateTime lookup will always find a match. However, if you later decide to track history, then a full reload will be required.
Tracking History – When SCD2 attributes do exists, then it is important to carefully consider what value to assign to the RecordDateTime property in PSA. Things to consider include:
- Dimensions must have a record with an assigned RecordDateTime which covers all related fact RecordDateTime values. Otherwise the lookup will fail resulting in -1 as the assigned FK value.
- It is rarely appropriate to assign the LoadTime as the RecordDateTime. LoadTime is tracked by LeapFrogBI, but this is not related to the RecordDateTime.
- Modified dates are often a good candidate to be assigned as the RecordDateTime.
- Remember that this value is used in both Dimension (LFBI_Start) as well as Fact components. In all cases the assigned RecordDateTime will be sourced from the component’s source. Joined components RecordDateTime is not considered.
- When aggregations are applied to a component, RecordDateTime will also be aggregated using the maximum function.
