How to create a Geospatial Dimension for Geomaps

To implement the configuration changes detailed in this guide requires changes to the Processing Script for your company instance.  These changes should only be made by users with experience in editing the One Model processing script or One Model Data Engineers. 

Read the Introduction to Geomaps article.

One of our most engaging visualizations here at One Model is the Geomap, which allows the viewer to look at results Geographically. For example, we can look at Headcount by City to see where all our employees are located across the US and Europe:

To support a visualization like this, we need to link our people to not just a location name, but a geographical location. To do this, we create a Geospatial Dimension.

1. Get the data for a Geospatial Dimension

A Geospatial Dimension is created just like any other dimension in One Model with a processing script and connection to a data source. Due to its geographical nature, you will need to source geolocation data including the latitude and longitude coordinates for your locations.

The Data for a Geospatial dimension includes the standard location information, which will include some or all of the following:

• Continent
• Country
• State/Province
• City
• Address

In addition, we need to add a Latitude and a Longitude value for each level of the Dimension so they can be calculated. Ideally the geospatial data would be part of the original source, so that as new locations are added they automatically flow through to One Model.

Some HRIS’ already have geolocation data included in the core information for employees or office locations etc. If you do not have this information, One Model has a limited data set of locations available for our customers, but you may have to source and generate the geolocation data to populate the Geospatial Dimension. 

If your source system doesn’t include geospatial data, you might have to leverage a third-party solution.  These Latitude and Longitude values are easily obtained from a geocoding API or other provider. Still, they may have Terms of Use restrictions (for example, see the restrictions on the Google API). These Terms of Use prohibit One Model from storing or caching the geocoordinates obtained through Google Maps). 

For our Enterprise customers, One Model has a standard geospatial dataset that is calculated from a mixture of different sources. Something to be aware of is that if these sources are used then we will have to create a static mapping of this data to your HRIS data and then there is a high likelihood that at some point in the future new locations will flow through from your source systems and require manual updating by the customer. 

When Enterprise customers are implementing their company instance, they will work with their Customer Success Lead and One Model Data Engineer to build a geospatial dimension and initial load of standard coordinate data for the following:

Countries

States (US only)

Zipcode (US)

Essentials customers should speak with their Customer Success Lead to discuss their Geomaps options. 

2. Organise the data for the Geospatial Dimension

Once you have the data from the required source, it needs to be combined in a single table in the following format:

• ID - the overall ID for the current node (which may match the lowest level ID)
• levelNid - a unique ID for the Location
• levelNname - the Name of the Location - which doesn't need to be unique
• levelN_latitude - the latiude of the Location
• levelN_longitude - the longitude of the Location
• Ordering - a numeric field that allows the application to order the nodes in the Dimension when viewed outside of a Geomap.

This will be repeated for each level (Continent/Country/etc.) for the Geospatial diemnsion, where N is the number of the level (starting at level 1). An example table could look like this:

3. Configure the Geospatial Dimension

Once the Geospatial Dimension is organised into a table like the above, you can then configure the Geospatial Dimension simply using the following in the Processing Script:

dim_location:

create geospatial_dimension

from ${<my_location_table>}

category ('Employment')

keys(${location}.id)

This will autoamtically include the ID, Name, Latitude, and Longitude values from the above table. You can also optionally specify this using the levels() command, like so:

dim_location:

create geospatial_dimension

from ${<my_location_table>}

category ('Employment')

levels (

(1, level1id, level1name, ‘Level 1’, level1_latitude, level1_longitude),

(2, level2id, level2name, ‘Level 2’, level2_latitude, level2_longitude),

(3, level3id, level3name, ‘Level 3’, level3_latitude, level3_longitude),

(4, level4id, level4name, ‘Level 4’, level4_latitude, level4_longitude)

)

keys(${location}.id)

Once the required syntax has been added in, Save and Promote the new script to Production. It can then either be Run from the Data Loads page, or wait until the Processing Scripts are run from another Data Source

4. Add the Geospatial Dimension to a role

Once the new Geospatial Dimension has been created, you can add it to a Role in the Data Access Roles page:

This will allow any user to see and use that Dimension.

Watch this video to learn how to create a geomap.