Relations between fact tables in a data warehouse

Question

I'm designing a data warehouse and I keep reading that there should be no relations between fact tables.

I'm sure everyone here is well aware that businesses are full of facts which are inherently related to each other, so how is one supposed to represent these relationships without relations between fact tables?

To give just a couple of simple scenarios, what if I have refund facts which each relate to a purchase fact? Or if I have page view facts (a large volume of) a small percentage of which relate to error facts?

Answer 1

In the examples you give, the smaller "fact table" could just be a dimension of the larger fact table, which would remain a fact. Like so:

• Sale = fact. Refund = dimension of sale
• Page view = fact. Error = dimension of page view

It's fine to have large dimension tables. This is actually pretty common in your typical retail star schema. For example, customer table or product table are typically dimensions of sale, and can be quite large.

See these star schemas for example.

In terms of further reading - Kimball Group is often considered one of the best authorities on DW I believe. I have taken one of their courses and read their books. You could check out their site for more information. Hope this helps.