0

I know there are three types of facts, and I've read that Transactional-Fact tables have fully additive facts which are the most useful type, but can non-additive facts be there as well? Or even semi-additive facts for that matter.

I'm asking this because my teacher had this in one of her presentations:

"While non-additive facts are not stored in fact tables, it is important not to lose track of them. For many processes, ratios are critical measurements without which a solution would leave much to be desired. Non-additive facts should be documented as part of the schema design."

If they can't be stored in there, how can they be documented as part of the schema design?

jruivo
  • 447
  • 1
  • 8
  • 22

1 Answers1

1

It isn't correct to say "... are not stored in fact tables", there are circumstances in which it is desirable to store them.

For example, I recently worked on a data warehouse which had three dates - order, activation and completion. Those dates were related through dimensions, but the fact measures included days-order-to-activation, days-activation-to-completion, and days-order-to-completion.

Best practice would be to derive these measures in the BI tool. In this case, you would document the calculation of the day measures, to demonstrate how the requirement was met from existing data values.

In our recent example, however, these were KPI-level measures, critical to the business. Rather than have people calculating them (possibly differently) in Excel, Tableau, PowerBI, etc., we chose to implement those measures in the fact table.

They were documented as non-additive, because the sum(days-order-to-completion) is meaningless, although it is worth noting that the minimum, maximum and average values ARE meaningful in this case.

Ron Dunn
  • 2,971
  • 20
  • 27