Nullability & Performance

View previous topic View next topic Go down

Nullability & Performance

Post  DBADS on Thu Sep 30, 2010 6:13 am

Hello,

I have a number of measures inside a fact, but they are not loaded together. i.e.: measure 1 can be loaded in today's load but measure 2 can come 2 days later (will be loaded as an update for the existing record inside the fact)
This implies that all my measures inside the fact will be nullable. Will this affect the performance? And how severe will be the effect is any?


Thank you

DBADS

Posts : 7
Join date : 2010-08-22

View user profile

Back to top Go down

Re: Nullability & Performance

Post  ngalemmo on Thu Sep 30, 2010 12:25 pm

Nulls won't affect performance. But it does raise questions over the design of the fact table itself.

Unless this is an aggregate or snapshot of some sort, you generally want to design fact tables that reflect the business process and capture individual events in toto as they occur. For example in a sales cycle, orders, shipping and invoicing are individual independent events that occur at different times. Each event would be reflected in its own fact table.

Any fact that would incorporate all three would need to be an aggregate of some sort. Designing a warehouse that only maintains such an aggregate not only overcomplicates the load processes, but limits the level of analysis that can be performed.

Not knowing what you are modeling, it is not clear wither such a late arriving update to measures is appropriate or not.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Nullability & Performance

Post  Jeff Smith on Thu Sep 30, 2010 2:43 pm

Would a better approach be to create a fact table with a single measure and a "measure dimension" that contains a description of the measure? This way you never have nulls in the fact table. The down side is that the fact table can dramatically increase in size.

Updating every row of a big fact table would take a long time - some databases may have a hard time completing the update.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Nullability & Performance

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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