CEDE 10.0 Database Reference
What's New (previous versions) in Touchstone Databases
Revision History for the CEDE Database Reference > What's New (previous versions) in Touchstone Databases
 What's New in Touchstone® 2021 (9.0) Databases

Touchstone 2021 (9.0) includes enhancements to AIR models, flexibility (in modeling), marine modeling, terrorism modeling, and licensing by business unit. It also offers expanded database flexibility and loss import capabilities. This release also includes a technical preview of the new Next Generation Models (NGM) framework. AIR has modified the existing Touchstone database schema to integrate the NGM capabilities in Touchstone.

AIR has made changes to the database schema to accommodate the following features and enhancements for Touchstone 2021.

The following database changes have been introduced in the Touchstone 2021 release:

Some of the changes apply to new features and enhancements in Touchstone Re.

New Tables

AIR has added the following database tables in Touchstone 2021.

Tables Details
AIRExposureSummary.dbo.tLossAnalysisCustomFrequencyInfo

Stores information for the detailed loss analysis run with custom event frequency. This table contains the custom frequency configuration such as the type of event, custom catalog model code, and the unique sequential identifier associated with an analysis.

AIRExposureSummary.dbo.tLossAnalysisCustomFrequencySet

Stores information about the catalogs that contain custom, user-defined event footprints and the associated custom event frequency for a detailed loss analysis.

AIRExposureSummary.dbo.tLossAnalysisCustomFrequencySetXref Stores the results of analysis run with 'Custom Event Frequency' in a Detailed Loss analysis.
AIRReference.dbo.tCargoPacking

Stores information about the cargo packing details.

AIRReference.dbo.tCargoProtection

Stores information about the protection of cargo package.

AIRReference.dbo.tContentDamageability

Stores detailed damage information for each content.

AIRReference.dbo.tModelGeographyXref Stores the geographic location identifiers that correspond to each model.
AIRReference.dbo.tSalvagePotential Stores potential damage level associated with each salvage potential code. This table contains information to evaluate the potential loss and the corresponding damage level to determine the total loss for policy conditions.
AIRReference.dbo.tSpecieStorage

Stores code that represent types of storage used in special construction structures.

AIRReference.dbo.tSprinklerType

This table contains the sprinkler identifier for each sprinkler type.

AIRReference.dbo.tFWMExposureArea2010 Stores the possible Florida Wind Mitigation (FWM) 2010 exposure areas. Used during the Hazard analysis.
AIRReference.dbo.tFWMWindSpeedRegion2010 Stores the possible Florida Wind Mitigation (FWM) 2010 range of speed. Used during the Hazard analysis.
AIRResult.dbo.tLossAnalysisCustomFrequencyInfo Stores the results of a detailed loss analysis run with custom event frequency. This table contains the custom frequency configuration such as the type of event, custom catalog model code, and the unique sequential identifier associated with an analysis.
AIRResult.dbo.tLossAnalysisCustomFrequencySet Stores information about the catalogs that contain custom, user-defined event footprints and the associated custom event frequency anaysis for a detailed loss analysis.
AIRResult.dbo.tLossAnalysisCustomFrequencySetXref Stores the results when "Custom Event Frequency" is selected from the Detailed Loss Analysis Options.
AIRResult.dbo.tRRRR_LOSS_DimReCurrencyExchangeRateSetConversion

Stores information about the exchange rate set that corresponds to each currency in the application.

AIRResult.dbo.ByLocationByFacultative Stores the reinsurance results by location from a detailed loss analysis.  This table is created when the user specifies "Save Loss By" and selects Facultative reinsurance type in the analysis Output Options.
AIRResult.dbo.ByLayerByFacultative Stores the reinsurance results by layer from a detailed loss analysis.  This table is created when the user specifies "Save Loss By Layer" and selects Facultative reinsurance type  in the analysis Output Options.
AIRResult.dbo.ByLocationByTreaty Stores the reinsurance results by location from a detailed loss analysis.  This table is created when the user specifies "Save Loss By Location" and selects a reinsurance treaty type  in the analysis Output Options.
AIRResult.dbo.ByLayerByTreaty Stores the reinsurance results by layer from a detailed loss analysis.  This table is created when the user specifies "Save Loss By Layer" and selects a treaty type  in the analysis Output Options.
AIRResult.dbo.ByContractByTreaty Stores the reinsurance results by contract from a detailed loss analysis.  This table is created when the user specifies "Save Loss By Contract" and selects a treaty type  in the analysis Output Options.
AIRUserSetting.dbo.tLossCustomFrequency

This table stores information about the custom, user-defined custom event frequency selected during the loss analysis.

Custom Frequency template file allow the user to customize events that direct the Loss analysis engine to apply a specified events, AIR model, product, catalog, and event years directly to calculate the Ground-Up losses.

The loss engine scales the entire Ground-up distribution, not just the mean loss, and applies all policy conditions to the modified losses. It does not cap the modified losses at the total insurable value (TIV). Any custom event frequency modification that is applied to losses from a peril applies also to losses generated by the respective sub-perils.

AIRProject.dbo.tLossAnalysisCustomFrequencySetXref

 Stores the loss analysis information associated with the custom frequency set.

AIRReference.dbo.tSequelImpactMMSData Stores information about the support codes for Gulf of Mexico (GOM) with area code and their corresponding AIR specific sequential identifier. For internal use only.
AIRReference.dbo.tSequelAirGOMArea Stores information about the SEQUEL Impact Offshore structure including the type and number of structures.

Modified Tables

AIR has modified the following database tables for Touchstone 2021.

Tables Details
AIRAddressServer.dbo.tZIP5 AIR has modified the centroid latitude and centroid longitude for approximately 45282 records.
AIRExposure.dbo.tLocFeature

