Touchstone 4.0 Database Documentation
AIRExposureSummary Database
The AIRExposureSummary database stores information used to populate the various views on the Exposure Summary Dashboard.
Note: the variable “RRRR” in the results table name represents the ResultSID (for example “tRRRR_LOSS_ByGeo”); you can use this value to review a specific results set. You can also use the Analysis ID to locate the results for a specific analysis.
To view the database schema diagrams for the AIRExposureSummary database, see Schema Diagrams.
| Table | Description | ||
|---|---|---|---|
| tAnalysisExposureView | When the analysis target is an exposure view, this table defines the exposure set(s) included in the exposure view. | ||
| tAnalysisExposureViewDefinition | When the analysis target is an exposure view, this table defines the exposure set(s) included in the exposure view. | ||
| tAnalysisReinsAppliesToGeo | Stores information about the geographic areas to which reinsurance treaties are applied. | ||
| tAnalysisReinsurance | Stores information about any reinsurance treaties considered during the analyses. | ||
| tAnalysisResult | Contains general information about the analyses that produced the results. The table contains one row for each analysis in the database, regardless of the analysis type (Loss, Loss Grouping, Catastrophe Excess of Loss, or Marginal Impact). | ||
| tAnalysisTarget | This table stores information about the data structures input to each analysis:
| ||
| tClusterCacheResolution | Stores the cluster cache resolution information required for a Geospatial analysis. | ||
| tDBVersion | Defines the version of the database. | ||
| tDQ_AMB_25I | Stores information for A.M. Best reports, specificallyTIV percent, value, and count data of known attributes by personal property, commercial property, and worker's compensation. See tDQ_AMB_25K Table for the A.M. Best reports of "Most Frequency Observed Value" by personal property, commercial property, and worker's compensation. | ||
| tDQ_AMB_25K | Stores information for A.M. Best reports, specifically the "Most Frequency Observed Value" by personal property, commercial property, and worker's compensation. See tDQ_AMB_25I Table for the A.M. Best reports of TIV percent, value, and count data of known attributes by personal property, commercial property, and worker's compensation. | ||
| tDQAnalysisResultStatistics | Stores the statistical information for the Data Quality analysis. | ||
| tDQAUGExcludedLocations | Stores the excluded locations filter list that is used by the user interface. | ||
| tDQAUGPrePostSummaryCoverage | Stores pre- and post-summary coverage data. The data stored is used as part of the Data Quality - Augmentation PDF reports. | ||
| tEXPS_ByConstruction | Stores the data displayed on the Exposure Summary Dashboard when "Construction Type - Distribution of Replacement Values by Construction" is selected from the View Options. | ||
| tEXPS_ByCountryLOBOccupancy | Stores the data displayed on the Exposure Summary Dashboard when “LOB- Distribution of Replacement value by User Line of Business” option is selected from the View Options. | ||
| tEXPS_ByCoverage | Stores the data displayed on the Exposure Summary Dashboard when "Coverage Type - Distribution of Replacement Values by Coverage Type" is selected from the View Options. | ||
| tEXPS_ByCoverageBand | Stores the data displayed on the Exposure Summary Dashboard when “Coverage Type – Distribution of Replacement Value by Coverage Type” is selected from the View Options. | ||
| tEXPS_ByEmployee | Stores the data displayed on the Exposure Summary Dashboard when "Distribution of Number of Employees" is selected from the View Options. | ||
| tEXPS_ByGeography | A support table that contains the unique set of all geographies for anExposure Summary. Reserved for future use. | ||
| tEXPS_ByGeoMatchLevel | Stores the data displayed on the Exposure Summary Dashboard when "Geocode Match Level- Distribution of Replacement Values by Geocode Match Level" is selected from the View Options. | ||
| tEXPS_ByOccLimitType | Stores the data displayed on the Exposure Summary Dashboard when "Limit Type Distribution" is selected from the View Options. | ||
| tEXPS_ByOccupancy | Stores the data displayed on the Exposure Summary Dashboard when "Occupancy Type- Distribution of Replacement Values by Occupancy" is selected from the View Options. | ||
| tEXPS_ByPeril | Stores the data displayed on the Exposure Summary Dashboard when "Peril - Distribution of Replacement Values by Peril" is selected from the View Options. | ||
| tEXPS_BySecondaryRisk | Stores the data displayed on the Exposure Summary Dashboard when "Proportion of Known to Unknown Secondary Characteristics" is selected from the View Options. | ||
| tEXPS_ByStories | Stores the data displayed on the Exposure Summary Dashboard when "# Stories - Distribution of Replacement Values by Stories" is selected from the View Options. | ||
| tEXPS_BySubmitStatus | Stores the data displayed on the Exposure Summary Dashboard when "Status - Distribution of Replacement Values by Status" is selected from the View Options. | ||
| tEXPS_ByTimeElementBand | Stores the data displayed on the Exposure Summary Dashboard when "Per Diem- Distribution of Replacement Values by Per Diem Time Element" is selected from the View Options. | ||
| tEXPS_ByUserLineOfBusiness | Stores the data displayed on the Exposure Summary Dashboard when "LOB - Distribution of Replacement Values by User Line of Business" is selected from the View Options. | ||
| tEXPS_ByYearBuilt | Stores the data displayed on the Exposure Summary Dashboard when "Year Built - Distribution of Replacement Values by Year Built" is selected from the View Options. | ||
| tEXPS_ControlTotal | Stores the data displayed on the Exposure Summary Dashboard when "Peril-Distribution of Replacement Values by Peril" is selected form the View Options. | ||
| tEXPS_ReinsuranceType | Stores the data displayed on the Exposure Summary Dashboard when "Number of Contracts by Reinsurance Type" is selected from the View Options. | ||
| tLossAnalysisDemandSurgeOption | For detailed loss analysis run with demand surge, this table stores the demand surge identifiers that correspond to the selected demand surge functions. If demand surge is not included, this table will contain no rows for that analysis. | ||
| tLossAnalysisFloodOption | Stores the flood option(s) used to generate the results in a detailed loss analysis. If the user has selected specific flood zones for a loss analysis, the table will contain one or more rows that identify a value for the custom flood zone secondary modifier for locations. Touchstone uses this value as a filter when determining which locations to include in the analysis. If the user has not configured loss analysis to filter by flood zone, this table will contain no rows for that analysis. | ||
| tLossAnalysisOption | Stores the loss analysis options used to generate the results. This table contains one row for each Loss analysis. | ||
| tLossAnalysisPerilSetULOBOption | Stores the peril set options used to generate the results in a detailed loss analysis. | ||
| tLossAnalysisSTStormOption | Stores the severe thunderstorm peril options used to generate the results in a detailed loss analysis. If sever thunderstorm is being modeled in an analysis, the table will include one or more rows for the analysis indicating the categories of storm events to be included or excluded. | ||
| tLossAnalysisTerrorismOption | Stores the terrorism peril options used to generate the results in a detailed loss analysis. If terrorism is being modeled in an analysis, the table will include one or more rows for the analysis indicating the categories of terrorist events to be included or excluded. | ||
| tRRRR_CLSH_ContractSummary | Stores statistical information about the contracts that are affected by the clash underwriting analysis. | ||
| tRRRR_CLSH_LocationByContract | Stores statistical information about the locations (within the contract) that are affected by the clash underwriting analysis. | ||
| tRRRR_CLSH_LocationByPortfolio | Stores statistical information about the locations (within the portfolio) that are affected by the clash underwriting analysis. | ||
| tRRRR_CMPLOSS_AnnualEPKey | Stores annual exceedance probability losses to be used a Comparative Loss analysis. This table is created for each comparative Loss analysis. By default, for each year in the analysis that experienced loss, the table will contain at most one Aggregate row and one Occurrence row per financial perspective for the Standard EP curve. But if losses for a particular financial perspective in that year were 0, no row is generated. The table may contain additional rows if the user requests additional EP curves for subsets of the results. | ||
| tRRRR_CMPLOSS_ContractKey | Stores information about the contracts used during the Comparative Loss analysis. | ||
| tRRRR_CMPLOSS_EventKey | Stores information about the events used during the Comparative Loss analysis. | ||
| tRRRR_CMPLOSS_GeoKey | Stores information about the location used during the Comparative Loss analysis. | ||
| tRRRR_CMPLOSS_LOBKey | Stores information about the Line of Business used during the Comparative Loss analysis. | ||
| tRRRR_DQ_AUG_ConsistencyResultSummary | Stores information regarding the number/percent of locations and replacement values that were in violation of each rule during the Data Quality - Augmentation analysis. | ||
| tRRRR_DQ_AUG_PrePostCharacteristics | Stores pre- and post- augmentation information. | ||
| tRRRR_DQ_AUG_ResultDetail | Report detail results for the Data Quality - Augmentation reports. | ||
| tRRRR_DQ_AUG_ResultDetailAggregate | Stores detailed summary results for the Data Quality - Augmentation reports. | ||
| tRRRR_DQ_AUG_ResultSummary | Stores summary results for the Data Quality - Augmentation reports. Contains augmentation values for display in report summary frame. | ||
| tRRRR_DQ_AUG_ResultValues | Defines actual values referenced by the augmentation rules. Stores both user and industry value for easy comparison and augmentation. Referenced by the tRRRR_DQ_AUG_ResultDetail Table to provide multiple values per location. Each location affected by the augmentation should have an ISOBIN record within this table to keep track of the augmentation data source. | ||
| tRRRR_DQ_DVA_ResultDetail | Stores result details for the Data Quality - Validation reports. Each table contains detail data for one report and every location in the report is represented by a record. See tRRRR_DQ_DVA_Violations Table to review the multiple rule violations for each location record. | ||
| tRRRR_DQ_DVA_ResultDetailAggregate | Stores the aggregate results for the Data Quality - Validation reports. | ||
| tRRRR_DQ_DVA_ResultDetailTerms | Stores detailed term result for the Data Quality - Validation reports. | ||
| tRRRR_DQ_DVA_ResultSummary | Stores summary results for the Data Quality - Validation reports. Each table contains summary data for one report. Records contain Data Quality - Validation values that are pre-aggregated to the format for display in report summary frame. | ||
| tRRRR_DQ_DVA_Violations | Stores all Data Quality - Validation rule violations for each location record in tRRRR_DQ_DVA_ResultDetail Table. Each single table contains detail data for one report and each row represents a failed rule for specific location. The ViolationDescription attribute combines the information from rule and rule set for easy access. (For example "RV A is less than 50000") | ||
| tRRRR_DQ_IDB_ResultDetail | Stores report details for the Data Quality - Industry Data Benchmark reports. | ||
| tRRRR_DQ_IDB_ResultSummary | Stores summary results for the Data Quality - Industry Data Benchmark reports. | ||
| tRRRR_DQ_IDR_ResultSummary | Stores summary results for the Data Quality - Industry Data Review reports. | ||
| tRRRR_DQ_SCR_ResultDetail | Stores result details for the Data Quality - Scoring reports. Each single table contains detail data for one report. | ||
| tRRRR_DQ_SCR_ResultSummary | Stores summary results for the Data Quality - Scoring reports. | ||
| tRRRR_EC_ByAccumulator | Stores the accumulated results of a Exposure Concentration analysis within a user-defined area (zones, rings, or polygons). One or more of the properties can be accumulated, such as location, risk, replacement value, employee count, and loss. | ||
| tRRRR_EC_ByContract | Unique system-generated sequential identifier for the zones, rings, or polygons in which exposure concentration is accumulated for a specified peril in a Geospatial analysis.Stores the results of the Geospatial analysis at the contract level. | ||
| tRRRR_EC_ByLayer | Stores the results of the Geospatial analysis at the layer level. | ||
| tRRRR_EC_ByLocation | Stores the results of the Geospatial analysis at the latitude/longitude location level. | ||
| tRRRR_EC_ByReinsurance | Stores information about the reinsurance program that is applicable to a given Accumulator and Peril combination. | ||
| tRRRR_EC_ClusterBoundingBox | Used for optimizing the Spatial Cube data. Reserved for internal use only. | ||
| tRRRR_EC_DimAccumulator | Stores information about each accumulation object. tRRRR_EC_ByAccumulator Table refers to the data in this parent table. | ||
| tRRRR_EC_DimContract | Stores attributes associated with each contract. These attributes are displayed on the user interface together with data from table tRRRR_EC_ByContract Table. | ||
| tRRRR_EC_DimLayer | Stores information about each layer defined for an analysis. tRRRR_EC_ByLayer Table refers to the data in this parent table. | ||
| tRRRR_EC_DimLocation | Stores attributes associated with each location included in the analysis. These attributes are displayed on the user interface along with data from tRRRR_EC_ByLocation Table. | ||
| tRRRR_EC_DimReinsurance | Stores reinsurance program attributes such as its ID and type. tRRRR_EC_ByReinsurance Table refers to the data in this parent table. | ||
| tRRRR_EC_DimSubAccumulator | Stores information about each sub-accumulation object. A sub-accumulation object is a concentric ring that is assigned a peril-specific damage ratio. All locations that lie within this ring are analyzed together as a single group. | ||
| tRRRR_HAZ_AttributeSummary | Stores attribute-specific summary loss results from a Hazard analysis. | ||
| tRRRR_HAZ_Detail | Stores basic information about the results of a Hazard analysis. | ||
| tRRRR_HAZ_Intensity | Stores detailed hazard information for events that cause loss to specific locations during a hazard analysis. | ||
| tRRRR_IMPACT_AnnualEPSummary | Stores annual exceedance probability summary losses for a marginal impact assessment. | ||
| tRRRR_LOSS_AnnualEP | Stores annual exceedance probability losses for an analysis. This table is created for each analysis. By default, for each year in the analysis that experienced loss, the table will contain at most one Aggregate row and one Occurrence row per financial perspective for the Standard EP curve. But if losses for a particular financial perspective in that year were 0, no row is generated. The table may contain additional rows if the user requests additional EP curves for subsets of the results. | ||
| tRRRR_LOSS_AnnualEP_ByModel | Stores annual exceedance probability losses by model. This table is created for each analysis. By default, for each year in the analysis that experienced loss for a specified model, the table will contain at most one Aggregate row and one Occurrence row per financial perspective for the Standard EP curve. But if losses for a particular financial perspective in that year were 0, no row is generated. The table may contain additional rows if the user requests additional EP curves for subsets of the results. | ||
| tRRRR_LOSS_AnnualEP_ByPeril | Stores annual exceedance probability losses by peril. This table is created for each analysis. By default, for each year in the analysis that experienced loss for a specified peril, the table will contain at most one Aggregate row and one Occurrence row per financial perspective for the Standard EP curve. But if losses for a particular financial perspective in that year were 0, no row is generated. The table may contain additional rows if the user requests additional EP curves for subsets of the results. | ||
| tRRRR_LOSS_AnnualEPSummary | Stores annual exceedance probability summary losses. This table is created for each analysis. By default, the table will contain rows for each financial perspective for which results are saved, one row each for aggregate EP, occurrence EP, aggregate TVAR, and occurrence TVAR. The table may contain additional rows if the user requests additional EP curves for subsets of the results. | ||
| tRRRR_LOSS_AnnualEPSummary_ByPerilModel | Stores annual exceedance probability summary losses for both peril and model. This table is created for each analysis. By default, the table will contain rows for each financial perspective for which results are saved, one row each for aggregate EP, occurrence EP, aggregate TVAR, and occurrence TVAR. The table may contain additional rows if the user requests additional EP curves for subsets of the results. | ||
| tRRRR_LOSS_ByContract | Stores contract-level loss results from a detailed loss analysis. This table is created when the user specifies "Save Loss By Contract" in the analysis Output Options. | ||
| tRRRR_LOSS_ByContractGeo | Stores contract geography-level loss results from a detailed Loss analysis. This table is created when the user specifies "Save Loss By Contract and Geography" in the analysis Output Options. | ||
| tRRRR_LOSS_ByContractSummary | Stores contract summary loss results from a detailed loss analysis. This table is created when the user selects Contract Summary in the analysis Output Options. | ||
| tRRRR_LOSS_ByEvent | Stores event-level loss results from a detailed Loss analysis. This tables is created for every loss analysis. | ||
| tRRRR_LOSS_ByExposureAttribute | Stores event attribute loss results from a detailed Loss analysis. | ||
| tRRRR_LOSS_ByExposureAttributeGeo | Stores the exposure’s results by geographic attribute from a detailed loss analysis. This table is created when the user specifies "Save Loss By Line of Business and Geography" in the analysis Output Options. | ||
| tRRRR_LOSS_ByGeo | Stores geography-level loss results from a detailed Loss analysis. This table is created when the user selects a geographic area from the "Geography" drop-down box in the analysis Output Options. | ||
| tRRRR_LOSS_ByGeoPerilAAL | Stores average annual loss results, at the peril- and geography-level, from a detailed Loss analysis. This table is created when the user selects "Save Loss By Peril" and also selects a geographic area from the "Geography" drop-down box in the analysis Output Options. | ||
| tRRRR_LOSS_ByLayer | Stores layer-level loss results from a detailed Loss analysis. This table is created when the user specifies "Save Loss By Layer" in the analysis Output Options. | ||
| tRRRR_LOSS_ByLocation | Stores location-level loss results from a detailed Loss analysis. This table is created when the user specifies "Save Loss By Location" in the analysis Output Options. | ||
| tRRRR_LOSS_ByLocationPerilAAL | Stores average annual loss results, at the peril- and location-level, from a detailed Loss analysis. This table is created when the user specifies both "Save Loss By Peril" and "Save Loss By Location" in the analysis Output Options. | ||
| tRRRR_LOSS_ByLocationSummary | Retained loss value, which is the Ground-Up loss minus the insured Gross loss.Stores location summary loss results from a detailed Loss analysis. This table is created when the user selects Location Summary in the analysis Output Options. | ||
| tRRRR_LOSS_ByMAOL | Stores Maximum Any One Life (MAOL) loss results from a detailed Loss analysis. This table is created when the user selects MAOL in the analysis Output Options. | ||
| tRRRR_LOSS_ByMAOLGeo | Stores Maximum Any One Life (MAOL) and geographic loss results from a detailed Loss analysis. This table is created when the user specifies "Save Loss By Geography" and selects MAOL in the analysis Output Options. | ||
| tRRRR_LOSS_ByReinsurance | Stores the reinsurance treaty loss results from a detailed loss analysis. This table is created when the user specifies any reinsurance type in the analysis Output Options. | ||
| tRRRR_LOSS_ByReinsuranceExposureAttribute | Stores the reinsurance results by exposure attribute from a detailed loss analysis. This table is created when the user specifies "Save Loss By Geography" and selects any reinsurance type in the analysis Output Options. | ||
| tRRRR_LOSS_ByReinsuranceExposureAttributeGeo | Stores the reinsurance results by exposure attribute from a detailed loss analysis. This table is created when the user specifies "Save Loss By Line of Business and Geography" and selects any reinsurance type in the analysis Output Options. | ||
| tRRRR_LOSS_DimContract | Stores information about contracts that experienced Loss in the analysis. This dimension table is generated when losses are saved by contract or by location. | ||
| tRRRR_LOSS_DimCountryLOB | Stores information about a country’s Line of Business that experiences loss during a loss analysis. This dimension table is created when the user specifies "Save Loss By Line of Business" and a CLF option is selected in the analysis Output Options. | ||
| tRRRR_LOSS_DimEvent | Stores information about a terrorism event that experiences loss during a loss analysis. This dimension table is created when losses are saved by "Event". | ||
| tRRRR_LOSS_DimExposureAttribute | Stores specific exposure attribute values that were 1) used to aggregate losses and 2) experienced loss in the analysis. For example, if the loss results were saved by line of business, this table will include one row for every line of business that had a loss. The ExposureAttributeTypeCode would be ‘ULOB’, and the ExposureAttribute values would contain the names of the lines of business. This dimension table is created when losses are saved by any exposure attribute, such as Line of Business. | ||
| tRRRR_LOSS_DimExposureDataSource | Stores information about databases that contain exposures that have been analyzed. This dimension table is created for every Loss analysis. | ||
| tRRRR_LOSS_DimLayer | Stores information about layers that experienced loss in the analysis. This dimension table is created when losses are saved by layer. | ||
| tRRRR_LOSS_DimLocation | Stores information about locations that experienced loss in the analysis. This dimension table is created when losses are saved by location. | ||
| tRRRR_LOSS_DimReinsurance | This table stores information about reinsurance treaties that experienced loss in the analysis. More descriptive information about the reinsurance can be found by joining to the tAnalysisReinsurance Table. This dimension table is created when losses are saved by treaty. | ||
| tRRRR_NONCAT_AnnualEPSummary | Stores annual exceedance probability summary losses from a non-catastrophe analysis. This table is created for each analysis. By default, the table will contain four rows for each financial perspective for which results are saved, one row each for aggregate EP, occurrence EP, aggregate TVAR, and occurrence TVAR. The table may contain additional rows if the user requests additional EP curves for subsets of the results. | ||
| tRRRR_NONCAT_ByContract | Stores contract-level loss results from a non-catastrophe detailed loss analysis. This table is created when the user specifies "Save Loss by Contract" in the analysis Output Options. | ||
| tRRRR_NONCAT_ByLayer | Stores layer-level loss results from a non-catastrophe detailed loss analysis. This table is created when the user specifies "Save Loss by Layer" in the analysis Output Options. | ||
| tRRRR_NONCAT_ByLocation | Stores location-level loss results from a non-catastrophe detailed loss analysis. This table is created when the user specifies "Save Loss by Location" in the analysis Output Options. | ||
| tRRRR_NONCAT_ByReinsurance | Stores the reinsurance treaty loss results from a non-catastrophe loss analysis. This table is created when the user specifies any reinsurance type in the analysis Output Options. | ||
| tRRRR_NONCAT_DimContract | Stores information about contracts that experienced loss in a non-catastrophe loss analysis. This dimension table is created when losses are saved by contract. | ||
| tRRRR_NONCAT_DimLayer | Stores information about layers that experienced loss in a non-catastrophe loss analysis. This dimension table is created when losses are saved by layer. | ||
| tRRRR_NONCAT_DimLocation | Stores information about locations that experienced loss in a non-catastrophe loss analysis. This dimension table is created when losses are saved by location. | ||
| tRRRR_NONCAT_DimReinsurance | This table stores information about reinsurance treaties that experienced loss in a non-catastrophe loss analysis. This dimension table is created when losses are saved by treaty. | ||
| tRRRR_TTTT_CMPLOSS_AnnualEP | Stores the deltas for the aggregate annual exceedance probability losses that are calculated during a Comparative Loss analysis.
| ||
| tRRRR_TTTT_CMPLOSS_AnnualEPSummary | Stores the deltas for the aggregate annual exceedance probability summary that are calculated during a Comparative Loss analysis.
| ||
| tRRRR_TTTT_CMPLOSS_ByEvent | Stores the deltas for the aggregate event losses that are calculated during a Comparative Loss analysis.
| ||
| tRRRR_TTTT_CMPLOSS_ContractLOBAAL | Stores the deltas for the aggregate average annual losses by contract LOB that are calculated during a Comparative Loss analysis.
| ||
| tRRRR_TTTT_CMPLOSS_GeoAAL | Stores the deltas for the aggregate average annual losses by location that are calculated during a Comparative Loss analysis.
| ||
| tSIDControl | Contains a list of all SID tables and the last SID value in use for each table. After each insert operation, the SID value will update. |
