We are working on a data mart for a risk analytics business. Now there are certain facts that are calculated through complex algorithms and printed on the reports. In order to achieve good query performance, which report on these facts, (a majority of the queries do report on these facts) we are thinking of calculating them and storing in a fact table rather than doing the calculation on the fly. Is this approach correct?


Chuck Kelley's Answer: Absolutely, if - and only if - the data in the fact table is the same granularity. If they are not the same granularity, then you need to create an aggregate table (as another fact, if you desire!) and store the values there. I find it quite amusing when we make the query tool do a query many times, when we can do it once and store it in the database. Yes, it takes up more disk space, but I will trade off disk space for processing (and thus query) speed every time!

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access