SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Posted by leased line on 17 November 2011

It provide good help.

Leave a Comment

Please register or log in to leave a comment.