Designing Dimension Tables with Keys, Attributes, and Hierarchies

Hello! This module moves from deciding what a fact row represents to designing the dimensions that make those facts useful for analysis. We’ll begin with an ordinary dimension table: how to give each row a warehouse-controlled key, retain its source identity, and expose descriptive attributes and hierarchy levels that people can filter and group by.

The goal is a design you could hand to an ETL developer and a report author without leaving either to guess what “property,” “region,” or a key value means. Estimated study time: about 40 minutes.

Start with what one dimension row describes

A dimension supplies context for a measurement. In the property-sale star schema below, PropertySale holds sale-related measurements, while surrounding tables describe the time, property, branch, buyer, and other circumstances of a sale.

The diagram identifies candidate columns, but it does not, by itself, settle every grain question. Suppose your fact table has one row per recorded property sale. Before designing PropertyForSale, decide whether its lowest-level member is a physical property, a listing of that property, or a version of a listing. Those are different things: a property might be listed more than once, and a listing’s description might change.

For our worked design, assume that the source assigns an identifier to each listing, and that a sale refers to exactly one listing. We will design one dimension row to describe one listing at one recorded version of its attributes. The version qualification leaves room to preserve historical descriptions when required; we are not choosing a change-handling policy yet.

This gives you an immediate test for every proposed column: Does this attribute have one well-defined value for this listing version? Property type and the listing’s city can pass that test. A list of every promotion used to market the property may not. The dimension’s grain is the contract that keeps its descriptive columns coherent.

Give the row two different kinds of identity

Watch this short demonstration before choosing keys. It shows why a source identifier that works for one dimension row can fail when that same business object has two historical rows.

A useful dimension keeps these identities separate:

• Surrogate key: A unique value assigned by the warehouse to this dimension row. property_key is the dimension’s primary key and appears as a foreign key in the sale fact table.
• Retained source key: The identifier supplied by the operational source, such as source_property_id. It helps the load process find the corresponding listing and trace data back to its source. It is an attribute of the dimension, not its primary key.

If you integrate two feeds, both might contain a listing numbered 42. Retain source_system alongside source_property_id so that those values are distinguishable. Even the pair must not automatically be declared unique across all dimension rows: if you preserve two versions of one listing, both rows retain the same source identity but have different surrogate keys.

For example:

Here, 701 and 745 identify different rows describing the same source listing. 702 belongs to another source altogether. A fact referring to the first version must join on property_key = 701, not merely on source_property_id = 42. How the appropriate version is selected during loading is a later design decision; the key structure makes that selection possible.

The Kimball Group reading establishes this distinction and the dimension table’s basic shape.

The source key is worth keeping precisely because it has a different job. In ETL terms, it supports source-to-target matching and lineage; the surrogate key supports the fact-to-dimension relationship. Keeping both avoids making either do work it cannot reliably do.

Make attributes useful to an analyst

A dimension is not just a key-translation table. Its attributes supply the values used in report labels, filters, and GROUP BY clauses. Compare a report offering only property_type_code = 'APT' with one offering property_type_name = 'Apartment'. Retaining the code may help reconciliation with the source; the description makes the result intelligible without a lookup outside the model.

For PropertyForSale, a compact design sketch might be:

Keep names role-specific. The property’s city describes where the listed property is. A Branch dimension’s city would describe where the branch is. Both could be legitimate ways to analyze the same sale, but calling them simply “city” in a reporting layer would invite a misleading result.

Codes and names serve different purposes. Keep a code where it identifies a category reliably, but provide a readable label alongside it. Where names can repeat—for example, two cities with the same name—do not treat the name alone as an identifier. Define codes and their scope clearly; if source-specific codes need reconciliation, the load process must establish the common location identities before you promise consistent regional reporting.

Put stable hierarchy levels on the row

Assume each listing has one city, each city belongs to one region, and each region belongs to one country under the agreed reporting geography. Then a listing row can carry separate city, region, and country columns. An analyst can group sale prices by region without navigating additional geography tables.

This is deliberate duplication: many listing rows may repeat the same region name. In an operational model you might normalize that name into one region record. In an analytical dimension, a wide, descriptive row often makes the intended query easier to write and understand. The load process, however, must maintain consistent codes and labels. Denormalization is not permission for one region code to acquire conflicting names.

The hierarchy also needs a reality check. Do not automatically treat postal_code as a level between property and city; that would require the business’s postal codes to map cleanly to one city in the reporting geography. Likewise, property_type_name is a classification attribute, not a level of the location hierarchy.

Explicit levels support an equally explicit query. If each sale fact row has one property_key, a report can group sales by the property’s region with one fact-to-dimension join:

SELECT
    p.country_code,
    p.country_name,
    p.region_code,
    p.region_name,
    SUM(f.selling_price) AS total_selling_price
FROM property_sale_fact AS f
JOIN dim_property_for_sale AS p
    ON p.property_key = f.property_key
GROUP BY
    p.country_code, p.country_name,
    p.region_code, p.region_name;

Grouping by the codes as well as the labels protects the report when labels are duplicated. The query presumes selling_price is a sale-level amount that can be summed across the selected sales; the dimension provides the grouping context, not the measurement.

A design you can review before building

You now have enough to describe a dimension without committing to database-specific DDL. A concise design contract for the worked example is:

dim_property_for_sale contains one row per property listing per recorded attribute version. property_key uniquely identifies the row. source_system and source_property_id retain its source identity. Descriptive attributes include property type and address. The agreed reporting-location hierarchy has separate country, region, and city codes and names, with each listing assigned to one city for that version.

Before accepting that contract, review it from three perspectives. ETL needs to know whether (source_system, source_property_id) identifies a listing consistently and how source locations are mapped. Reporting needs clear labels and dependable hierarchy membership. Fact loading needs exactly one intended property_key for each sale event—not every dimension row that happens to share a source identifier.

Those checks are more informative than counting the columns. A dimension with many attributes is useful if each has clear meaning at the declared grain; a small dimension is unsafe if its key or hierarchy permits ambiguous joins.

Conclusion

A sound dimension table separates row identity from source identity. Its surrogate primary key identifies the precise descriptive row a fact should use; its retained, source-qualified key supports matching and traceability. Readable attributes give facts analytical meaning, while explicit, validated hierarchy columns make grouping predictable.

Next, we’ll examine what changes when hierarchy levels are not consistently shaped—particularly ragged and parent-child relationships—and when flattening alone no longer describes them well.