Recent Articles
Categories
About Us
Seopromo articles provides information on different categories,seopromo articles started on the year 2004 , Its a five year old website, we provide informative articles. Dimensional Modeling – Fact Table
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
| Field Name | Type |
| Loc_Id | INTEGER (4) |
| Loc_Code | VARCHAR (5) |
| Loc_Name | VARCHAR (30) |
| State_Id | INTEGER (4) |
| Country_Id | INTEGER (4) |
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) |
| Field Name | Type |
| Country_Id | INTEGER (4) |
| Country_Name | VARCHAR (50) |
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