DW Design

[cognostechie]

This is about DW design and I don't know which forum to post in so I am posting it here. If there is an appropriate forum for this then maybe the moderators can move it there?

Might sound like an easy question for some but I want to know how others are doing this. Having a few attributes at the transaction line level is very common especially fields like the following:

Description  - Let's presume there are some charges on top of the Product price and this is to capture a description for that. Yes, there would be a dimension for
this too but it can be edited at the line level so this Description would belong only to that line

Comments  -  Some text used in providing reasons for returns etc., additional description in addition to the return reason - return reason is already in a dimension   

Since the fact tables are only supposed to have keys and measures, where would these fields reside? I usually create another table and append the 'attr' word to the name of the Fact table which these are related to. So, if the Fact table name is F_Something then this would be in F_Something_Attr and then this table is joined to the Fact.

Good or bad? How are you doing this?

[Lynn]

I think the Kimball term for a dimension that is at the same granularity as the fact table is called a degenerate dimension. I have recently done a warehouse design where virtually every fact table has some left over goodies that simply don't belong in any other dimension or on the fact table itself. Generally these are relevant when doing some very low-level operational reporting but don't really lend themselves to analysis.

The approach we took is pretty much exactly what you described, although we tend to append "_details" to the fact table name, but I'm not sure that even really qualifies as a difference to your approach  :D

We may sometimes conform these detail degenerate dimensions to other fact tables when there is a reason to do so. If you think of the classic order header and order detail scenario, these may be two different fact tables because some metrics exist in both and the header measures are not aggregates of the detail measures. We may have the order header details degenerate dimension conformed to both the order detail fact and to the order header fact. For detail operational reporting it ensures that you can get all the details that go with a particular header.

Sometimes the hardest part is deciding when something belongs as a degenerate attribute or when it warrants becoming a dimension on its own. The things we ask ourselves to help decide are things like: does the element have applicability to other facts, and if so in what way; is there a reasonably discrete set of possible values vs free-form text; would there be value in constructing a hierarchy or grouping of the elements into something more meaningful for analysis; would it be used very often as a prompt/filter value such that a performance implication exists; etc.

Good conversation to have and always worth learning what others do!

[cognostechie]

Thanks Lynn. Now I know that I am not all that crazy !

Ahh .. The Order Header and Order Line, something I fiddled with for a while till some time ago. I decided to convert Order Header into Order Dimension since it has one line per Order and that way all attributes remain there so I don't have to create another table for the degenerate fields. The Order Line becomes Order Fact. That way the Order Dim can also be joined to Invoice Fact to trace Invoices for any Orders. Same approach for Invoice Header too.

[Lynn]

My scenario isn't really order header and order line, but the concept is similar. In my case there really are facts at the higher level granularity as well as at the lower level of granularity. The lower level facts are additive to the higher level, but the higher level facts do not break down equally to the lower level fact grain. Otherwise I'd have done just what you describe.

[cognostechie]

Ok ,so it seems that the Order Header is considered to be a Fact because of the presence of some measures in it ? In that case, my way of understanding it is a little different. The Order Header, in my case too, has measures like Freight which is not equally distributed among the lines. I somehow thought that the presence of measures would not qualify a table to be a Fact because in the Customer Dimension there are always some measures like Credit Limit. This is one thing where I would like to get opinions too. This understanding actually changes the whole scenario.