A fact table can often have measures which are basically trivial. For example Orders.NumOrders will probably be "1" for every real record (synthetic records representing "no order" may populate this field with a zero). Assuming that such a measure even needs to be instantiated in SQL at all, I see two ways to do so:

  1. Include an INT column which is always populated with "1" (except for the exception described).
  2. Include an AS 1 or AS CASE WHEN [IsARealOrder] THEN 1 ELSE 0 END calculated column. Obviously there's no point in persisting this calculation, or you're back to #1.

The advantage of approach #1 is simplicity. The advantage of approach #2 is self-documentation, and reduced record size. Given that databases tend to be IO-bound, it seems that adding a trivial calculation to save four bytes x millions or billions of rows could be worthwhile.

I've used both approaches in the past, but have not done any profiling. Are there any important reasons to favor one over the other?

Kimball's toolkit implies method #1, but does not address the matter specifically.

  • 1
    Never been a very big of famous people saying you should do it this or that because it rhymes with their patented methodology. I spontaneously like #2, because it uses less space, and it probably performs better in the query plan as well - for starters, you won't have to include the amount column in a covering index. Aside from that, is there an option to just let your BI tool (SSAS?) just perform a COUNT(*) operation, in those cases where the calculated column is always 1? Aug 11 '14 at 19:49
  • Agreed, I'm just throwing it in there. Agreed again, it's less data to read. Sure, end tools have their own ways to account for this kind of thing, but I find there's usually at least a little business logic - e.g., the "N/A" customer should have a zero for NumCustomers - which I'd like to model at the lowest possible level. Aug 11 '14 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.