AIR has added the following columns:

  • ContentDamageabilityCode
  • CargoPackingCode
  • CargoProtectionCode
  • SpecieStorageCode
  • SprinklerTypeCode
  • SalvagePotentialCode
AIRExposure.dbo.tReinsuranceEPCurve

This table applies to Touchstone Re. However, the database schema may contain tables common to both Touchstone and Touchstone Re installation. Removed the following columns:

  • ModelCode
  • EPLossUnadjusted
  • EPLossAdjusted
AIRExposure.dbo.tLayer

AIR has added the following attachment points for including additional financial terms in the contract layer.

  • AttachmentPoint2
  • AttachmentPoint3
  • AttachmentPoint4
AIRReference.dbo.tActivityType

AIR has added these 8 new activity types in both Touchstone and Touchstone Re for monitoring job requests.

  • CUSTFRQDEL: Custom Frequency Delete
  • CUSTFRQEXP: Custom Frequency Export
  • CUSTFRQIMP: Custom Frequency Import
  • ELT_TO_ULF: ELT To ULF Conversion
  • EPTOULF: EPTo ULF Conversion
  • EXT_PORT: Extract Portfolio Request
  • LEXPALZGRA: Upload Results to AnalyzeRe Graphene
  • LOSS_NGM: Detailed Loss NGM
AIRReference.dbo.tActivityTypeActivityStageXref

AIR has added PRE, PRO, and POST activity stages in both Touchstone and Touchstone Re for monitoring the status of submitted activities.

  • CUSTFRQDEL
  • CUSTFRQEXP
  • CUSTFRQIMP
  • ELT_TO_ULF
  • EPTOULF
  • EXT_PORT
  • LEXPALZGRA                                   
AIRReference.dbo.tAIRConstOccRegionConstXref

AIR has added 60 construction occupancy region codes in the range of 2701 to 2720 for the CAR, U.S., and Japan regions.

AIRReference.dbo.tAIRConstOccRegionOccXref

AIR has added 36 new occupancy codes in the range of 1001 to 1012 for the CAR region.

AIRReference.dbo.tAIRConstruction

AIR has added construction codes in the range of 2701 to 2720  for the Marine Cargo line of business.

AIR Construction Code AIR Construction
2701 Automobiles
2702 Break bulk
2703 Dry bulk
2704 Liquid bulk
2705 Consumables
2706 Temperature Controlled
2707 Electronics
2708 Chemical Products
2709 Explosives
2710 General Cargo
2711 Heavy Industry
2712 Light Industry
2713 Petroleum Products
2714 Pharmaceuticals
2715 Project Cargo
2716 Livestock
2717 General Specie
2718 Fine Art & Collectibles
2719 Cash In Transit
2720 Jewelers Block
AIRReference.dbo.tAIROccupancy

AIR has added occupancy codes in the range of 1001 to 1012 for the Marine Cargo Line of Business.

  • 1001 – Designated Facilities / Vaults
  • 1002 – Silo
  • 1003 – Liquid Tanks

  • 1004 – Gas Tanks
  • 1005 – Inside Warehouse at Port
  • 1006 – Containerized Inside Warehouse
  • 1007 – Containerized Stacked Outside
  • 1008 – Open Lot or Stockpiled Outside
  • 1009 – At Destination in Warehouse
  • 1010 – At Destination Retail
  • 1011 – Museums, Institutions, Public Buildings
  • 1012 – Retail and Private Buildings
AIRReference.dbo.tCoverage Removed the column IsLossCostTemplateEnabled.
AIRReference.dbo.tConvertCTCurrency AIR has added the CT Currency code 191 for the currency code BYN.
AIRReference.dbo.tConvertUPXPerilSet

AIR has added the following perils for the severe thunderstorm options.

UPXPeril PerilSetCode Description
PHL 1048576 Hail
PSW 8388608 Straight-Line Winds
PTD 2097152 Tornado
AIRReference.dbo.tPerilSet

Added the following perils:

  • CoversHail
  • CoversTornado
  • CoversStraightLineWinds
AIRReference.dbo.tCountryCurrencyXref

AIR has added 82 new countries and the corresponding currency codes. 

  • AF = AFN
  • AM = AM
  • AO = AOA
  • AZ = AZM
  • AZ = AZN
  • BA = BAM
  • BD = BDT
  • BI = BIF
  • BN = BND
  • BO = BOB
  • BO = BOV
  • BT = BTN
  • BW = BWP
  • BY = BYN
  • BY = BYR
  • CD = CDF
  • CV = CV
  • DJ = DJF
  • DZ = DZD
  • EG = EG
  • ER = ERN
  • ET = ETB
  • FJ = FJD
  • FK = FKP
  • GE = GEL
  • GH = GHS
  • GI = GIP
  • GM = GMD
  • GN = GNF
  • GY = GYD
  • IQ = IQD
  • IS = ISK
  • KE = KES
  • KG = KGS
  • KH = KHR
  • KM = KMF
  • KP = KPF
  • LA = LAK
  • LK = LK
  • LR = LRD
  • LS = LSL
  • LY = LYD
  • MA = MAD
  • MD = MDL
  • MG = MGA
  • MM = MMK
  • MN = MNT
  • MR = MRO
  • MT = MTL
  • MU = MU
  • MV = MVR
  • MW = MWK
  • MX = MXV
  • MZ = MZ
  • NA = NAD
  • NG = NGN
  • NP = NPR
  • PK = PKR
  • PY = PYG
  • RW = RWF
  • SB = SBD
  • SC = SCR
  • SD = SDD
  • SD = SDG
  • SH = SHP
  • SI = SIT
  • SL = SLL
  • SO = SOS
  • SR = SRD
  • ST = STD
  • SY = SYP
  • SZ = SZL
  • TJ = TJS
  • TN = TND
  • TO = TOP
  • TZ = TZS
  • UG = UGX
  • UY = UYU
  • UZ = UZS
  • VU = VUV
  • WS = WST
  • ZM = ZMK
