Fact table

Fact table

In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema, surrounded by dimension tables.

Fact tables provide the (usually) additive values which act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their "grain". The grain of a fact table represents the most atomic level by which the facts may be defined. The grain of a SALES fact table might be stated as "Sales volume by Day by Product by Store". Each record in this fact table is therefore uniquely defined by a day, product and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records. These "affiliate dimensions" allow for additional slices of the independent facts but generally provide insights at a higher level of aggregation (region is made up of many stores)

Example

If the business process is SALES, then the corresponding fact table will typically contain values representing both raw facts and aggregations such as:
* "$12,000", being "sales for New York store for 15-Jan-2005"
* "$34,000", being "sales for Los Angeles store for 15-Jan-2005"
* "$22,000", being "sales for New York store for 16-Jan-2005"
* "$40,000", being "sales for Los Angeles store for 16-Jan-2005"
* "$21,000", being "average daily sales for Los Angeles Store for Jan-2005"
* "$65,000", being "average daily sales for Los Angeles Store for Feb-2005"
* "$33,000", being "average daily sales for Los Angeles Store for year 2005""average monthly sales" is a measurement which is stored in the fact table. The fact table also contains foreign keys from the dimension tables, where time series (e.g. dates) and other dimensions (e.g. store location, salesman, product) are stored.

Fact TableThe centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.Fact tables store different types of measures like additive, non additive and semi additive measures.

Measure Types * Additive - Measures that can be added across all dimensions. * Non Additive - Measures that cannot be added across all dimensions. * Semi Additive - Measures that can be added across few dimensions and not with others.

A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables).

In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called as Factless Fact tables.

* Steps in designing Fact Table Identify a business process for analysis(like sales). * Identify measures or facts (sales dollar). * Identify dimensions for facts(product dimension, location dimension, time dimension, organization dimension). * List the columns that describe each dimension.(region name, branch name, region name). * Determine the lowest level of summary in a fact table(sales dollar).


Wikimedia Foundation. 2010.

Игры ⚽ Нужно сделать НИР?

Look at other dictionaries:

  • Table wine — In the United States, table wine is used as a legal definition to differentiate standard wine from stronger (higher alcohol content) fortified wine or sparkling wine [ [http://www.eosvintage.com/glossary.html EOS Wine Glossary ] ] .In the… …   Wikipedia

  • Fact-value distinction — The fact value distinction is a concept used to distinguish between arguments which can be claimed through reason alone, and those where rationality is limited to describing a collective opinion. In another formulation, it is the distinction… …   Wikipedia

  • Table bridge — A table bridge is a moveable bridge in which the deck moves along the vertical axis. Hydraulic pillars under the bridge raise the bridge deck to allow barge traffic to pass beneath it. In contrast to a lift bridge, where the deck is pulled… …   Wikipedia

  • Table of vowels — This table lists all the vowel letters of the International Phonetic Alphabet. Where vowels appear in pairs, the vowel to the left of the bullet (•) corresponds to an unrounded vowel and the vowel to the right of the bullet corresponds to a… …   Wikipedia

  • Table of thermodynamic equations — For more elaboration on these equations see: thermodynamic equations. The following page is a concise list of common thermodynamic equations and quantities:Variables{| class= wikitable style= width: 25em; + Constants !style= width:3em; | kB… …   Wikipedia

  • Table of muscles of the human body — This is a table of muscles of the human anatomy. There are approximately 640 skeletal muscles within the typical human, and almost every muscle constitutes one part of a pair of identical bilateral muscles, found on both sides, resulting in… …   Wikipedia

  • Table of political parties in Europe by pancontinental organisation — The majority of major political parties in Europe have aligned themselves into one of the pan European political organisations listed below. Nine of these organisations have been legally recognized by the European Union as political parties at… …   Wikipedia

  • Dimension table — In data warehousing, a dimension table is one of the set of companion tables to a fact table. The fact table contains business facts or measures and foreign keys which refer to candidate keys (normally primary keys) in the dimension tables.… …   Wikipedia

  • Early-arriving fact — In the Data Warehouse practice of ETL, an early fact or early arriving fact [ [http://www.rkimball.com/html/designtipsPDF/KimballDT57EarlyArriving.pdf Kimball, Ralph. Design Tip #57: Early Arriving Facts. August, 2004.] ] denotes the detection of …   Wikipedia

  • World War II Casualties, Table — ▪ Table military civilian estimated country killed, died prisoners deaths due total of wounds, or wounded or to war deaths in prison{1} missing{2} Allied Powers Belgium 12,000 76,000 88,000 Brazil 943 4,222 1,000 British Commonwealth 373,372… …   Universalium

Share the article and excerpts

Direct link
… Do a right-click on the link above
and select “Copy Link”