Clear all

Business Date of source system

Posts: 149
Topic starter
Estimable Member
Joined: 2 years ago

I have two questions related to this --


Our DWH is extracting data from OLTP systems monthly after the a End-of-month job has executed in the source and the system moves to the next Business Date. For a batch run the ETL process knows for which business date the data is getting extracted. Should I add a column "business date" in each satellite table in the Raw Vault even if the source tables are not providing this data as a column but this is a implicit knowledge for a batch run?


We have a requirement to create a view in the information mart layer which will feed a downstream system. The view joins multiple Hubs, links and their satellites, one of the user requirement is to be able to do "as of" data reporting from this view, example, if the customer address is XYZ as of 31 Jan and then later updated to ABC as of 31 July. I Aug if the user queries for data as of 28 Feb they expect to see the customer address as XYZ and not ABC (latest record). 

Options I see are

1. Create a Dimensional Mart with Type 2 Dims and Fact.

2. Create a bridge table which will cover all the Primary keys of  hub,links and the satellites used in the join and the "Snapshot Date" as the business date.

Please suggest.

6 Replies