Non-additive information

general discussion
1

Hi All,

I just have a general question and I’d appreciate if you can provide your thoughts on this.

I have a fact table called Orders. This information stored not only quantity, sale etc but also order details such as order number, order date, price etc. Is this a good practice? As soon as I looked at this table, I thought that we sholdn’t put non-additive information in a fact table, instead it should be stored in what is called a “Degenarate” dimension. What do you gous advice? Should I separate this information from the fact table or create a view called “Order detail” off of the fact table and use it as a dimension in the Data Warehouse?

Thanks in advance for your thought.

PeaceOut (BOB member since 2011-07-09)

2

You should create an Orders table and Order lines, two fact tables at different granularity.

Order number should appear on both facts. Order date should be used to create a key for the calendar dimension.
Price and line item detail shoud be stored as part of the order line fact.

Mak 1 :uk: (BOB member since 2005-01-06)

3

Keep in mind that price rarely is a fact/measure in the BusinessObjects world/universe. Price * volume = Revenue is a measure.Price typically is a dimension or detail obhjects (from a universe persepctive).

Andreas :de: (BOB member since 2002-06-20)

4

Agreed, I should have added that price is usually an attribute (non additive) of a product dimension.

Mak 1 :uk: (BOB member since 2005-01-06)