SpatialKey Bridge ETL (currently in beta) is a user-friendly app that sits on a user’s desktop and does the work of converting and uploading RMS EDM information for use in SpatialKey. Historically, insurers developed custom transformation queries to manually extract data for further analytic processing within SpatialKey applications. Now, it’s as simple as installing the application, pointing to your SQL Server and selecting one or many portfolios from your library of EDMs – the rest is automatic, and insurers will have ready-access to their exposure data.
This page will serve as a reference for where to find information needed to use Bridge ETL and outline assumptions related to EDM extraction and import into SpatialKey. SpatialKey Bridge offers additional ways to integrate your data and you can learn more here.
What is an EDM?
An EDM is Risk Management Solutions‘ (RMS) Exposure Data Model (EDM). This is where portfolio location and policy information is described and stored for use in RMS applications and models. Bridge ETL provides an easy way for SpatialKey customers who license RMS products to transform and transfer data from an EDM into SpatialKey for further analysis and visualization. Read more about our partnership with RMS here.
Using SpatialKey Bridge ETL
Provide login credentials
You’ll need to authenticate for both SpatialKey and SQL.
- You can find your User API Key and Org Keys in SpatialKey. Note that only Super Admins user can see the org level keys in SpatialKey.
- You can authenticate to SQL via your Windows or SQL credentials.
Select what you want to import
EDM versions 10, 11, 13, and 15 are supported for selection at this time. Select which portfolios from the selected EDM you want to import into SpatialKey.
Note that SpatialKey does not support currency conversion at this time, so please perform any currency conversion/standardization before uploading. In this version, we pull values from the EDM without conversion and display them according to the display currency you select.
Understand what’s pulled from the selected EDM
EDM fields pulled include address information, peril specific values, limits and deductibles, primary building characteristics, and some secondary characteristics. If available, policy information will also be pulled, including policy terms, underwriter name, branch, LOB, and inception and expiration dates. Contact us for additional details.
SpatialKey import settings
- SpatialKey Bridge ETL will create a new dataset within SpatialKey – there is no option to append or overwrite at this time.
- Dataset permissions will be defaulted to ‘All Users’ as ‘Viewers’.
- Dataset will not be re-geocoded, geocoding results from RMS will be extracted.
- Country and U.S. State, County, and Postal Code thematic boundaries will be enabled by default.
EDM Extraction assumptions
- Workers compensation risks will not be included.
- Only fields where ‘is valid’ = 1 will be included.
- ‘Blanket/Minimum Deductible’ will be set as the ‘Minimum Deductible Amount’ when it exists, otherwise ‘Blanket Deductible Amount’.
- Peril values are calculated assuming values were entered into the EDM once per location (e.g. EQ Building Value is the sum of ‘value amounts’ when ‘label ID’ equals 7 or 28)
- ‘Total Insured Value’ will be set as the maximum value across all peril-specificcoverage values, assuming that both individual and combined coverages were not defined for the same location (e.g. EQ Total Insured Value = EQ Building + EQ Other Structures + EQ Contents + EQ Business Interruption + EQ Combined)
- Several RMS code-to-description translations are available. The following field translations are supported:
- Construction Class
- Occupancy Type
- Country Scheme and Country Code
- Peril Types
- Coverage Values
- Floor Area Units