Degenerate Dimensions

Degenerate dimensions, also called fact dimensions, are standard dimensions that are constructed from attribute columns in fact tables instead of from attribute columns in dimension tables.  This is because useful dimensional data is sometimes stored in a fact table to reduce duplication, especially when you have a very large fact table.

Think of a Sales fact table that contains a PurchaseOrder field and a CarrierTrackingNumber field.  In theory, you could create a dimension table that uses the same key information as the Sales fact table and move the other two attribute columns, PurchaseOrder and CarrierTrackingNumber, to that dimension table.  However, you would be duplicating a significant portion of data and adding unnecessary complexity to the data warehouse to represent just two attributes as a separate dimension.  Instead, create a fact dimension, which is easy to do is SSAS.

Note to use fact dimensions in SSAS you must have a primary key on the fact table.  And you may want to make this dimension using ROLAP to save space.

Fact dimensions are frequently used to support drillthrough actions because the drillthrough action in SSAS requires that you select the attributes from a dimension.  So if you users want to see certain fields when they do a drillthrough, you must have those fields in a dimension.  As an example using the Sales fact table mentioned above, a user may want to search through that table for a particular Purchase Order.  Once you add the fact dimension to the cube (and probably hide it from the users), you can include any of its attributes in the drillthrough action definition.

A user may request a bunch of fields to see via drillthrough that are in the fact table but not in a dimension, such as order address, ship to address, timestamp fields, etc.  When that happens I ask the user to use the source system instead (preferably via the application that uses the source data), but sometimes they don’t have access to the source system, only the cube.  In that case, degenerate dimensions come in handy.  But realize this could greatly increase cube processing time.

An alternate approach would be to call an SSRS report via the Reporting Action instead of using a drillthrough action.

More info:

Dimension Relationships

Degenerate Dimensions in Datamarts

Kimball Design Tip #46: Another Look At Degenerate Dimensions

Data Warehousing: Degenerate Dimensions

Dimensional Modeling: Junk vs Degenerate

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in Dimensions, SQLServerPedia Syndication, SSAS. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>