ETL database query example

You can use database queries to extract product data from policies. The following example queries the PolicyCenter database to extract policy data from Personal Auto policies.

The following SQL statement on the PolicyCenter database returns all instances of Collision (PACollisionCov) coverage in Personal Auto.

An instance of Collision coverage is a PersonalVehicleCov entity with PatternCode of PACollisionCov. The ChoiceTerm1 column contains values like opt_320 that do not provide actual values for the Collision deductible. Although the Data Dictionary does not show ChoiceTerm1 on PersonalVehicleCov, it is defined in the entity (PersonalVehicleCov.etx). The PersonalVehicleCov entity is stored in the database in the pc_personalvehiclecov table.

The following SQL statement gets the identifier, pattern code, and choice term from all Collision coverages in the database:

SELECT
ID, PatternCode, ChoiceTerm1
FROM pc_personalvehiclecov t4
WHERE t4.PatternCode = 'PACollisionCov';

The PACollisionCov pattern code does not convey the name of the coverage. The opt_319 choice term does not provide name or type of the selected coverage term option.

The Product Model Loader creates database tables that provide access to names of coverages, coverage term names and types, coverage term option names and values, and other product model objects. The tables are in the PolicyCenter database, making the information available to database queries in an ETL process. You can write a query joining the policy data with this product model data. You can store the results of this query in a data warehouse or data store.

The following SQL statement joins the ETL data with the policy data to get the value of the coverage term options. The statement selects all instances of Collision coverage and joins with ETL product model tables. You now have the currency and option values selected for the Collision deductible.

SELECT
t4.ID, t4.PatternCode, t3.PatternID, t2.PatternID, t1.Value, t1.Currency
FROM pc_personalvehiclecov t4
JOIN pc_etlclausepattern t3 on t4.PatternCode = t3.PatternID
JOIN pc_etlcovtermpattern t2 on t2.ClausePatternID = t3.ID
JOIN pc_etlcovtermoption t1 on t1.CoverageTermPatternID = t2.ID
AND t1.PatternID = t4.ChoiceTerm1
WHERE t4.PatternCode = 'PACollisionCov';