From Master Address List to GIS Enabled Data Warehouse

Download Report

Transcript From Master Address List to GIS Enabled Data Warehouse

Master Address List
GIS Enabled
Data Warehouse
The Infrastructure
 One
Central GIS Map that Drives our
Location Data
 One Central Database containing our
Addresses and Location Data
 All operational applications have their
address data loaded and managed
from the Central GIS MAP and
Location Database
 GIS Enabled Data Warehouse
Clear and Straightforward Goals
Save (Time and Money) and
Improve (Quality and Consistence)
Managing Location Data
– Enterprise Perspective (everyone must be on
– Manage Locations in one place and push to all
operational databases
Create a Simple Data Warehouse from
several key operational databases
– Event
– Date
– Location
Save (Time and Money)
Improve (Quality and Consistence)
 Must
have the entire organization
supporting this initiative
 Must Manage addresses and
locations in only one place in the
– We have 13 operational applications
that depend on Addresses from Police
dispatch to Permit and Development
Enterprise Perspective
CIO and City Manager must champion all
things that are enterprise wide
 Project leader reports directly to City
 Involve all Department heads and their
key people in determining the business
process to centrally manage locations and
 Manage all types of locations not just
Manage Locations in one place
 Clean
your data (you now have a
place to store it)
 Departments will no longer add
addresses into their own databases
 All addresses and street names will
be added into the central location
Clean Your Master Data
Master list of street names
 Master list of segments from the Centerline
GIS data
 Associate segments and streets
 Set ranges on segments
 Routing data for segments i.e. speed limits
 Point addresses
 Validate intersections
 QA, QA, QA
Relate Segments to Road names
Relate loc (Addresses) to segment/roads
Citywide Addressing and Location Infrastructure
Location Repository
Address Information
Map Files
Address Information
By May 19
By May 19
Initial Clean
Street names
and Addresses
Process to
Clean & Manage
Look at address information
incrementally loaded from
External Organization
Parallel Dependent on
Ext Org
By Dec 31
Business Process By
June 1
Technology By Dec 31
External Organizations
i.e. Polk County, Polk
Directory, Warren County,
Fire House, IDOT (LRS),
MSAG, Water Works, USPS,
Incremental Load
By April 18
Each Business Area will feed address
information to the Central Address Business
By June 19 Rest
Address Information
By Oct 15
Business will determine what to send to the DW
- Address Information
- Event Information
- Date and Time Information
Operational Applications
By Dec 15
Data Warehouse
i.e. I/CAD, I/Leads, Tidemark,
Heat, Firehouse, Storm
Water, Police Narc, Route
Smart, People Soft,...
\\cdmdb1\E-VOLUME\DBA\Projects\DBA URISA Conference Presentation\Designing Planning and Communications\Address Data Transformation Architecture.vsd
No longer add address to operation
 This
is why you need the CIO, City
Manager and department heads
involved at the planning stage.
 By far the biggest Business
commitment and the first step.
Benefits of Central management
 All
13 applications will have the exact
same address data
– When Departments work together they
all have the same addresses to talk
– We do not have any cleaning to do to
load the location data from these 13
operational applications into our Data
Benefits (Cont)
 If
one department finds a problem
then the fix is added to our central
address database and pushed out to
all 13 operational databases so the
entire city organization benefits from
one person in one department
discovering a problem
Benefits (Cont)
 Likewise
if one external partners
such as the Water Works finds a
problem we can verify this
discrepancy and fix the problem
which will benefit all 13 of our
operational applications and all of our
external partners.
Benefits (Cont)
 Once
the infrastructure is in-place,
we can use 1/13th as much effort to
get really great quality address data
and all 13 applications will get the
same extremely high quality data
with this very small amount of effort.
Benefits (Cont)
 When
we add our 14th or 15th
application we simply tie them into
our architecture and they will
immediately benefit from our
extremely high quality of addresses.
Benefits (Cont)
 We
have our very best address
people making the decisions as to
what the addresses should be for our
Problems with Central Management
 It
took a real cultural change for the
departments to give up control of
their address data and look to the
enterprise to provide this data and
functionality. The key was that our
CIO and City Manager were 100%
behind this initiative.
Problems (Cont)
 Departments
now have a
dependency on the central Address
Administrators group to change the
address and the department must
now wait for one of the Address
Administrators to change the address
before the change can get into their
departmental database.
We had to build an infrastructure to
support this centralized architecture.
– Central Address Database (ERS)
– Initially populate and clean the ERS database
– Create a GUI application to manage the ERS
– Create Business Process to Centrally Manage
– Create Data transformations from ERS to each
operational database
– Create Data transformations from each
operational databases to our Data Warehouse
(This step is infinitely easier then if we would
not have implemented this architecture)
Key Components of Central
Address Repository
Location Repository
GIS Maps
Iowa DOT
Our Central
Address DB
Key Strategies to move data from
the Central Database to
Operational Databases
Expose your master data in a canonical format so
you can change the master structures without
changing all transformations
Process to Immediately move address data to
operational database when a change is made in
the master database
– Replication Server
– SQL Triggers
– Third party tool
Special work needs to be done for applications
that are integrated with GIS map data.