Narrowing the fact table ... or not?

View previous topic View next topic Go down

Narrowing the fact table ... or not?

Post  antonkov on Tue Jun 26, 2012 2:34 am

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?

antonkov

Posts : 5
Join date : 2012-06-18

View user profile

Back to top Go down

Re: Narrowing the fact table ... or not?

Post  Jeff Smith on Tue Jun 26, 2012 9:42 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Narrowing the fact table ... or not?

Post  antonkov on Tue Jun 26, 2012 11:54 am

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.

antonkov

Posts : 5
Join date : 2012-06-18

View user profile

Back to top Go down

Re: Narrowing the fact table ... or not?

Post  Jeff Smith on Tue Jun 26, 2012 4:28 pm

No one ever got fired for using best practices.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Narrowing the fact table ... or not?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum