Hello Guys;

I’m studying computer science on my third year. we are asked to develop a project based on data warehousing and business intelligence, using java language and postgresql.

the main thing that is expected from us, to create fact and dimension tables from existing tables and then fire simple queries on the fact and dimension tables.

now though i have read about fact and dimension tables alot, still i’m confused, i have some question if you are welling to help.

Below are questions:

1> What is the use of fact and dimension tables? is it because we have some tables that are not related with each other (stand alone tables), but all of them have a primary key, and fact table is used to relate these tables together (acting as a relation to a situation where the tables are related with each other with many to many relationship)?
2> Is the meaning of using fact table in above situation to act as a index in order to speedup the data retrieval or if any other special purpose of fact table please let me know.
3> the important thing is the real life example of creating fact and dimension tables, here below i will give me some tables, and see if you can help me with creating fact table for them:

Students Table
s_no (pk) ------ s_name---------s_add
1------------- - AA ----------- aa
2--------------- BB ----------- bb
3 ------------- - CC ----------- cc
4 --------------- DD ----------- dd

Teacher Table
t_no(pk) ------------t_name---------t_add
1 -------------- XX ----------- FF
2 -------------- YY ----------- GG
3 -------------- ZZ ----------- HH

Class Table
c_no(pk) ------------c_name ----------c_location
1 ------------------ -- MM --------------LL
2 --------------------- NN --------------VV
3 --------------------- OO --------------RR
4 --------------------- SS --------------YY


Fact Table

s_no (fk)--------t_no(fk)------------- c_no(fk)
1
2
3
4
---------------- 1
---------------- 2
---------------- 3
--------------------------------------- 1
---------------------------------------- 2
-----------------------------------------3
-----------------------------------------4

please correct me with the mistakes that i have. by the time i don't understand how to create fact table, i'm sure i won't be able to complete the project.

besides the actual tables can remain as dimension table as i think and we only need to create the fact table so that if any query fired on multiple tables we can use the fact table as references ?

am i right, please correct.

if you want to give me any links, please make sure the link at least have an illustrated example as above.

thanks for reading and attentions

regards,

Rafi

Dear, if you've time to read I can send you a book about Data warehouse.
About your questions
Say we've business man who has super market with a lot of branches...
The business man interested to know net profit by year\region\product\sales persons
Fact table: is th point of interest the decision maker needs to know (simple business definition about it)
Fact table: net profit
Dimension table: the categorization used to spread out an aggregate measure aka fact (net profit) to reveal its constituent parts.
Dimensions tables: Year, Region, Product and Sales persons.

Send me email @ <EMAIL SNIPPED> if you need a book regarding this.

hey thanks for the help.

i'm trying to figure out how to mark a thread as solved.