Narrowing the fact table ... or not?

[antonkov]

I am modeling a fact table that has transaction line item grain. The business driver for the project is to allow fact history tracking. The facts are about bookings (pre-sales). Unlike real, invoiced transactions, any booking attribute including keys and qty can change (e.g. customer calls and changes ProductA qty3 with ProductB qty5 and src. system just updates the booking order). Again, no aggregates in fact, analytical performance is not the main priority, history is.
(Dimensional history is done with SCD2 and fact history is calculated with qty deltas and reversals in ETL - it is not relevant, just to explain the business driver)

The dilemma is around accommodating some 100+ factual attributes (text notes, details of Product customization, ship-to address, delivery terms, etc) that don't seem fit into other dimensions .

My temptation is to call these attributes a degenerate dim and to host them in the fact where they logically belong. From a modeling perspective though, it is a bad practice, but mainly for performance reasons. On the other hand, transactional reporting doesn’t need to retrieve massive amount of data to be concern about the disk reads.

Given the mission goals (history tracking, transactional reporting) should I worry about the width of the fact table and create a separate Booking_Line_Item_Dim(1:1) to unload the attributes from the fact or is it still acceptable to have them in a fat fact table?

[Jeff Smith]

I would break up the 100 plus attributes into multiple Junk or mini dimensions. Having the 100 plus attributes in a dimension table that is 1:1 with the fact doesn't get you anything.

[antonkov]

Having the attributes in a separate table, even if it is in 1:1 to the fact, makes the fact records physically smaller, which should translate into faster reads and better performance. On the ETL side though, it translates into extra table to maintain in parallel with the fact.

If I spin out more dimensions that are not business driven, I will still have smaller fact records, but more joins and more complex ETL. On the positive side, I will comply with best practices and get an extra performance that I am not really concerned about. Hard to decide.

[Jeff Smith]

No one ever got fired for using best practices.

[Sponsored content]