AIRReference.dbo.tCurrency Added Belarusian Ruble currency code: BYN.
AIRReference.dbo.tExportType Added the export type code for Export Loss to AnalyzeRe Graphene (LEXPALZGRA).

AIRReference.dbo.tResultViewBy

Removed the columnIsLossCostTemplateEnabled.
AIRReference.dbo.tEventFilterTerrorismWeaponType

AIR has made the following modifications to the weapon types in the AIR Terrorism Model for the U.S. 

  • Added a new weapon type: 19 = Medium Truck (10-ton TNT).
  • Renamed the existing Delivery Truck (6-ton TNT) as Small Truck (6-ton TNT) weapon.
AIRReference.dbo.tPeril

AIR has added a new code for the straight-line winds peril and enabled the existing perils for tornado and hail. 

  • Straight-line wind = 8388608
AIRReference.dbo.tReinsuranceCountryPerilYear AIR has added several hundred records to include perils set code for each country and updated the Industry Exposure Data (IED)
AIRReference.dbo.tHazardAttribute

AIR has added the hazard attribute for:

  • 8 = tFWMWindSpeedRegion2010 
AIRReference.dbo.ModelCountryXref

AIR has added 7 new model codes for the following countries:

  • 1 = US
  • 2 = US
  • 3 = JP
  • 22 = US
  • 52 = JP
  • 75 = PE
  • 521 = US
AIRReference.dbo.tActivityType AIR has added the following activity code for identifying activities enabled with the NGM Loss Engine in a detailed loss analysis.
ActivityTypeCode ActivityType
LOSS_NGM Detailed Loss NGM
AIRReference.dbo.tConvertCL2ConditionDeductibleType

AIR has added the following deductible types to capture additional layer conditions in policies.

OldConditionTypeCode OldDeductibleTypeCode OldDeductibleType UPXDeductibleType NewTable NewColumn NewCode
10 12 Combined Deductible CB tLayer DeductibleTypeCode CB
10 13 Coverage Deductible C tLayer DeductibleTypeCode C
10 14 Combined With Time Deductible CT tLayer DeductibleTypeCode CT
20 12 Combined Deductible CB tLayerCondition DeductibleTypeCode CB
20 13 Coverage Deductible C tLayerCondition DeductibleTypeCode C
20 14 Combined With Time Deductible CT tLayerCondition DeductibleTypeCode CT
AIRReference.dbo.tDeductibleType

AIR has added a new location deductible type to apply on the coverages, similar to the 'Site Percent Of Loss' deductible.

DeductibleTypeCode DeductibleType
CPL Coverage Percent Of Loss
AIRReference.dbo.tOccLimitType

AIR has added a new occurrence limit type. This limit type is applied to the total loss for buildings, other structures, and contents. The layer coverage and combined deductibles will no longer be remapped to a blanket layer deductible and a coverage or combined deductible Sublimit. Therefore, you can import a 'Layer Coverage' or 'Combined Deductible' types as is.

 OccTypeCode Description OccLimitType
CT

A single limit amount is applied to the total loss for buildings, other structures, and contents. A separate limit value is entered for business interruption. Attachment points may also be specified.

Combined, Including Time Separately
AIRReference.dbo.OutputTable

AIR has added one more output table type to view the results of detailed loss analyses.

OutputTableSID OutputTable OutputTableTypeCode OutputDetailedAALCode Description
77 LOSS_ByTreatyGeo RNGM Null

Added as part of NGM Treaty by Geo

AIRCompanyLoss.dbo.tCompanyLossCatalog

Added a new column:

  • AreaDetail
AIRExposureSummary.dbo.tLossAnalysisCustomFrequencySet

AIR has removed the following column CatalogTypeTag.

AIRExposureSummary.dbo.tLossAnalysisOption

AIR has added the following columns:

Column Data type Description
IsSelectedCustomFrequencySet Bit

Indicates whether you want to run the detailed loss analysis with a custom frequency set template.

  • 1 = Yes
  • 0 = No
IsApplyAIRAnalysis Bit

Indicates whether you want to run the custom frequency set analysis with the default AIR Analysis mode. If unselected, the analysis will not apply AIR model results.

  • 1 = Yes
  • 0 = No
AIRProject.dbo.tECARingOption

AIR has added the following:

Column Data type Description
RemoveOverlap Bit Indicates whether overlapping rings, having the same latitude and longitude, should be removed or retained to produce overlapping exposure data.
  • 1 = Yes (remove overlapping rings)
  • 0 = No (retain overlapping rings)
FinancialPerspectiveFindHighestLossRing Varchar Stores results of a ring with highest loss accumulation in a geospatial analysis.

AIRProject.dbo.tLossAnalysisOption

AIR has added the following:

Column Data type Description

IsApplyAIRAnalysis

Bit

Indicates whether you want to run the custom frequency set analysis with the default AIR Analysis mode. If unselected, the analysis will not apply AIR model results.

  • 1 = Yes
  • 0 = No
AIRProject.dbo.tLossAnalysisOption

AIR has added the following columns to categorize and save loss analyses results.

Column Description Datatype
SaveSubPeril Save loss analysis results by sub peril. BIT
SaveTreaty Save loss analysis results by treaty. BIT
SaveFacultative Save loss analysis results by facultative program. BIT
AIRUserSetting.dbo.tModelInfo

To support with the disaggregation logic and FUWLA features: AIR has updated the following 9 records in the Model Info table to include the conventional weapon type updates for the Terrorism model for U.S. The existing M01 and M02 model entries are retained as PMV and any new entries are included with an updated Model dll version.

