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