7186 Statement of WorkCity of Denton Technology Services
601 E. Hickory, Suite A Denton, TX 76205 | www.cityofdenton.com
p. (940) 349-7519 | f.(940) 349-8533 | Lisa.Collaud@cityofdenton.com
CITY OF DENTON TECHNOLOGY SERVICES
Enterprise Data Strategy
STATEMENT OF WORK AND SERVICES
Work and Services Statement
Any vendor proposal submitted must accurately describe an understanding of the requirements detailed below,
provide an outline of the process that will be utilized to accomplish these goals, and at a minimum must include
the deliverables, key goals and expectations listed below. Please include with proposal case studies and work
samples or links to these materials.
City’s Key Goals and Expectations
The following requirements shall be met by the vendor:
At all times keep City of Denton data confidential and act in our best interest
Vendor will have or acquire CJIS Certification for Project Team Members by Public Safety project launch
Vendor will have or acquire NERC CIP Certification for Project Team Members by DME project launch
Deliverables will be achieved by estimated time frames or project timelines will be adjusted by City of
Denton Technology Services Project Manager
On-site requirements will be met
Data warehouse design will include development, test, and production database instances (on-prem or
cloud-based, servers owned / maintained by the City, data transfer automation owned by vendor)
Data warehouse design will include full backup and capacity planning for future needs (on-prem or
cloud-based, servers owned / maintained by the City, data transfer automation owned by vendor)
Data warehouses and reports will be delivered to the following City divisions:
o Customer Service
o Public Safety
o Denton Municipal Electric
Denton Municipal Electric will have a new validated data stream that combines data sources 1 and 18 Payments will be tied to milestone and deliverable achievements Estimated timeframe for completion
o Customer Service by June 1, 2020
o Public Safety – March 1, 2020 contingent upon ongoing ICS implementation (November 1,
2019 go live)
o Denton Municipal Electric by October 1, 2020
Background Information
Technology Services is seeking a three-year professional services contract for analytics services. The following
Statement of Work defines the analytics services requirements to be fulfilled under this contract for three City of
Denton departments: Customer Service, Public Safety and Denton Municipal Electric. Other City departments
can make purchases under the contract once established. Each department utilizing these analytics services will
provide their own funding, have their own group of stakeholders, and have a separate project schedule.
2
Preferences
Expertise with data, SQL, analytics, and Microsoft Power BI
Experience working with municipalities, Public Safety, utilities, NorthStar, JDE, ERCOT data, and FME
Locally based firms that can meet the on-site demands cost effectively
Vendors with Payment Card Industry Data Security Standard (PCI DSS) familiarity
Vendors registered as Historically Underutilized Business (HUB) Program Participants or Certified
Minority-Owned Businesses
Deliverables
Data analytics services provided to Denton Municipal Electric, Customer Service and Public Safety to
accomplish the following:
o Data sources contained in the Data Sources Table below will be included in the data
warehouses:
Customer Service: sources 1 - 9
Public Safety: sources 2, 8 - 15
Denton Municipal Electric: sources 1, 16 – 32
DME will require data source 1 matched to 18 as the top priority for this project
o Complete backup of data warehouses (on prem or cloud-based, servers owned / maintained by
the City, data transfer automation owned by the vendor)
o Dashboards & reports for the following:
Customer Service Operations
Customer Service will require reports with timestamped static data
automatically saved to a network drive at regular intervals
Convert four existing Public Safety dashboards to new CAD / RMS system
Data validation for Police Dashboard still needed
Add Crime Rates Tab to Police Dashboard
Add Crime Analyst requested data to data warehouse Develop homeless tracking map for Police & Fire Dashboards
Add new visuals to utilize new data sources 2 and 8 - 15
Animal Services
DME Management
Energy Management Organization
Energy Services Organization
ESO will require the NorthStar data stream to include three years of historical
data from reports with timestamped static data automatically saved to a
network drive at regular intervals
Electric Regulatory & Risk Division
Denton Energy Center
Electric Operations & Maintenance
DME System Operations
Electric Distribution
Electric Substations
Electric Engineering
Mapping of customers’ meters up to larger electric assets
Electric Metering Operations
Geographic Information Systems
Any other requirements uncovered during the data discovery phase
o Metrics and visuals for the reports and additional needs will be discussed and suggested during
the data discovery phase and creation of draft reports
o Design, implement, test, troubleshoot, and maintain data transfer automation process for
sources, by schedule and in quantity identified in data sources table below
o Configure Power BI reports and dashboards to refresh data from data warehouse at refresh
rate and for quantity specified below in data sources table (within Power BI refresh rate limits)
o Automatic data transfer processes running successfully, and Power BI apps configured
correctly before each launch
3
o Stakeholder sign-off during phase 11 of project (User Acceptance Testing)
Exclusions
Three City divisions have prepared requirements to purchase analytics services under this contract: Customer
Service, Public Safety and Denton Municipal Electric. Other City divisions will require a separate project plan,
stakeholders, and funding to begin initiatives under this contract with vendor selected.
City of Denton Responsibilities
City staff will provide:
Project management services
Contact information for Wärtsilä to enable vendor to design and establish API for data source 19
Contact information for DENCO to enable vendor to design and establish API for data source 14
Identification and adjustment of server resources. Technical infrastructure build, configuration, and
support including virtual servers with Windows 2016 operating system, 64-bit, and at least 4 gigs of
memory for data warehouses and backups. Ongoing maintenance of servers
List of servers where data needed for project currently resides
Login credentials for servers included in project
Access to conference rooms and work space within City buildings for project staff to meet with
stakeholders and complete work
Access to an online meeting service for remote work
Microsoft Office 365 licenses to each project team member including access to Outlook, Power BI, and
Microsoft SQL Management Studio (version matching operating system on servers)
Remote access to network
SharePoint updates for end users to view links to dashboards
Active Directory groups containing end users for each division
List of stakeholders and smaller work teams of technical staff for each division (see Stakeholder Tables)
List of data sources to be included that are ranked by criticality (see Data Sources Table). Divisions
participating will be encouraged to prepare a list of metrics they want to cover in their reports, sources of
those metrics, including data to be used for validation, sample reports or reports to be recreated /
replaced
Crime Analyst will provide a list of requested new data to be included in the Public Safety Data
Warehouse during phase three (Creation of Data Warehouse Database Instance and Repository)
Project work teams must verify completion of milestones and deliverables periodically throughout project
before invoice payment is issued
Travel Expenses
The City of Denton does not provide per diem or travel allowances for project staff assigned to this
project
Provide within your proposal the estimated travel expenses related to this statement of work that will be
required for project staff to meet on-site requirements outlined in Project Phases Table. Expenses
should include: transportation, lodging, meals, and sundries. Clearly identify / estimate how many days you propose to work on-site versus remotely for each phase of
the project
Receipts for all travel expenses billed to the City of Denton are to be included with every invoice
4
1. Project Kick-Off: On-site or remotely attend meeting lead by City of Denton Technology Services Project
Manager to discuss:
a. Project plan
b. Scope
c. Project team
d. Process
e. Major milestones
f. Schedule
g. Requirements
h. Work teams
i. Measures of success
j. Assumptions & risks
k. Contingency plans
2. Data Discovery: Identification & exploration of:
a. Relevant data sources
b. Necessary fields
c. Relationships between data sources
d. Reporting needs
e. Data validation metrics
3. Creation of Data Warehouse Instance & Repository (on-prem or cloud-based, servers owned / maintained
by the City, data transfer automation owned by vendor):
a. Data warehouse design will include development, test, and production database instances
Project Phases
Project Phases
On-site
Requirement UAT Sign-Off
1 Project Kick-Off
2 Data Discovery X
3
Creation of Data Warehouse Database Instance &
Repository
4 Data Modeling X
5 Data Cleansing and Validation X X
6 Data Ingestion X
7 Creation of Draft Reports & Dashboards
8 Finalize Reports & Dashboards X
9 Access & Security Configuration X
10 Documentation X
11 User Acceptance Testing X
12 Training & Knowledge Transfer X
5
b. Take into account capacity planning for future
c. Creation of complete backup for data warehouse
d. Crime Analysis will provide a table / field list of data for inclusion in the Public Safety Data
Warehouse
e. Load partial amount of requested quantity of data from data sources table into data warehouse (1-3
years’ worth for testing recommended)
4. Data Modeling: Build necessary relationships between data sources including:
a. Build lookup / reference tables
b. Join tables in data warehouse
c. Organize tables into facts & dimensions
d. Load data sets into Power BI and arrange relationships between tables
e. Denton Municipal Electric’s top priority is to have data source 1 combined with 18 and available for
use in a customer portal
f. Customer Service will specifically require a master employee table that matches up JDE numbers
with NorthStar IDs, and Cisco IDs
g. DME Engineering will specifically require a mapping of customers’ meters up to larger electric
assets
5. Data Cleansing & Validation:
a. Hold on-site meetings with data owners to validate data for accuracy matched against data
validation metrics
b. Research & resolve data inconsistencies
c. Clean data of multiple types of blanks or errors
d. Remove unwanted data types (test, low priority, irrelevant, etc.)
e. After data is validated, load full requested quantity of data from data sources table into data
warehouse
6. Data Ingestion:
a. Design, implement, test, troubleshoot, and maintain data transfer automation process between data
sources and data warehouse
b. Design, implement, test, troubleshoot, and maintain data transfer automation process between data
warehouse and Power BI reports / dashboards
c. Set-up refresh schedule for both interfaces
d. Set-up alerts for refresh failure by email to work team
7. Creation of Draft Reports & Dashboards:
a. Assist work teams with creation of reports and dashboards in Power BI
b. For City divisions without technical staff, create dashboards and reports in Power BI
c. Gather feedback from work team and implement necessary modifications to reports, dashboards,
data modeling structure, data warehouse, or automated data refreshes
d. Customer Service will require reports with timestamped static data automatically saved to a
network drive at regular intervals
e. Assist the Public Safety Work Team with conversion of four existing Power BI reports to new data
warehouse
f. Assist the Public Safety Work Team with addition of visuals utilizing new data sources: DENCO
phone records, Telestaff personnel data, Kronos personnel data, and JDE personnel data
g. Assist the Public Safety Work Team with creation of a report from the data warehouse that
combines Telestaff, Kronos and JDE personnel data in SSRS, SQL, or Excel
h. Add a Crime Rates Tab to the Police Dashboard using calculations provided
i. Complete needed data validation for the Police Dashboard
8. Finalize Reports & Dashboards: Build and review all content including
a. Calculated columns & metrics
b. Visuals
c. Headings
d. Drillthrough pages
e. Tooltips interactive displays
f. Tab names
g. Table names
h. Add design elements: photos, logos, color blocks, etc.
i. Refine views and visualizations for relevance and to be consumable
9. Setup Access & Security Configurations:
a. Setup rights to data warehouse for team members designated in Stakeholder Tables and provide /
test logins
6
b. Create apps in Power BI linked to Active Directory groups
c. Publish reports and link to apps
d. Assign work team members admin or view rights to app
10. Documentation:
a. Create documents including data warehouse key, list of groups with access, map of data sources,
and data dictionary
11. User Acceptance Testing: Have designated team members sign-off on completion after reviewing the
following items:
a. Data warehouse and backup
b. Data modeling
c. Data validation
d. Data ingestion
e. Final dashboards and reports
f. Access and security configuration
g. Documentation
12. Training and knowledge transfer
a. City of Denton work teams will receive training from vendor project team on creation of reports and
dashboards in Microsoft Power BI
b. Each report will have a launch meeting where end users receive training
c. Vendor will assist City staff with creation of materials for launch meetings
d. Vendor will provide / assist staff with end user training
e. Ensure City team members have basic support knowledge
f. Ensure City team members have support escalation information
Data Sources
D.No Data Source Data Type Vendor Criticality Quantity
Refresh
Rate
Data
Owner
1 NorthStar
Customer
Service Data
Customer
Information,
Utility Usage
& Billing Data
Harris Utilities
*will also require use
of historic data from
reports saved to
network drive
1 3 years
historic
rolling for
DME, 5
years
historic
for CS
Real-
time to
15
minute
CS-Christa
Foster
2 Cisco Phone
Data
Phone
Records
Cisco 1 1 year Real-
time to
15
minute
TS- Josh
Regenold
3 Workforce
Management
Workforce
Scheduling
Software
Calabrio 1 5-10
years
historic
rolling
Real-
time to
15
minute
TS- Josh
Regenold
4 Paymentus Billing Data Paymentus Corp. 1 5-10
years
historic
rolling
5 times
daily
CS- Cyndi
Williams
5 Exceleron Prepay
Billing Data
Exceleron 1 5-10
years
historic
rolling
5 times
daily
CS- Cyndi
Williams
6 Quick Tap
Survey
Survey Data Quicktapsurvey.com 4 5-10
years
historic
rolling
5 times
daily
CS- Cyndi
Williams
7 Phone
Survey Info
Survey Data City of Denton 4 5-10
years
historic
rolling
5 times
daily
TS- Josh
Regenold
7
Data Sources
D.No Data Source Data Type Vendor Criticality Quantity
Refresh
Rate
Data
Owner
8 Kronos Data Workforce
Scheduling
Software
Kronos 1 5-10
years
historic
rolling
5 times
daily
TS-Jim
Barnes
9 Axis People
Counter
Camera Data Axis
Communications
1 5-10
years
historic
rolling
Real-
time to
15
minute
TS-John
Durnell
10 Shelter Pro
Data
Animal
Identification
Data
Shelter Pro Software 1 5-10
years
historic
rolling
5 times
daily
TS- Lowell
Morris
11 Engage
Denton App
Citizen
Engagement
& Service
Requests
CitySourced 2 1 year 5 times
a day
Public
Affairs
12 Target
Solutions
Data
Fire
Department
Training
Assignments
Target Solutions 4 5-10
years
historic
rolling
5 times
daily
TS-
Hovannes
Garabedian
13 Guardian
Tracking
911 Dispatch
Training
Assignments
Guardian Tracking 4 5-10
years
historic
rolling
5 times
daily
TS- Paul
Bradley
14 DENCO
Data
911 Phone
Records
DENCO 1 5-10
years
historic
rolling
5 times
daily
TS-
Suzanne
Kaletta
15 Telestaff
Data
Workforce
Scheduling
Software
Kronos 2 5-10
years
historic
rolling
5 times
daily
TS-Jim
Barnes
16 NorthStar
mCare
Workorders
Metering &
Operations
workorders
Harris Utilities 2 5-10
years
historic
rolling
5 times
daily
DME-
Brandon
Hamby
17 Trilliant Smart Meter
Data
Trilliant 1 5-10
years
historic
rolling
Real-
time to
15
minute
DME-
Brandon
Hamby
18 MDMS
Metering
Data
Smart Meter
Data
Itron 1 5-10
years
historic
rolling
Real-
time to
15
minute
DME-
Brandon
Hamby
19 W.O.I.S. &
W.I.S.E
Gas Plant
Emissions
Data
Wärtsilä 2 5 years
historic
rolling
5 times
daily
DME-
Jason
Brown
20 SCADA Data Electric
System Data
OSI 2 5-10
years
historic
rolling
Real-
time to
15
minute
DME- Coia
Sneed
21 ERCOT Data
Texas
Electric Grid
Data
ERCOT 2 5-10
years
historic
rolling
Real-
time to
15
minute
DME- Jose
Gaytan
8
Data Sources
D.No Data Source Data Type Vendor Criticality Quantity
Refresh
Rate
Data
Owner
22 Clevest Data Workorder
Data
Clevest 1 5-10
years
historic
rolling
5 times
daily
DME- Jerry
Looper
23 JDE Data Electric
Revenue &
Expense
Data
J.D. Edwards &
Company
2 20 years
historic
rolling
5 times
daily
DME- Mary
Dickinson
24 GIS Data Geographic ESRI / SQL 2 5-10
years
historic
rolling
5 times
daily
TS- Sandra
Allsup
25 Lifecycle
Assets
Asset
Management
& Workorder
Data
SSP Innovations 3 5-10
years
historic
rolling
5 times
daily
TS- Sandra
Allsup
26 Synergi Data Engineering
Analysis
Data
DNV GL Group 2 5-10
years
historic
rolling
5 times
daily
DME- Jerry
Fielder
27 Responder
OMS Data
Elec Outage
Management
Data
Schneider Electric 2 5-10
years
historic
rolling
Real-
time to
15
minute
DME- Jerry
Looper
28 Protective
Relay
Settings
Electric
Asset Data
Spreadsheet 3 5-10
years
historic
rolling
Real-
time to
15
minute
DME-
Chuck
Sears
29 Electric
System
Compliance
Data
Electric
System
Performance
Data
SharePoint 3 5-10
years
historic
rolling
5 times
daily
DME- Jerry
Looper
30 NERC
Compliance
Management
Data
Electric Utility
Compliance
Data
Sigmaflow 4 5-10
years
historic
rolling
5 times
daily
DME-
Smith Day
31 Substation
Weather
Data
Weather
Data
? 4 5-10
years
historic
rolling
Real-
time to
15
minute
DME- Jerry
Fielder
32 Denton CAD Appraisal
District Data
? 2 5-10
years
historic
rolling
5 times
daily
DME-
Elizabeth
Ruiz
STAKEHOLDER TABLES
Technical staff that will work with vendor project team on creation of data warehouse and Power BI reports
and dashboards are highlighted in yellow
9
Technology Services Stakeholders
Technology Services Stakeholders Responsibility DW
Access
Report
Access
UAT Sign-
off
Melissa Kraft, Chief Technology Officer Project Owner
Drew Allen, IT Project Manager Project Manager
Cherie Reed, Senior Manager Application &
Support Services
Project
Consultant
Denton Municipal Electric Stakeholders
S.No Denton Municipal Electric Stakeholders Responsibility
DW
Access
Report
Access
UAT
Sign-
off
1 Jerry Fielder, Engineering Division Manager DME Project
Sponsor
2 Tony Puente, Interim General Manager Project Owner
3 Terry Naulty, Asst. General Manager of Power
Supply
Project Owner
4 Brent Heath, Executive Manager of Energy
Delivery
Team Member
5 Stephen Johnson, Market Operations
Supervisor
Team Member
Customer Service Stakeholders
Customer Service Stakeholders Responsibility
DW
Access
Report
Access
UAT Sign-
off
Christa Foster, Customer Service
Manager
Project Owner
Cyndi Williams, Business Information
Analyst
Team Member
Steve Prachniak, Assistant Customer
Service manager
Team Member
Pam Dugger, Customer Service
Supervisor
Consultant to the
Project
Public Safety Stakeholders
Public Safety Stakeholders Responsibility
DW
Access
Report
Access
UAT Sign-
off
Suzanne Kaletta, Public Safety Communications
Division Manager
Project Owner
Hovannes Garabedian, Applications Architect II Project Lead
Lisa Collaud, Management Analyst Team Member
Ashka Patel, Systems Analyst Team Member
Tiffany Wei, Business Information Analyst Team Member
Chris Womack, Crime Analyst Team Member
Pam Miller, Crime Analyst Team Member
Randi Weinberg, Animal Services Manager Team Member
Gayla Nelsen, Animal Services Supervisor Team Member
Julien Peralta, Animal Services Supervisor Team Member
10
Denton Municipal Electric Stakeholders
S.No Denton Municipal Electric Stakeholders Responsibility
DW
Access
Report
Access
UAT
Sign-
off
6 Jose Gaytan, Senior ERCOT Transmission
Analyst
Team Member
7 Chris Lutrick, Engineer V / Energy Services
Organization Manager
Team Member
8 Michael Wilson, ERCOT Settlement & Rate
Administrator
Team Member
9 Elizabeth Ruiz, Energy Programs & Key
Account Administrator
Team Member
10 Smith Day, Regulatory & Risk Division
Manager
Team Member
11 Sandra Allsup, DME Enterprise Applications
Manager
Team Lead
12 Jason Brown, D.E.C. Plant Manager Team Member
13 J.R. Richardson, Metering/Communications
Superintendent
Team Member
14 Brandon Hamby, Electric Meter Manager Team Member
15 Mary Dickinson, Business Manager Team Member
16 Jerry Looper, System Operations Manager Team Member
17 Brad Watts, Line Superintendent Team Member
18 Ralph Patterson, Line Superintendent Team Member
19 Sam Mall, Engineer V Team Member
20 Tony Jones, Electric SCADA Supervisor on
behalf of Sam Bridges, Substation /
Transmission Superintendent
Team Member
21 Philip DiPastena, Senior Risk Control Analyst Team Member
22 Tom Cruse, Business Intelligence Analyst Team Member
23 Coia Sneed, SCADA Analyst Data Owner
Go Live Support
Be available to provide support during the weeks where go live meetings occur (off-site acceptable)
Assist in development of materials for go live meetings
Be on-site at go live meetings
Assist with presenting material during go live meetings
Incident management escalated initial response time of four hours maximum during go live weeks Incident management escalated resolution time of 48 hours or rescheduling of go live meeting during go
live weeks
Warranty
Provide warranty language in proposal that obligates elimination of any defects in operation or
construction of data warehouse, reports, and dashboards
Define period of time warranty is in effect
Define components or services not covered by the warranty
Define initial response time and resolution time expectations for warranty period
Explain how customers will get service under the warranty
11
Maintenance Contract
Provide within proposal a fixed number of calls and hours of work available for support annually and
associated price
Provide within proposal hourly cost for support that exceeds annual allowance
Incident management initial response time expectation is 24-48 hours or less
Incident management resolution time expectation is 48-120 hours or less