Degenerate dimensions in SSAS
This post is about a quirk in Analysis Services (version 2008 is used for this article) that can be quite annoying (and difficult to debug) if you’re not aware of it. The next steps describe how you can reproduce the issue with a simple example.
First, create a new table:
CREATE TABLE sales (product VARCHAR(50), product_nr INT, price MONEY)
We will use this table for creating a fact table (measure = price) with a degenerate dimension (product) in SSAS. The key for the degenerate dimension is product. The column product_nr is only added to illustrate more clearly what is happening.
Add some data: