What are Dimensional Tables..?

Dimension Tables are also tables and contains descriptive information used to describe the rows in the fact table. look at the following fact table row....

There is no product Name, no staff name, no Customer name. With out  having these names, how do you describe the qty_sold as 1 and unit_sales_price as 200..?  We can't..!

By  looking prod_id (3), customer_id (5) in the product dimension and Customer dimension, we will get product name as "Santoor" and customer name  as "Jhon". So, by using dimensional tables we can describe fact rows with information such as product name, customer name etc..

What is a Fact Table?

A fact table is also a Table in the database contains Facts (Measures such as quantity sold, revenue, account balance)  and foreign keys. ( These keys will be used to connect to various dimension tables such as Product, Time, Customer, Staff  to get information like product name, staff name, customer name etc..)

Take a look at the following Image.. The yellow shaded columns are fact columns and the remaining columns are key columns , which will be used to connect to dimension tables..

What is Datawarehousing Architecture..?

Datawarehousing inclueds two Phases 1) ETL 2) Reporting

The following Image shows both Phases ....
 

The following Diagram shows a Detailed ETL PHASE....

We have datasourses ODS1, ODS2, ODS3 and so on... These contain Operational Data or Real time data... These systems may locate in various placess across geographical regions..

We extract the data from these data sources and we keep the data in Staging area for a while and we apply  Data Transformations such as Data formatting, Data Cleansing, Data aggregations..Then we load the transformed data from Staging area into Datawarehouse,

A Datawarehouse is just a Database contains Fact tables and Dimension Tables to form Star Schema..