The as-is START system has a building lookup, which then pre-populates the building address. Some people call this location data.

The ultimate destination in ServiceNow is the table cnm_location

This data has not previously existed within Service Now.

As such, we needed a new data ingress for building data.

Where the data lives

Facilities manages a data source called FAM1, which is part of FAMIS. Then Emily Ureski has a service that scrapes FAM1 and rehashes the data in a store called YTM1. The YTM1 data store has some manual fix ups from the FAM1 data.

How the data gets to ServiceNow

We have a scheduled import task in ServiceNow that scrapes YTM1 with a custom query that David Backeberg wrote. The address data is not very consistent within YTM1, so there are conditionals to walk around the inconsistencies and make it as good as it can be given the constraints.

Data Source

Data source is called Buildings. YTM1 lives on Kim. We have a service user on YTM1 for pulling the query. Here's the query David Backeberg wrote:

select 
       BUILDING AS BUILDING_NUMBER
     , DESCRIPTION AS BUILDING_DESCRIPTION  
     ,CASE
        -- first case is that there are no commas; just pass it on
        WHEN instr(address_1,',') = 0 THEN address_1
        -- second case is that there is a comma, with number immediately following. Otherwise it would have been caught by the first case
        WHEN instr(address_1,', ') = 0 THEN to_char(substr(address_1, (instr(address_1, ',') ) + 1, (length(address_1) - instr(address_1, ','))) || ' ' || substr(address_1, 1, instr(address_1, ',') - 1))
        -- third case is that there is a comma, then space, then number immediately following.
        -- this is the most common case, but we write this upside down logic for Oracle
        ELSE substr(address_1, (instr(address_1, ',') ) + 2, (length(address_1) - instr(address_1, ','))) || ' ' || substr(address_1, 1, instr(address_1, ',') - 1)
        -- without the separate second and third cases, we cut off the first digit of the street number
        END AS STREET_ADDRESS 
     , ADDRESS_2 AS CITY_STATE
     , ADDRESS_3 AS ZIP
     , STATUS
from YU_AXIS.YUSTART_BUILDING_LOCATIONS_V WHERE ADDRESS_1 is NOT NULL

Transform

We do a transform called Buildings. This is a straightforward transform from a staging table called u_ytm_buildings, moving data into cmn_building. We extended cmn_building for this purpose.

We coalesce on building_number, which is really called u_building_number, because it's an extension of the base table column set.

Scheduling

After talking with Emily Ureski, she says this data changes rarely. Maybe once per month. We do it weekly to lessen the lag following an update. We can of course kick this off manually if we ever need to do so.

Displaying the data in ServiceNow

This data feeds the catalog items for requesting an ethernet drop, and moving a desk telephone. (These items are part of START replacement and are hidden as of June 2013).

The raw data can be dumped by typing cmn_building.list into the ServiceNow filter text at the top left corner of the UI.