ModelCode Model PerilSetCode CatalogModelCode ModelDLLVersion ModelLOB
52 AIR Earthquake Model for Japan 8204  52  7.0.0 PP
3 AIR Japan Personal Accident 8204 52 1.1.0 PWC
62 AIR Typhoon Model for Japan 4353 60 7.0.0 PP
521 AIR Hurricane Model for the U.S. 257 521 1.0.0 PP
1 The AIR Terrorism Model 32 1 4.0.0 PP
2 AIR U.S. Workers Compensation Model 36 13 2.2.0 PWC
2 AIR U.S. Workers Compensation Model 36 14 2.2.0 PWC
2 AIR U.S. Workers Compensation Model 32 1 2.2.0 PWC
2 AIR U.S. Workers Compensation Model 26 11 2.2.0 PWC
AIRUserSetting.dbo.tCatalogType

AIR has renamed the terrorism weapon type from the Delivery Truck (6-ton TNT) to Small Truck (6-ton TNT).

AIRUserSetting.dbo.tLossCustomFrequency

AIR has removed the following column:

  • CatalogTypeTag
AIRUserSetting.dbo.tCurrencyExchangeRateSetConversion AIR has added BYN currency code and the current exchange rate to this table.
AIRIndustry.dbo.TblOffshoreStructures AIR has added 1328 records for new structures.
AIRResult

AIR has renamed the existing table names to store loss results generated using the NGM Loss Engine. The schema is same for both Touchstone and NGM Loss Engine and the tables in the left column are mapped with the newly created tables in the right column. Therefore, when you run analyses for one of the seven selected models in the Touchstone UI with the NGM Loss Engine setting, Touchstone dynamically generates the loss tables shown in the right column and populates loss results data only for these tables in NGM Loss Engine. 

Touchstone tables NGM Loss Engine tables
ByReinsuranceExposureAttribute ByTreatyExposureAttribute
ByReinsuranceExposureAttributeGeo ByTreatyExposureAttributeGeo
ByReinsuranceGeo ByTreatyGeo
AIRResult We now include standard deviation and maximum loss for all NGM analysis initiated with net financial perspective selection.
AIRResult

AIR has added loss columns such as loss, standard deviation, and maximum loss for the GroundUp, Gross, Recovery, and NetOfPreCat. Therefore, all reinsurance tables now contain the following loss key definitions for reporting the loss, standard deviation and maximum loss financial perspectives:

  • GroundUpLoss
  • GroundUpSD
  • GroundUpMaxLoss
  • GrossLoss
  • GrossSD
  • GrossMaxLoss
  • RecoveryLoss
  • RecoverySD
  • RecoveryMaxLoss
  • NetOfPreCatLoss
  • NetOfPreCatSD
  • NetOfPreCatMaxLoss
AIRResult.db.tLossAnalysisOption

AIR has added the following columns to categorize and view loss analyses results.

Column Description Datatype
SaveSubPeril Save loss analysis results by sub peril. BIT
SaveTreaty Save loss analysis results by treaty. BIT
SaveFacultative Save loss analysis results by facultative program. BIT

AIRResult.dbo.tLossAnalysisCustomFrequencySet

AIR has removed the following column:

  • CatalogTypeTag
AIRResult.dbo.tLossAnalysisOption

AIR has updated the Analysis Output Options in Touchstone to include the Custom Frequency Set, Save By Options, and minimum maximum logic code. To support these capabilities, we have added the following fields in the Touchstone database. 

Column Data type Description
IsSelectedVulnerabilitySet Bit

Indicates whether the custom vulnerability set is included in the analysis.

  • 1 = Yes
  • 0 = No
MinMaxLogicCode Bit Code that represents the minimum maximum core logic for NGM.
SaveSubPeril Varchar Indicates whether you want to save loss results by sub peril. 1 = Yes 0 = No
SaveTreaty Bit Indicates whether you want to save loss results by sub peril. 1 = Yes 0 = No
SaveFacultative Bit Indicates whether you want to save loss results by sub peril. 1 = Yes 0 = No

IsSelectedCustomFrequencySet

Bit

Indicates whether you want to run the detailed loss analysis with a custom frequency set template.

  • 1 = Yes
  • 0 = No

IsApplyAIRAnalysis

Bit

Indicates whether you want to run the custom frequency set analysis with the default AIR Analysis mode. If unselected, the analysis will not apply AIR model results.

  • 1 = Yes
  • 0 = No
AIRGeography.dbo.tRemodeledGeographyXref AIR has remapped the user-defined Geography SID with the 'Remapped Geography SID' field for approximately 2000 records.
AIRGeography.dbo.tGeoCountry AIR has added additional data for supporting the reverse geocode via address service for Japan and U.S regions.

Removed Tables

AIR has removed the following tables for the Non-catastrophe loss functionality beginning with Touchstone 2021.

Database Table
AIRExposureSummary and AIRResult tRRRR_NONCAT_AnnualEPSummary
AIRExposureSummary and AIRResult tRRRR_NONCAT_ByContract
AIRExposureSummary and AIRResult tRRRR_NONCAT_ByLayer
AIRExposureSummary and AIRResult tRRRR_NONCAT_ByLocation
AIRExposureSummary and AIRResult tRRRR_NONCAT_ByReinsurance                        
AIRExposureSummary and AIRResult tRRRR_NONCAT_DimContract
AIRExposureSummary and AIRResult tRRRR_NONCAT_DimLayer
AIRExposureSummary and AIRResult tRRRR_NONCAT_DimLocation
AIRExposureSummary and AIRResult tRRRR_NONCAT_DimReinsurance
AIRReference

tNonCatTemplateRule

AIRProject

tNonCatAnalysisOption

AIRUserSetting

tCustomLossCostTemplateRule

AIRUserSetting tLossCostAdjustmentTemplateRule
AIRUserSetting tDefaultNonCatAnalysisOption

Datatype Changes

AIR has modified the following columns in Touchstone 2021.

Table Column Previous Datatype Current Datatype

AIRResult.dbo.tRRRR_EC_ByAccumulator

Risks Integer Big integer
AIRExposureSummary.dbo.tRRRR_EC_ByAccumulator Risks Integer Big integer

AIRUserSetting.dbo.tLossModTemplateRule

YearBuilt

Small integer Varchar (8000)

AIRUserSetting.dbo.tLossModTemplateRule

Stories

Small integer

Varchar (8000)

 What's New in Touchstone®  2020 (8.0) Databases

The following database changes have been introduced in Touchstone 8.0:

Some of the changes apply to new features and enhancements in Touchstone Re.

New Tables

AIR has added the following database tables in Touchstone 8.0:

Table Details
[tUpgradeLog]

Stores information about upgrade scripts.

Added for several databases.

[AIRProject].[dbo].[tEXPMODActivityOption]

Stores the additional options used for the Exposure Modification analysis.

Touchstone generates the table for each analyses for which the user has modified the default perils on the Modify Assigned Perils UI in Touchstone. 

[AIRProject].[dbo].[tLossAnalysisFloodPlainOption]

Stores information about the source of flood plain options in detailed loss analyses.

Applicable only for US Model in Touchstone.

The Inland Flood Peril applies to precipitation events that cause inland flooding due to river overflow, as well as to overflow caused by inadequate drainage systems. Based on the region, user can select the flood plain options before running an analysis.

[AIRProject].[dbo].[tAnalysisSliceXref] Stores partition and slice references for each analysis options. 

System table, used internally in Touchstone.

[AIRProject].[dbo].[tLossAnalysisAdditionalOption]

Stores information about the additional financial settings for each loss analysis. Touchstone generates the table only for analyses for which the user has selected spatial correlation or coverage correlation options on the Next Generation Modeling UI in Touchstone. By default, these options are set as 'ON'.

Spatial correlation defines the risks associated in areas of similar condition such as same soil types or when the same builder has constructed the buildings. 

Coverage correlation defines the risks at policy coverage level such as buildings, other structures, contents, and time element.  

[AIRGeography].[dbo].[tblCountyMappingAreaCode] Stores valid scheme paths for each country and area code record.
[AIRGeography].[dbo].[TblSourceTargetMap_CAR] Stores grid identifier and weight type that corresponds to each source code.The table also stores 'look-up' area codes that the application uses for disaggregated locations in Caribean (CAR) and the weight for each location.
[AIRGeography].[dbo].[tReinsuranceGeographyTSXRef]

Stores the geographic reference mapping identifiers between Touchstone and Touchstone Re.

[AIRDQIndustry].[dbo].[tDBKeyAndIndex]

System table, used internally by the system to apply packages.

[AIRReference].[dbo].[tAggregateDeductibleType]

Stores codes that represent types of aggregate deductibles.

During a loss analysis calculation, the system uses the information in the table to fetch the codes of aggregate deductibles applied to a contract, layer or treaty.

[AIRReference].[dbo].[tAggregateLimitType]

Stores codes that represent types of aggregate limits.

During a loss analysis calculation, the system uses the information in the table to fetch the codes of aggregate limits applied to a contract, layer or treaty.  

[AIRReference].[dbo].[tMinMaxDeductibleType]

Stores codes that represent minimum maximum deductible types for location terms.

During a loss analysis calculation, the system uses the information in the table to fetch the codes of minimum maximum deductible policy logic corresponding to each location terms.

[AIRReference].[dbo].[tInjuryType]

Stores codes that represent types of injuries.

During the import of Worker's Compensation exposure data in Touchstone, the system uses the information in the table to map injury code for each employee in the location file. 

[AIRReference].[dbo].[tMinMaxLogic] Stores codes for MinMaxLogic types used in layer and location terms.
[AIRReference].[dbo].[tSEQUELImpactSubPerilGroupingXRef]

Stores codes, descriptions, and groupings of the peril codes generated in Touchstone.

Sequel Impact is a powerful exposure management tool offered by Sequel Business Solutions. AIR Worldwide and Sequel Business Solutions are both Verisk businesses.Sequel Impact users have the flexibility to trigger the transfer manually or with automated scheduling and can do this for individual accounts or entire portfolios. Touchstone provides a broad set of initial setup options to provide users with flexibility in how the data is brought into the application, including, but not limited to, defining where the data is stored and which exposure views it flows through into.

[AIRReference].[dbo].[tTimeofShift]

Stores codes that represent types of shift definitions.

Touchstone supports shift definitions for United States and Japan.

[AIRReference].[dbo].[tVulnerabilitySetFileType]

Stores codes that represent types of vulnerability set files in Touchstone.

[AIRReference].[dbo].[tWetFloodProofing] Stores codes that represent types of protection systems for flooding in a location. 
[AIRUserSetting].[dbo].[tLossVulnerabilitySet] Stores information about vulnerability loss sets.
[AIRUserSetting].[dbo].[tLossVulnerabilitySetDetails] Stores additional information about each vulnerability file sets.
[AIRUserSetting].[dbo].[tUserGeocodeMatchLevelMatchingSet]

Stores the AIR-specific and user-supplied geocode for each match level mapping set. 

Touchstone now supports the creation of user-supplied geocode match level in mapping sets. The mapping sets are used in import of exposure data, at location-level, and during regeocoding of exposure views. Touchstone generates the table to map user-supplied geocode provided in mapping sets with AIR-defined geocodes.

[AIRExposureSummary].[dbo].[tLossAnalysisAdditionalOption]

Stores the additional information used to generate results during a loss analysis.
Touchstone creates one row for each analysis.

