Hi Experts!
We have a data model which is based on snapshot excel files that are provided to us on a monthly basis.
These files consist of time dependent employee master data (joining date, termination date, etc). The reporting shows the rolling history over the last 12 months.
This means each month shows the truth of the associated snapshot file.
Example:
| Snapshot |
Employee |
Gender |
FTE |
| Nov-23 |
1000 |
M |
100 |
| Dec-23 |
1000 |
M |
100 |
| Jan-24 |
1000 |
M |
100 |
| Feb-24 |
1000 |
M |
100 |
| Mar-24 |
1000 |
M |
100 |
| Apr-24 |
1000 |
M |
100 |
| May-24 |
1000 |
M |
100 |
| Jun-24 |
1000 |
M |
100 |
| Jul-24 |
1000 |
M |
100 |
| Aug-24 |
1000 |
M |
100 |
| Sep-24 |
1000 |
M |
100 |
| Oct-24 |
1000 |
F |
80 |
Currently we report all the historical truth.
Now the business would like to report on the latest snapshot facts over all the historical periods. Exmaple:
| Snapshot |
Employee |
Gender |
FTE |
| Nov-23 |
1000 |
F |
80 |
| Dec-23 |
1000 |
F |
80 |
| Jan-24 |
1000 |
F |
80 |
| Feb-24 |
1000 |
F |
80 |
| Mar-24 |
1000 |
F |
80 |
| Apr-24 |
1000 |
F |
80 |
| May-24 |
1000 |
F |
80 |
| Jun-24 |
1000 |
F |
80 |
| Jul-24 |
1000 |
F |
80 |
| Aug-24 |
1000 |
F |
80 |
| Sep-24 |
1000 |
F |
80 |
| Oct-24 |
1000 |
F |
80 |
We think, that this requirement is not possible with our current snapshot logic which is based on a key with the associated month.
Can you please share you experience and opinion on this?
Thank you!
