How do I handle this scenario related to degenerate/fact dimension in SSAS cube?

0
votes

I have an SQL Server 2008 SSIS/SSAS Datawarehouse cube that I am publishing, in this cube I have the following:

Dimensions
----------
Company
Product
Sales Person
Shipped Date (time dimension)

Facts
-----  
Total Income
Total Revenue 
Gross

For the above, I have setup primary (PK) / surrogate (SK) keys for the dimension/fact data referencing.

What I would also like to include is things such as Order Number or Transaction Number which in my mind would fit in a fact table as the order number is different for every record. If I were to create a order number dimension it does not make much sense as I would have as many order numbers as I would facts.

Right now, when I load my fact data I do multiple Lookups on the dimensions to get the surrogate keys, I then pass in the fact data and also include these Order Number and Transaction Number varchar columns when I load my fact data but they cannot be used as they are not something you can aggregate on so they don't show up in my SSAS, only columns of numeric data type do for the fact table (total income, total revenue, etc).

Is there something I can do to make these available for anyone using the Cube to filter on?

1
sql-server-2008ssisssasdata-warehousedegenerate-dimension
Just to add to this, I want the ability to present columns such as "invoice number" in my SSAS Cube, but I don't think it makes sense to create a "invoice number" dimension.. is there a way to allow this type of data to be available in a Cube without a Invoice Dimension? – user1709091
Google the term "degenerate dimension." – Bill

1 Answers

2
votes

Invoice number is a perfect candidate for a degenerate dimension. It can be included in your fact table, yet not be linked to any dimension. These sorts of numbers are not useful in analytics except when you want to drill down and investigate and need to trace back a record to your source system, and they don't have any sensible "dimensionality". Kimball calls them degenerate dimensions. In SSAS they are called "fact dimensions" http://msdn.microsoft.com/en-us/library/ms175669(v=sql.90).aspx

You are essentially putting an attribute column into the fact table, rather than a dimension table.

One important tip. In dimensional modelling, yes you are trying to do a star schema with perfectly formed dimensions, but don't be afraid to ignore the ideal when it comes to practical implementation. Kimball even says this, sometimes you need to break the rules, with the caveat that you test your solution. If it's quick then do it! If conforming to the Kimball ideal makes it slower or adds unecessary complexity, avoid it.