Modified Tables

AIR has modified the following database tables for Touchstone 8.0:

Table Details
[tDBHistory]

Modified for several databases such as AIRGlobalSettingAIRExposureAIRCompanyLossAIRIndustryAIRSpatialAIRUserSetting.

Added columns: IsIgnorable, IsRolledBack, RolledBackScript 

[AIRExposure].[dbo].[tLocFeature] Added column: WetFloodProofingCode
[AIRExposure].[dbo].[tLayer]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: Limit3, Limit4, Deductible3, Deductible4, AggregateLimitTypeCode, AggregateLimit, AggregateAttachmentPoint, AggregateDeductibleTypeCode, AggregateDeductible.

[AIRExposure].[dbo].[tLayerCondition]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: Deductible3, Deductible4, AggregateLimitTypeCode, AggregateLimit, AggregateAttachmentPoint, AggregateDeductibleTypeCode, AggregateDeductible, LayerConditionName, LayerConditionTier.

[AIRExposure].[dbo].[tReinsAppliesToExp]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: RiskAggregateRetention, RiskAggregateLimit, RiskLimitReinstatement.

[AIRExposure].[dbo].[tLocation]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: DamageFunctionID, GeocoderAccountId.

[AIRExposure].[dbo].[tLocTerm]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: AggregateLimitTypeCode, AggregateLimit, MinMaxDeductibleTypeCode, MinimumDeductible, MaximumDeductible, AggregateDeductibleTypeCode, AggregateDeductible.

[AIRGeography].[dbo].[tGeoCountry]

Added columns: IsPremiumGeocodingEligible, ReverseGeocodeLevel

Modified the ReverseGeocodingTolerance values for Israel and New Zealand.

Countries Previous Column Value Current Column Value
Israel 16000 14000
New Zealand 2000 4000
[AIRUserSetting].[dbo].[tDefaultEnterpriseOption]

Added the following columns:

  • IsCustomLOBOn
  • CoversCustomLOB
  • DefaultClusterSetSID
  • Reinstatements
  • Reinsurance Premiums
  • AmountProvision
  • TimeProvision
  • CoversChinaMPCIBarley
  • CoversChinaMPCIPeanut
  • CoversChinaMPCIPotato
  • CoversChinaMPCISugar
  • CoversChinaMPCIRubber
  • CoversChinaMPCIDairyCattle
  • CoversChinaMPCIOtherCattle
  • CoversChinaMPCIBreederSow
  • CoversChinaMPCIPig
  • CoversChinaMPCIPoultry
  • CoversChinaMPCISheep
  • CoversChinaMPCILivestock
[AIRReference].[dbo].[tAIRConstOccRegionConstXref]

Added construction codes for the existing Australia (AUS) region: 204-206, 211-212, 231-238, 241-246, 251-260, 270-276, 2010-2013, 2015-2016, 2021-2022, 2031, 2131-2132, 2141-2142, 2150-2152, 2210, 2211, 2221, 2231-2233, 2241-2243, 2251-2253, 2261-2263, 2270-2276, 2281-2286.

Added construction codes for the existing Caribbean (CAR) region:138-141, 157, 159, 259, 260, 265- 267, 270- 276, 2010-2013, 2015-2016, 2021, 2022, 2031, 2131, 2132, 2141-2142, 2150- 2152, 2210-2211, 2221, 2231-2233, 2241-2243, 2251-2253, 2261-2263, 2270-2276, 2281-2286

[AIRReference].[dbo].[tAIRConstOccRegionCountryXref] Added country codes for the existing CAR region.
[AIRReference].[dbo].[tActivityType]

New activity type codes:

Activity Type Code Description
CMP_COPY Copy TSRe Company
CONVRT_ULF Convert ULF File
EXPMOD Exposure Modification
IDTEVEXPRT Geospatial Terrorism Ring to IDT Event Set Export
LOSS_VUL Vulnerability Modified Detailed Loss
VULMODDEL Vulnerability Modifications Delete
VULMODEXP Vulnerability Modifications Export
VULMODIMP Vulnerability Modifications Import
[AIRReference].[dbo].[tActivityTypeActivityStageXref]

New stages to support new activity types.

