Handle Late Arriving Dimensions|Early Arriving Fact|Dimensional Modeling Techniques

Sometimes the facts arrive before the dimensions resulting in tricky situations.These records are calling Early arriving fact or late arriving dimensions.
This situation in an OLAP system can be handled in multiple ways which are explained in the video.
#latearrivingdimensions #earlyarrivingfact #techcoach

[prashantmhatre]

Thanks a lot, we are building our data warehouse system and we have encountered this issue. we are planning to go ahead with insert a dummy value option at this moment. the way you have explained all methods were really helpful. Thanks a lot

[Manasuna_manasai]

Nice video. Thank you.
I have used 2nd option. We used to keep data in the staging source table for 40 days and process everyday along with incremental data. If the employee is assigned to a team(say for example after 10 days of first load), we load the data along with the team id in the stage fact table. If we don't get employee after 40 days, we ignore the record. I believe this is the clean way of processing because in this way we will get correct dimension key and fact data.

[shrutisinghal]

You are a savior! Thanks!! I will go through all your videos..

[ranjanmohapatra]

One of the beautifully explained video on this ! Thanks Man

[mikewashington]

Bravo! Outstanding video with great examples of COA’s. I’ve used the parking method in my jobs.

[ronnyfernandez]

Nice video!! Thanks

I agree conceptually but we know that most data warehouses will also use surrogate keys. So with option 4, you will have to not only insert in dimension but also get back the surrogate key generated and apply it on the fact. In your examples the distinction is missing between natural and surrogate keys which is quite crutial from ETL perspective. Think this step complicates things specially if you are doing this across all dimensions. Can you tell me how you would handle this?

Also with option3, again assuming we have surrogate keys, you would need to heal that in fact and one way would be to hold both natural and surrogate key in fact and run an update process on fact at periodic intervals where you only fix if key is dummy.

Can you share some thoughts on these ?

[Rafian]

This is the only video which explains this concept so easily. Great job sir!!

[AK-rwzq]

Good work keep up the work.. and thanks for sharing knowledge..looking forward to gain knowledge from you.

[saugatadey]

Nicely explained. I have seen approach 3 being used more often. while doing so, data integrity, accuracy and quality is maintained. This will also help improve the quality of the OLTP systems vis a vis the business process at that layer

[mayankshyamsukha]

We do both park and retry as well as dummy entries, first is the dummy entry, we check whenever a dummy entry is created, it has to be repaired so we send a notification to get it fixed after parking it. Once it is repaired, we use the new value in the parked record and process it.

[gokukanishka]

Can you please make video on fast changing dimensions / rapid changing dimensions

[surajjavir]

Hi there,
I have small doubt. You said it will give error when we try to insert null value in foreign key column. But we can insert null value in foreign key column.
I am not from DWH background so not sure about this.
Could you please clear my doubt.