Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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


Posted by leased line on 17 November 2011

It provide good help.

Leave a Comment

Please register or log in to leave a comment.