[AIRReference[.[dbo].[tAIRConstruction]

Added new construction code:

AIRConstructionCode ConstructionCategory AIRConstruction
141 Concrete Reinforced Concrete Frame with 2nd story Wood Frame or URM Addition
[AIRReference[.[dbo].[tAIRConstOccRegionOccXref]

Added occupancy codes for the existing Caribbean (CAR) region: 381-384

Added occupancy codes for the existing AUS region: 381-384

[AIRReference].[dbo].[tCatalogModelXref]

New mappings between model codes and catalog model codes:

CatalogModelCode ModelCode
8 8
15 15
27 25
51 51
[AIRReference].[dbo].[tCatalogType]

Removed a catalog type code: EXT.

[AIRReference].[dbo].[tCLFIndustryLOB] Added industry line of business for New Zealand.
[AIRReference].[dbo].[tCLFINFVersion] Added INFVersion for the catalog type code: HIST
[AIRReference].[dbo].[tCLFAreaLOBIndex]

Added the following:

CLFAreaLOBIndex ModelCode OldModelVersion NewModelVersion
1004 15 2.2.0 2.2.2
1004 15 2.2.1 2.2.2
1005 27 19.0.0 19.0.1
1005 27 19.1.0 19.1.1
1005 27 20.0.0 20.0.1
1005 27 20.1.0 20.1.1
1005 27 21.0.0 21.0.1
1005 27 21.1.0 21.1.1
[AIRReference].[dbo].[tConvertCL2Country]

Added the following rows:

OldAreaLevel1 OldAreaLevel1Name CountryCode CountryName Description OldAreaLevel1BriefName
283 Bonaire and Sint Eustatius and Saba BQ Bonaire and Sint Eustatius and Saba Caribbean Netherlands BQ
284 Curacao CW Curacao CW
[AIRReference].[dbo].[tConvertCL2Currency]

Added the following row:

OldCurrency ConversionRate CurrencyCode OldCurrencyCode Currency
Cuban Convertible Peso 1 CUC CUC Cuban Convertible Peso
[AIRReference].[dbo].[tConvertCTCurrency]

New currencies:

  • 48 = UF
  • 190 = CUC
[AIRReference].[dbo].[tCountry]

Added the following countries:

CountryCode Description
BQ Bonaire and Sint Eustatius and Saba
CW Curacao
INC India Crop
[AIRReference].[dbo].[tCountryCurrencyXref]

Added new currency codes for countries:

CountryCode Currency
BQ USD
CW ANG
[AIRReference].[dbo].[tCountryIndustryLOBXref]

Removed the existing industry line of business code for New Zealand.

Added the following industry line of business codes:

IndustryLineOfBusinessCode CountryCode
2 BQ
2 CW
3 BQ
3 CW
4 AT
4 BE
4 BQ
4 CH
4 CW
4 DE
4 DK
4 FR
4 GR
4 IE
4 IL
4 IT
4 LU
4 NL
4 NO
4 PT
4 SE
5 BQ
5 CW
25 NZ
[AIRReference].[dbo].[tCountryPerilXref]

Added the following rows:

CountryCode MainPerilSetCode CombinedPerilSetCode
AG 4 8196
AI 4 8196
AN 4 8196
AW 4 8196
BL 4 8196
BQ 1 4097
BQ 4 8196
CU 4 8196
CW 1 4097
CW 4 8196
DM 4 8196
GD 4 8196
GP 4 8196
HT 4 8196
KN 4 8196
LC 4 8196
MQ 4 8196
MS 4 8196
TC 4 8196
VC 4 8196
VG 4 8196
[AIRReference].[dbo].[tCurrency]

Added the following:

CurrencyCode Currency
CUC Cuban Convertible Peso
[AIRReference].[dbo].[tEPCurveType]

Added the following rows:

EPCurveTypeCode EPCurveType Description
Model Model Model EP Curve
Peril Peril Peril EP Curve
Zone Zone Zone EP Curve
ZonePeril ZonePeril ZonePeril EP Curve
[AIRReference].[dbo].[tEPTargetType]

Added the following rows:

EPTarget Type Code EPTargetType Description
RITRT  Reinsurance Treaty  Reinsurance Treaty
RPRG  Reinsurance Program  Reinsurance Program
[AIRReference].[dbo].[tExportType]

Added the following row:

ExportTypeCode ExportType Description
IDTEVEXPRT  IDT Export Geospatial Terrorism Ring to IDT Event Set Export
[AIRReference].[dbo].[tFinancialPerspective] Added several rows.
[AIRReference].[dbo].[tHazardAttribute]

Added the following rows:

HazardAttributeCode OutputTypeCode HazardAttributeTypeCode SubsetHazardAttributeTypeCode HazardAttribute HazardFieldName HazardFieldType
227 HAZTC VALUE NULL Florida Wind Mitigation – Exposure Area 2010  TCFWMExposureAreaCode2010 SMALLINT
228 HAZTC VALUE NULL Florida Wind Mitigation – Exposure Area 2010 TCFWMWindSpeedRegionCode2010 SMALLINT
229 HAZTC VALUE NULL Florida Wind Mitigation – Exposure Area 2010 TCFWMWindborneDebrisRegion2010 BIT
230 HAZTC VALUE NULL Florida Wind Mitigation – Exposure Area 2010 TCFWMHighVelocityWindRegion2010 BIT
[AIRReference].[dbo].[tIndustryLineOfBusiness]

Added the following:

  • IndustryLineOfBusiness = 25
  • Residential Land
[AIRReference].[dbo].[tIntensityAttribute] Added several rows.
[AIRReference].[dbo].[tMainPerilSubPerilXref] Added several rows.
[AIRReference].[dbo].[tModel]

Removed ModelCode codes: 230-237

[AIRReference].[dbo].[tModelCountryXref] Added several rows.
[AIRReference].[dbo].[tObjectType]

Added te following row:

ObjectTypeCode ObjectType Description
USERGEOMATCH_MAPPINGSET User Geocode Match Mapping Set User Geocode Match Mapping Set
[AIRReference].[dbo].[tReinsuranceUNICEDELOB] Added several rows.
[AIRReference].[dbo].[tReinsuranceUNICEDEUpgradeMapping] Added several rows.
[AIRReference].[dbo].[tRiskMeasure]

Added the following rows:

RiskMeasureCode RiskMeasure Description
ACC TRV+(Emp * ShiftPercent * InjuryPayout) Primary Property TRV + (WC Employees Count * Emp percent in the shift selected * Injury Payout as per Injury Type Selected)
WCP Emp * ShiftPercent * InjuryPayout WC Employees Count * Emp percent in the shift selected * Injury Payout as per Injury Type Selected
[AIRReference].[dbo].[tServiceEquipmentProtection]
ServiceEquipmentProtectionCode ServiceEquipmentProtection Description
3 Medium Protection for flood Indicator of flood protection of service equipment (mechanical, electrical and plumbing services) located in the floodable parts of the building
4 High Protection for flood Indicator of flood protection of service equipment (mechanical, electrical and plumbing services) located in the floodable parts of the building
[AIRReference].[dbo].[tReinsuranceUNICEDEUpgradeMapping] Added column: IsAlternateGeo.
[AIRReference].[dbo].[tUNICEDE2CountryLOBXref]

Added the following rows:

UNICEDE2LineOfBusiness CountryCode
6 BQ
6 CW
50 BQ
50 CW
51 BQ
51 CW
52 BQ
52 CW
53 BQ
53 CW
54 BQ
54 CW
55 BQ
55 CW
[AIRReference].[dbo].[tValidtationError]

Added the following rows:

ValidationErrorCode ExposureTargetTypeCode ExposureField ValidationRule ValidationErrorMessage NameSpace ValidationErrorTypeCode
20066 LOC Geography Match Level Code Geography Match Level Code must be a valid code Geography Match Level Code must be a valid code AIR.EntityDataModel.Exposure BUS
20067 LOC Enhanced GeoMatch Level Code Enhanced GeoMatch Level Code must be a valid code Enhanced GeoMatch Level Code must be a valid code AIR.EntityDataModel.Exposure BUS
20068 LOC Geocoder Code Geocoder Code must be a valid code Geocoder Code must be a valid code AIR.EntityDataModel.Exposure BUS
20069 LOC Geocoder AccountId Geocoder AccountId cannot be greater than 50 characters long Geocoder AccountId cannot be greater than 50 characters long AIR.EntityDataModel.Exposure BUS
[AIRReference].[dbo].[tGeocoder]

Added the following column:

GeocoderCode Geocoder Description
SYNCSORT Syncsort Geocoded through Syncsort, using international street-level geocoding data.
[AIRReference].[dbo].[tOutputTypeTableXref]

Added the following OutputTableType codes to store AIR's Next Generation Detailed Loss Module-specific financial terms: FNGM and RNGM.

[AIRReference].[dbo].[tOutputTable]                        
OutputTableSID OutputTable OutputTableTypeCode OutputDetailedAALCode Description
68 LOSS_ByLocationByFacultative FNGM NULL Added part of NGM Facultative by location
69 LOSS_ByLayerByFacultative FNGM NULL Added part of NGM Facultative by layer
70 LOSS_ByTreaty RNGM NULL Added part of NGM Facultative by location
71 LOSS_ByLocationByTreaty RNGM NULL Added part of NGM location by Treaty
73 LOSS_ByTreatyExposureAttribute RNGM NULL Added part of NGM LOB by Treaty
74 LOSS_ByLayerByTreaty RNGM NULL Added part of NGM Layer by Treaty
75 LOSS_ByContractByTreaty RNGM NULL Added part of NGM Contract by Treaty
76 LOSS_ByTreatyExposureAttributeGeo RNGM NULL Added part of NGM LOB by Treaty Exposure Attribute Geo
[AIRProject].[dbo].[tLossAnalysisVulnerabilitySetXref] Stores the additional options for vulnerability set files associated with loss analyses.
[AIRResult].[dbo].[tRRRR_DQ_AUG_ResultDetail] Added columns: GeocoderCode, GeocoderAccountId.
[AIRResult].[dbo].[tRRRR_LOSS_DimLocation]

Added columns: ReplacementValueA, ReplacementValueB, ReplacementValueC, ReplacementValueD, ReplacementValueDaysCovered, Premium, Territory, InceptionDate, ExpirationDate, GrossArea, GeocoderCode, GeocoderAccountId, DamageFunctionID.

[AIRResult].[dbo].[tRRRR_HAZ_Detail] Added columns: TCFWMExposureAreaCode2010, TCFWMWindSpeedRegionCode2010, TCFWMWindborneDebrisRegion2010, TCFWMHighVelocityWindRegion2010, GeocoderCode, GeocoderAccountId.
[AIRUserSetting].[dbo].[tDefaultLossAnalysisOption] Added MinMax logic code column.
[AIRUserSetting].[dbo].[tEventSet] Added Metadata column.
[AIRUserSetting].[dbo].[tImportDefault] and [AIRUserSetting].[dbo].[tImportMappingSet] Added columns: Signature, SignatureHash
[AIRDBAdmin].[dbo].[tExposureKeyAndIndex] Added column: FilterDefinition
[AIRDBAdmin].[dbo].[tPackageContent] Added columns: IsIgnorable, RollbackScript.
[AIRProject].[dbo].[tECARingDefinition] Added column: GridSpacing.
[AIRProject].[dbo].[tExposureConcentrationAnalysisOption]

Added columns:

  •  IsPropertySelected: BIT          
  •  SaveInjuryType: TINYINT      
  •  SaveTimeOfShift: TINYINT
[AIRProject].[dbo].[tLossAnalysisOption] Added columns: IsSelectedVulnerabilitySet, MinMaxLogicCode.
[AIRResult].[dbo].[tRRRR_DQ_AUG_ResultDetail] Added columns: GeocoderCode, GeocoderAccountID.
[AIRResult].[dbo].[tRRRR_DQ_DVA_ResultDetail] Added columns: GeocoderCode, GeocoderAccountID.
[AIRResult].[dbo].[tRRRR_DQ_SCR_ResultDetail] Added columns: GeocoderCode, GeocoderAccountID.
[AIRResult].[dbo].[tRRRR_EC_DimLocation] Added columns: GeocoderCode, GeocoderAccountID.
[AIRReinsurance].[dbo].[tReinsurance]

Added the following columns to support the integration of AIR's Next Generation Detailed Loss Module in Touchstone 8.0: RiskAggregateRetention, RiskAggregateLimit, RiskLimitReinstatement.

Datatype Changes

AIR has modified the following columns in Touchstone 8.0:

Table Column Previous Datatype Current Datatype
[AIRProject].[dbo].[tExposureSet] ExposureSetName VARCHAR (255)

VARCHAR (300)

[AIRResult].[dbo].[tEXPS_ByConstruction] AIRConstruction VARCHAR (40) VARCHAR (70)
[AIRReference].[dbo].[tAIRConstruction] AIRConstruction VARCHAR(40) VARCHAR(70)