Identifying Logical Dimension Tables

Dimension tables contain attributes that describe business entities such as Customer Name, Region, Address and Country.

A business uses facts to measure performance by well-established dimensions, for example, by time, product, and market. Every dimension has a set of descriptive attributes. Dimension tables also contain primary keys that identify each member. Unlike logical fact tables, which are different from physical fact tables in relational models, logical dimension tables behave very much like relational dimension tables.

Dimension table attributes provide context to numeric data, such as being able to categorize Service Requests. Attributes stored in the dimension table could include Service Request Owner, Area, Account, and Priority.

Dimensions in the business model are conformed dimensions. If a specific data source has five different instances of a specific Customer table, the business model should only have one Customer table. To achieve conformance, all five physical source instances of Customer are mapped to a single Customer logical table, with transformations in the logical table source as necessary. Conformed dimensions hide the complexity of the Physical layer from users, and enable combining data from multiple fact sources at different grains. Conformed dimensions also enable federating multiple data sources.

In the business model, use business keys for dimension and level keys rather than generated surrogate keys. Use Customer Name with values like Oracle instead of Customer Key with values like 1076823. Using business keys in the business model ensures that all sources for that dimension can conform to the same logical dimension table with the same logical key and level key.

Although generated surrogate keys can still exist in the Physical layer, where they are useful for their query performance advantages on joins, you typically do not have surrogate key columns in the Business Model and Mapping layer at all.