Loading...
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