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
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access