insight_admin No Comments

Major enhancement covering functionality, Data Store and Source DB and Performance and Design enhancements.

Functionality

Policy Termination (Mortality and Withdrawal) Study

  • The Mortality and Withdrawal Rate tables were merged into Policy Termination Rates (based on the Mortality Structure with the addition of PolicyTermReasonGroup attribute).  The existing mortality rates migrated into the ClntFactors,ClntMap and StdRate table with PolTermRsnGrp = Mortality, while Withdrawal rates migrated into ClntRates with PolTermRsnGrp = Withdrawal.
  • Policy Mortality and Policy Withdrawal applications have been discontinued.  This functionality is now accessible in PolicyTermination application with PoliTermRsnGrp = Mortality or Withdrawal.
  • Drill-through is now available naturally on all real measures.  The separate, hidden Withdrawal ExpdRates dimension has been eliminated.
  • Rate Tables can be set up separately for Mortality and Withdrawal, or for both.
  • Other custom termination reasons can be introduced, although unless the”Other” reason is used, conflicts will arise with EBS.

Policy Amounts Change

  • A new Policy Amounts Change application has been added to calculuating increases and decreases in contract premium and contract benefit amounts.  To supplement this, ContractPremiumYr, ContractBenefitYr exposure measures have been added to the Policy Amounts Snapshot.

Fund Movements

  • Fund Transaction Rate measure has been added to FundMovement using the expsosure measure FundValueYr that has been added to Policy Values Snapshot.
  • Fund Transaction Type now has 3 additional generic attributes for grouping fund transaction types.

Unitization Processes

  • New Business and Renewal Expenses Allocated amounts can now be specified by per coverage count or per premium or both.  These amounts will be unitized separately per coverage count or per coverage contract premium.  Resulting unit factors will be summed to give a single  expense transaction.
  • Multiple unitized transactions can now be created for NBExpense, RenExpense, OthExpense, NII, by adding additional, incremented StdIncomeTypes to StdIncomeType table (e.g. NBExpense2, NBExpense3, ..etc), mapping them into AccountIDs into srcAccount, and added additional ramounts into the aggregate amounts tables identified with the new StdIncomeType.
  • External Unitized  transactions (calculated by Client) can now be loaded alongside transactions calculated in the Insight unitization processes.

Deferred Ceded Admin Data

  • For monthly admin loads, ceded admin data can now be loaded later in the month if it is not available for load when the main admin data is available.  For ceded amounts, this may require the study processing to be deferred until after the ceded amounts are loaded.

Configuration Parameters

  • The date parameters have been simplified to 5 dates, one initial and 4 backcast start (Policy, UW, Reserve and Fund).  Other optional or derived dates have been eliminated.
  • Parameters have been added to avoid loading ceded studies where not required and ceded admin data is loaded.  Ceded studies can contribute significantly to processing and data volumes but may be unseen and unused.

DataStore – Source DB

Policy Termination Rate Tables

  • The Mortality and Withdrawal Rate tables were merged into Policy Termination Rates (based on the Mortality Structure with the addition of PolicyTermReasonGroup attribute).  The existing mortality rates migrated into the ClntFactors,ClntMap and StdRate table with PolTermRsnGrp = Mortality, while Withdrawal rates migrated into ClntRates with PolTermRsnGrp = Withdrawal.

Expected Rates Tables

  • The have been renamed for clarity, moving the study reference to the front of the table name, and renaming Incd and Term to ClmIncd and ClmTerm.

Expense Alloication tables

  • Now have additional AmountPrem field for contract premium unitization.

PolicyAmounts and CededPolicyAmounts

  • SourceID has been introduced for audit process.

Additional Transaction Type Attributes

  • FundTransType and AgentTransType now have 3 additional generic attributes for grouping transaction types.

Performance and Design

1) The Insight ETL processes have been redesign to allow direct load from Source DB into DW fact tables eliminating the staging tables.  Subsequent DW processing is to be carried out directly on the fact tables.

  • This has been completed for all fact tables except for those related to the Reserve and Allocation process due to the complexity of those processes.
  • The coverage keys are now accessed at the start of the Insight ETL when fact tables are picked up from Source DB.  This required the creation of a historical coverage key view directly from DW.
  • The CoverageKey process introduces a constraint that the Source DB must be on the same server as the DW DB. Where this is not possible, an additional process and database (DWKey) has been introduced  to copy the coverage keys from DW into DWKey which is to reside on the same server as the Source DB.
  • The above changes have Staging packages being eliminated in the ETL process.
  • The DW packages have been split into single process packages to give greater control, increasing the number of packages.

2) Policy related cubes have been merged into a single cube (X Policy). This avoids the need for linked measures between movements and earnings (and other transactions.

3) Independent Fact Reloads.

  • The ability to reload facts independently of dimensions have provisionally been developed for some fact tables.  This is still an Insight Development process with manual steps and not for Client Release.  However the expansion and productization of this functionality will offer clients alternative implementation paths avoiding full reload as well as key maintenance functionality giving a simple approach to resolving data issues identified after data has been loaded into the DW.

Leave a Reply

Your email address will not be published. Required fields are marked *