Fact and Dimension Table

[c_skyscraper]

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

[Ramy Mahrous]

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.

[c_skyscraper]

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.

[vinodh123]

Hi All,
I am new bie to Datawarhousing.I am asked to create the star schema.I don't have any idea about that.
They gave 40 table name only without column.

* drugs - Supports in-house drug sales. This is the list of available drugs.
* drug_inventory - Supports in-house drug sales. Each row represents a "lot" of purchased drugs.
* drug_sales - Supports in-house drug sales. Each row represents a drug sale, i.e. an invoice line item.
* drug_templates - Supports in-house drug sales. Each row represents a shortcut for a common dispensation of a drug.

We created some structure for that tables.I mentioned belows

Drugs

Drug_id
Drug_name
Quantity
Price
Expiry_date

Drug_Inventory

Inventory_id
Drug_name
Drug_id
Quantity
Price
Manufacture_date
Expiry_date
Supplier_id

Drug_sales
Sales_id
Sales_date
Drug_Name
Expiry_date
Quantity
Price

I don't have idea about drug_templates .

Please how to create the dimension table and fact tables