BUILDING AN ACTUARIAL DATABASE Dos and Donts WHY

BUILDING AN ACTUARIAL DATABASE Do’s and Don’ts

WHY A DATABASE? Review long term needs Is Excel a database? Separate the data from the calculations and reports It’s all about relationships Auditors will find you Focus on actuarial specific issues

PLANNING Who is going to use the system (it’s not about you!) Understand your data flow (Business logic out) What will be your tables Set up relationships between the tables Indexing and primary keys (say Google) Normalize Controls …

KEY CONTROLS Inventory (record count, reserves etc. ) Micro calculation / auditing capabilities Trending, reasonableness Output transfer – nothing lost … worth the price of admission

IMPORTANT CONTROLS Using lookup tables – Data integrity Properly defining fields – Dates Forced relationships preserve data integrity

STRATEGIES Path independence Function separation – data (admin vs. user), code, report Compacting … automatically Object naming conventions (no spaces) Display progress (status bar) Document, document, and document

THE MAGIC OF QUERIES Standard Query Language (SQL) Select, Delete, Update, Append, Make Table Make them dynamic for real power Use global functions (with care)

DATA INPUT Usually most critical part Controls and Monitor “Caching” data … Indexed Annuity

CALCULATION AND AUTOMATION Code vs. Macros Beware of external references Power of VBA

OUTPUT Separate from data Minimize reports … dynamic Make an actuary happy … pivot table friendly Avoid cross tab queries in a report No smart reports

FINAL ANALYSIS Functionality vs. Resources Study