Data Warehousing Objects
Fact tables and dimension
tables are the two types of objects commonly used in dimensional data warehouse
schemas.
Fact tables are the large
tables in your warehouse schema that store business measurements. Fact tables
typically contain facts and foreign keys to the dimension tables. Fact tables
represent data, usually numeric and additive, that can be analyzed and
examined. Examples include
sales, cost,
and profit.
Dimension tables, also
known as lookup or reference tables, contain the relatively static data in the
warehouse. Dimension tables store the information you normally use to contain
queries. Dimension tables are usually textual and descriptive and you can use
them as the row headers of the result set. Examples are
customers or products.
Fact Tables
A fact table typically has
two types of columns: those that contain numeric facts (often called
measurements), and those that are foreign keys to dimension tables. A fact
table contains either detail-level facts or facts that have been aggregated.
Fact tables that contain aggregated facts are often called summary tables. A
fact table usually contains facts with the same level of aggregation. Though
most facts are additive, they can also be semi-additive or non-additive.
Additive facts can be aggregated by simple arithmetical addition. A common
example of this is sales. Non-additive facts cannot be added at all. An example
of this is averages. Semi-additive facts can be aggregated along some of the
dimensions and not along others. An example of this is inventory levels, where
you cannot tell what a level means simply by looking at it.
Dimension Tables
A dimension is a
structure, often composed of one or more hierarchies, that categorizes data.
Dimensional attributes help to describe the dimensional value. They are
normally descriptive, textual values. Several distinct dimensions, combined
with facts, enable you to answer business questions. Commonly used dimensions
are customers, products, and time.
0 comments: