Touchstone 4.0 Database Documentation
AIRResult
Description

The AIRResult database stores analysis results, as well as information used during the analysis. Distributed across the Analysis Metadata tables, Results tables and Dimension tables, the AIRResult database stores contextual information about the analyses and data structures that were input to each analysis, and the actual analysis results.

Analysis results included in the AIRResult database include: 

  • Detailed Loss Analysis results
  • Data Quality Analysis (Validation, Scoring, Industry Data Review, Industry Data Benchmark, Augmentation) results
  • Geospatial Analysis results
  • Hazard Analysis results
  • Clash Underwriting Analysis results
  • Non-Catastrophe Analysis results
  • Comparative loss Analysis results
  • Marginal Impact Analysis results

The variable “RRRR” in the results table name represents the ResultSID (for example “tRRRR_LOSS_ByContract”); 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.

However, for comparative loss analyses, tRRRR denotes the Results SID for the comparative analysis and TTTT represents the identifier for the loss analysis.

Schema Diagram

To view the database schema diagrams for the AIRResult database, see Schema Diagrams.

    Tables
    TableDescription
    tAnalysisExposureViewWhen the analysis target is an exposure view, this table stores information about the analyzed exposure view and any filters used to create it.
    tAnalysisExposureViewDefinitionWhen the analysis target is an exposure view, this table defines the exposure set(s) included in the exposure view.
    tAnalysisReinsAppliesToGeoStores information about the geographic areas to which reinsurance treaties are applied.
    tAnalysisReinsuranceStores information about any reinsurance treaties considered during the analyses.
    tAnalysisResultContains 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:

    • A detailed loss analysis has a single analysis target, e.g. an exposure view.
    • A marginal impact analysis has two analysis targets, the reference portfolio and the new portfolio or policy whose impact will be assessed.
    • Loss grouping has two or more analysis targets, the loss result sets or loss groups that are being grouped together.
    tClusterCacheResolutionStores the cluster cache resolution information required for a Geospatial analysis.
    tDBVersionDefines 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.

    tDQAnalysisResultStatisticsStores the statistical information for the Data Quality analysis.
    tDQAUGExcludedLocationsStores the excluded locations filter list that is used by the user interface.
    tDQAUGPrePostSummaryCoverageStores 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_ByCountryLOBOccupancyStores 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_ByCoverageBandStores 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_ByGeographyA 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.

    tLossAnalysisDemandSurgeOptionFor 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.
    tLossAnalysisFloodOptionStores 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.
    tLossAnalysisOptionStores the loss analysis options used to generate the results. This table contains one row for each Loss analysis.
    tLossAnalysisPerilSetULOBOptionStores the peril set options used to generate the results in a detailed loss analysis.
    tLossAnalysisSTStormOptionStores 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.
    tLossAnalysisTerrorismOptionStores 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_ContractSummaryStores statistical information about the contracts that are affected by the clash underwriting analysis.
    tRRRR_CLSH_LocationByContractStores statistical information about the locations (within the contract) that are affected by the clash underwriting analysis.
    tRRRR_CLSH_LocationByPortfolioStores statistical information about the locations (within the portfolio) that are affected by the clash underwriting analysis.
    tRRRR_CMPLOSS_AnnualEPKeyStores 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_ContractKeyStores information about the contracts used during the Comparative Loss analysis.
    tRRRR_CMPLOSS_EventKeyStores information about the events used during the Comparative Loss analysis.
    tRRRR_CMPLOSS_GeoKeyStores information about the location used during the Comparative Loss analysis.
    tRRRR_CMPLOSS_LOBKeyStores information about the Line of Business used during the Comparative Loss analysis.
    tRRRR_DQ_AUG_ConsistencyResultSummaryStores 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_PrePostCharacteristicsStores pre- and post- augmentation information.
    tRRRR_DQ_AUG_ResultDetailReport detail results for the Data Quality - Augmentation reports.
    tRRRR_DQ_AUG_ResultDetailAggregateStores detailed summary results for the Data Quality - Augmentation reports.
    tRRRR_DQ_AUG_ResultSummaryStores summary results for the Data Quality - Augmentation reports. Contains augmentation values for display in report summary frame.
    tRRRR_DQ_AUG_ResultValuesDefines 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_ResultDetailStores 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_ResultDetailAggregateStores the aggregate results for the Data Quality - Validation reports.
    tRRRR_DQ_DVA_ResultDetailTermsStores detailed term result for the Data Quality - Validation reports.
    tRRRR_DQ_DVA_ResultSummaryStores 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_ViolationsStores 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_ResultDetailStores report details for the Data Quality - Industry Data Benchmark reports.
    tRRRR_DQ_IDB_ResultSummaryStores summary results for the Data Quality - Industry Data Benchmark reports.
    tRRRR_DQ_IDR_ResultSummaryStores summary results for the Data Quality - Industry Data Review reports.
    tRRRR_DQ_SCR_ResultDetailStores result details for the Data Quality - Scoring reports.  Each single table contains detail data for one report.
    tRRRR_DQ_SCR_ResultSummaryStores summary results for the Data Quality - Scoring reports.
    tRRRR_EC_ByAccumulatorStores the results of the Geospatial Accumulation analysis. An Accumulation analysis determines the accumulation of risk in peril-specific high risk areas. One or more of the properties in this table can be accumulated, such as location, risk, replacement value, and loss.
    tRRRR_EC_ByContractStores the results of the Geospatial analysis at the contract-level.
    tRRRR_EC_ByLayerStores the results of the Geospatial analysis at the layer-level.
    tRRRR_EC_ByLocationStores the results of the Geospatial analysis at the latitude/longitude location-level.
    tRRRR_EC_ByReinsuranceStores information about the reinsurance program that is applicable to a given Accumulator and Peril combination.
    tRRRR_EC_ClusterBoundingBoxUsed for optimizing the Spatial Cube data. Reserved for internal use only.
    tRRRR_EC_DimAccumulatorStores information about each accumulation object. tRRRR_EC_ByAccumulator Table refers to the data in this parent table.
    tRRRR_EC_DimContractStores attributes associated with each contract. These attributes are displayed on the user interface together with data from table tRRRR_EC_ByContract Table.
    tRRRR_EC_DimLayerStores information about each layer defined for an analysis. tRRRR_EC_ByLayer Table refers to the data in this parent table.
    tRRRR_EC_DimLocationStores 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_DimReinsuranceStores reinsurance program attributes such as its ID and type. tRRRR_EC_ByReinsurance Table refers to the data in this parent table.
    tRRRR_EC_DimSubAccumulatorStores 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_AttributeSummaryStores attribute-specific summary loss results from a Hazard analysis.
    tRRRR_HAZ_DetailStores basic information about the results of a Hazard analysis.
    tRRRR_HAZ_IntensityStores detailed hazard information for events that cause loss to specific locations during a hazard analysis.
    tRRRR_IMPACT_AnnualEPSummaryStores annual exceedance probability summary losses for a marginal impact assessment.
    tRRRR_LOSS_AnnualEPStores annual exceedance probability losses for an analysis. This table is created for each 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_LOSS_AnnualEP_ByModelStores 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_ByPerilStores 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_AnnualEPSummaryStores annual exceedance probability summary losses for an analysis. This table is created for each Loss 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_ByPerilModelStores 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_ByContractStores 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_ByContractGeoStores 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_ByContractSummaryStores 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_ByEventStores event-level loss results from a detailed Loss analysis.  This tables is created for every loss analysis.
    tRRRR_LOSS_ByExposureAttributeStores loss results by event attribute from a detailed Loss analysis.
    tRRRR_LOSS_ByExposureAttributeGeoStores 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_ByGeoStores 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 the 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_ByLayerStores 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_ByLocationStores 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_ByLocationSummaryStores 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_ByMAOLStores 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_ByMAOLGeoStores 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_ByReinsuranceStores 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_ByReinsuranceExposureAttributeGeoStores 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_DimContractStores 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_DimExposureAttributeStores 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_DimExposureDataSourceStores information about databases that contain exposures that have been analyzed. This dimension table is created for every Loss analysis.
    tRRRR_LOSS_DimLayerStores information about layers that experienced loss in the analysis. This dimension table is created when losses are saved by layer.
    tRRRR_LOSS_DimLocationStores information about locations that experienced loss in the analysis. This dimension table is created when losses are saved by location.
    tRRRR_LOSS_DimReinsuranceThis 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_AnnualEPSummaryStores annual exceedance probability summary losses from a non-catastrophe loss analysis. 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_NONCAT_ByContractStores contract-level loss results from a non-catastrophe loss analysis.  This table is created when the user specifies "Save Loss by Contract" in the analysis Output Options.
    tRRRR_NONCAT_ByLayerStores layer-level loss results from a non-catastrophe loss analysis.  This table is created when the user specifies "Save Loss by Layer" in the analysis Output Options.
    tRRRR_NONCAT_ByLocationStores location-level loss results from a non-catastrophe loss analysis.  This table is created when the user specifies "Save Loss by Location" in the analysis Output Options.
    tRRRR_NONCAT_ByReinsuranceStores 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_DimContractStores 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_DimLayerStores 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_DimLocationStores 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_DimReinsuranceThis 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 denotes the Results SID for the Comparative Loss analysis and TTTT represents the identifier for the 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 denotes the Results SID for the Comparative Loss analysis and TTTT represents the identifier for the loss analysis.
    tRRRR_TTTT_CMPLOSS_ByEvent

    Stores the deltas for the aggregate event losses that are calculated during a Comparative Loss analysis.

    tRRRR denotes the Results SID for the Comparative Loss analysis and TTTT represents the identifier for the 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 denotes the Results SID for the Comparative Loss analysis and TTTT represents the identifier for the loss analysis.
    tRRRR_TTTT_CMPLOSS_GeoAAL

    Stores the deltas in the aggregate average annual losses by location that are calculated during a Comparative Loss analysis.

    tRRRR denotes the Results SID for the Comparative Loss analysis and TTTT represents the identifier for the loss analysis.
    tSIDControlContains 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.
    See Also

    Related Objects

    Locating Results for a Specific Analysis

     

     


    AIR Client Confidential

    © 2016. AIR Worldwide is a registered trademark. Touchstone is a registered trademark of AIR Worldwide.

    Please contact AIR Worldwide with questions or comments. For database questions. For documentation comments.