0

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?

borjab
  • 11,149
  • 6
  • 71
  • 98
  • 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? –  Nov 05 '12 at 21:05
  • Google the term "degenerate dimension." – Bill Nov 05 '12 at 21:19

1 Answers1

2

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.

Davos
  • 5,066
  • 42
  • 66
  • Thanks, this is exactly what I need; fact dimension. The issue I have now is I have created a fact dimension but when I process the cube I am getting errors for "duplicate key found", which in my case is OK as I can have multiple of the same Order ID. How can I adjust SSAS to allow this? Currently it fails to process the cube due to this. I have set the relationship type to "regular" and also the MemberNamesUnique to false but no luck. –  Nov 06 '12 at 00:28
  • 1
    You still need a surrogate key for each row in the fact table. The OrderID is not a good candidate key so don't use that. Also don't create a primary key on the fact table from all the foreign keys to the dimension tables, use a new one just for the fact table. – Davos Nov 06 '12 at 04:25