HomeMy WebLinkAboutFIN-10-127 - Customer Information System AssessmentK .R
Firru►0— d13&vicek--
REPORT
REPORT TO: Mayor Carl Zehr and Members of Council
DATE OF MEETING: September 7, 2010
SUBMITTED BY: Dan Chapman, Chair, and Members of the Customer
Information System (CIS) Steering Committee
PREPARED BY: Joyce Evans, Director of Revenue
WARD(S) INVOLVED: n/a
DATE OF REPORT: August 30, 2010
REPORT NO.: FIN -10 -127
SUBJECT: CUSTOMER INFORMATION SYSTEM (CIS) ASSESSMENT
RECOMMENDATION:
THAT, in accordance with the findings of the Customer Information System (CIS) Assessment
appended to staff report FIN -10 -127, staff be directed to:
1. Continue with due diligence to pursue the acquisition of the SAP tax and utility solutions
to replace the existing CIS system and further consolidate the number of major
applications at the City; and
2. Establish a senior level position within the CIS project using the existing budget, in lieu
of continuing with the external CIS consultant, to support ERP Business Transformation
to lead the business process design and optimization processes for the new system
AND FURTHER THAT staff be directed to report back to Council in 2011 with a detailed
business case for the Customer Information System, including refined costing and a more
detailed project schedule, prior to moving forward with a system change
BACKGROUND:
The Customer Information System (CIS) supports the tax and utility billing processes of the City.
The utility billing functionality supports water, wastewater, gas and storm sewer, the last
currently being under development. The CIS application was acquired from Sierra Systems ten
years ago, when the company decided it was not profitable to complete development and offer
either of the tax or utility billing components to municipalities across Canada. At that time, the
City made the decision to acquire the system and continue to enhance it with the assistance of
the system architect. That individual has been consulting to the City of Kitchener for the past ten
years. One of the strengths of the current CIS is the integration between the tax and utility
customer databases, something which Council previously affirmed as an important
characteristic of the CIS.
The City retained Prior and Prior Associates Ltd. in 2009 to undertake a CIS Assessment to
determine if the City is achieving value from the funding of the CIS application, and whether
there are other opportunities that would be more attractive in the future. There were three
objectives within the scope of the review:
1. Determine whether the City is getting value for the funds expended on the CIS system
going forward;
2. Assess options and alternatives to continuing with the current in -house developed
solution; and
3. Provide the City with a template and approach for evaluating other similar situations.
The purpose of this report is to summarize the results of the review and make recommendations
for the next steps to be taken. Please refer to the consultant's report (attached) for the detailed
analysis.
REPORT:
The Customer Information System Assessment report (attached) identified three major options
to providing a tax and utility billing solution for the City:
1. Continue with the current CIS system;
2. Buy an off - the -shelf (OTS) solution for one or both components of the CIS; and
3. Re- develop (re- architect) the CIS system to a more sustainable architecture and
technology platform
The second option identified a number of constraints specific to the City of Kitchener, including
the gas de- regulation requirements for the Province of Ontario and the highly integrated
functionality between tax and utility billing at the City.
Evaluation Criteria
Two sets of evaluation criteria were employed in order to compare and contrast the three
options — strategic factors and tactical criteria. These criteria were applied to tax and utility
solutions together although a separate scoring was done for tax. Strategic factors included:
• Alignment with industry trends to buy technology solutions rather than build internal
systems;
• Alignment with the City's corporate technology direction to buy versus build;
• Leverage the City's ERP investment by implementing the SAP tax and utility solutions;
• Leverage industry best practices inherent in purchased solutions rather than perpetuate
what are often less desirable internal practices by incorporating them into in -house
developments;
• Position the organization to adopt the expanded and integrated functionality of industry
solutions, such as customer relationship management (CRM) rather than a separate
system for this purpose (ACR);
• Become part of a large forum of users and implementers that are knowledgeable about
the solution;
• Availability of system upgrades through regular maintenance releases; and
• Availability of skilled and knowledgeable resources when needed for expansion or future
upgrades
The following tactical criteria were employed in evaluating the three options, with the weightings
appearing in brackets:
• Implementation considerations (10)
• Ability to address ongoing enhancements /upgrade considerations (15)
• Staff satisfaction and impact (15)
• Risks (10)
• Support (10)
• System / Software functionality, features and suitability (40)
• Costs (software, implementation, 15 -year support and maintenance)
Cost was handled separately from the weighted scores.
Significant Findings
The scoring indicated that:
• Over a ten year period, there is no standout option in terms of total costs, although there
is more disparity in costs over the fifteen years.
• The purchased option, while not outdistancing the others, seems to be a reasonable
option with good scores across all categories, better overall scores and lower costs over
the fifteen year period.
• Re- architecting the system would seem to be a higher risk option with regard to the
acquisition of senior technology resources and timely delivery; with a less tangible
overall result and lower costs over the ten year period.
• Status quo is of low overall risk, has no adverse implementation impact but does not
score well on future implementation and is of somewhat higher cost compared to the
purchased systems option.
• Over a longer time frame (fifteen years), the cost - benefit of the purchased option will
continue to improve. Tier 1 systems (SAP), well maintained, will not need replacement
for many years.
• The purchased option will allow the City to invest in a critical mass of SAP professionals
which will be available to support other SAP solutions, such as the Financial System
and /or possibly Human Resources and Payroll in the future.
• While still having somewhat higher cost, this evaluation shows that the current CIS
application delivers reasonable value for the funding expended.
Significant Recommendations
The report provides a number of recommendations, but proposes two major recommendations
for immediate action:
1. Continue with due diligence to pursue the acquisition of the SAP tax and utility solutions
to further consolidate the number of major applications at the City; and
2. Establish a senior level position to support ERP business transformation to lead the
business process design and optimization processes for the new system.
The proposed new role would lead the change management process during the implementation
of a new tax and utilities system, as it will require changes in business practices entrenched
over many years which are supported by an internal application. This role is proposed in lieu of
continuing with the current CIS consultant. This position would also be responsible for leading
the business transformation and optimization processes for tax and utility billing, also potentially
extending to continuing improvements to the SAP Financial System in the future, as the
organization moves to best practices. This role will be instrumental in completing the due
diligence and business case work prior to making a final decision on the direction for CIS.
There is considerable urgency to moving forward with these recommendations, so as to
minimize expenditures on the current CIS system by stopping all non - essential development
and enhancements.
FINANCIAL IMPLICATIONS:
The new position proposed to be created, along with the costs of preparing a business case, will
be funded from the CIS project budget in lieu of continuing with the external CIS consultant.
ACKNOWLEDGED BY: Dan Chapman, General Manager of Financial Services
August 2010
City of Kitchener
Customer Information System
(CIS) Assessment
CUSTOMER INFORMATION SYSTEM (CIS) ASSESSMENT
TABLE OF CONTENTS
EXECUTIVE SUMMARY ......................................... ..............................1
CIS GOVERNANCE & APPROACH ........................... ..............................9
CIS - CURRENT SITUATION ................................ ............................... 13
FINDINGS FROM THE ENVIRONMENTAL S CAN ........... .............................23
OPTIONS & ALTERNATIVES .................................. .............................29
DETAILED ANALYSIS OF OPTIONS ........................ ............................... 39
RECOMMENDATIONS ......................................... .............................47
POTENTIAL ROADMAP ....................................... .............................49
APPENDICES
A CIS TECHNICAL EVALUATION
B UTILITY SOFTWARE BENCHMARKING
C EVALUATION DETAILS
Executive Summary
Prior & Prior Associates Ltd. was retained by the City of Kitchener
to develop a Corporate Technology Business Plan.
Project scope The Customer Information System (CIS) Assessment is a sub
project of the Corporate Technology Business Plan (CTBP)
project. The CIS system supports the tax and utility billing
processes of the City. The objective of the review was to
determine if the City is achieving value from the funding of the
CIS application, and whether there are other opportunities that
would be more attractive.
The objective of the review is three fold:
■ Determine whether the City is getting value for the funds
expended on the CIS system going forward
■ Assess options and alternatives to continuing with the current
in -house developed solution
■ Provide the City with a template and approach for evaluating
other similar situations.
The utility billing functionality supports water, wastewater, gas
and storm, the latter currently being under development. The CIS
application was acquired from Sierra Systems about 10 years ago,
when the company decided it was not going to be profitable to
complete the development and offer either of the tax or utility
billing components to municipalities across Canada. At that time,
Kitchener took the decision to acquire the system and continue to
enhance it with the assistance of the system architecture. That
individual left Sierra at that time and has been consulting to the
City of Kitchener for the past 10 years.
Prior & Prior Associates Ltd. page 1
CIS Assessment Executive Summary
Environmental scan The industry scan served two purposes, the first to find out how
other municipalities had progressed with the Sierra system from
both tax and utility billing perspectives, and the second to
identify potential alternatives to the City's CIS. The findings from
the environmental scan confirmed the scarcity of suitable
products in the marketplace to support the gas utility. Details of
the scan can be found in Section 3.
City of Kitchener Options The review identified three major options to providing a tax and
utility billing solution for the City:
■ Continue with the current CIS system
■ Buy an off - the -shelf (OTS) solution for one or both
components of the CIS; the assessment has been profiles in
the basis of SAP's utility and tax solutions
■ Re- develop (re- architect) the CIS system to a more sustainable
architecture and technology platform
The second option led down a fairly narrow path, with the gas de-
regulation requirements for the Province of Ontario being the
most significant delimiter, and the highly integrated functionality
between tax and utility billing coming in second.
Evaluation criteria Two sets of evaluation criteria were employed — strategic factors
and tactical criteria. They were applied to tax and utility
solutions together although a separate scoring was done for Tax.
Strategic factors These factors taken on their own could result in a decisive
position on the available options. To further substantiate the
analysis, the tactical decision framework was applied. Strategic
factors included:
■ Alignment with industry trends to buy technology solutions
rather than build internal systems
■ Alignment with the City's corporate technology direction to
buy versus build
■ Leverage the City's ERP investment by implementing the SAP
tax and utility solutions, and in future potentially HR / Payroll
■ Leverage industry best practices inherent in purchased
solutions rather than perpetuate what are often less desirable
internal practices by incorporating them into in -house
developments
Prior & Prior Associates Ltd. page 2
CIS Assessment
Executive Summary
■ Position the organization to adopt the expanded and
integrated functionality of industry solutions, such as
customer relationship management (CRM) rather than a
separate system for this purpose (ACR)
■ Become part of a large forum of users and implementers that
are knowledgeable about the solution
■ Availability of system upgrades through regular maintenance
releases
■ Availability of skilled and knowledgeable resources when
needed for expansion or future upgrades
Some of these factors have been taken into consideration in the
tactical assessment, albeit under slightly different considerations.
At the strategic level, the evaluation of the solutions is either a
positive or negative, while with the tactical assessment there are
scores and weightings.
Tactical criteria The following tactical criteria were employed in evaluating the
three options. Weightings appear in brackets.
• Implementation considerations (10)
• Ability to address ongoing enhancements /upgrade
considerations (15)
• Staff satisfaction and impact (15)
• Risks (10)
• Support (10)
• System / Software functionality, features & suitability (40)
■ Costs (software, implementation, 15 years support and
maintenance); cost is handled separately from the weighted
scores (cost per point is determine on the basis of the total
points for each option)
The weightings used in the assessment were vetted and approved
by the CIS Steering Committee prior to the scoring exercise.
Prior & Prior Associates Ltd. page 3
CIS Assessment
Executive Summary
69.3
67.1
Results of strategic The following
table shows how the strategic factors
were
assessment applied:
ME==
Industry trend to buy vs build
No
Yes
No
City corporate policy buy vs build
No
Yes
No
Leverage City's ERP investment
No
Yes
No
Leverage industry best practices
No
Yes
Yes for technical;
less likely for
business
processes
Flexibility to expand functionality
No
Yes
To a limited extent
through configuration vs development
as the core
functionality does
not exist
Opportunity join a large forum of users
No
Yes
No
Availability to share system upgrades
No
Yes
No
through regular maintenance releases
Availability of skilled and
No
Yes
No
knowledgeable resources
Tactical assessment Behind each of the tactical evaluation criteria are many factors
results with the first five ranging from 7-1O detailed criteria tothe
functionality containing over 100. The total scores for each
option are as follows. |t should be noted that itis not uncommon
for evaluation frameworks to result in overall scores that are
quite close. The detailed scoring can be found in Appendix [.
Macau=
Scores / 100 points 67.6
69.3
67.1
Prior & Prior Associates Ltd. page 4
CIS Assessment
Executive Summary
Costs The costs took into consideration a number of factors:
■ Software licence costs for each of tax and utility solutions
■ Implementation costs for each of tax and utility solutions
■ The cost of additional functions such as GDAR
■ The cost of the common integration
■ Maintenance costs for each (10 and 15 years)
■ City project management & business staff involvement
■ Hardware and software costs not related to the business
applications are considered neutral to the options and not
included in the calculations, as are training, business case
preparation, or inflation
■ City support for options 1 and 3
It should be noted that while the costs that can be measured are
quite close for the first several years, there are other costs that
are not so obvious, such as the overhead associated with deciding
on future enhancements for options 1 and 3, and the ongoing cost
of business support. With the ever increasing resource
requirements for the current CIS application, the costs continue to
escalate, as with the current increase in resources due to the
storm water management rate.
Implications of the scoring The scoring indicates that:
■ Over a ten years period, there is no standout option in terms
of total costs over the 10 years, although there is more
disparity in costs over the 15 years. The scoring and cost per
point are all within relative proximity of one another.
■ The purchased option, while not outdistancing the others,
seems to be a reasonable option with good scores across all
Prior & Prior Associates Ltd. page 5
RIMMM
10 Year costs
$15,950,000
$15,130,000
$14,950,000
15 Year costs
$23,920,000
$20,220,000
$20,470,000
It should be noted that while the costs that can be measured are
quite close for the first several years, there are other costs that
are not so obvious, such as the overhead associated with deciding
on future enhancements for options 1 and 3, and the ongoing cost
of business support. With the ever increasing resource
requirements for the current CIS application, the costs continue to
escalate, as with the current increase in resources due to the
storm water management rate.
Implications of the scoring The scoring indicates that:
■ Over a ten years period, there is no standout option in terms
of total costs over the 10 years, although there is more
disparity in costs over the 15 years. The scoring and cost per
point are all within relative proximity of one another.
■ The purchased option, while not outdistancing the others,
seems to be a reasonable option with good scores across all
Prior & Prior Associates Ltd. page 5
CIS Assessment
Executive Summary
categories, better overall scores and lower costs over the 15
year period.
■ Re- architect the system would seem to be a higher risk option
with regard to the acquisition of skills senior technology
resources, timely delivery (a 4 four implementation was used
compared to two years for the purchased solution), with less
tangible overall result and lower costs over the 10 year period.
■ Status quo is of low overall risk, has no adverse
implementation impact but does not score well on future
implementation and is of somewhat higher cost compared to
the purchased systems option.
■ Over a longer time frame (15 years), the cost benefit of the
purchased option will continue to improve substantially, with
the OTS option gaining approximately $400,000 for each
additional year. Tier 1 systems (SAP), well maintained, will not
need replacement for many years.
■ As well, the purchased option will allow the City to invest in a
critical mass of SAP professionals which will be available to
support other SAP solutions, such as the Financial System
and /or possibly Human Resources and Payroll.
■ While still having somewhat higher cost, this evaluation shows
that the current CIS application delivers reasonable value for
the funding expended.
While the above considerations indicate that "purchased system"
is the preferred option, the key deciding factors will therefore also
be:
■ Re- affirmation of the City's direction with respect to "buy'
versus "build"
■ Confirmation of the functioning of the corresponding Tier 1
system (SAP) in Enbridge, and the availability of the GDAR
implementation or configuration model in that organization;
initial discussion suggests that securing the configuration is
quite possible, costs might be another factor
■ Feedback in the industry with regard to the SAP partnership
with London, Cambridge and Sudbury having failed was found
not to have any negative report about the SAP product rather
some poor decision - making with regard to the selection of the
systems integration vendor.
Prior & Prior Associates Ltd. page 6
CIS Assessment
Executive Summary
Major recommendations 1. Continue with due diligence to pursue the acquisition of the
SAP tax and utility solutions to further consolidate the
number of major applications at the City.
2. Establish a senior management position to support ERP
Business Transformation to lead the business process design
and optimization processes for the new system.
With regard to the second major recommendation, it is essential
that the City acquire the services of a senior manager/ project
manager to lead the change management process during the
implementation of a new utilities system, as it will require
changes in business practices entrenched over many years and
supported by an internal application.
This new position will be responsible for leading the business
transformation and optimization processes, not only for Tax and
Utility Billing, but also the continuing improvements to the SAP
Financial System, as the organization moves to best practices.
There is considerable urgency to moving forward quickly, so as to
minimize expenditures on the current CIS system by stopping all
non - essential development and enhancements.
Prior & Prior Associates Ltd. page 7
CIS Governance &Approach
This section contains a description of the CIS governance framework
and an overview of the approach to the assignment.
CIS governance The CIS application is managed through the CIS Steering
framework Committee which meets monthly to review status on active
projects, provides direction and sets priorities for projects, and
addresses issues related to projects, resources, equipment, and
so on. Steering Committee agendas are prepared by the CIS
Project Manager.
1 Mike Bolger I
I Manager Of I
1 Development �
Information Technology
CIS Steering
Committee
an unapman 1
GM Financial
1 Services /City
Project 3 sponsor
1
Wally Malcom
1 Director of Utilities
U tilities
Project Management
Les Piotrowski
BSA — Team
Leader
i
Information Technology
Joyce Evans 1
1 Director Of
Revenue
Revenue
Prior & Prior Associates Ltd. page 9
CIS Assessment
I i
I Diane Kwan I
I Gya Pdministrator /
�- - - - - -- -- - - - -i�
CIS Governance & Approach
The City has also established a CIS Task Force comprised of
representatives from the departments and users. The Task Force
meets weekly to discuss:
• Support and maintenance
• Changes in priority for Support Log /Dev Track items
• Proposes support issue Items for maintenance releases
• Addresses implementation matters wrt maintenance releases
with the CIS Development Team
• May recommend project priorities and direction to CIS
Steering Committee
■ May address resource issues related to CIS
■ May identified Working Groups to implement projects with
the CIS Development Team
■ CIS high level estimates
This group meets weekly which based on comments from
members of the group, this is probably too frequent. Also, many
times the agenda focuses on a specific project which could be
addressed either through a project team meeting or sub group
rather than having the entire Task Force attend each meeting.
CIS Task Force
as Piotr owski
BSA Team
I Leader
Carolyn Boegel I
QAAdminislrator
I i
I Diane Kwan I
I Gya Pdministrator /
�- - - - - -- -- - - - -i�
CIS Governance & Approach
The City has also established a CIS Task Force comprised of
representatives from the departments and users. The Task Force
meets weekly to discuss:
• Support and maintenance
• Changes in priority for Support Log /Dev Track items
• Proposes support issue Items for maintenance releases
• Addresses implementation matters wrt maintenance releases
with the CIS Development Team
• May recommend project priorities and direction to CIS
Steering Committee
■ May address resource issues related to CIS
■ May identified Working Groups to implement projects with
the CIS Development Team
■ CIS high level estimates
This group meets weekly which based on comments from
members of the group, this is probably too frequent. Also, many
times the agenda focuses on a specific project which could be
addressed either through a project team meeting or sub group
rather than having the entire Task Force attend each meeting.
CIS Task Force
Carol King Feras AbdulHadi
Colections Property Tax
Supervisor Administrator
Sheila Taylor
CIS System
1 —
Administrator
Revenue
Prior & Prior Associates Ltd. page 1®
orame
Baillargeon
Direct Purchase
Sharon McDonald
RWH Marketing
Grou
I
I
I
\
I
1
`-
--------
------------ -!
Utilities
Carol King Feras AbdulHadi
Colections Property Tax
Supervisor Administrator
Sheila Taylor
CIS System
1 —
Administrator
Revenue
Prior & Prior Associates Ltd. page 1®
CIS Assessment
CIS Governance & Approach
CIS Development Team The CIS development team is comprised 16 full time staff plus an
external consultant. The team is responsible for the following
items, and reports to the Manager, Development.
• Prioritization of support log items
• Implementation of maintenance releases with user
departments
• Develops solutions and implements projects with user
departments
• Proposes support issues that should be addressed in future
maintenance releases
■ Identifies and supports recurring maintenance projects, such
as server and database upgrades, PowerBuilder upgrades.
The following organization chart shows the core team of
development staff in the ITS division plus other dedicated staff in
the departments. The team is comprised of a project manager,
developers, four QA staff and dedicated users.
r 1
I Les Piotrowski I
I BSA -Team Lead I
j - � ____
I r__
I Kathy Atherton
I SystemsAnaNst
I
I Ron Lohr
I Systems Analyst
I
I
I
I Ruben Patolsky
� Sr. Programmer
I
I
Andrei Nil�laev
Sr. Programmer
1
\- -- -- -- - --- -- -- - --
— �evlopers
I
Paula Costa
Systems Analyst
Steven Liu
Sr. Programmer
I
I
Marius Beceriga
Sr. Programmer 1
I
I
I
I
I
1
ol
� �I
Melanie Hill I I John Little
Data Base Analyst I I I Consultant j l
\ '� 1; .— . —. —. —. .
` CM7` nsultaT
Data Base Analyst
Prior & Prior Associates Ltd. page 11
Caro n Boe el
N g
i Diane Kwan
I QA Adminstrator
h
" " ": QA Adminstrator
I
I
Godfrey
I
I
Mwesigwa
.......... Victoria Pavlova
QA Analyst
QA Analyst
�� �u�ityF�ssurance �
1
1 CISRerour�
�
CISRerource
I
I
Revenue
Utilities I
1
I
I
Vacant
I
�
Revenue
I
1
User Be partments
r 1
I Les Piotrowski I
I BSA -Team Lead I
j - � ____
I r__
I Kathy Atherton
I SystemsAnaNst
I
I Ron Lohr
I Systems Analyst
I
I
I
I Ruben Patolsky
� Sr. Programmer
I
I
Andrei Nil�laev
Sr. Programmer
1
\- -- -- -- - --- -- -- - --
— �evlopers
I
Paula Costa
Systems Analyst
Steven Liu
Sr. Programmer
I
I
Marius Beceriga
Sr. Programmer 1
I
I
I
I
I
1
ol
� �I
Melanie Hill I I John Little
Data Base Analyst I I I Consultant j l
\ '� 1; .— . —. —. —. .
` CM7` nsultaT
Data Base Analyst
Prior & Prior Associates Ltd. page 11
CIS Assessment CIS Governance & Approach
CIS budget The CIS application is currently funded out of the Utilities capital
budget at $1.3+ million annually. Expenditures have increased
substantially since 2007 when the new gas regulations became
effective and additional contract staff were authorized by
Council.
Discovery phase Both the technology plan and CIS review rely heavily on
interviews and workshops. CIS interviews were conducted with
all directors, managers and supervisors involved with both the
tax and utility billing components. Workshops were held with
tax, revenue and utilities to gain an understanding of the
application and support of the system. The following topics were
addressed:
■ Functional gaps — what the system does and is expected to do
relative to current and emerging business requirements
— Baseline functions, statistics, number of users
— Efficiency functions such as the combined move in/ move
out transaction
— Regulatory requirements
— Integration with other systems or databases
■ IT support & services for CIS relative to user expectations
— Planned new functional development
— Planned enhancements & modifications
— Reactive modifications
— Reactive corrections
— Ongoing support services— minimum level, optimal
— Implementation services
An overall SWOT (strengths, weaknesses, opportunities,
threats) against the CIS system
0 Future expectations and vision for the system
Information gained in the interviews and workshops was followed
up with more focused discussions on selected topics. There was
also a review of sections of programming code.
Environmental scan The second component of the data collection focused on what
other jurisdictions are doing with the Sierra solution, and other
industry solutions. The findings from telephone and onsite
interviews are documented in Section 3 of this report.
Prior & Prior Associates Ltd. page 12
CIS - Current Situation
This section of the report describes the current CIS functionality,
characteristics of the system, and enhancements both planned
and in progress.
Current functionality The CIS system has two major functional components — Tax
administration and billing and Utility account management and
billing. The system supports the following key functions:
Tax administration and The following are key functions of the tax administration
billing component:
• MPAC assessment data is loaded annually with quarterly
supplementary updates
• OPTA data for industrial and commercial properties (non
residential assessment data) is loaded annually
• Calculation and preparation of tax bills twice annually
• Payment processing and payment options
• Assessment roll accounts are created and maintained
• Other tax account management functions include local
improvement information, letters, rebates, etc.
• Address data is maintained in conjunction with the Corporate
Database (GIS), the Amanda system, and Utilities
• Exceptions, messages and to do logs are produced
Utility account The system current supports the following functions for water,
management and billing sewer and gas billing. The system will support a storm water
system rate for January 2011.
■ Account creation and maintenance
• Meter management
• Meter reading, data export and import
• Service order processing
■ Collection processing
Prior & Prior Associates Ltd. page 13
CIS Assessment
CIS - Current Situation
• Message and to do logs
• Rental water heater management
• Calculation and preparation of bills
• Payment processing and payment options
• Single address maintenance function in conjunction with CIS,
the Corporate Database (GIS), the Amanda system, and
Utilities
• Exceptions, messages and to do logs
The system has been maintained as an in -house application since
the system was acquired by the City 10 years ago.
System characteristics Key characteristics include:
• It is a large, critical application to the City
• It combines Tax and Utility functions which makes the solution
unique in local government
• The combination of tax and utilities gave rise to certain
integrated functions for tasks such as "move in ", "move out ",
lawyer's inquiries, etc.
• There are linkages for addresses, accounts, and parcels among
several systems — GIS /Corporate database, Amanda, CIS
• There are three payment streams (CLASS Point of Sale for over
the counter transactions, Banks to CIS, RCS payment
processing)
• There are file transfers and interactions with MPAC, OPTA,
Banks, and Direct Purchase Gas Contractors
• CIS integrates with other city systems — SAP, RCS, CLASS,
Amanda, USS (minimal), ITRON (meter reading) many through
the GIS /Corporate Database
Statistics CIS manages the largest volumes of any city application with:
■ 35,000 move -in and move -out transactions annually
■ 764,000 utility payments; 418,000 tax payments
■ 2.95 million utility transactions; 836,000 tax transactions
■ $481 million revenue from tax and utilities
Prior & Prior Associates Ltd. page 14
CIS Assessment
CIS - Current Situation
CIS functional architecture The following diagram provides a high level overview of the
various functions and interfaces.
Figure 4 — CIS Functional Architecture
SAP
Finance
1
RCS CLASS
(Payment Proc Integrated Functions POS
Banks H
PAC
Assessment
OPTA
Ind, Comm
FINAPP
TAX
Tax modules / Functions
DUtilities modules / Functions
OJointed modules / Functions
OOther City Systems / Functions
External Entities
Bill Printing I Collection
Billing I'
Service Orders I Water Heater
Utility Acct
Meters
Admin '.
Utilities
Cras
I Contractors
H ITRON
Meter Read ing
__ USS
� hedule; a ointmc t)
Using the legend as a guide, the boxes in green (Tax) refer to the
key functions of the Tax component of the CIS system, while the
boxes in yellow (Utilities) refer to the major utility functions. The
CIS system has a single database, shared by both tax and utilities.
This level of integration supports those functions coloured grey,
including functions to efficiently process "move -in" and "move -
out" transactions and lawyers inquiries affecting both tax and
utility accounts, by linking tax and utility accounts to maintain
correct addresses and property parcel references between the
CIS, the corporate /GIS database, and the Amanda system.
The boxes around the periphery identify the external systems
which are integrated to CIS, the key system being the ITRON
meter reading system, which imports the gas and water meter
readings, and upload them into the CIS system.
Prior & Prior Associates Ltd. page 15
CIS Assessment
CIS - Current Situation
The CLASS point of sale (Active Network) module processes over
the counter payments. Mail -in payments are processed by the
RCS payment processing system. FINAPP is a standalone system
that manages the financing processing for gas appliances. USS
(Utilities Scheduling System) handles scheduling of appointments
for utility operations for related services.
Tax assessment information is provided by MPAC (Municipal
Property Assessment Corporation) for the residential properties
and OPTA (the Online Property Tax Analysis tool from the Ontario
Finance Ministry) is used to import commercial, industrial and
multiple residential property data.
CIS technical platform The system was built using a commercially available
development toolkit known as PowerBuilder. It was a common
development tool in the late 90s, but has since experienced a
declining user base as web tools have taken over. The technical
platform and design may be characterized as follows:
■ Built on the Oracle database platform using PowerBuilder
development tools
■ Currently on an earlier PowerBuilder version as upgrades to
newer versions are generally done only every two years
■ Developed using an earlier (pre 2000) paradigm or technical
architecture of PowerBuilder
■ The original design uses PowerBuilder windows and triggers
extensively, resulting in complicated coding structures and
paths, becoming difficult to maintain
■ The original design uses very few PowerBuilder "User
Objects ", thereby not using strongest features of the
PowerBuilder development environment with respect to
object oriented design and programming which was designed
to reduce maintenance effort (in large part a function of the
external consultant's lack of knowledge of PowerBuilder,
coupled with a penchant for embedding significant and older
design features in the business requirements documents)
■ Very large billing modules
■ Extensive customized integrations with other systems
Prior & Prior Associates Ltd. page 16
CIS Assessment
CIS - Current Situation
■ Highly dependent on the original designer / external
consultant for analysis of major changes and enhancements,
and development of business requirements documentation
■ Perhaps most worrisome is the admission that there is no
person at the City that understands the entire system, from
either a functional or technical perspective.
CIS technical assessment Appendix A contains an independent assessment of the technical
structure of the system resulting from discussions with the
project manager, CIS external consultant, CIA team and code and
data base observations with the City's assigned database
administrator.
Summary of key strengths The key strengths of the CIS system are in the integrated nature
& weaknesses of the system, tying Tax and Utility transactions together via the
common elements — people and location (customer, owner,
address, roll number, property parcel). This allows the system to
support integrated queries and services (such as move -in, move -
out). Such features are not found in any other installations, even
when the vendor provides both a tax and utility billing
application.
Strengths The strengths are:
■ Integrated Tax & Utility components:
— To provide unified customer service views
— For specialized functions that cross systems (e.g. move -in,
move -out, lawyers inquiries, including for outstanding
taxes and utility payments)
■ Corporate approach to linking parcels, addresses, roll
numbers, and utilities accounts such that the information is
consistent among the major systems (CIS tax, CIS utilities,
corporate /GIS database, Amanda)
■ Customization and flexibility due to being an in -house
customized system
■ System is considered very easy to use (user friendly) as it has
been designed to match their practices (good or bad)
■ It is a reliable system where changes are subject to extensive
quality assurance testing using dedicated in -house CIA
resources and knowledgeable users
Prior & Prior Associates Ltd. page 17
CIS Assessment
CIS - Current Situation
■ Some functions are considered by users to be very efficient
and effective. Examples of most successful functions include:
— Integrated " move -in ", "move -out" functions
— Exception queues (to do lists to handle processing
exceptions)
— Utilities collection processes
— Billings
— Service orders processing
Weaknesses The major weaknesses of the system are related to two areas —
the quality of gas de- regulation functions, and that tax
adjustments not completely automated. Among the major
weaknesses are:
■ Gas deregulation (GDAR), including Direct Purchase Contracts
was developed and implemented in 2007; at that time the
urgency to comply with the new regulations caused most of
the changes to be "hard coded ", with some others remaining
manual. GDAR I took place over 18 months at a cost of $1.5
Million, during the 2006 -7 period.
■ When the utility collection processes were automated, tax
collection was also specified but never implemented
■ "Tax Adjustments" functions not automated; set up of tax
billing runs are also not automated and require support from
the CIS development team on request by the Tax Business
Unit
■ "Utility Operations" functions for scheduling and appointment
are support by another system, external to CIS
■ Functions are considered "unforgiving ", so error correction of
accounts is time consuming and difficult
■ There are no E- service to provide direct access by the public
via the web
■ Features such as adding additional messages to the bill are
impossible to implement
■ Mobile computing is not available for Utility field staff
Prior & Prior Associates Ltd. page 1S
CIS Assessment
CIS - Current Situation
Enhancements Enhancements, past, in progress and planned provide some
insight into work load and resource utilization.
Recent major The CIS system has been in constant state of change since it was
enhancements acquired by the City. Recent enhancements include:
■ GDAR Phase I --This is the implementation of the gas de-
regulation functions done in 2007 over a 18 months duration
cost approximately $1.5 Million.
■ Utilities collection processing provides support for the
workflow and documentation related to collecting overdue
utility payments
■ E -Post to send bills via Canada Post's e -post service (this was
requested by Council but has had minimal uptake)
Planned enhancements Most of the planned enhancements support regulatory
requirements. Clean up of the GDAR Phase 1 programming and
automation of outstanding manual processes represent the
majority of the planned work.
New requirements include:
■ Harmonized Sales Tax (HST) must be ready for July 1" for
utilities
■ A new storm water rate was approved by Council for
implementation January 1, 2011; the method for calculation
of the storm water rate impacts both tax and utility billing
modules; the shell will be available January 1St, but additional
changes will be required to handle the final business rules; a
report to Council, May, 2010 seeks approval for 2 additional
developers, one QA resource and one Revenue resource for
2011 to be added to the overall CIS work complement for
enhancements and changes over the next two years rather
than compete with other priorities
These new requirements are having a significant impact of the
GDAR Phase 2 projects. The external consultant was continuing to
work on Direct Purchase Contracts while other project staff
pursue the new requirements.
GDAR Phase 11
■ MDR (Multiple Delivery Rates) to support GDAR was
implemented May 2 2010
Prior & Prior Associates Ltd. page 19
CIS Assessment
CIS - Current Situation
■ Direct Purchase Contracts —technical specifications underway;
one development & one QA resource assigned
■ GDAR Transactions deferred until 2011
■ Large Volume Customers Billing — decision taken not to
automate this function as the manual process takes less than
one day
PowerBuilder Upgrade (Development / Technology Platform)
complete July 1s' 2010 with HST rollout
Deposit Interest (in progress) for Revenue originally scheduled for
3rd quarter 2010m has been rescheduled to 4t" quarter or
potentially 1St Q, 2011 due resource constraints with the storm
rate
Tax Collection (specifications done) with no planned work in 2010
Low Income Rebate Program (not scheduled)
Wish list The wish list of outstanding items contains:
■ Maintenance releases for both tax and utilities(firmer timing,
frequency); users didn't understand that all such releases
were put on hold during the Delta projects as the resource
allocation for maintenance was re- directed to SAP and
Cityworks interfaces
■ Tax Collection
• Meter Routing Enhancements
• E- Services
• Billing Messages Enhancements
• Tax Adjustments
• Document attachments
• Field computing
• Gas regulators (linkage with meters)
• System performance during billings (Tuesday)
Prior & Prior Associates Ltd. page 20
CIS Assessment
CIS - Current Situation
Other issues As currently envisioned, compliance with regulatory
requirements or other city priorities, such as the storm water
management rate, will consume the majority of the CIS
resources, both development and users. The continuing demand
for new functionality regardless of the source has continued to
outpace the capacity of the development team. Regular
maintenance releases suffer as do a large number of
enhancements that keep getting deferred, such as the purge and
archive processes. It almost looks like work expands to fill the
time allowed. Further, it is quite evident that the tax component
of the project suffers by comparison to utilities as any shortfalls
impact many fewer staff in tax than on the utilities side.
Certainly this would appear to be the case, where additional
development and testing resources will be added to the CIS team
beginning in 2011 to support the continuing enhancements to the
storm water management rate.
To the credit of the development effort and the routine and
extensive level of testing, the CIS system is generally considered
to be sound and reliable system. The quality of the underlying
code will eventually present risks, in fact, as new functionality is
testing, the CIA resources are still finding old bugs that are buried
in the code.
The following summarizes some of the technology support
requirements and issues, assuming that the CIS application
continues to support the City's business:
■ High demand for resources to support regulatory changes,
■ More available, in -depth subject matter knowledge on the
system as a whole
■ Backup resources and succession planning to the regular
support (when the regular support is assigned to other
projects)
■ Getting advice and consultation from ITS for first line problem
diagnostics and identification, resolution, and work a rounds
seems to be problematic
■ Regular maintenance releases and fixes are needed
■ Long outstanding items (fixes and minor enhancements in
addition to the major item), need some clarity and decisions
with regard to their disposition
Prior & Prior Associates Ltd. page 21
CIS Assessment
CIS - Current Situation
■ Tax requirements should be addressed
■ Time to delivery enhancements and fixes is unacceptable from
an industry perspective; this is both a resource issue as well as
a visibility /transparency issue
■ Business documentation to front end technical specifications
(current documentation, while labeled as business document,
tends to be too technical
■ Need a process for more business like and deliberate process
for priority setting among enhancements and changes
■ CIS task force weekly meeting appears are too frequent and
could benefit by less frequent but perhaps more intensive
meeting.
■ There may to be more connection between CIS task force and
Steering Committee so that information can be effectively
communicated to members of the steering committee in a
timely fashion to allow them the opportunity to discuss
agenda items with their staff who sit on the task force
Need an ongoing "active" and accessible work plan covering
current and upcoming enhancements and changes for both
the CIS task force and the Steering Committee (January work
plan/ spreadsheet not updated until May)
Prior & Prior Associates Ltd. page 22
Findings from the
Environmental Scan
This section of the report describes the findings from the
environmental scan. This scan is comprised of three components
- the Sierra (CIS) system and current users, a review of solutions
used by Ontario gas utilities, and a survey of off the shelf (OTS)
solutions that could support tax and utility billing.
Other Sierra system users Research revealed that the City of Saskatoon was the first
contractor with Sierra Systems for the CIS application. As other
municipalities came on board, Saskatoon actually acquired
modest royalties (funding) from other organizations (Medicine
Hat, Kingston, etc.)
CIS Assessment
Findings from the Environmental Scan
Medicine Hat Utilities
Electric, gas, water, sanitary
Recently purchased the
Cogsdale utility billing system
32,000 accounts' 150,000
(Charlottetown PEI); when
services; high level linkage to
contacted the vendor refused to
Parcel PIN & civic addressing;
consider configuration of the gas
processing to support the Ontario
deregulation; system easily
handles MDR
one systems administrator
City of Kingston Water & wastewater 2.5 staff for development and
application support; 2 help desk
100,000 utility customers Electric staff
Kingston plans to "re- architect" Gas Currently share a DBA with City
the account management & Contracted services for re-
collections components while Storm (in planning stage)
development from Fuse Forward
moving meter management to
their Maximo application (focus
on asset management); GDAR
implemented outside CIS; plan to
incorporate tax
City of Kitchener Water & wastewater 16 staff with 2 additional staff
approved for storm water rate
72,000 utility accounts Gas (for additional detail see chart in
60,000 parcels Storm (in planning stage) Section 1)
Additional comments — Utilities Kingston's re- architect project is driven by the need to
Kingston comply with the Ontario hydro industry legislation by 2011. They
have done the EBT work for the electric component prior to
doing the GDAR work for the gas de- regulation functions, and
they were able to apply much of their electric EBT know -how to
the GDAR work. As well, they used Oracle workflow to program
the processing around the GDAR transactions outside of CIS,
rather than programming all these functions and rules in
PowerBuilder.
They are in the process of re- developing the CIS system via an
external system development firm, using web tools (.net) to get
Prior & Prior Associates Ltd. page 24
CIS Assessment
Findings from the Environmental Scan
out of the PowerBuilder environment (they only have one
PowerBuilder developer) and to modernize the CIS system. They
will likely treat the CIS system as purely for account management
and billing. Functions for customer service, service order, work
order, equipment and meters will likely be handled by the work
management system (Maximo). They find the current CIS system
very difficult to enhance and maintain.
All users of the original suite are largely in maintenance mode
without any major enhancements since initial implementation,
contrary to the City of Kitchener.
Gas Utilities in Ontario & There are five gas utilities in Ontario — Enbridge, Union Gas,
their software solutions Kitchener, Aylmer and Kingston. The de- regulation of the gas
industry in Ontario while similar in some ways to the electric de-
regulation, suffers from a smaller pool of clients, and therefore
many fewer options for re- automation.
The systems in use in these organizations are:
Kitchener —the CIS system as originally developed by Sierra and
maintained by the City
Utilities Kingston — same CIS system as originally developed by
Sierra and maintained by the Utilities Kingston, but on a much
lower level of enhancement
Enbridge — SAP Utilities solution with the GDAR implementation
via the SAP EBT function
Union Gas —The SCT Banner CIS system, as customized over time
by Vertex (the US vendor of Enlogix CIS, which is the successor to
SCT Banner CIS)
Of the three gas utilities, only Enbridge offers a real option to the
City of Kitchener. The Enbridge solution is of particular interest if
the City is to pursue the "purchase" system path to replace the
current CIS system. There would be synergy between the City's
SAP Financial System and the SAP Utilities solution. Enbridge has
gone live with the new system, which has been configured to
address the GDAR requirements. Initial contact with Enbridge
suggests they would be willing to discuss their configuration of
the GDAR requirements should the City decide to pursue the SAP
option.
Prior & Prior Associates Ltd. page 25
CIS Assessment Findings from the Environmental Scan
Of the shelf (OTS) utility The firm of Util- Assist provided an initial overview of available
billing solutions utility billing applications widely used in North America. Their
report can be found in Appendix B.
In summary, it comments on the level of consolidation of firms
offering utility management solutions. These comprise a range of
software products that support small to mid size hydro utilities,
and they provide a range of costs for these solutions. They also
identify what are known in the industry as a Tier 1 solution, where
they discuss the SAP Utilities solution.
There are many CIS systems available in the market place,
installed and in use in various Ontario municipalities and utilities.
However, these are mostly for water, wastewater and hydro.
None have been configured to support the gas industry in Ontario
with the exception of SAP.
Vailtech tax & utility One Ontario firm, Vailtech (Ottawa based) offers the municipal
billing systems sector both a tax administration system and a utility billing
application. The City of Waterloo has both of these systems and
supports their operations without any demands on IT resources.
Vailtech has more than 50 clients, most using the tax system
which has been a leader with municipalities for several years.
The utility billing application is more recent, and was designed to
support municipal water and wastewater billing operations, and is
not suitable for gas or electric utility billing. There are no linkages
between the two systems, and obviously the utility billing system
does not support the gas industry requirements. It is useful to
note that the combined cost of both applications with
implementation is less than $600,000, excluding customization to
support the gas industry in Ontario.
Tax billing systems There are three OTS tax billing systems in use in Ontario.
The Vailtech tax billing system is used by many Ontario
municipalities, with Ottawa and Hamilton among the largest
customers. The system is also used by the City of Waterloo.
Vailtech has more than 65 customers and is based out of Ottawa.
The CSDC tax system is used by several Ontario municipalities,
including Burlington, Windsor and Oakville, which are of similar
size to Kitchener. This system is owned by the same firm as the
Amanda system (CSDC). The Amanda is one of the three
integrated components (CIS tax - utilities, CSDC Amanda,
Corporate /GIS Database) for properties and addresses within the
Prior & Prior Associates Ltd. page 26
CIS Assessment
Findings from the Environmental Scan
City of Kitchener. There is not integration between the CSDC
systems.
The SAP tax administration system has been purchased by the City
of Toronto, and they are currently in the process of doing a fit -gap
assessment. Implementation has not yet commenced. Toronto
also has the SAP financial suite implemented in 1999.
Requirements for the tax system were collected from a cross
section of municipalities. The first installation was in Christchurch,
New Zealand. There are no installations in Ontario at this time.
In summary, SAP is generally considered to be a Tier 1 enterprise
level system with robust functionality, high performance and
reliability, but would require substantial implementation costs
and effort. Both the Vailtech and the CSDC systems may be
considered as Tier 2 solutions. Vailtech is the most commonly
used tax system in Ontario. However it is less configurable (i.e.
most functions and workflow processes are pre set and the
technology platform is not as current (e.g. it is still on older
version of Oracle). Vailtech also has a system for water and sewer
billing (which in fact contains linkage to the tax - assessment
property parcel, but no functionality to tie customers together,
but has no corresponding gas utilities functions. The effort to
customize it to handle gas utilities and to integrate the customer
functionality would be significant and that presents a significant
risk.
SAP Utility Billing System This application has been available for several years. The SAP
solution is of particular interest since the City of Kitchener
already has the SAP Financial suite. It was decided to investigate
the partnership between London, Cambridge and Sudbury, with
regard to their joint project to implement the SAP Utilities
system.
The SAP Utility System is currently in production at London Hydro.
They are satisfied that they have selected the right product, but
admit they have had considerable difficulty with their systems
integrator. The partnership with Cambridge and Sudbury fell
apart with each municipality having a different reason for
withdrawing from the project. Cambridge backed out first for a
variety of reasons related to both the systems integrator and the
arrangements with London. All three partners agree that the
choice of the integrator was a mistake, going for the lowest cost.
The off shore integrator lacked the detailed knowledge of the
system and forced the parties (particularly London) down a path
Prior & Prior Associates Ltd. page 27
CIS Assessment
Findings from the Environmental Scan
of customization. They are now backing out those modifications.
For Sudbury the costs increased as the project proceeded, and
time was running out. The quality of the solution was not enough
to keep them in the project. Sudbury left the partnership when
Cambridge backed out, and the costs would be too high with only
two partners. Both are pursuing other solutions.
From an integration perspective, all three and Enbridge have
commented that the highest level of expertise rests with
independent consultants who are retained by the system
integrators to boost their team. This is an important consideration
when proceeding with an SAP solution.
Integrated systems It is possible to purchase a solution for both tax and utility billing
from the same vendor, as with SAP or Vailtech. Both cases
involve uncertainties (tax billing for SAP and Gas Utilities for
Vailtech. As well, it should be noted that even in the case of
using the products from the same vendor, integrated business
functions (move -in, move -out etc.) must still be configured or
customized, as such functions are not available out of the box.
Prior & Prior Associates Ltd. page 28
Options &Alternatives
This section of the report outlines the key options identified with
regard to whether to continue with the current CIS application, or
to re- architect it, or to purchase an off the shelf solution. It should
be noted that while three options are discussed, there are more
than three implementation strategies.
Three options There are three options with respect to the City's choices to provide
tax and utility billing services in the future.
Continue with the current Under this option, the City will continue with current CIS system and
CIS system the IT support organization. This does not preclude changes and
fine - tuning with regard to support arrangements, management
oversight, prioritization mechanisms and other practices that would
enhance the support and maintenance of the system.
Purchase an off the shelf Under this option, new system or systems will be purchased and
(OTS) solution implemented to replace the current CIS Tax and Utilities system.
The support organization would be aligned to reflect the needs of
the initial implementation and changes in ongoing support. The
current system will be put on "sunset mode" and the IT /CIS staff will
re- aligned to manage and support the configuration and
implementation of the new system(s). As the new solutions phase
into production, the IT /CIS staff will also be aligned to adapt to the
new support environment and support arrangements. City IT /CIS
will be needed to explore new opportunities, to analyze and
configure future changes and enhancements, to provide operational
support in relation to CIS system, to provide database management
services, etc.
Prior & Prior Associates Ltd. page 29
CIS Assessment
Options & Alternatives
Re- architect the CIS The CIS application would be re- developed systems for improved
maintainability and functionality. Under this option, there would be
an "overhaul" of the system taking into account the outstanding
and evolving enhancements, and to evolve a more maintainable
system for the longer term. This assumes that investment in the re-
architecting the system will reduce the downstream effort in
enhancements, changes, maintenance and support.
A holistic approach will cost less over the longer term than doing
all the bits and pieces of enhancements separately. The recent
undertaking of MDR (Multiple Delivery Rate) enhancement is an
example, which incorporates large number of outstanding billing
changes. This option would be taking that approach to the entire
system.
Expertise not available at the City would need to be recruited to
lead the analysis and design of the new architecture. The
architecture will consider the areas (or features) to be retained
and other areas to be re- architect. For example, some of the
presentation windows and data windows could be retained while
in other cases, some objects will be developed and inherited or
used. Much of the billing module may be retained while the
coding structures may be strengthened. The GDAR structure and
core functionality may be retained while using workflow functions
to implement some transactions. This type of analysis and design
will require substantial expertise and skills, as well as knowledge
of the subject matter.
ITS /CIS staff will also be aligned to assist in the design,
documentation, development and implementation of the re-
architected system. The implementation would be more
straightforward by comparison to a purchased system, as the
system will likely retain the "feel' of the current system. As the
re- architected system takes hold, ITS /CIS support staff would be
reduced to align with the reduced demand for enhancements,
changes and support.
The advantages and disadvantages of each option are discussed
below.
Continue with current CIS There are many good reasons to continue with current CIS
system, as the current system has many strong points:
■ It is a working system, so there would be no risk to
implementing a new system
Prior & Prior Associates Ltd. page 30
CIS Assessment
0ptions & Alternatives
■ Reliability -- it is a well proven system and has been in use for
a decade
■ The technology platforms (Microsoft operating system, Oracle
database) are industry leaders, and there is little chance of
them becoming obsolescent
■ The development tool (PowerBuilder) is City's standard and is
still used in the industry
■ The system's architecture (combining Tax and Utilities
functionality in an integrated coherent system) aligns with the
City's direction of customer service and efficient government
■ City staff generally consider the software to be reliable,
functional and easy to use
■ Significant customization has been undertaken to align with
the City's business practices and preferences
■ The City has developed and sustained a core team to support
the CIS system
■ City staff take comfort with having internal support close at
hand
There are also reasons for looking at alternatives, as there are
latent threats and issues:
■ The trend in municipal information technology is to go with
purchased systems as much as possible for the complex
systems, in order to avoid the complexity of developing,
enhancing and supporting complex systems (note the City of
Waterloo has no support staff for either of the Vailtech tax or
utility billing)
■ The Corporate Business Technology Strategy recommends
purchased solutions as opposed to in -house developed
wherever possible to replace complex, in -house complex
systems or secure new functionality
■ Another principle will focus on leveraging ERP investments
(SAP, CLASS, GIS, Amanda) before buying or building a new
solution
■ Skilled development staff are difficult to recruit and retain
Prior & Prior Associates Ltd. page 31
CIS Assessment
0ptions & Alternatives
■ The CIS support/ development organization has been costly to
sustain
■ The system architecture from the original design does not
easily support ongoing enhancement and maintenance. It is
becoming increasingly more difficult and time consuming to
develop enhancements and to fix problems; enhancements
and fixes create more complexity which in turn makes the
system more difficult to enhance and maintain, forming a
vicious cycle. As referenced earlier, Appendix A contains a
technical assessment of the current CIS system from a system
architecture perspective.
■ The current technical architecture was not designed to be
"configurable" to cope with potential business and regulatory
changes; one of the examples is its inability to address
multiple delivery rates.
■ The enhancements, given the workload in relationship to
staffing, have been focused more towards Utilities
(particularly the revenue side) as the mandatory and
regulatory changes have focused on the gas de- regulation.
Even with a staff compliment of 16, it has been impossible to
address some long- standing enhancements and requests that
are not regulatory or urgent in nature.
While the development tool is still in general use and is the
City's standard, the vendor has a declining user base as
witnessed from the declining revenue of the vendor. Over the
longer term, the risk exists for obsolescence of the
development tool, either by way of declining support, vendor
changes or product changes. As a case in point, the vendor's
progress in moving from client server to web products has
been slow and somewhat difficult.
■ There are outstanding enhancements and new functions that
need to be implemented, such as HST and storm water
management charges; thee include collection and adjustment
functions for Tax, meter routing enhancements for Utilities.
■ Similarly, it is difficult for the CIS support team to put effort
and attention into regular enhancement (and problem fixing)
releases given the workload mentioned above. With this
option, the City would still have to continue with the
enhancement and support of a complex, in -house system.
Prior & Prior Associates Ltd. page 32
CIS Assessment 0ptions & Alternatives
Purchase Tier 1 Solution Under this option, a new system or systems will be purchased
(SAP) and implemented to replace the current CIS Tax and Utility
system. While there are two sets of solutions that fall into the
purchase option, the analysis with regard to purchasing a new
system to replace the current CIS is based on a Tier 1 solution
(SAP) rather than a Tier 2 solution (Vailtech). This position has
been taken as the Tier 2 solution has no experience with the gas
industry and similar to other utility billing applications widely
used in the electric industry, would require significant
enhancement to comply with the GDAR legislation, in addition to
the integration of tax and utility management of customer
related functions.
Under this option, new system(s) will be purchased and
implemented to replace the current CIS Tax and Utilities system.
The support organization would have to re -align with the needs of
initial implementation and changes in ongoing support.
Reasons to pursue this option include:
■ The industry trend for municipal systems is to purchase as
opposed to develop major applications; this principle has been
adopted by the City's senior management team as part of the
Corporate Business Technology Strategy
■ Purchased systems are available and have been implemented
for tax administration in all Ontario municipalities. Some of
these systems are widely used by larger municipalities, such as
the cities of Hamilton and Ottawa. There is only one internally
developed tax application in use in Ontario, developed and
supported by the City of Mississauga and implemented by
Markham and Richmond Hill.
■ Purchased systems are available for Ontario utilities and
municipal organizations, specifically for the water and sewer
utilities and hydro commissions. Purchased systems that
support gas utilities are fewer, and less so in Ontario, where
there are only four gas utilities in Ontario. However, Enbridge
Gas, the largest gas utility in Ontario, has recently completed
the implementation of a purchased CIS system from SAP. The
delimiter on purchase options is the de- regulation of the gas
industry in Ontario.
Prior & Prior Associates Ltd. page 33
CIS Assessment
0ptions & Alternatives
■ With a purchased solution, the City will be able to share in the
synergy and experience of the other users
■ Vendors of "Tier 1" software systems have been quite
aggressive in lowering the pricing of the software licenses for
small and medium size organizations
■ Ongoing maintenance costs are expected to be lower
■ Purchased solutions are designed to be more "configurable"
for both processes and data, to better support future business
and regulatory changes
■ The vendor is responsible for core upgrades and
enhancements (technology platform alignment, common
functions, uncovered deficiencies, industry standard for
integration, etc.), with customer staff focusing on functionality
specific to the City (customized integration, address changes,
etc.)
■ Implementation of new functionality (e.g. customer self
service, mobile computing for field staff, etc.) is expected to
be easier and more timely to implement with a purchased
system.
■ If the Tier 1 OTS solution is SAP, there will be considerable
synergy from a support perspective with regard to the SAP
Financials, Tax and Utility Billing.
The negative factors with regard to the purchased system option
are:
■ The Utility system and the Tax system could come from
different vendors, resulting in a multi- vendor environment
that the City has to bring together, although this would not be
recommended
■ The solution for Ontario gas industry is limited. While there
are a number of off the shelf systems that support the gas
industry, only one has been configured to meet the Ontario
regulatory requirements. Enbridge has successfully configured
the SAP Utilities solution to comply with the Ontario gas de-
regulation (GDAR) requirements.
■ Implementing a Tier 1 solution similar to the Delta project
would be a significant undertaking
■ Tier 1 solutions are more costly
Prior & Prior Associates Ltd. page 34
CIS Assessment
Options & Alternatives
■ Any new system will have a significant impact on ongoing
operations and staff
■ There are a number of inherent risks to be overcome -
incomplete implementation, project cessation, cost over -run,
delays, functional deficiencies, etc.)
■ Integrated functions across Tax and Utilities are not likely to
be in place "out of the box ", and would need to be customized
during the initial implementation unless the City is willing to
forego this functionality. These functions within CIS are
valued for both customer service and for staff productivity
reasons
■ Integration to other City systems would need to addressed
■ City staff may find a purchased system less "user friendly' and
too "generic" in look and feel after years of using CIS. This is a
change management issue.
■ The CIS support team would need some different skills and
resources.
■ Adapting to changes from internal support to external support
will challenge both the business staff and ITS staff
■ A Tier 2 solution would not support either the integration or
the gas de- regulation and would incur costs similar to options
1 and 3.
Re- architect CIS This option is intended to achieve improved functionality and
sustainability. This option involves the re- development of the CIS
solution with a view to adopting a more forward looking
platform and best practices program development to reduce
maintenance and enhancement complexity and costs. Reasons
to pursue this option include:
■ A more reliable and stable system upon completion
■ Reduced complexity, maintenance and enhancement effort
and resources
■ Holistic approach to incorporating outstanding enhancements
■ Retaining the "feel" of the current system, considered by
users to be easy to use and user friendly as opposed to an off
the shelf solution
Prior & Prior Associates Ltd. page 35
CIS Assessment
Options & Alternatives
■ Retaining the functional integration of the current system and
alignment with the customer service focus of the City
The majorconcerns and deficiencies include:
■ Acquiring the appropriate skill mix in resources needed to
design and develop such a solution will be challenging
(expertise in business system analysis and technical system
design, sophisticated expertise in PowerBuilder or other
platform, excellent subject matter /business knowledge
• Risk that the undertaking is not successful, not resulting in a
system that is simpler and easier to use and maintain
• Substantial initial investment in re- architecting
• Risk of cost over run and time delays
• Interruption due to requirements definition, presentation,
testing and implementation
• Staffing re- alignment to support the re- architecting while still
supporting the current system
• A substantial support organization would still be needed but
with new skills
■ The City would still have to continue with the enhancement
and support of a complex, in in -house system
■ Should PowerBuilder be the platform of choice, this would be
an inherent risk with regard to development tool
obsolescence
The City would be open to challenges from the vendor
community, and potentially Council and members of the public
for taking a contrary position to best practice — buy vs build
Prior & Prior Associates Ltd. page 36
CIS Assessment 0ptions & Alternatives
The 4t" option —Tier 2 tax From the initial discussions on CIS, city staff touted the
and utility billing systems integrated nature of the current system which links processes
and data between the tax and utility transactions. As a result of
this finding, the continued analysis placed a very high value on
not only this functionality, but also on the potential level of
acceptance any solution might receive which did not embrace
this level of integration. The productivity and efficiencies behind
these linked transactions is highly valued.
The evaluation categories and their weightings addressed in the
next section of the report will have significant impact on the
scoring the different options. Because integrated functions and
client satisfaction are rated so high, the Tier 2 solutions were
never included in the detailed scoring. This fourth option would
be to implement the Vailtech tax system and their utility billing
system. Since the utility billing system has been designed only for
water & wastewater, it would need to be customized extensively
to comply with the Ontario gas utility requirements. Other
vendors who support gas (Cogsdale) had no appetitive to
configure their solution to the Ontario market with so few
customers. With any of the Tier 2 solutions, the City would not be
any further ahead than staying with the current CIS.
Prior & Prior Associates Ltd. page 37
Detailed Analysis of Options
The executive summary contains most of the information on the
analysis of options at a summary level. That material is repeated
here with some additional information.
City of Kitchener Options The review identified three major options to providing a tax and
utility billing solution for the City:
■ Continue with the current CIS system
■ Buy an off - the -shelf (OTS) solution for one or both components
of the CIS; the assessment has been profiles in the basis of
SAP's utility and tax solutions
■ Re- develop (re- architect) the CIS system to a more sustainable
architecture and technology platform
The second option led down a fairly narrow path, with the gas de-
regulation requirements for the Province of Ontario being the
most significant delimiter, and the highly integrated functionality
between tax and utility billing coming in second.
Evaluation criteria Two sets of evaluation criteria were employed — strategic factors
and tactical criteria. They were applied to tax and utility
solutions together although a separate scoring was done for Tax.
Within each category, there are weighted detailed items against
which scores of 0 to 10 are assigned. The weighted scores are then
summarized for the category and scaled to a range of 0 —100. This
way, the categories themselves can be weighted regardless of the
number of detailed line items. The categories are then weighted
and the weighted scores for the options are then scaled to a range
of 0 —100, as the final score. The costs are divided by the scores
Prior & Prior Associates Ltd. page 39
CIS Assessment
Detailed Analysis of Option
(maximum 100) to derive the costs per point as an indicator of
value for money.
Strategic factors These factors taken on their own could result in a decisive
position on the available options. To further substantiate the
analysis, the tactical decision framework was applied. Strategic
factors included:
■ Alignment with industry trends to buy technology solutions
rather than build internal systems
■ Alignment with the City's corporate technology direction to
buy versus build
■ Leverage the City's ERP investment by implementing the SAP
tax and utility solutions, and in future potentially HR / Payroll
■ Leverage industry best practices inherent in purchased
solutions rather than perpetuate what are often less desirable
internal practices by incorporating them into in -house
developments
■ Position the organization to adopt the expanded and
integrated functionality of industry solutions, such as customer
relationship management (CRM) rather than a separate system
for this purpose (ACR)
■ Become part of a large forum of users and implementers that
are knowledgeable about the solution
■ Availability of system upgrades through regular maintenance
releases
■ Availability of skilled and knowledgeable resources when
needed for expansion or future upgrades
Some of these have been taken into consideration in the tactical
assessment, albeit under slightly different factors. At the strategic
level, the evaluation of the solutions is either a positive or
negative, while with the tactical assessment there are levels of
scores and weightings.
Tactical criteria The following tactical criteria were employed in evaluating the
three options. Weightings appear in brackets.
■ Implementation considerations (10)
■ Ability to address ongoing enhancements /upgrade
considerations (15)
■ Staff satisfaction and impact (15)
Prior & Prior Associates Ltd. page 40
CIS Assessment Detailed Analysis of Option
■ Risks (10)
• Support (10)
• System / Software functionality, features & suitability (40)
• Costs (software, implementation, 15 years support and
maintenance); cost is handled separately from the weighted
scores (cost per point is determine on the basis of the total
points for each option)
The weightings used in the assessment were vetted and approved
by the CIS Steering Committee prior to the scoring exercise.
Results of strategic The following table shows how the strategic factors were
assessment applied:
Prior & Prior Associates Ltd. page 41
t
Industry trend to buy vs build
No
Yes
No
City corporate policy buy vs build
No
Yes
No
Leverage City's ERP investment
No
Yes
No
Leverage industry best practices
No
Yes
Yes for technical;
less likely for
business
processes
Flexibility to expand functionality
No
Yes
To a limited extent
through configuration vs development
as the core
functionality does
not exist
Opportunity join a large forum of users
No
Yes
No
Availability to share system upgrades
No
Yes
No
through regular maintenance releases
Availability of skilled and
No
Yes
No
knowledgeable resources
Prior & Prior Associates Ltd. page 41
CIS Assessment Detailed Analysis of Option
Tactical assessment Behind each of the tactical evaluation criteria are many factors
results with the first five ranging from 7 —10 detailed criteria to the
functionality containing over 100. The total scores for each
option are as follows. It should be noted that it is not uncommon
for evaluation frameworks to result in overall scores that are
quite close. The detailed scoring can be found in Appendix C.
Costs The costs took into consideration a number of factors:
■ Software licence costs for each of tax and utility solutions
■ Implementation costs for each of tax and utility solutions
■ The cost of additional functions such as GDAR
■ The cost of the common integration
■ Maintenance costs for each (10 and 15 years)
■ City project management & business staff involvement
■ All hardware and software costs not related to the business
applications have been excluded, and considered neutral
across all three options, as are training, business case
preparation, or inflation
■ City support for options 1 and 3
10 Year costs
$15,950,000
$15,130,000
$14,950,000
15 Year costs
$23,920,000
$20,220,000
$20,470,000
It should be noted that while the costs that can be measured are
quite close for the first several years, there are other costs that are
not so obvious, such as the overhead associated with deciding on
future enhancements for options 1 and 3, and the ongoing cost of
business support.
Assumptions The cost projections are outlined in Appendix C. The major
assumptions are:
■ The software and configuration costs correspond to the
estimates from vendors of for similar systems
Prior & Prior Associates Ltd. page 42
CIS Assessment
Detailed Analysis of Option
■ Current CIS ITS and support staffing for Option 1 would
continue (the costs included in this category do not reflect a
recent recommendation to add two additional developers and
one revenue resources to the team to support the storm water
management rate over the next two to three years)
■ Options 2 and 3 will require ITS support / staffing to ensure a
high level of quality assurance and internal support for
configurations and integration of these solutions
■ There will be reduced ITS support / staffing for options 2 and
option 3; it is estimated that the current CIS team will be
reduced to 9 resources for option 2 from 16, and to 12 for
option 3. In both cases the resulting team members may be
totally different individuals than currently support the CIS
system.
■ Contract expertise is required in the form of project managers
for options 2 and 3, with the vendor providing much of the
configuration resources while the IT staff providing additional
parallel resources (to ensure continuity of services for
configuration and integration)
■ Contract expertise is required in the form of system designer
and expert developer for option 3, with the IT staff providing
the bulk of the development resources providing that the
development platform selected is PowerBuilder. A change of
platform would require different resources for the most part.
■ For purchased systems, it is assumed that a Tier 1 solution is
the highest cost option, and that a Tier 2, mid -tier solution (ex.
Vailtech with integration & enhancement) the lower cost
option within the purchase option, but still very costly to
replicate the customer integration and gas de- regulation
functionality.
■ Assume that the GDAR is configured on top of the vendor EBT
servers /features, while utilizing much of the existing Kitchener
hub and its user interfaces to avoid changes in the user
interactions (vendors to City) and to use as much as the current
set up as possible.
Costs are based on 2010 -based dollars; no platform (hardware,
software, development software, operating system) costs are
included, nor training, business case preparation or inflation.
Prior & Prior Associates Ltd. page 43
CIS Assessment
Detailed Analysis of Option
More cost detail The following shows the breakdown of costs by category for
each of the options. It is followed by the summary scoring for
each options and resulting cost per point by option.
Vendor
Software license CIS
$
250,000
Software license Tax
$
125,000
Implementation CIS
$
1,250,000
Implementation Tax
$
750,000
Additional functions
$
611,000
Common integration
$
300,000
10 r CIS maintenance
$
600,000
10yr Tax maintenance
$
300,000
Vendor total
$
4,186,000
City
Implementation - CIS
City Project Manager
$
300,000
City Business Staff
$
405,000
Implementation - Tax
City Project Manager
$
300,000
City Business Staff
$
270,000
IT Develop /Implement
$
3,390,000
Business resources CIS
$
270,000
Business resources tax
$
180,000
10 yr City support
$
15,950,000
$
9,664,000
$
11,110,000
City Total
$
15,950,000
$
10,939,000
$
14,950,000
10 year Total
$
15,950,000
$
15,130,000
$
14,950,000
15 year Total
$
23,920,000
$
20,220,000
$
20,470,000
$211K per year of resources has been added to Option 1 to
support the storm rate for every year in perpetuity. $211K was
added once to option 2 for additional implementation costs to deal
with any conversion issues with regard to storm. For option 3,
211K is added for the first four years while the system is being
converted. It is anticipated that Option 2 will provide a higher level
of configuration for the storm water rate functionality, and not
require custom development going forward.
Hardware and software costs are considered neutral across all
three options, as are training, business case preparation, or
inflation
Prior & Prior Associates Ltd. page 44
CIS Assessment Detailed Analysis of Options
The following chart summarizes the scoring and the unit costing:
NIM! h t, om : t�! nlm
Categories
Implementation considerations
10
86.0
61.2
69.6
860
612
696
Ongoing enhancements /upgrade considerations
15
55.8
70.8
65.2
838
1062
978
Staff satisfaction and impact
15
68.8
64.4
70.6
1031
966
1059
Risks
10
75.7
75.7
62.9
757
757
629
Support
10
71.4
76.4
72.1
714
764
721
Application / Software
40
63.9
69.3
65.7
2557
2773
2629
100
6758
6934
6712
Total scaled scores
67.6
69.3
67.1
10 Year basis
Costs
$15,950,000
$15,130,000
$14,950,000
Cost per point
$ 236,000
$ 218,000
$ 223,000
15 Year basis
Costs
$ 23,920,000
$ 20,220,000
$ 20,470,000
Cost per point
$ 354,000
$ 292,000
$ 305,000
Implementation time frame
Ongoing
2.5 - 3 years
3 -4 years
Resulting system type
Current
System
Tier 1
purchased
Re- architect
System
Implications of the scoring The scoring indicates that:
Over a ten year period, there is no standout option in terms of
total costs, but they begin to separate over the 15 years. Even
so, the costs, scoring and cost per point are all relatively close
to one another.
■ The purchased option, while not outdistancing the others,
seems to be a reasonable option with good scores across all
categories, better overall scores and lower costs.
■ Re- architect the system would seem to be a higher risk option
with regard to the acquisition of skilled senior technology
resources, timely delivery (a 4 four implementation was used
compared to two years for the purchased solution), with less
tangible overall result and higher cost per point over the 10
and 15 year periods.
Prior & Prior Associates Ltd. page 45
CIS Assessment
Detailed Analysis of Option
■ Status quo is of low overall risk, has no adverse
implementation impact but does not score well on future
implementation and is somewhat higher in cost compared to
the purchased systems option.
■ Over a longer time frame (15 years), the cost benefit of the
purchased option will improve substantially, with that option
gaining approximately $400,000 each additional year. Tier 1
systems, with their ongoing upgrades, have been known to go
beyond 10 years. Occasionally every 10 to 15 years, it does
have a major "seismic" upgrade that would call for a more
substantial upgrade implementation. The system should be
reviewed after 10 years to produce a new CIS Assessment
Report.
■ As well, the purchased option will allow the City to invest in a
critical mass of SAP professionals which will be available to
support other SAP solutions, such as the Financial System
and /or possibly Human Resources and Payroll.
While the above indicates that "purchased system" is the preferred
option, the key deciding factors will therefore also be:
■ Re- affirmation of the City's direction with respect to "buy'
versus "build"
■ Confirmation of the functioning of the corresponding Tier 1
system (SAP) in Enbridge, and the availability of the GDAR
implementation or configuration model in that organization;
initial discussion suggests that securing the configuration is
quite possible, costs might be another factor
■ Feedback in the industry with regard to the SAP partnership
with London, Cambridge and Sudbury having failed was found
not to have any negative report about the SAP product rather
some poor decision - making with regard to the selection of the
systems integration vendor.
Prior & Prior Associates Ltd. page 46
Recommendations
Recommendations emerging from the assessment vary from very
significant to relatively minor, but are recorded here for
completeness.
Major recommendations 1. Pursue with diligence a Tier 1 solution for tax and utility billing
based on the SAP suite of products to further consolidate the
number of software applications.
2. Establish a senior management position for ERP Business
Transformation to lead the business process design and
optimization processes.
Supplementary Stop all development/ enhancement to the current CIS that is
recommendations not absolutely necessary until a confirmation of the decision to
proceed with one of the three options has been made.
■ Terminate the services of the CIS external consultant
immediately, or when he concludes the current work
assignment.
■ Assess the resources and the five year contracts for CIS
resources which come due in 2011.
■ Secure Council approval to engage in discussions immediately
with SAP and Enbridge to confirm the path forward.
■ Engage the services of one of the industry experts on the SAP
utility solution to review the numbers and strategy outlined in
this report for reasonableness rather than re -do them.
With regard to the second major recommendation, it is essential
that the City acquire the services of a senior manager/ project
manager to lead the change management process during the
Prior & Prior Associates Ltd. page 47
CIS Assessment
Recommendations
implementation of a new utilities system, as it will require
changes in business practices entrenched over many years and
supported by an internal application. This individual will be
responsible for leading the organization in the adoption of best
practices underlining business transformation and optimization
processes, not only for Tax and Utility Billing, but also the
continuing improvements to the SAP Financial System
implementation.
Prior & Prior Associates Ltd. page 48
Potential Roadmap
Purpose of this section This section sets out the possible road map to pursue the
purchased system option. The assumptions are:
■ The City will adopt the purchased system option, as it is the
preferred direction of the City. This option will provide
functional improvements at no additional cost over a 10 year
period and with substantial cost advantages thereafter.
■ The City prefers a robust system and implementation to align
with its enterprise system solution and practice, and to
position the City for additional capabilities in the areas of e-
services (for utility management and for Tax) and mobile
computing in the field (for Utilities)
■ Customized functions will be included to ensure an integrated
Tax - Utility- Customer Service solution, and to ensure
consistency of property addresses throughout the City
■ The current support organization will be retained internally to
provide configuration support, "operational" support, system
platform support and technical support for the customized
components. The level of knowledge needed for configuration
will need to be assessed. Software maintenance and support
will be provided by the system vendor.
■ The City would be interested in exploring the opportunity for
assessing and pursuing Enbridge's configuration for the Gas
De- regulation functions in the SAP environment.
■ The City of Toronto underlying for Tax billing solution via SAP
should be monitored, and existing implementations in
western Canada investigated.
Prior & Prior Associates Ltd. page 49
CIS Assessment
Potential Roadmap
■ Timeframe estimates are for Tier 1 solutions and estimated at
two years. In the event that a Tier 2 solution was selected, the
implementation timeframe may potentially be shortened.
■ The overall timeframe may vary depending on the
investigation of products and opportunities. It is possible to
implement Tax and Utilities in parallel (much in the same way
of implementing Cityworks and SAP in parallel), or to
implement the utility solution first, closely followed by Tax.
Roadmap components The road map consists of the following major components:
• Recruit Project Director
• Software investigation and monitoring
• System acquisition strategy
• System specifications development
• System acquisition process
• System implementation process
• IT Organization transitioning
The following chart shows the approximate timeline for these
components. The first 3 components could be shortened if
necessary.
Components
Senior Manager (Business Transformation) recruitment
IT Organization transitioning
Software investigation and monitoring
System acquisition strategy
System specifications development
System acquisition process
System implementation process
Utilities
TAX
I Year 1 I Year 2 1 Year 3 1 Year 4 1 Year 5 1 Year 6 1 1
0
Prior & Prior Associates Ltd. page 50
CIS Assessment
Potential Roadmap
ITS organization A transition plan will need to be developed to support the
transitioning migration of existing CIS resources to a team of SAP support
staff. Based on the assumption that there will be some
customization for integrated processes between the two
solutions, there may be a requirement for staff to support this
functionality in - house.
Some roles will be retained — management, database
administration, business support, quality assurance, and
operational support, but the people may change. The tasks
include:
• Organization alignment for the new solutions
• Approval of plans
• Execute staffing plan
• Establish training plan and for IT staff
■ Staff assignment plan during implementation and transitional
period
■ Ongoing IT support
Software investigation This component deals with investigating the software options
and monitoring available so as to attain a comfort level and confirm the direction
going forward will meet the City's requirements. It is important
for both the ITS and the business teams to be involved in the
investigation. Products of particular interest include the
Enbridge installation of SAP Utilities solution and the GDAR
implementation, and the SAP tax solution.
The City may wish to see the Vailtech Tax Billing application in a
major municipality, as well as CSDC tax installation in a
municipality of similar size to Kitchener (e.g. Windsor, Oakville, or
Burlington). The potential exists to conduct an RFI to solicit
further information if necessary. The tasks include:
■ Site visits
• Investigation and discussion with Enbridge of the SAP
configuration and GDAR configuration with a view to acquiring
this configuration
• Discussion on software options and limitations, licensing
arrangements, implementation options
■ Potentially conduct an RFI process to gather additional
information
Prior & Prior Associates Ltd. page 51
CIS Assessment
Potential Roadmap
System acquisition After investigating the software products, the City should be in a
strategy position to determine out the system acquisition strategy. If the
City is interested in pursuing modules offered by the current
vendors (e.g. SAP, CSDC), it may be possible to handle such
purchase as extension to current software. The City may then be
in a position to obtain preferable discount pricing or other
software arrangements. In that case, the acquisition will include
acquisition for software extension and acquisition for "system
integration" services. It may be a joint software acquisition
process for Tax and Utilities or a separate process, single vendor
or multiple vendors. There is sufficient risk in the SAP
integration market, that the manner in which an integrator is
acquired warrants careful planning. There are many vendors and
many different support scenarios.
• Identify the potential software products
• Consider options and advantages with respect to acquisition,
software extension situation or potential, system integration
services
■ Determine the best approach with respect to value and
outcomes
Functional and technical It is likely that specifications will be required to support an RFP
specifications process. They would address existing functionality, planned,
future requirements such as e- Service and mobile computing, as
well as platform and technology requirements. It may be
possible to acquire specifications from another organization as a
straw man for many of the standard functions. Getting organized
will require:
• Forming a requirements team
• Documenting and consolidating current system functions and
outstanding requirements
• Identifying new and future requirements through work
sessions with business units
• Preparing, reviewing and finalizing systems specifications
System acquisition process The system acquisition process is for both the Tax and Utilities.
Depending on the acquisition strategy, it may be for software,
system integration services at the same time or sequentially,
involving one or more products. The City may wish to mix and
match products and vendors. The City may also want to
separate software and system integration services via sequential
RFPs, in the event that the City may wish to acquire a system
integrator with the help of the software vendor. The tasks would
Prior & Prior Associates Ltd. page 52
CIS Assessment
Potential Roadmap
include:
• Issue RFP(s)
• Process RFP(s)
■ Negotiate for contracts
• If necessary, consult with respect to system integrator
acquisition
• System integrator acquisition (if via separate RFP)
System implementation The City may choose to implement these together or separately.
process Regardless it is likely that two teams will be required as the
subject matter is different.
Tax should be implemented 3 to 6 months prior to the issuance of
the interim tax billing, thereby giving the City ample time to
prepare for the next billing cycle. The Utilities implementation
timeframe is more flexible and therefore can be established in
harmony with the Tax implementation (so as to facilitate the
integration customization between the two systems).
The suggestion would be to configure and test all specified
functions in parallel (other than e- Service for Tax and Utilities and
mobile computing for Utilities). The actual business
implementation (i.e. actual use of the system) may choose to
defer some functions such as tax collection. The implementation
tasks, for each of Tax and Utilities, include:
• Recruit project manager
• Staff assignments
• Project planning in conjunction with vendor
• Blueprint or business analysis
• Integration design (joint session between Tax and Utilities)
• Transition planning
• System configuration
• Functional customization
• Integration customization
• Business procedures
• Testing
• Training
• Conversion planning, development & execution
• Go live
Subsequent to the initial go -live and after a period of "warranty ",
there will be less demand on IT support services for configuration
and customization.
Prior & Prior Associates Ltd. page 53
CIS Assessment
Potential Roadmap
Suggested timeline The following chart shows the approximate timeline of the
roadmap.
ID
Task Name
Year 1 Year 2 Year 3 Year 4 lye ar 5 Year 6
1
Senior Manager [Business Transformation] Recruitment
—
'
—
'
'
—
—
—
—
-
—
—
-
-
2
Secure approval for full time FTE as Senior Manager, Business Transf.
3
Develop position description & recruitment profile
4
Recruit candidate
5
Develop initial scope of work
6
IT Organization transitioning
7
Organization structure development IT support unit
6
Approval of plans
9
Execute staffing plan
10
Establish training plan and for IT staff
11
Staff assignment plan during implementation & transition
12
Participation in implementation (start)
13
Ongoing support
14
Software investigation and monitoring
15
Investigation of software by SAP expert
16
Site visits to the software product installation
17
In depth investigation and discussion with Enbridge
16
Explore opportunities for pursuing configuration model
19
Discussion with potential vendor(s)
20
Potentially conduct an RFI process to gather information
21
System acquisition strategy
22
Identifythe potential software products
23
Consider options and advantages with respectto acquisition
24
Determine the best approach with respectto value and outcomes
25
System specifications development
26
Form requirements team
27
Document & consolidate current & outstanding requirements
26
Identify new & future requirements
29
Prepare, review and finalize systems specifications
30
System acquisition process
31
Issue RFP[s]
32
Process RFP[s]
33
Negotiate for contracts
34
Consultation with respectto system integrator acquisition
35
System integrator acquisition [if via separate RFP]
36
System implementation process
37
Utilities
36
Assign or recruit project/module manager
39
Staff assignments
40
Project planning in conjunction with vendor
41
Blueprint or business analysis
42
Integration design {joint session between Tax and Utilities)
43
Transition planning
44
System configuration
45
Functional customization
46
Integration customization
47
Business procedures
48
Testing
49
Training
50
Conversion planning, development & execution
51
Go live
52
TAX
53
Assign or recruit project/module manager
54
Staff assignments
55
Project planning in conjunction with vendor
56
Blueprint or business analysis
57
Integration design [joint session between Tax and Utilities]
56
Transition planning
59
System configuration
60
Functional customization
61
Integration customization
62
Business procedures
63
Testing
64
Training
65
Conversion planning, development & execution
66
Go live
Prior & Prior Associates Ltd. page 54
CIS Technical Evaluation
Kitchener CIS Evaluation
Prepared by: Ian Woodbury
Date: 19 November 2009
Context
The Sierra CIS system was developed by Sierra in an attempt to have a commercial CIS to market to
municipalities across Canada. Sierra built the system, signed up 5 clients and then abandoned the
system because they did not consider the product to be profitably sustainable.
The City of Kitchener has been maintaining and enhancing the Sierra CIS system since 2000. During that
time, the team has grown from a core of 4 staff to 14. They have been using the CIS for Water, Gas and
Tax purposes. Due to legislative and regulatory changes, the city has had to add significant functionality
to the system over this time.
We held a 3 hour session to discuss the system with the primary architect, John Little. During this
session he guided us through a look at the system through the user interface and the City's process for
delivering software changes from design to implementation.
We held a 1 hour session with one of the CIS Business Analysts. During this session we looked inside the
Powerbuilder code base to see the design patterns used, the level of documentation, the level of
consistency and an overall sense of the sustainability of the development methodology
Architecture Review
There is no current documentation of the overall architecture. There are materials from when Sierra
built the system and the existing designs for changes, but nothing to pull these together into a cohesive,
understandable design. As a result, no one feels they truly understand all aspects of the application or
its design.
From our review, the architecture is simply a repackaging of standard, procedural programming with
some leveraging of reusable components in the user interface and security layers. This does not match
current best practices but instead reflects the environment in a custom development group in the 90's.
The primary issues are:
• Stored procedures are not good candidates for maintainable business logic. They are not fault
tolerant, have no access to application logging layers, and they grow longer and more unwieldy
as time passes. This logic should be put into business objects that logically structure the
functionality aligned with how the business operates. The design should take advantage of
inheritance to simplify the implementation of new functionality - instead of copy and paste.
• The application is completely focused on artifacts, like customers and bills instead of on
automating the process users need, like moving a customer or changing a rate. This leads to
overly complicated screens with many tabs where users have to hunt down the data they need
to complete a task. It is telling that the architecture documents immediately focus on screen
design changes instead of on the business requirements.
Code Review
The system was originally developed during the implementation of Tax. The coding structure during this
phase is very basic. All datawindows are manually constructed. All windows are individually constructed
and contain multiple code snippets and local functions to manage the user interface and to check
validity for user triggered actions. The objects all have either zero or one level of inheritance with some
structures but no business related objects.
During the implementation of Water and Gas, the Powerbuilder Foundation Classes were applied along
with an add -in from Cornerstone. These added a great deal of inherited functionality to manage the user
interface but none to support the business functions.
Design Patterns
The primary design pattern shown is to heavily leverage embedded SQL, custom datawindows and
stored procedures to deliver functionality. New components are typically produced by copying existing
components and changing functions. This was a typical development approach among early
Powerbuilder development groups, especially where the tool was not well understood within the
organization. The stored procedure approach allowed traditional database - focused procedural
development to be folded in. The embedded SQL allowed quick turnaround from database design to
functional delivery. The datawindows can be laid out very quickly within the tool and allow leveraging of
the foundation classes. Both of these approaches, however, push the business logic into the persistence
layer and create a tight coupling between the front and back ends of the system. This creates inherent
inflexibility.
Internal Program Documentation
In all the coding examples we examined, the internal documentation was inconsistent and relatively
sparse. There appears to have been an effort to put base documentation in at the start of procedures,
but the internal logic is poorly documented. More recently developed objects show this same poor level
of internal documentation.
Process Review
Design Process
The Design stage combines some user requirement workshops and reviews with a very detailed design
document that starts with high level descriptions of the requirements and drills all the way down to the
detail level of implementation and screen design. The main issue with this approach is the blending of
levels of detail. This unnecessarily constrains the solution that makes the documents difficult to
maintain and inadequate for all purposes.
Design documents need to deliver three levels of detail
• Logical: Describes the requirements at a business level and the solution architecture that will
deliver them. It should contain the logical business elements involved and the services they
deliver - not screen shots and pseudo code. This should be developed in the workshops with the
users and the architect.
• Solution: This design includes implementation level details, screen designs, impacted functions
and possible crossover to other components. It should be detailed enough to support CIA in
generating test plans. It should be developed by the Business Analyst with feedback from the
architect.
• Implementation: This document includes the details of the actual software changes that were
made. It needs to be aligned to the application design so that if the developer wishes to use a
different approach than the application design specifies they have to first get it approved and
put into the application design.
Each of these levels of detail is different enough to separate out responsibility for maintenance.
Unfortunately, the current approach delivers two documents that overlap in detail. This causes a
number of difficulties, not the least of which is that the architect is getting too deep into the details of
the solution and its implementation.
Development Process
The development processes currently in place for CIS reflects a history of individual software
development in a very loosely monitored environment. There is no internal code review, no source
control, and no development standards to drive consistency and maintainability. This is typical where a
development group has grown organically from a small team with a high level of specialization in
development domains.
The developers are not involved in the design of the components, but build based on the technical
Database Review
The database design includes a huge number of tables, 1500 according to the architect. Over half of
these tables are maintenance or lookup tables. This creates an unnecessary complexity. It also shows
that the design of the system is based on every object being treated as unique - instead of taking
advantages of the commonalities of many objects to simplify the table structure and the design of the
overall application.
The tables themselves are not fully normalized. A number of them include multiple columns to capture a
one -to -many relationship in a single row. This violates a basic design principle for tables.
Conclusions
If the current architecture, design and development processes continue then the CIS product will
become progressively more expensive and less and less able to meet the City's business needs. If no
suitable candidate is found in the search of off - the -shelf products, then the City will need to engage in a
major revamping of these processes to move this legacy application into a sustainable structure.
The changes required include:
• design of an overall enterprise architecture for the solution space at the conceptual and logical
levels
• development of a core set of flexible, inheritance -based business objects to act as the basis for
future changes
• documentation of core software development principles including coding standards, support
tools, CIA processes and a documentation approach
• implementation of a functional area in the application as a pilot to fully exercise these software
components an engrain the new policies
• implementation of all core functionality going forward in the new architecture and object -
oriented software components
Interview Notes
Architect Walkthrough
Sierra CIS
• Took over in 2000. Team of 4 that had to grow. Council wanted a single customer database
instead of splitting into multiple systems.
• Utilities
• View, Processes (batch) and Reports
• Customer View
• Based on the artifact (eg. Search for the customer)
• Utility account /Tax account - stored by number
• Assessment and Property Tax
• Configuration
• Parameterization: primitive PB classes
• Table maintenance (base tables make up most)
• DWs look dynamic (eg, GL mapping the columns were GI instead of GL)
• Sierra RCS - Revenue Collection System
• Receives payment info from Class and sends to CIS
• Receives payments from mail payment
• Sends all pre- authorized debits to banks
• Class Cashier Module brings in the money
o Journal entries to SAP
• DB View of accounts with amount owing
• Take payment and then pass data to all the systems when the cashier closes
• Sierra developed. 5 clients and then dropped out.
• Design Principles
o Single data entry at source
o 3rd normal form database
o 1500 tables, 1 or 2 that are non - normal
o Oracle DB
o PB Focused
o Use DW structure
• Flexible queries
• Visual Aspects - colours, etc. based on a study
• 10M for Sierra to develop, made about 6M in billings
• 3rd Party Security System: PB software: SAM
• Create a set of permissions for a rule
• No security on DB
• Cornerstone
o Incorporated on top of PB Foundation Classes
• Other initiatives
• Customer walk through
o Artifact based
• Some hard coded actions (Move -In and Move -Out for customer launches a new
application)
• Create an application
• Mandatory fields
o Reminders
o Creates service orders
• Service orders sent to the ITRON units
• Billing
• Creates an entry, stores all the data for the bill using EDI
• EBT - Electronic Business Transaction System
• Vendor sign up
o EPOST
• Only used for CIS
• Postal Code Verification /Code to Walking Route
• Corporate Database of Customers Loaded from CIS and potentially other systems
• Addresses pulled from Corporate DB
• Exact match on street name (KING ST - no match, KING ST W matches)
o Site address and Service Address
o Optimal path?
o No workflow
• USS: Utilities Scheduling System: Used to schedule work, service orders where a repairman is
needed (replace or fix meter) only for gas. Manually entered over from CIS and other requests.
• SAP MM (inventory) - holds the water meter inventory as issued to vendors
• 400M per year income from the gas delivery
QA Group
• Need one document pulling together the business design and the tech specs so there is
traceability. Don't want to test based on code but based on what the users expect. Need it from
the output perspective, what they can see.
• Business Design, TecSpec, Programmer updates spec, update to Design required.
• Have never back - filled the CIA process.
• Would like consistency on user review of builds to avoid misunderstandings
• Impact Analysis
• Users are responsible for documentation - just not done
• Senior Developers /BA's hold key data
• Enterprise 25% of the db design decision making - more gatekeeper than architect
• CIA principles: Minimize bugs that impact day -to -day and customers and maximize user
friendliness. Have not pursued quarterly meetings and methodologies.
• Source Control: None in place now. Waiting for move to PB 11
• Application Feedback
• Archaic, try to fix things: 4 out of 10.
• Inconsistent naming
• Cross section of users. Changes rattle the users.
• Application is just slow. Especially in testing.
• Try to do two maintenance releases per year. They have the users pick issues (they get
estimates to inform this, they get 20 days each per maintenance release). Try to roll those
changes out.
• Project changes are driven by steering committee
Utility Software Benchmarking
I - . .
�l
I I I
i ■ Wsb
Prepared By:
Russ Newall
17705 Leslie Street, Suite 103
Newmarket, Ontario
L3Y 3E3
(t) 905.967.0770
(e) rnewall @util - assist.com
(w) www.util - assist.com
Contents
Introduction........................................................................... ..............................3
CISOverview ......................................................................... ..............................3
SystemSecurity .................................................................... ..............................3
Third Party Interfaces ........................................................... ..............................4
Reporting Functional ity ........................................................ ..............................5
UserI nterface ........................................................................ ..............................5
Customer and Account Management ................................. ...............................
5
MeterManagement .............................................................. ...............................
6
ServiceOrders ..................................................................... ...............................
6
Financial Management and Cash Processing ................... ...............................
7
Collections and Bad Debt Processing ............................... ...............................
8
Automation............................................................................ ..............................9
RateManagement ................................................................ ...............................
9
De- regulated Gas Billing ..................................................... .............................10
GDR Specifications and EBT Workflow Diagram .............. .............................10
Vendors in the Ontario Marketplace ............................................................... .............................12
AdvancedUtility Systems ................................................................................. .............................12
CayentaSoftware ............................................................................................... .............................13
Daffronand Associates ...................................................................................... .............................14
HarrisNorthstar ................................................................................................ .............................15
SAP...................................................................................................................... .............................16
SPL/ Oracle ......................................................................................................... .............................17
Sungard(HTE) .................................................................................................. .............................18
VendorSummary: ..........................................................................................................................
19
Conclusion......................................................................... ...............................
20
2
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
Introduction
They City of Kitchener (The City) has requested that Util- Assist assist them with
evaluating its current CIS software. As part of this engagement, The City has asked for
information on software vendors that support all of the services that it currently bills with
an emphasis on the ability to bill gas customers in a de- regulated environment.
The CIS vendors who tend to dominate the Ontario marketplace all support de- regulated
gas billing. There is a large range in the pricing and implementation times for each
vendor and we will outline each of these vendors in detail to provide you with an
overview of the major players in the Ontario marketplace.
Prior to comparing different players, we think it is important The City understand the key
features that customers are looking for in today's market.
CIS Overview
In today's increasingly complex marketplace, customers are looking for a system that will
not only support utilities billing processes today, but a system that will allow them to
support new initiatives well into the future. As technology is constantly changing, it is
also critical that the system have a strong technological architecture that can evolve as
the business grows and new mandates are approved.
From a technological perspective, The City should focus on a few key areas when
evaluating its options for a CIS solution.
System Security
Customer privacy is a critical concern of utilities and The City needs to ensure that its
software fully protects its customer data. Private data such as customer banking, credit
card, SIN and driver's license information should be fully encrypted in a secure database
to prevent fraud or theft. The software should also support functionality to allow this data
to be viewed by staff with the appropriate security credentials. Individual or group
security must be easily configurable and range from read only users who are unable to
change account information, to system administrators who can configure the software as
well as set up users' access to the software. Users should only be able to access the
forms or web pages within the application as defined by the system administrator.
From a user security auditing perspective, the software should be able to track changes
by individual users and also identify who ran specific tasks within the application.
The system should also support comprehensive back -up processes that can be
scheduled and are fully automated. The software should be able to be backed up on a
nightly, weekly and monthly basis and data that is archived must be easy to restore.
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
The system must have adequate system redundancy and the CIS vendor should have
recovery planning such that a hardware failure at any level of the software will not result
in any downtime lasting more than 2 hours, with no loss of data.
More severe disasters, resulting from more than simple hardware failure (eg. building fire
or telecommunications interruption) will be planned for to ensure that no data is lost in a
worst -case scenario.
It is also highly recommended that The City keep a test system on -line to allow users to
complete training, to test billing or rate scenarios, as well as to allow staff to pull data for
reporting purposes. The City should refresh the test system on a monthly basis, take a
snapshot of the system at month end for reporting purposes and archive the data should
it be needed for future auditing purposes.
From a database perspective, DB2, SQL or Oracle are the industry standards for utilities
and have strong security features that database administrators can take advantage of to
ensure the data remains secure.
Third Party Interfaces
Today's CIS applications need to interact with multiple systems within a utility's
organization and have the ability to easily integrate and communicate with external
systems. Hardware cannot be underestimated in this regard either.
On the hardware integration front, when processing cash or printing bills, the CIS system
needs to be able to interact with multiple printer types from laser printers, to report
printers, and receipt printers. The CIS system should also interact with various OCR
type scanners. This will allow users to quickly process individual payments at the counter
or process multiple payments using a batch payment import process such as a lock box
or other remittance processing payment file.
The software should easily integrate with third party banking or other financial institutions
that accept payments on your behalf. And it should allow these payments to be quickly
processed using an electronic payment interface that can quickly import them into the
CIS software, validate the account information, and then process and update all the
payments received.
The software should also support interfaces to financial institutions to allow The City to
support automated withdrawal for customers who are enrolled in pre- authorized payment
plans such as equal, levelized, or a full balance withdrawal plan. The software should
also support the withdrawal of funds on a customer's date of choice or on the customer's
due date.
The software should also integrate seamlessly with The City's SAP financial and asset
management application, any GIS systems, Itron meter reading devices, tax systems,
web based customer portals, IVR interfaces, The City's UCS bill print system and future
AMI systems. From an integration and reporting standpoint, the software should also
11
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
allow for the easy extraction of reports in txt, MS Excel, PDF, CSV or other common
formats.
Reporting Functionality
Reporting is a key requirement for any software application and is usually the area
where most CIS applications struggle to meet the needs of their customers. It is highly
recommended that the application have strong base (out of the box) reporting
capabilities as well as an embedded report writer to allow users to create customized
reports specific to their needs. Depending on the capabilities of The City's IT resources,
The City may also choose to use a third party report writer such as Crystal Reports
which is connected to the CIS application's database through ODBC to allow The City to
write their own reports. It is also recommended that The City have a sandbox and /or
test system to allow them to do some performance and metrics testing on new reports as
they are required.
The application should also have the ability to allow users to create quick queries on
different fields within the system that can be saved at the user level and the system level
(all users have access). This search tool should use Boolean logic and allow users to
search on specific account(s), balances, statuses, customer classes, etc. It should also
return any results in a timely manner.
User Interface
The different vendors currently available to The City all have graphical user interface
(GUI) with full windows functionality allowing users to use their mouse and keyboard to
navigate quickly and easily within the software. These vendors allow multiple windows
and functions to be completed by the user simultaneously and integrate seamlessly with
other commonly used products such as MS word, MS Excel, and MS Outlook. These
user interfaces are generally menu driven and system administrators can set -up menus
by individual user or departments.
All vendors in the Ontario marketplace have web enabled components in their software
and most of them are looking to have fully web enabled software in place in the near
future.
Users also have access to on -line help and documentation from within the application to
allow them to quickly reference "How To" information from within the application.
Customer and Account Management
It is critical that The City have a system that tracks both the physical account as well as
the individual customer information. This will ensure that all activities for one customer
are tracked within the system and will also improve the efficiency for The City's staff to
easily track customer's collection history (credit rating) and to complete consolidated
billing. This design enhances the move -in /move out process and allows The City's staff
to easily maintain and look -up all of the history on the individual customer and account.
17705 Leslie 5t. Suite 103 Newmarket, ON L3-Y, 3E3 905.967.0770 1 www.utilassist.com
From an individual customer's perspective, the software should track a customer's call
history, individual contract information, third party addresses and contacts, plus multiple
phone contacts, landlord information, GPS coordinates, metered and unmetered
services, all transaction history, credit history, disconnect history and more. All customer
information should be searchable by the user and all of the customer's history will need
to be tracked. The system should also track whether or not the customer is enrolled with
a retailer to ensure the billing process is properly audited and the account is closed off
properly should the customer move out of the premise.
From an account perspective, the application should track all billing history related to the
services at the account including metered and non metered services, all move ins and
move outs, landlord information and also list multiple customers under one account.
Meter Management
From a meter management perspective, The City needs a system that tracks all metered
and unmetered services. From a metered perspective, all water and gas readings need
to be stored and easily referenced for billing purposes. All meters at one physical
location should be accessible to the user. The software should also track master
meters and sub - meters and allow the data from multiple meters to be consolidated at the
master level.
The CIS software should also have a meter inventory to allow users to track the status of
a meter and the movements of a meter between physical locations and meter status. At
a minimum, the inventory should also track the physical meter number, serial number,
test history, read type, meter type, installation number, location code, meter size, meter
manufacturer, purchase date, purchase price and future test date. From a meter reading
perspective, the meter should also include fields for meter reading notes (forced and
unforced), meter location codes, and meter trouble codes to allow actions to be triggered
by issues reported by the meter reader.
The City may also be interested in moving to an AMI network in the future and the
requirements of this network should also be considered when looking at different CIS
solutions.
From a service perspective, the CIS software should be able to track multiple services at
an account and support the ability to remove or activate a service on the account.
Services that apply to The City include water, sewer and gas. However, The City may
wish to look at a software vendor who also supports such services as electric and trash
service in the event The City bills for these services in the future.
Service Orders
As utilities continue to upgrade their technology and business processes, one of the key
areas being investigated is the ability to process paperless service orders. Most vendors
today support this functionality and it is important that The City look at solutions that
seamlessly integrate with their City Works workforce management system.
I
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
It is critical that your CIS system supports multiple service order types and that they can
easily be dispatched to different departments by either the individual user or by using a
centralized service order process. The software should also allow system
administrators to easily create new service orders without the assistance of the vendor's
support department.
From a CSR's perspective, all service order history should be tracked on the customer's
account and pending service orders should also be displayed. In the case of critical
pending service orders for items such as disconnects, these should be flagged to the
user by using colour coding or by forcing the user to view the pending service order
when the customer's account is accessed.
The software should also audit a customer's account prior to the creation of a service
order to check for duplicate open service orders. If using a fully integrated real time
connection to the office, users should also be notified in a timely manner when a service
order has been closed off.
Financial Management and Cash Processing
The City requires a CIS solution that meets its current requirements and will easily
integrate with new technology as it emerges. From a pure cash processing perspective,
the software must have the ability to easily receive process and post individual and batch
driven payments. Each payment must be tracked by the time of payment, type of
payment, payment amount, and the source of the payment.
The software must easily interface with financial institutions for receiving payments made
at banks, by telephone, and on -line. It must also interface with any third party systems
that may be receiving payments outside of a financial system, such as third party internet
payments. It must also be able to deliver payment information to financial institutions for
customers enrolled in pre- authorized payment plans. Some software vendors also allow
Kiosk payments and The City may want to look at implementing Kiosk solutions to
provide additional customer service to its customers.
The software must have no restrictions on the type of payments that can be received
and must allow for an unlimited amount of transaction codes. The system needs to be
able to associate both individual and group transactions to their associated GL codes
allowing all transactions to interface with The City's SAP financial software.
The software should allow for deposit calculation, deposit interest tracking, and also
have an automated process to allow customers with good credit rating to have their
deposits refunded to them in a timely manner. The rules surrounding deposit refunds
should be flexible enough to be configured for different customer types and different
services. Should The City decide to put outstanding arrears on the tax roll, the
application must easily track and support these transactions and interface seamlessly
with The City's tax system.
7
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
From a payment allocation standpoint, the software should ensure that it can fully
accommodate The City's business rules and support the order in which payments are
applied to services or AR's. Users should also have the ability to override these rules
should manual adjustments be required to a customer's account. Users should be able
to apply a single payment to multiple accounts. The system should also support the
ability to drill down on a customer's payment history thereby easily identifying previous
transactions.
From a pure cash processing perspective, cashiers should be able to quickly scan or
enter multiple payments into the "cash register' without issue. Pending payments should
also be clearly displayed on a customer's account. This will ensure that customers in a
disconnect status, who have made payments that have not yet been posted, are clearly
identified.
Collections and Bad Debt Processing
Collections and Bad Debt Processing functionality are critical to the cash flow process of
any utility and The City requires a system that has the flexibility and reporting capabilities
to make managing delinquent accounts straightforward and as painless as possible.
At the customer level, the software should offer the ability to track each individual
customer's credit rating so CSR's can quickly identify the status of the customer they are
dealing with. The software should fully support single or multiple payment arrangements
and broken arrangements should result in the auto - generation of a notice or letter to the
customer. The software should also support the ability to generate a letter outlining the
customer's arrangements for distribution to the customer by mail or in person.
The collection configuration should support multiple levels of customer notification from
reminder letters, to disconnect and write -off notifications and procedures. If customers
are enrolled in e- billing, it would be beneficial if communications could also be distributed
electronically to customers. The software should allow for the ability to deliver copies of
letters to appropriate third parties, such as landlords or multiple offices, for customers
who may have multiple customers in arrears. Should a customer break a payment
arrangement, the system should automatically calculate interest to include the period
prior to the broken arrangement on the account.
The City's staff should also be able to designate certain customers or groups of
customers as notice or penalty exempt. Users should also have the ability to set an
expiry date for customers who may be penalty or notice exempt. The software should
ensure that pending payments are used as part of the arrangement processing and that
any pending payments are taken into account when running disconnect notices. Should
The City agree to accept post -dated cheques as part of an arrangement, these should
also be referenced as part of the daily collection notice routine.
The information that is to appear on collection letters or notices should be fully
configurable by the system administrator and should not rely on the assistance of the
software vendor's support department. Users should be able to quickly run reports on
customers at different stages of the collection routine. As The City has multiple services,
17705 Leslie 5t. Suite 103 Newmarket, ON L3-Y, 3E3 905.967.0770 1 www.utilassist.com
the software should be able to support the disconnection of two services (i.e. Water and
Gas).
The City also needs the ability to easily move debt that is no longer collectable by the
utility to the customer's tax roll. The software needs to be able to easily identify these
accounts and have a process in place to easily transfer non - collectable accounts to The
City's tax roll for collection purposes.
The software should also support an Aged Arrears Report that can be run in a short time
frame without over taxing the software's resources.
Automation
Many of the software vendors today are focused on automating specific processes,
actions or rules to enhance the functionality of their systems. Many of them have
system schedulers to allow resource taxing processes to be run at a scheduled time
after hours ensuring that their systems run at maximum efficiency during the business
day. They also have rules engines in place that allow certain actions within the
application to trigger other actions without user intervention. For example, a system
administrator can set -up the software to automatically trigger an NSF letter to a customer
when a NSF transaction code is updated in a cash batch. Rules engines can also
trigger multiple tasks within the system to allow users to set a sequence of actions when
creating a rule. This technology is allowing utilities to focus on customer service and
allows the software to look after many tasks that were previously user driven and
manually intensive.
Rate Management
The software available in the Ontario marketplace is very robust when it comes to rate
management. This is mainly due to the complexities involved in setting up, maintaining
and changing electricity rates in Ontario's de- regulated marketplace.
Rate engines need to be flexible enough to pro -rate some services, while not pro- rating
other services on the same account. They need to allow for rate modelling, allowing
utilities to forecast and budget future customer rates. Rate engines also need to give
users the ability to set -up rate changes well in advance of the actual rate change date.
In certain circumstances, rates may also need to be attached at the meter level for
specific customers. Rate engines must be able to calculate block or tiered rates, flat
rate, consumption based rates tied to other services and also be able to accommodate
the rate ready billing found in Ontario's gas industry. Rates must also be stored
historically for auditing purposes as well for the purpose of cancelling and rebilling
customer bills.
If The City has miscellaneous fees or point of sale items, then the rate and billing engine
should also be able to accommodate these scenarios.
7
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
De- regulated Gas Billing
One of the areas that will be a small challenge that CIS vendors in the Ontario
marketplace will have to deal with is the ability to accommodate the structure of Ontario's
gas marketplace. All vendors currently have the ability to communicate with the hubs
and spokes in place for the Ontario electricity marketplace. However, the gas industry
uses point to point communication for the exchange of EBT's. This is a small detail, but
it must not be overlooked should The City decide to look at outside vendors for its CIS
needs.
From a positive perspective, the gas utility working group that set -up the current
electronic business transaction (EBT) system was modelled after the infrastructure in
place in the Ontario electricity market and uses many of the same business transactions.
The business rules are different from gas and electricity with the main difference being
that the gas model is almost always based on a rate ready market, while the electricity
sector is based on a bill ready market. This should not be a large obstacle for a vendor
to overcome, but it should definitely be considered a mandatory component for any
vendor who is looking to supply The City with a CIS solution.
GDR Specifications and EBT Workflow Diagram
The overview below depicts the high -level flow of information in Ontario's retail open
access marketplace for gas supply where a Low - Volume Consumer signs a contract with
a natural gas marketer licensed by the OEB. It should be recognized that there are
other ways Distributors, Consumers and Gas Vendors may conduct business, but this
should provide a high level overview.
Requests Acct
May notify of enrol, _
drop, switch and/or
move .'
rr
r
Consumer
or
Consumer's Agent
1
■
Contracts with VendDrw
May notify of drop and /or
move
10
t
t
pits
Enter into SA &
DPContract
I
I
r
r
r
r
Jar
� J
17705 Leslie 5t. Suite 103 Newmarket, ON UY 3E3 905.967.0770 1 www.utilassist.com
If The City does move to outsourcing its CIS solution, it is critical the vendors fully
understand the rules and regulations outlined in the GDAR report. The software MUST
have the ability to receive, interpret, validate, and reject electronic business transactions
(EBT's) using the XML programming language as outlined in the point to point
architecture used by the gas industry in Ontario.
It is highly suggested that as part of any bid, all vendors provide background on their
experience in this area, as well as a full development specifications document outlining
how they will manage this process. The document should be both functional and
technical and fully outline the costs and timelines associated with making any
programming enhancements required to accommodate the de- regulated gas
environment required by The City.
11
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
Vendors in the Ontario Marketplace
Please note that the pricing associated with each system is fairly generic and that a full
discovery process to identify unique interfaces and processes at The City would be
required to provide more specific pricing.
Advanced Utility Systems
Experience and Financial Stability:
Advanced Utility Systems is a Harris owned company that has been providing their CIS
solution, CIS Infinity, to utilities and municipalities since 1997. Advanced began as a
small start -up servicing utilities mainly in the Ontario market and has expanded
operations to be a strong service provider to the US and Caribbean.
Advanced CIS Infinity can bill for gas, water and sewer and supports a de- regulated
billing environment. They have extensive experience with similar sized municipalities
with similar business practices as The City..
Advanced's parent company is Constellation Software Inc. and they are the second
largest software company in Canada with revenues in excess of $250 million dollars in
2008
Solutions Overview:
CIS Infinity was one of the first CIS software products to offer a GUI interface to users.
Known for its ease of use and rich functionality, CIS Infinity supports all of the services
and rate requirements of The City. Advanced has experience in working with gas
companies in a de- regulated marketplace and also has functionality to support EBT
transactions.
CIS Infinity also supports interfaces with many third party systems and provides the
functionality required by The City of Kitchener.
Technology
Advanced CIS Infinity application is a Microsoft.net application that can run on either an
SQL or an Oracle database. Advanced also uses Microsoft's IIS web server application
to manage their web based applications such as the customer self service portal and
paperless service orders. The client server architecture is scalable and can
accommodate up to 100,000 customers and The City would be considered one of their
larger clients.
Implementation Timelines and Costs
Advanced is considered a tier 2 solution provider that specializes in dealing with utilities
in the 5,000 — 100,000 customer range. An implementation for The City would take
approximately 8 -12 months and would cost approximately $600,000 - $750,000.
Company Web -site: www.advancedutility.com
12
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
Cayenta Software
Experience and Financial Stability:
Cayenta has been providing software solutions to the utility and municipal sectors for
over 20 years. Cayenta is also a wholly owned subsidiary of Harris Computer Systems
and has experience in the Ontario utility marketplace with their largest Ontario customer
being Peterborough Distribution Systems (PDC).
Solutions Overview:
Cayenta's Utility Management System can deliver the wide range of services required by
The City. It fully supports all the billed services demanded by The City and can integrate
with third party systems such as GIS, Financials, Workforce Management and Meter
Reading systems. The software has a GUI interface that allows users to drill down on
customer, meter, service and reporting information.
They support a fully mobile workforce management tool that eliminates the requirement
for paper service orders. Cayenta also supports a web enabled dashboard application
for executives that allow users to drill down on customer, meter, service and reporting
information. In terms of integration, Cayenta has experience in integrating with SAP's
financials and has many clients in the gas industry.
Technology
Cayenta's CIS solution application is a Uniface application that can run on either an SQL
or an Oracle database. The web application runs on IIS and manages Cayenta's web
based applications such as Cayenta's customer's portal and service order management
tool. Cayenta clients tend to be in the small to medium range and most of them run on
Oracle, as Uniface was originally developed to run on an Oracle platform. The client
server architecture is scalable and can accommodate over 100,000 customers and The
City would be considered one of their medium sized clients.
Implementation Timelines and Costs:
Cayenta is considered a Tier 1 solution provider and as such, their implementations and
conversion costs tend to be more expensive than some of the other service providers in
the Ontario marketplace. It is estimated that a Cayenta implementation would take 8 -12
months and would be in close to one million dollars for a utility the size of The City.
Company Website: www.cayenta.com
13
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
Daffron and Associates
Experience and Financial Stability:
Daffron & Associates have been offering comprehensive solutions to the Ontario
marketplace since 1976. Daffron is a privately held company and services over 150
utilities in the Caribbean, US and Canada. In the Ontario marketplace, Daffron's biggest
customer is Horizon Utilities in Hamilton.
Solutions Overview:
Daffron's latest product, iXP is completely browser based and supports all of the
services required by The City. Daffron's browser based solution supports full drill down
capability and due to their experience in the Ontario marketplace, they fully support
billing for retailers and standard supply services.
From a technology standpoint, Daffron focuses on an Enterprise solution using API's
and database management tools to integrate different software solutions from their suite
of products as well as third party software. All transactions are completed using XML
and the data is then displayed to the user on the iSP user interface which is browser
based.
Daffron also offers utilities the option to provide hosting and IT services as part of their
suite of products and services.
Technology
Daffron has developed a strategic solution called Integration X Platform or iXp. iXP uses
a J2EE tool set that is a 100% Java -based Web application development tool and
runtime engine allowing utilities to use the browser to access customer data utilizing
Java, SQL, and a Web browser. Daffron's application is scalable and can accommodate
The City's customer requirements.
Implementation Timelines and Costs:
Daffron is a Tier 2 software solution that services a lot of utility co -ops and is scalable to
well over 100,000 customers. An implementation of Daffron's software at The City would
take approximately 8 -12 months and the price for their implementation would be
approximately $400,000 - $600,000.
Company Website: www.daffron.com
14
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
Harris Northstar
Experience and Financial Stability:
Harris' Northstar Division has been in the utility and municipal sector for over 30 years
and is a major software provider to utilities in the Ontario marketplace with over 25
installations. Harris' parent company, Constellation Software Inc. is the second largest
software company in Canada with revenues in excess of $250 million dollars in 2008.
Solutions Overview:
Harris's Northstar CIS solution is Harris' flagship product and supports the complex rates
and de- regulated billing requirements of The City. Harris has experience billing for gas
utilities throughout North America and has recently upgraded the product with new web
based add -on modules to improve customer service, mass deployment of smart meters
and introduce paperless field work. Their software supports the billing of all the services
required by The City. Northstar interfaces with multiple third party systems such as
financials, GIS and workforce management systems.
Technology
Harris' Northstar application is a Java application that can run on an SQL, Informix or an
Oracle database. From a web server viewpoint, Northstar uses IIS as its platform and
Tomcat is used to manage the applications. It is not recommended that The City go with
the Informix database as this is an older technology and the only reason Harris offers it
is that the older versions of their software ran exclusively on Informix. The client server
architecture is scalable and can accommodate up to 100,000 customers and The City
would be considered one of their larger clients.
Implementation Timelines and Costs
Harris is considered a tier 2 solution provider and deals mainly with small to medium size
utilities from the 10,000 — 100,000 customer range. A Harris implementation for a utility
the size of The City would typically take 8 -12 months and would cost in the range
$400,000 — $600, 000.
Company Web -site. www.northstarutilities.com
15
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
SAP
Experience and Financial Stability:
SAP has been providing software solutions to utilities for over 35 years and has
experience in the Ontario marketplace. SAP has recently been installed at London
Hydro, Cambridge and North Dumfries Hydro and is currently being implemented at
Sudbury Hydro. London Hydro hosts the system at their location and SAP's CIS system
fully supports retailer and standard supply billing functionality for the electric and gas
sector.
Solutions Overview:
Solutions from SAP for utilities support all of the important customer care and billing
processes required in regulated and liberalized utilities markets. It fully supports both
AMI and regular meter reading interfaces. The billing engine fully supports TOU billing
and their strong energy data management software provides the functionality needed for
complex billing, such as real -time pricing or time -of -use pricing. Their application fully
supports the billing of energy, water, wastewater services. From complex tiered rates to
metered sewer, metered water, sewer calculated on water or straight flat rate services,
the SAP system will support any of The City's water related billing requirements. These
include residential, commercial and industrial customers and implementation of customer
segmentation strategies. SAP also supports gas billing for both retail and standard
service supply customers.
Technology
SAP's CIS application is written in its own SAP language and is a very proprietary
system. The database is Oracle and the architecture is robust and scalable and can
accommodate a very large number of customer accounts. The City would be considered
a small client for SAP.
Implementation Timelines and Costs
SAP is considered a tier 1 solution provider and as such, their implementations tend to
be more expensive than other service providers in the Ontario marketplace. It is
estimated that a SAP implementation would take 12 -18 months and would be in the
seven figure range for a utility the size of The City.
Company Web -site: http: / /www.sap.com /industries /utilities /index.epx
16
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
SPL /Oracle
Experience and Financial Stability:
SPL was a leading software provider to the Utility industry prior to their purchase by
Oracle in the fall of 2006. Oracle is one of the world's largest and best known
Enterprise Software Solution Company and their financial backing has seen SPL Oracle
become a major player at large utilities and municipalities. With the backing of Oracle,
SPL/Oracle has made a large push into the large utilities in the Ontario marketplace
including going live recently at Enersource in October 2009. They are currently
implementing at Toronto Hydro and plan to go -live in July 2010.
Solutions Overview:
SPL/Oracle's CIS solution is targeted towards medium to large utilities and provides the
flexibility and interface capabilities required by The City. Oracle offers multiple solutions
for utilities that can work in conjunction together or can be configured using API's to
allow for bi- directional communication between existing legacy systems. It can handle
complex rate scenarios and is capable of billing gas, water and wastewater services.
The software allows users to compare and forecast different rate scenarios and pricing
proposals which gives utilities the flexibility they require for modeling rates and
calculating future revenue.
SPL Oracle offers a full web - enabled customer self service portal that allows customers
to make changes to their accounts, access billing information, sign -up for e- billing, make
on -line payments and request service from the Utility.
They have multiple API's in place that allow easy integration with their own additional
utility focused applications that can be configured to analyze and present information
from different systems together in a dashboard application.
Technology
SPL/Oracle's solution CIS solution application is a Cobal and Java application that can
run on an Oracle, SQL or DB 2 database. The web applications run on Websphere,
Weblogic, or OAS (Oracle Application Server)and manages Oracles web based
applications such as the Customer Self Service Portal, Oracle Utilities Mobile Workforce
Management tool and Executive Reporting Dashboard. They offer different O/S options
such as Linux, Windows, Solaris, and AIX. SPL/Oracle's solution is extremely robust
and focuses on very large utilities ranging from 100,000 to over 1,000,000 customers.
Implementation Timelines and Costs:
SPL is considered a Tier 1 solution provider and as such, their implementations and
conversion costs tend to be more expensive than some of the other service providers in
the Ontario marketplace. It is estimated that a SPL Oracle implementation would take
12 -18 months and would be in excess of one million dollars for a utility the size of The
City.
Company Website: http: / /www.oracle.com /industries /utilities /product map.html
17
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
Sungard (HTE)
Experience and Financial Stability:
Founded in 1981 Sungard HTE's software solutions are specifically targeted towards
City and Municipal Governments. With over 400 employees' worldwide and strong
solutions for the public sector, Sungard (HTE) meets the needs of The City. In Ontario,
Sungard (HTE) has a strong presence in North and Northwest Ontario and it services the
needs of 6 electrical utilities including Atitkokan, Fort Frances, Kenora, Sioux Lookout,
North Bay, and Thunder Bay Hydro.
Solutions Overview:
Sungard (HTE's) CIS solution provides the flexibility and interface capabilities required
by The City. The software has a strong platform and a rate engine that can
accommodate the requirements of retailer billing.
The software is built around dynamic customer /service location relationships (i.e. once a
customer signs up they retain their ID regardless of status or service location.) Service
locations are also uniquely identified and active flowing accounts become an amalgam
of customer I D /Locations ID.
Sungard (HTE) also has a customer self service web application (Click2Gov), that is
available and would suit the needs of The City. There is also a full work order
application available that interfaces with CIS as well as other application i.e.
Purchasing /Inventory/ Payroll/ General Ledger. They also offer an entire suite of
potentially integrated applications that can be purchased for integration but will also
stand alone if required.
Technology
The current Sungard product line, including CIS, runs on an IBM iSeries platform with its
own proprietary O /S. Sungard is actively working on a net version but this version is not
yet publicly available. The code is RPG /CL with, as indicated, a direction to have a net
version 3. There is also an optional web based front end that runs on a separate
Microsoft server platform (IIS) coded with Java /Jacada.
Implementation Timelines and Costs:
Sungard (HTE) is considered a Tier 2 solution provider. It is estimated that a Sungard
(HTE) implementation would take 8 -12 months would be somewhere between $400,000
and $600,000 for a utility the size of The City.
Company Website: http: / /www.sungardps.com /segments /utilities.aspx
I:
17705 Leslie 5t. Suite 103 Newmarket, ON L3-Y, 3E3 905.967.0770 1 www.utilassist.com
Vendor Summary:
CIS Vendor
Experience in
Ontario
Supports
Kitchener
Services*
Price
Range **
Implementation
Time
Advanced
Yes
Yes
$$
8 -12 months
Cayenta
Yes
Yes
$$
8 -12 months
Daffron
Yes
Yes'
$
8-12 months
Harris Northstar
Yes
Yes
$
8 -12 months
SAP
Yes
Yes'
$$$
12 -18' months
SPL /Oracle
Yes
Yes
$$$
12 -18 months
Sungard(HTE)
Yes
Yes
$
8 -12 months
** Some program enhancements may be required for de- regulated gas billing
* * Price Range Legend
$$$ - over $1,000,000
$$ - $600,000 - $1,000,000
$ - 400,000-$600,000
19
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
Conclusion
Util- Assist believes that all of the vendors outlined above can provide a solution to The
City should they decide to outsource their CIS software. Each of these companies has
strong financial backing and has had success in both the North American and Ontario
marketplace. They each have their own strengths and different technologies, but if The
City does proceed with tendering out their CIS solution, it is recommended that these
solutions be investigated further.
20
17705 Leslie 5t. Suite 103 Newmarket, ON BY 3E3 905.967.0770 1 www.utilassist.com
Evaluation Details
CIS Assessment
Appendix C - Evaluation Details
The following sets out the assessment criteria and the scoring against each of the options.
Prior & Prior Associates Ltd.
page C1
CIS Assessment Appendix C - Evaluation Details
Prior & Prior Associates Ltd. page C2
CIS Assessment Appendix C - Evaluation Details
Prior & Prior Associates Ltd. page C3
CIS Assessment Appendix C - Evaluation Details
Prior & Prior Associates Ltd. page C4
CIS Assessment Appendix C - Evaluation Details
Prior & Prior Associates Ltd.
page C5
CIS Assessment
Appendix C - Evaluation Details
Prior & Prior Associates Ltd. page CS
100
6934
69.3
Implementation considerations
100
612
61.2
61.2
10
612
Ease of implementation
20
5
100
Demand on testing resources
3
4
12
Demand on City business staff resources
3
4
12
Demand on City IT staff resources
4
7
28
Timeline to implement (expeditiousness)
20
5
100
Ease of implementing integration to City systems
10
6
60
Reliability of software & readiness for implementation
20
8
160
Flexibility to allow for changes during implementation
10
7
70
Ability to incorporate customization
10
7
70
Ongoing enhancements /upgrade considerations
77
545
70.8
70.8
15
1062
Ease of implementation upgrades and fixes
10
8
80
Availability of regular release
10
8
80
Alignment with technology upgrades
5
7
35
Demand on testing resources
4 1
7
1 28
Demand on City business staff resources
4
6
24
Demand on City IT staff resources
4
7
28
Timeline to implement fixes (expeditiousness)
10
7
70
Ease of implementing changes to integration
10
7
70
Reliability of fixes and upgrades
10
8
80
Ability to incorporate customization
10
5
50
Assume configurable
Staff satisfaction and impact
80
515
64.4
164.4
15
966
Likely staff satisfaction
30
6
180
Positive impact on staff productivity (functional impact)
10
7
70
Positive impact on workload (e.g. e- service)
5
7
35
Positive impact on interruption (reliability, downtime)
5
8
1 40
Positive impact of having customized functions
5
6
30
Positive impact of reducing demand on City resources
5
6
30
Responsiveness to demands and needs of staff
10
7
70
Flexibility to demands and needs of staff
10
6
60
Risks
70
530
75.71
75.7
10
757
Reliability of software
10
8
80
Completeness of software & functionality robustness
10
8
80
Risk of non - compliance / unsuccessful impl.
10
6
60
Risk of cost over -run
10
6
60
Organization sustainment risk
1 10
9
1 90
smaller org
Software sustainment / obsolescence risk
10
8
80
Prior & Prior Associates Ltd. page CS
CIS Assessment
Appendix C - Evaluation Details
Prior & Prior Associates Ltd. page C7
Service level sustainment risk
10
8
80
Support
140
1070
76.4
76.4
10
764
Stability
10
8
1 80
Size of organization - (backup, availability)
10
8
80
internal + external
Time coverage (for support)
10
8
80
Service level options
10
8
80
Provision of customization
10
8
80
Upgrade path, maintenance releases
10
8
80
Customer base
10
8
80
Support for add -ons
10
8
80
Responsiveness
10
7
70
Readiness /timeliness for on -site support
10
6
60
Installation (fixes, upgrades)
10
8
80
Operations support
10
8
80
Assistance with problems
10
7
70
Query support
10 1
7
70
Application 1 Software
100
6933
69.3
40
2773
Business Process Functions
CIS:
265
18798
70.9
40
2837
Core system
2451
1815
74.1
50
3704
Handle Water / Sewer CIS
40
8
320
Handle Gas CIS
40
8
320
Integrated billing
40
8
320
Incorporating other charges
Storm water management
20
6
120
Other fees
5
7
35
flexible rate setup
Other flat charges
5
7
35
Other variable charges
5
7
35
Account set up
20
8
160
Account maintenance update
10
8
80
Integrated data maintenance /update (move in /out)
30
6
180
customization ind.
Integrated /validated addressing re. corporate /GIS DB
30
7
210
customization incl.
Metering
60
430
71.7
20
1433
Meter routing
10
7
70
Meter reading import
20
8
160
Reading adjustment
10
8
80
Other fees / charges import
10
7
70
Future AM
10
5
50
1113iffing
1951
1740
77.9
30
2337
Prior & Prior Associates Ltd. page C7
CIS Assessment
Appendix C - Evaluation Details
Prior & Prior Associates Ltd. page CS
Deposit processing
10
8
80
Deposit interest
5
8
40
Billing process
40
8
320
Billing cycles and split billing
20
8
1 160
Message incorporation
10
7
70
Handling complex rate structures / changes
10
7
70
Payment
70
560
80
20
1600
Payment options
20
8
160
Payment processing
40
8
320
Refund processing
10
8
80
Collection
30
240
80
15
1200
Collection processing
20
8
160
Collection case management/ workflow
10
8
80
Enquiry
30
240
80
20
1600
Customer enquiry, contacts, workflow
20
8
160
To do list (log), workflow
10
8
80
Integration general
45
340
75.6
20
1511
Financial system integration
20
8
160
Work management system integration
5
4
20
adaptor etc
Payment system (POS) integration
20
8
160
E- service
401
1 280
70
110
1 700
E- service / Web presence
20
6
120
E -post
20
8
160
Rental water heater (RWH)
80
540
67.5
15
1013
Rental water heater (RWH) information / billing
40
8
320
RWH integration to City system (GL, AP, Flint, SAP)
20
4
80
Marketing functions
10
7
70
RWH repair
10
7
70
Equipment and work
100
600
60
20
1200
Meter & equipment (e.g. gas regulator)
20
7
140
Integration to Cityworks for meters & equip.
5
4
20
Integration to Cityworks for work order
5
4
20
Service order
20
8
160
Work scheduling (internal staff)
10
6
60
Work scheduling (external vendor)
10
3
30
e-mail or dir access
Mobile function
20
6
120
Utilities legislated standards & safety (e.g. TSSA)
10
5
50
GDAR related
90
1510
56.7140
2267
GDAR transaction processing
40
5
200
Wholesale & retail settlement
20
7
140
Large volume user billing
10
7
70
Prior & Prior Associates Ltd. page CS
CIS Assessment
Appendix C - Evaluation Details
Prior & Prior Associates Ltd. page CS
GDAR rules / restriction
20
5
100
Other
30
140
46.7
5
233.3
Rebate program
10
8
80
GIS view
20
3
1 60
Tax:
850
5700
67.1
67.1
40
2682
MPAC data loading
100
8
800
Property/ Roll updates
50
8
400
Ownership changes
10
8
80
OPTA loading
30 1
8
1240
Billing processing
100
8
800
Split billing (capped versus non - capped property)
20
3
60
Payment in lieu
10
7
70
Payment options
30
8
240
Payment processing
1001
7
700
Severance processing
10
3
30
Local improvement processing
20
8
160
To do list (log), workflow
10
5
50
ARB - Appeal result processing
10
3
30
Other adjustments
10
4
40
Enquiry
20
8
160
Customer enquiry, contacts, workflow
10
8
80
Reporting
20
6
120
Tax certificate
10
8
80
Notice (mailing) - e.g. rental legislation notice
10
7
70
GIS view
30
6
180
GIS interface in place
E- service / Web presence
30
4
120
E -post
10
6
60
Integrated viewwith CIS
40
5
200
Integrated /validated addressing re. corporate /GIS DB
40
6
240
Financial system integration
20
7
140
Refund processing
10
6
60
Collection processing
20
5
100
Historic data
20
7
140
Archival processing
10
5
50
Configuration and changes
20
5
100
Customizable reports
20
5
100
Security
70
450
64.3
64.31
3
193
Ease of use
10
7
70
Individuals and group settings
10
7
70
Audit trail
10
5
50
Prior & Prior Associates Ltd. page CS
CIS Assessment
Appendix C - Evaluation Details
Prior & Prior Associates Ltd. page C1®
Entity level
10
7
70
Field level
10
7
70
Customization
10
5
50
Roll back, check point restart
10
7
1 70
General functionalities
305
2050
67.2
67.2
10
672
Look & feel (e.g. Web style)
10
7
70
Filtering and searching friendliness
10
8
80
Lookups
10
7
70
User configurability of look ups and lists
10
7
70
User configurability of tags and labels
5
7
35
Handling of links to ancillary information
10
7
70
Graphics (business graphics)
10
7
70
GIS functionality
20
5
100
Simple login process
10
7
70
Customizable screens
5
6
30
Overall ease of use
20
7
140
Speed / Performance
20
7
140
Archiving requirements
10
5
50
Data export capability
20
7
140
Integration customization
20
7
140
IUR support/ customization
20
6
120
Document linkage / attachment
20
7
140
Bar - coding
5
5
25
Mobile/ field computing
30
7
210
Additional data capabilities
20
7
140
Defined workflow capabilities
20
7
140
Platform characteristics
40
340
85
85
3
255
Operating system considerations
10
10
100
Database management system compatibility considerations
10
10
100
Web based platform
10
7
70
Expected performance
10
7
70
Training and documentation
30
220
73.3
73.3
4
293
Training
10
8
80
Tutorials
10
7
70
Documentation
10
7
70
Prior & Prior Associates Ltd. page C1®
CIS Assessment
Appendix C - Evaluation Details
Prior & Prior Associates Ltd.
page C:L:L
CIS Assessment Appendix C - Evaluation Details
Prior & Prior Associates Ltd.
page C:L2
CIS Assessment Appendix C - Evaluation Details
Prior & Prior Associates Ltd.
page M3
CIS Assessment Appendix C - Evaluation Details
Prior & Prior Associates Ltd.
page C:L4
CIS Assessment Appendix C - Evaluation Details
Prior & Prior Associates Ltd.
page M5
CIS Assessment Appendix C - Evaluation Details
Development cost assumptions for re- architect option (option3)
Prior & Prior Associates Ltd. page C16
il Milt
Costs
Software license
One time costs - CIS
0
250,000
0
One time costs - Tax
0
125,000
0
One time costs - GDAR
0
0
One time costs - Base system & integration
0
0
Implementation vendor costs
Implementation costs - CIS
0
1,250,000
0
Implementation costs - Tax
0
750,000
0
Implementation costs - additional (e.g. GDAR config)
0
611,000
0
Implementation costs - Base system & integration
0
300,000
0
Implementation City staff costs
City IT development & impl costs - CIS & TAX
3,389,339
Implementation IT City Proj Mgr costs - CIS
0
300,000
Implementation Business City staff costs - CIS
0
405,000
270,000
Implementation IT City Proj Mgr costs - Tax
0
300,000
Implementation Business City staff costs - Tax
0
270,000
180,000
Additional Enhancement costs (see 10 years support costs below)
(Performed under support by internal staff)
10 years maintenance costs (vendor)
Support costs - CIS
0
600,000
Support costs - Tax
0
300,000
10 years support costs (Internal)
15,945,022
9,664,329
11,108,542
Total 10 Year
15,950,000
15,130,000
14,950,000
15 years support costs (Internal)
23,917,533
14,304,493
1 16,625,101
Total 15 Year
23,920,000
20,220,000
1 20,470,000
Development cost assumptions for re- architect option (option3)
Prior & Prior Associates Ltd. page C16
CIS Assessment
Projected support costs are based on:
Appendix C - Evaluation Details
Year 1
1,594,502
1,004,833
1,406,945
Year 2
1,594,502
1,004,833
1,027,242
Year 3
1,594,502
1,004,833
1,027,242
Year 4
1,594,502
1,004,833
1,027,242
Year 5
1,594,502
1,004,833
1,103,312
Year 6
1,594,502
928,033
1,103,312
Year 7
1,594,502
928,033
1,103,312
Year 8
1,594,502
928,033
1,103,312
Year 9
1,594,502
928,033
1,103,312
Year 10
1,594,502
928,033
1,103,312
Year 11
1,594,502
928,033
1,103,312
Year 12
1,594,502
928,033
1,103,312
Year 13
1,594,502
928,033
1,103,312
Year 14
1,594,502
928,033
1,103,312
Year 15
1,594,502
928,033
1,103,312
Total 10 Years
15,945,022
9,664,329
11,108,542
Total 15 Years
23,917,533
14,304,493
16,625,101
During implementation option 2, assign 4 staff to legacy system support, and 5 to implementation.
For option 2, include 100% costs of ERP Business Transformation Manager for years 1 -5, and 50% costs for remainder
Include Storm staff costs in Option 1 for all years
Include Storm staff in implementation costs only in Option 2 equivalent to 1 years support
Include Storm staff costs in Option 3 for years 1 to 4 only
1
108,510
108,510
2
108,510
217,020
3
93,779
310,799
4
93,779
404,577
5
93,779
498,356
6
88,707
587,063
7
88,707
675,770
8
88,707
764,477
9
86,756
851,233
Option 2 staffing, Plus Manager ERP
Business Transformation, See below
10
84,489
935,722
11
83,795
1,019, 517
12
83,795
1,103,312
Option 3 staffing
13
79,604
1,182, 916
14
76,248
1,259,164
15
67,550
1,326, 714
16
56,788
1,383,502
STORM
211,000
1,594,502
Current System Year 2011 forward
Mgr ERP
Business
Transformation
153,600
Added to Option 2, 100% for each for
first 5 years, and allocate 50% to each
of the remainder years (Years 6 -15)
Prior & Prior Associates Ltd. page C17