2

I am building an EDW based on Kimballs approach. I have a parent/child relationship in our source system (Order/Line Items). The fact table I have is defined at the line item grain. Business would like to be able to slice and dice this data by additional order level attributes (ie. Shipmethod, order type, etc.). I'm planning on creating a Order Dimension instead of adding these attributes directly to the fact table. I don't want add these to the fact table directly cause adding all the possible attributes will make this fact table very wide.

So the question is... is it ok design to have a Order Dimension that has attributes to describe the Order? This dimension would not have any measures as all the measures will still be in the fact table. This is just additional data that describes the fact.

Thanks!

user3776554
  • 119
  • 4
  • That would seem reasonable to me. – BobC Feb 17 '17 at 01:34
  • Correct. This is how star schemas are built: dimensions hold attributes, facts hold measures. – momobo Feb 17 '17 at 07:53
  • Its good to get it into a dimension but the dimensional modelling approach would go a slightly different route: I'll try and put an answer together to describe it. – Rich Feb 20 '17 at 08:43

2 Answers2

2

The challenge with the above link Kimbalgroup design tip 95 is that there may be attributes that belong at header level fact. For example, order total amount is higher level of measure compared to order line table's grain. Measure attributes at the header level should not be combined with measure attributes at the line level.

A possible solution is to create multiple fact tables. The 1st header fact table shall include all measures at the header while the line table shall include measures or transactions at the line level. So all attributes are at the correct grain, and we can bring the header's natural key(s) to the line table (similar to the degenerate dimension). We do have to bring to include all the header dimensions to avoid having to join the two large fact tables.

This way, there is no direct foreign key between the parent to child fact able, and the grain of the attributes are preserved correctly at each level.

LinR
  • 21
  • 2
1

This is a very common dimensional modelling dillemma.

You're right that you shouldn't add these directly to the order line-level fact table. They are dimensional attributes in that they'll be used to filter down the fact table when querying. However, if you plonk them all in an Order dimension you'd likely end up with a very large dimension, especially if you had an Order # to include, and any analysis of things like order type or ship method would have to go via it. If you were modelling an order level fact, order type/ship method would be held in dimensions, possibly within an order details dimension created as a 'junk' dimension (but that's another question).

The Kimball Group's recommended approach is to have the order line level fact 'inherit' the dimensions you would have otherwise used in the order level fact, so they're available for analysis directly, rather than having an 'order' dimension. Note, the order # can be a 'degenerate dimension' in the fact table in this instance, as all the interesting information about the order has been captured in other dimensions.

The Kimball Group have a useful article about this here:

http://www.kimballgroup.com/2007/10/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/

in which the order dimension idea's flaws are highlighted and the recommended approach described.

Rich
  • 2,207
  • 1
  • 23
  • 27