Dimensional Modeling - Fact Table
In a Dimensional Model, Fact table contains the measurements or metrics or facts of business processes. If your business process is Sales, then a measurement of this business process such as "monthly sales number" is captured in the fact table. In addition to the measurements, the only other things a fact table contains are foreign keys for the dimension tables.

Online Transaction Processing (OLTP), the database is designed to achieve efficient transactions such as INSERT and UPDATE. This is very different from the OLAP design.

Unlike OLAP, normalization is very important to reduce duplicates and also cut down on the size of the data. our OLTP schema may look like this:

Locations Table

Location Dimension Table Schema

Field Name	Type
Dim_Id	INTEGER (4)
Loc_Code	VARCHAR (4)
Name	VARCHAR (50)
State_Name	VARCHAR (20)
Country_Name	VARCHAR (20)

In order to query for all locations that are in country 'USA' we will have to join these three tables. The SQL will look like:

SELECT * FROM Locations, States, Countries where Locations.State_Id = States.State_Id AND Locations.Country_id=Countries.Country_Id and Country_Name='USA'

Back to Introduction click here
Tags:
dimensional database design, datawarehousing concepts, basic structure data warehousing, data modelling examples, database ,oltp,data modelling,data mining concepts