Types of Fact Tables in Data Warehouse | Transaction, Periodic and Accumulating

Welcome to aroundbi.
In this tutorial, We will understand different types of fact tables - transaction fact, Periodic snapshot fact and Accumulating snapshot fact table - based on individual requirements, we create and use these different fact tables in warehouse schema. We will go through a comparative study to evaluate which fact table type is better in what scenario.

[@rickmemmer5625]

Another hallmark of excellence! I continue to be amazed by the quality of these videos -- it would be hard to improve on them. Thank you very much for your phenomenal work!!

[@aroundBI]

Your feedback is big encouragement to continue our effort. Thanks a lot.

[@rickmemmer5625]

You're very welcome! Thank you for the kind reply! :o)

[@midorile]

This video is amazing, I was reading the data warehouse toolkit and I love it, but this part was so confusing IMO. So I browsed around and found this, nothing left to clarify!

[@davida6146]

Same!

[@ih4722]

@@davida6146 Same!

[@lumaswwar995]

you are speechless, Very very good Explained, thank you

[@nishanthkumar4743]

I really liked the way you explained the tables with real time examples.. I was looking for multiple videos to get the correct understanding but this has enough information... Thanks much

[@tryambakeshwarkatre4666]

thanks for this meaning full explanation...........................................................................

[@harishgovekar3274]

Best Information, thank you Team.

[@MrMadmaggot]

Nice video man, thx a lot.

[@shreyalande7573]

Awsome !!!

[@komaljaiswal2826]

Good explanation! Thanks!

[@rajeshmacpant]

Very well Explained, thank you

[@CaribouDataScience]

Thanks, that explained a lot.

[@MrIrrepressible]

thanks you again for these videos, u are helping many people.

[@jojappakoyyuru8301]

very much useful sir. Thanks a lot.

[@yahooviareifsd]

Very Nice ... Shot n crisp info... Have one question if there is a periodic snapshot fact table or accumulating snapshot fact table then there should be be a transactional fact table by default, ... Right?

[@aroundBI]

In my experience, I have seen a transaction table also along with periodic snapshot. Not a rule but common practice.

[@travel_the_world252]

Thank you ! Well explained!

[@bidata4790]

Excellent Thank you.

[@mloera81]

Great video and playlist, thanks! Just an observation but when you are discussing Update frequency isn't it: Transaction - None Periodic - at defined intervals Accumulating - at each milestone

[@sukumar44]

2 suggestions: 1) please order your videos in the correct way within the playlist 2) please go slow while explaining I have watched all your videos and liked the content.

[@manualchnn]

Thank you great one

[@leoespejo7437]

Nice explanation, pretty clear, thanks

[@aroundBI]

Thanks for your support.

[@franciscochacon1194]

No entiendo como lo pudiste entender, encima los subtítulos están desactivados.

[@shashikanthpai2034]

another good video

[@user-mz8zv5uk5r]

it will be great if u also add an addition where,, u can show how to add a surrogate key from customer/ product dimension as well.

[@suleimanbashirshamsudeen3785]

Really good video series on datawarehousing concept..what type of fact table will you recommend for a CRM Saas application

[@ramakrishnachimmani7273]

thank you and impressive, clear explanation

[@kiranthemanable]

Thanks for the video

[@SandhyaSingh-zf6qr]

Nice 👌.. I have a question- what is additive, semi additive and non additive?

[@darialezhnova103]

really good tutorial!

[@chinmayee_traveller]

very well Explained!! :)

[@Meghaallstar_29102]

you are speachless

[@assiassou9621]

well explained ... thank you

[@SHAli1474]

well explained, thanks

[@aroundBI]

Thanks. Our aim is to keep things simple.

[@DataEngineeringGeek]

If a Transaction has more than 1 product that have been bought/sold then in Transaction Fact Table Transaction number will be the Degenerate Dimension and same transaction number will appear twice as two different product FK are there, with different price and quantity for each product in Transaction Fact table. So, it has one row /transaction does not necessarily hold true.

[@kumarprabhu]

Can a periodic snapshot contains lowest level of granularity...In other words business wants to take a snapshot of the entire fact table on daily basis, so that on a particular day they want see all the relevant detail rows anytime possible..How to handle this.

[@nilabjachattopadhyay8863]

why is the accumulating fact table not considered as a dimension table? it has the date dimensions.. same as role playing dimensions

[@maryam4071]

Thank you for the video, I need a help, User tracking, such as Google analytics data, we need to keep track of the user, what type of fact table is?

[@aroundBI]

Great question. Fact table type will be dependent on two factors- first is volume of data that we can expect. Most common criteria or key to store data would be unique visitor or visitor # or ID. Second factor would be type of analytics we want to do. Option would be periodic snapshot but I would go ahead and gather every detail just as transaction table. Again, thanks for your wonderful question.

[@maryam4071]

Thx @aroundBI for your response, I didn't get your sentences here "Most common criteria or key to store data would be a unique visitor or visitor # or ID". As far as I understood the lowest level of granularity in case of the snapshot is the "date", in my case is 'sec'. so we will get a row per each visitor Id + time. this would be unique over the file. Then this looks like a snapshot, right? Are u confirming me? Mostly you are selecting snapshot policy just for avoiding of data grow in fact, right?

[@NripaEmpowerthroughknowledge]

@@maryam4071 🙏

[@coolzankit]

Please add a video on late arriving dimension

[@sukhishdhawan]

Explained all in 9 minutes

[@Roamingcurlcircuit]

is unit price a additive fact or non-additive?

[@forstreetanimals3882]

Why you are not uploading video more than one year in you channel..?

[@malakwaseem1166]

What is historical data in data warehousing?

[@anupamachandra6338]

what is difference between fact and fact table

[@akashverma5054]

Hi need some help related dimensional modelling its urgent

[@Bharath_PBI]

How can a periodic snapshot fact table be less in size when compared to transaction fact? I have worked with snapshot facts which are in hundreds of millions whereas transactional fact are just in tens of millions.. can't say that is always less, as it depends on the grain

[@ranganathanb2586]

True

[@anukamythri6064]

Good explanation, but you need to speak a little slower please

[@vivek5868]

why dont you set play back speed of video to 0.75x...