Difference Between Fact Table and Dimension Table - Interview questions

hello friends uh today we're gonna see what is factable and dimension tables uh first let me go through the description for fact table or dimensions table uh the fact table is let me write it down over here uh fact table contains uh measurement of business processes and it contains uh foreign keys case for the dimension table so let me write it down over here vectable contains the measurements of business processes and it contain foreign keys for the dimension table div tables uh so as you can see over here uh in this example i took cells as affectable so cells fact table you see that all foreign keys coming from the different different dimension tables so here first our foreign key is item id second time id branch id so as you can see we have similar dimension table for those ids so here you can see item dimension table in that table you will find uh primary key item id which is here is a foreign key in in fact table so in dimension table we have all the descriptive values um in in one moment we gonna go through that uh definition for dimension table so here you see the item table which is dimension table which has item id name brand brand name same thing for branch table here you see the branch id here you can see as a foreign key here in the same descriptive values branch name honor name and so on same thing for time uh time dimension time id day month quarter year so now let's take a look dimension table what is dimension table so dimension table is nothing but uh is a table which contains attribute of measurement uh stored in the fact table so it's kind of attribute of measurement so it's kind of descriptive or text or textual values so let me write down over here the definition dim table contains at tributes of measurement stored in fact table so here you can see here is the fact values item id time id branch ids kind of numerical value so always infectable they stored numerical values and foreign keys from the dimension table as you can see and in dimension table it it contains attribute of the measurement so item id is here and here is the primary key but what is the item name what is the brand name um what is the you know region all those kind of uh the details it's stored in the dimension table so in short if you wanna just understand dimension table contains all descriptive or text or alpha numerical values but in fact table is always fact values like some amounts some ids like that uh let's take a look some more detail about fact and dimension table um so in fact table uh as i said uh affectable mainly consistent of business fact and foreign key that refers uh primary key in the dimension tables right as you can see here uh in and a dimension level can consist many descriptive attributes that are text uh textual fields um like here you see name brand name um owner name honor uh branch name so let's take a look some more detail um uh yeah so for load a load side let's see how it loads right so dimension table has to load first so dim table first load why because as you can see here we in dimension table we do have primary key which is unique number uh and then all the details right but fake table contains the foreign key so without without any primary key or the data available in dimension double we cannot load a fact table because fact table contains the all foreign keys so it is a reference integration right uh so referencing degree uh integrity uh so it's like without parent child cannot come right so same way uh without any data we cannot you know generate any foreign keys for uh for foreign keys for the fact table so like uh let's say this is name table and if you load one two three records so this is uh foreign uh so sorry this is the primary key for the dim table and this is factable so second load uh fact tables first name table and in fact table we do have four end keys for this d uh for this dim so f uh for fk 1 fk 2 fk3 so as you can see here is the reference into integrity so always dimension table loads first and then factable so hope this um clears what is factable in dimension table and this is very basic um understanding for data warehousing and many time interview time they ask you some questions regarding what is fact table dimension table and what kind of value it contains and also which kind of or what table loads first and why so those kind of you know interview related questions they may ask from this topic fact and dimension table thank you hope you like this video uh please subscribe my channel thank you you

Channel: Informatica Interview

Views: 20,601

Rating: 4.6835442 out of 5

Keywords: fact table, dimesion table, interview questions, informatica, ETL, database, datawarehouse, sql, what is fact table, what is dimension table, fact table vs dimension table, data warehousing

Id: 0CZyRiAhlDA

Length: 7min 24sec (444 seconds)

Published: Sun Sep 13 2020