Record DateTime Explained

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.

What We Do

We provide SMBs with custom data analytics services.

Built to Spec
We design custom solutions.

Low Cost per User
Empower as many report users as you like.

You Own It
Everything from the data solution to reports is yours to keep.

Month-to-Month
No upfront costs and no long-term commitment.

Available 24/7
We monitor and maintain everything 7 days a week.

Support
Unlimited access to a team of data experts.

Find out how 
our clients
are getting

10x ROIwithin 6 months

CLICK HERE

Stay Connected With Us

Join our monthly newsletter to receive LeapFrogBI’s latest insights and articles on automated, customized reporting.

LET’S TALK

Have any questions? Reach out to us, we would be happy to answer.

';