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

[@shrutisinghal8556]

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

[@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.

[@TechCoach]

If you are not getting data for 40 days that's a huge lag.

[@ranjanmohapatra6528]

One of the beautifully explained video on this ! Thanks Man

[@TechCoach]

Thanks Ranjan :)

[@Rafian1924]

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

[@TechCoach]

Thanks a lot sandesh for the kind words, I will upload a lot of new videos soon so please keep watching :)

[@Rafian1924]

@@TechCoach we will await your videos sir.

[@TechCoach]

The new video on Index organized table is live now, Check out here. Happy learning:) kzbin.info/www/bejne/o3rSZWePmdOXadE

[@mikewashington4188]

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

[@TechCoach]

Thanks Mike for the kind words :)

[@ronydz9045]

Very Well explained, thanks.

[@TechCoach]

Thanks a lot buddy, please consider subscribing to my other channel as well it will really help me :) kzbin.info/www/bejne/nYXKZ6F5itqMhZo

[@Jit02]

nicely explained !!!

[@TechCoach]

Thanks Buddy :)

[@ronnyfernandez1986]

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 ?

[@NK-er3ci]

Good comment...I know you posted this over a year ago but I'd just like to chime in with my thoughts on your suggestion regarding option 3. I agree that holding the natural key in the fact will allow for easy updates. You are suggesting to only update the fact if a surrogate key becomes available for rows which had the unknown dimension member assigned (-999). I'd be inclined to update the fact if any of the underlying natural keys have changed in the interim for whatever reason. Doing it this way would cover the above scenario and also all other corner cases where things change. I'm just thinking out loud here so feel free to disagree :)

[@anirvansen5024]

Awesome thank you

[@saugatadey1913]

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

[@TechCoach]

Thanks Saugata for the kind words, My apologies for the delayed reply

[@loudravetortoise]

blessings ! thank you

[@TechCoach]

Thanks Juan :)

[@mayankshyamsukha8301]

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.

[@TechCoach]

Nice Mayank, that's a good approach.

[@maheshwarvarma5933]

Very Well explained. Thank you!!

[@TechCoach]

Thanks a lot buddy for the kind words :)

[@AK-rw8zq]

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

[@TechCoach]

Sure AK1007, I will be creating new videos soon :)

[@gokukanishka]

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

[@TechCoach]

Sure Kanishk , I have noted it in my backlog I will work on it soon

[@gokukanishka]

@@TechCoach thankyou so much 😊😊

[@TechCoach]

Hi Kanishk , I have created the video and scheduled for day after tomorrow at 11 AM IST :)

[@gokukanishka]

@@TechCoach thankyou so much ❤️

[@mahendart4928]

Super sir good explanation

[@TechCoach]

Thanks Mahendar for the kind words I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@PRIYANKASHARMA-jv3xm]

Thanks for the detailed explanation👍🏻

[@TechCoach]

Thanks Priyanka for the kind words. I have a small request, I am working on this new channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@surajjavir5688]

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.

[@NagatiAbhilashPaul]

Null is acceptable in FK but if FK and PK are not mapping it throws an error