Q: There is a reporting need for cost-price comparison in various currencies. The current exchange rates may not be available while loading currency-wise cost-price in the fact table. In such cases historical rates are used for calculation and a flag is set. Whenever current exchange rates become available (in a dimension table), all the records with flag set in the fact table should get updated physically (no history required). Query runtime calculation of cost-price is not preferable due to large amount of data. What should be done in such a scenario to avoid unnecessary data processing of fact table data?

Evan Levy's Answer:

When I've seen these types of problems, it sometimes makes sense to normalize theprocess and store the date along with the currency type in the fact able and then a dimension table with a simple lookup of date and currency value. With the appropriate indexes, this should be a very quick (single I/O) lookup during the query.It's important to implement the physical database design correctly to prevent full-table scans.

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