Dimension Table

Dimension Table

Dimension tables contain attributes that describe fact records in the fact table. Some of these attributes provide descriptive information; others are used to specify how fact table data should be summarized to provide useful information to the analyst.

The dimension table rows provide meaning to the rows in the fact table. Each dimension table describes a particular business entity or aspect of the fact table entries. Typical dimension tables include time, geography, customers, and products. Dimension tables should consist of three types of columns. The first is a newly generated primary key (PK) for each row in the dimension table. The second is the original PK from the source system, and the third group consists of any number of additional columns that further describe the business entity. Columns in a dimension table can be used to categorize the information into hierarchical levels.

The following diagram illustrates a subsection of the AdvantureWorkDW sample database, in which the FactResellerSales fact table is related to two dimension tables, DimReseller and DimPromotion. The ResellerKey Column in the FactResellerSales fact table defines a foreign key relationship to the ResellerKey primary key column in the DimReseller dimension table. Similarly, the PromotionKey column in the FactResellerSales fact table defines a foreign key relationship to the PromotionKey primary key column in the DimPromotion dimension table.