In this post we are writing more on factt table in star schema because we believe that clear concept of fact table and its creation is critical to understand the difference between OLTP Relational Database’s logical model and then its de-normalization to create Star schema, which have fact table in the centre.

We found some useful writings on fact table its creation and in-depth analyses of the same. Please read these and add more about your view to help other readers to clearly understand the process of denormalization for star schema. Now lets enjoy what has been written about star schema;

The star schema is data modeling technique used to map decision support data into relational database. In effect, the star schema creates near equivalent of multidimensional database schema from the existing relational database. The star Schema was develop because existing relational modeling technique, ER and normalization did not yield database structure that served advance data analysis requirements well.
Star schema yield an easily implemented model for multidimensional data analysis while still preserving the relational structure on which the operation database is built. The basic star schema has four components : facts, dimensions, attributes and attributes hierarchies.[1]

Massive De-normalization: STAR Schema Design

The STAR schema design was first introduced by Dr. Ralph Kimball as an alternative database design for data warehouses. The name STAR comes directly from the design form, where a large fact table resides at the center of the model surrounded by various points, or reference tables.

The basic principle behind the STAR query schema is the introduction of highly redundant data for high performance. With a STAR schema, the designer can simulate the functions of a multidimensional database without having to purchase expensive third-party software. Kimball describes de-normalization as the pre-joining of tables, such that the runtime application does not have to join tables.

At the heart of the STAR schema, the fact table is usually comprised entirely of key values and raw data. A fact table is generally very long and may have millions of rows.

At first glance, it is hard to believe that this representation contains the same data as the fully normalized database. The new fact table will contain one row for each item on each order, resulting in a tremendous amount of redundant key information. Of course, the STAR query schema is going to require far more disk space than the 3NF database.

The STAR schema would most likely be a read-only database due to the widespread redundancy introduced into the model. Also, the widespread redundancy would make updating difficult, if not downright impossible. [2]
Source:
1- By Peter Rob, Carlos Coronel, Keeley Crockett – Database Systems
2- 2- Oracle Data Warehouse Tips by Burleson Consulting

See an Example on this topic