Q: I understand that when a particular dimension value is unique to each fact row (i.e., an invoice number perhaps) it should probably exist as a degenerate dimension in the fact table. Otherwise, that dimension table will contain as many rows as the fact table.
However, what about a situation in which a dimension table contains only one column aside from the surrogate key (i.e., a status of some sort) but is not unique to each fact row. In that case, should it be moved into the fact table as a degenerate dimension?
- Brian
Both cases are good applications for a degenerate dimension: a transaction identifier or a low-cardinality attribute that would otherwise need to be a very boring single-column table.
In all cases, the degenerate dimension is entirely optional.
Degenerate dimensions
A degenerate dimension is nothing more than a dimension attribute stored in a fact table, rather than a dimension table.
In all other respects, the degenerate dimension behaves exactly like any other dimension attribute. It can be used to provide context for facts, qualify queries, sort results, drive master-detail relationships, and so forth.The usual reason to create a degenerate dimension is simple: A dimension with one attribute really doesn't need to be in a separate table with a surrogate key.
Two important things to note here:
- Degenerate dimensions are optional
- There is nothing wrong with a 1:1 relationship between dimension table and fact table
Transaction identifiers as degenerate dimensions
For a transaction fact table, we usually set the grain at the lowest level of detail possible. Often that is an individual transaction such as an order line, invoice line or shipping line. (For a refresher on grain, see this post from 2009.)
Typically, these things have some kind of unique identifier which is carried into the dimensional model. Examples include an order line number, invoice line ID, transaction ID, etc. When these identifiers do not exist, we often create them, as I have discussed in a previous post.
Many designers opt to place these transaction identifiers directly into the fact table as a degenerate dimension. However, this is entirely optional. There is nothing wrong with a dimension table being in a 1:1 relationship with the fact table, as I have discussed previously.
Some designers forgo the degenerate dimension for transaction identifiers because that there will be other schemas where the attribute in question is also referenced. In many cases, our business intelligence software may function better if the attribute in question is located in exactly one place -- a shared dimension table.
As an aside, note that in these other schemas, the cardinality may be different. For example, the same invoice line may be referenced in multiple shipments or invoices.
Other degenerate dimensions
Not all degenerate dimensions are transaction identifiers. They may simply be dimension attributes with no other suitable home. The status code mentioned by the reader may be an example. Another example appeared in a previous Q&A post: Reason Overpaid.
Once again, the use of the degenerate technique is entirely optional. Many designers are comfortable placing the attribute in the fact tables, while others opt for dimension tables with one attribute.
Notice that a dimension table with one attribute does not require a surrogate key for slow change purposes. However, without the surrogate key, the attribute value would be stored in the fact table anyway, as a foreign key reference to a single column table!
Placing the attribute in a separate table may help with our BI tools, for the same reason given above. Database administrators may also prefer the option because it makes it easier to configure a star-join optimizer. Adding a surrogate key may reduce the size of the foreign key that will be stored in fact table rows.
Learn More
For more information on this topic, see the following posts:
- Degenerate Dimensions, ETL and BI (October 15, 2010) Discusses the use of a low-cardinality attribute as a degenerate dimension, and also looks at junk dimensions.
- Creating Transaction Identifiers for Fact Tables (October 17, 2011) Addresses creation of transaction identifiers when source systems do not provide them.
- Accumulating Snapshots (October 1, 2010) Discusses another situation where dimension table may have the same number of rows as the fact table, or perhaps even less.
- Rule 1: State Your Grain (December 9, 2009) Defines the concept of grain, and discusses the best practice of setting it at the lowest level of detail possible.
You can help support this blog by using the links here to order a copy from Amazon.com.
