(Project Name) Business Case: Yellow Highlighting Text To Be Replaced Blue Highlighting Example Text

[Project Name]
Business Case
Yellow Highlighting = Text to be Replaced
Blue Highlighting = Example Text

projectmanagement.com [Project Name] Business Case
Table of Contents
1. Introduction...............................................................................................................3
1.1. Purpose.......................................................................................................3
1.2. Scope..........................................................................................................3
1.3. Distribution..................................................................................................3
1.4. Intended Audience......................................................................................3
1.5. Copyright.....................................................................................................3
1.6. References..................................................................................................4
2. Executive Summary..................................................................................................4
2.1. Scope Statement........................................................................................5
2.2. Business Case Summaries.........................................................................5
3. Recommendations....................................................................................................8
4. Cost Benefit Analysis..............................................................................................10
4.1. Estimated DW Costs................................................................................10
4.2. Business Case Value & Rankings............................................................11
4.3. Hard ROI Cost Benefit Analysis...............................................................13
5. Business Cases......................................................................................................16
5.1. Waste Water Treatment Analysis.............................................................16
5.2. Treatment Process Regulatory Requirements Analysis...........................30
5.3. Maintenance Scheduling Decision Support.............................................42
5.4. Other Notable Business Cases................................................................57
6. Prototype Results...................................................................................................58
6.1. Prototype Overview..................................................................................58
6.2. Business Benefits.....................................................................................60
6.3. Data Elements..........................................................................................61
7. Attachment A Source Data Descriptions..............................................................62
7.1. CMMS.......................................................................................................62
7.2. Source Control..........................................................................................62
7.3. LIMS..........................................................................................................63
7.4. FIS.............................................................................................................63
7.5. CRISP.......................................................................................................64
7.6. Operations................................................................................................65
2012 projectmanagement.com 2
1. Introduction
1.1. Purpose
[Project Leader] is pleased to present this [Project Name] Business Case document to
[Project Customer/Sponsor]. [Project Leader] appreciates the time [Project
Customer/Sponsor] associates have spent with us to help us understand the critical
business issues you face. The Business Case document captures the vision we have
jointly created outlining how [Project Leader] can assist [Project Customer/Sponsor] in
confronting their critical issues.
This document contains the deliverables that were produced for the [Project Name]
Business Case project by [Project Leader].
1.2. Scope
This document includes the deliverables for the [Project Name] Business Case project
for [Project Customer/Sponsor]. It does not include intermediate documents (e.g., the
Project Tracking Documents).
This document does not include a detailed Work Breakdown Structure (WBS) for the
next phase or detailed costing information. These items will be provided to [Project
Customer/Sponsor] in a separate document to assist them in developing the next
components of the [Project Name].
1.3. Distribution
Name Company ProjectRole

ChiefInformationOfficer
ClientProjectManager
***
AccountManager
ProjectManager
DataWarehouseSpecialist
SeniorBusinessAnalyst
1.4. Intended Audience
This document is intended to be used by [Project Customer/Sponsor] and by [Project

Leader] staff. It will be the basis for budget justification, as well as the basis for
continued project-related work at [Project Customer/Sponsor]s [location/division].
Project team members, project reviewers and project executive sponsors are the
intended audience for this document.
1.5. References
[Project Customer/Sponsor] Strategic Information Plan (SIP) [Pub Date]
[Project Leader] Business Case Proposal [Pub Date]
2. Executive Summary
[Project Customer/Sponsor] engaged [Project Leader] to develop their [Project Name]-
related business cases. The project goal was to define and prioritize the optimal three
candidates for [project type] projects, selected from the recommendations of the
Strategic Information Plan (SIP). The deliverables from this project provide the
necessary information to budget and plan the first several [Project Type] projects.
In order for [Project Customer/Sponsor]s [Project Type] process to be fully successful it

must be effectively aligned with the business needs, efficiently implemented with best
practices and methodologies, and innovative in applying best of breed technology
customized to [Project Customer/Sponsor]s business environment. The Business
Case project specifically addresses the first criteria, which is ensuring that the [Project
Type] plan is effectively targeted towards business justified strategic processes. The
three business cases analyzed were:
Waste Water Treatment Analysis
Treatment Process Regulatory Requirements Analysis
Maintenance Scheduling Decision Support
[Project Customer/Sponsor]s mission is to protect the environment and public health by

cost-effectively treating wastewater while complying with federal, state and local
regulations. To align with the overall organization mission, the DW projects must
support business processes that enable cost-effective treatment while maintaining
health and compliance with federal, state and local regulations. Two of the candidate
projects (Treatment Process Regulatory Requirements Analysis, Maintenance
Scheduling Decision Support) address business processes targeted at cost savings
through more efficient usage of labor, equipment and materials. The third candidate
project, Treatment Process Regulatory Requirements Analysis, addresses compliance
and political awareness of public and environmental health. Both individually and as a
whole the DW projects will assist the district in obtaining improved cost effective
business processes. Being more efficient will improve the agencies political standing
on issues such as benchmarking, lowering the cost per million gallons of water,
reducing odor complaints, protecting the community and environment health, and
promoting community awareness and good will. Managing these political concerns will
reduce the threat of privatization.
Prior to the DW business case project, [Project Customer/Sponsor] completed the

Strategic Information Plan (SIP) which included the high level business cases for a DW
and established the feasibility for applying DW technology to [Project
Customer/Sponsor]s information technology requirements. The SIP was confirmed to
be a current and valid representation of [Project Customer/Sponsor]s business
requirements for information technology, and as such, the SIP was leveraged as a
starting point for the Business Case project.
The three candidate projects were selected from the information provided in the
Strategic Information Plan (SIP) and interviews with the project sponsors. The selection
criteria used to determine the top three candidates included business justifications,
anticipated return on investment (ROI) and perceived ability to complete successfully
on do-ability. Through business user focus sessions the business justification,
potential ROI and do-ability for each of the three candidate DW projects were refined.
Based on the analysis performed in this phase, the Waste Water Treatment Analysis
was selected as the first Data Warehouse population project. The other two business
cases, Maintenance Scheduling Decision Support and Treatment Process Regulatory
Requirements Analysis, would be accomplished by the next two population projects by
adding the additional source systems and additional elements in the existing Data
Warehouse.
Additionally, [Project Leader] has developed a Data Warehouse prototype, using actual
[Project Customer/Sponsor] data, to demonstrate the Data Warehouse capabilities to
[Project Customer/Sponsor] staff.
2.1. Scope Statement
The deliverables are focused on the three candidate projects. All business analysis
and data research was directly related to these three project areas. Focus session
attendees were chosen by the project sponsor based on the scope of the three
candidate projects. All attendees expressed support for the applicability of the DW and
many were enthusiastic proponents who are eagerly looking forward to the system
being available.
It was noted by the [Project Leader] staff that conducted the focus sessions that there
was a very strong cultural cohesiveness to the attendees of the sessions. The territorial
frictions that often negatively effect focus sessions containing representatives from
many departments was noticeably absent from the sessions conducted at [Project
Customer/Sponsor]. This is usually indicative of a very positive and productive working
relationship between departments. This collaborative relationship is a key factor in the
success for a DW project.
The deliverables of the project are intended to provide clear business justifications
together with user provided cost benefits and industry standard costing estimates for
executing these projects. The estimates provide sufficient detail and accuracy for
budgeting and approval purposes. All volumes, timings and other similar figures
obtained in support of this business case are estimates only and are based upon the
data that has been obtained from [Project Customer/Sponsor] during the focus
sessions and follow on meetings. Additional task information, work breakdown structure
(WBS) and costs will be provided in a separate document.
2.2. Business Case Summaries
Significant business justifications exist for each of the three candidate DW projects.
The merits of each case are detailed in Section 5, Business Cases. The following
section provides a short description of the business cases summarizing the substantial
business justifications.
2.2.1. Waste Water Treatment Analysis
Treating wastewater is the primary function of [Project Customer/Sponsor] and

all operational expenses are in some way related to this process.
The Waste Water Treatment DW will provide timely access to integrated lab
sample data, CRISP operational metrics with FIS costing information to enable
analysis of the treatment process and plant operations.
The business justifications are substantiated and include cost savings in the
following areas:
Optimize the entire treatment process by closely tracking, trending and

benchmarking the efficiencies within each plant process and the over-all
cost efficiency of the entire plant.
Enable a reduced labor force to work smarter with timely, accurate,
accessible, integrated information which provides knowledge for timely
tactical adjustments to the treatment process.
Accurately track, measure and benchmark cost benefits in plant process or
chemical use adjustments.
Enable management to work smarter with historical trend analysis and
costing analysis providing strategic knowledge for medium to long term
tuning of the entire plant process which will lead to more cost effective
contracts for materials and capital projects.
Save chemical cost by reduced usage through discovery of efficiencies and
the narrowing of tolerances.
Save chemical costs by improving delivery schedules, improved tracking of
actual deliveries and actual quantities delivered by vendors and by using this
knowledge as leverage to improve vendor contracts.
Reduce or eliminate duplicate data entry of lab samples, plant process
information and other key data.
Reduce manual integration and reporting of lab and operational information.
Empower a more productive workforce with a DW providing the business
knowledge base and a tool for analysis, reporting and decision support.
2.2.2. Treatment Process Regulatory Requirements Analysis
Meeting Environmental Protection Agency (EPA) regulatory requirements is a

must-do business process for [Project Customer/Sponsor].
The Regulatory Requirements Analysis DW will support a majority of the EPA

reporting requirements and enable analysis of wastewater treatment compliance
through the integration of lab sample data with regulatory information.
The substantial business justifications include cost savings in the following

areas:
Allow cost effective support of regulatory reporting, internal or external

research, audits, lawsuit inquires and source data for plume graphs.
Reduce or eliminate duplicate data entry and manual research of lab reports
which will automate the construct regulatory reports.
accessible integrated information which provides knowledge for timely
tactical adjustments to the treatment process where constituents approach
EPA thresholds.
Enable management to work smarter with historical trend analysis

providing strategic knowledge to tune the entire plant process to confidently
maintain compliance while reducing costs.
2.2.3. Maintenance Scheduling Decision Support
Maintenance and Scheduling is a cross-functional process vital to ensuring

undisrupted and effective processing of waste water.
The Maintenance Scheduling Decision Support DW will provide integrated

CMMS maintenance information with SCADA/CRISP operational metrics such
as fixed asset related runtime, temperature and analog measurements. While
portions of the Maintenance & Scheduling process are addressed by specific
systems (such as CMMS) the entire knowledge base of information required to
plan efficient schedules does not exist. The DW will address the needs for
comprehensive, integrated and historical information to support appropriate
scheduling.

areas:
Eliminate ineffectual manual research of process flow information to

determine maintenance requirements.
Produce maintenance cost savings by moving from a calendar interval
based maintenance to an actual usage/need based maintenance.
o Save the costs of unneeded maintenance on equipment with
unexpected low usage.
o Save the investment in fixed assets by triggering timely preventative
maintenance on unexpected high usage equipment.
o Validate or tune optimal calendar/interval based maintenance where
actual usage is very close to expected planned usage.
Save near-end-of-life high maintenance costs by projecting optimal fixed
asset replacement schedules. An average of 60% of the maintenance costs
are incurred in the last 2 years. A majority of these cost will be saved by
using the DW to determine the replacement schedules.
Save cost by negotiating more cost effective capital expenditure contracts
by scheduling predictive replacement. Provide engineering and contractors
lead time to more cost effectively planning capital replacements, retrofits and
capital upgrades.
Save the costs of down-time, emergency fixes and potential permit violations
due to equipment malfunctions.
Enable maintenance and scheduling management to work smarter in
utilizing the labor, materials and equipment. Long term, the business can
only be effectively supported by an optimal down-sized labor force if the
labor is tasked with a right-sized work load. Empowered by the DW analysis
and planning tools, management can reduce labor, materials and equipment
costs while confidently meeting maintenance requirements.
3. Recommendations
The recommended [Project Leader] approach to the analysis, design and construction
of a DW project for [Project Customer/Sponsor] is presented in this section. This
approach is based on the [Project Leader] DW methodology that is utilized globally in
the design and construction of data warehouses.
The road map below outlines the stages contained within the development of a DW.
The arrow pattern illustrates which portions of the process are iterated. Once the
overall DW Plan and Architecture have been developed and the scope defined during
the Business Question Assessment, stages from that point forward are repeatable,
constituting separate iterations of the Data Warehouse. [Project Leader] have
completed the first four steps of the process for [Project Customer/Sponsor]. The next
step in the process for [Project Customer/Sponsor] is Architecture Design and Review.
Data Warehouse
process road map...
Enterprise
Enterprise
Strategic
Strategic Value
ValueStream
Stream
Engineering
Engineering
Visioning
Visioning Assessment
Assessment
Assessment
Assessment
Business
BusinessQuestion
Question Data
Knowledge
Architecture
Architecture
Assessment
Assessment Warehouse
Warehouse
Design/Review
Design/Review
(Scope)
(Scope) Business
BusinessCase
Case
Tool
Tool
Iteration
Iteration Selection
Selection Implementation
Implementation
Implementation
Implementation Detail
DetailDesign
Design (RAD)
(RAD)
Plan
Plan
Transition
Transition
Kaizen
Kaizen Maintenance
Maintenance toto
Production
Production
Reference
Iterative Processes
The remaining recommended process steps for [Project Customer/Sponsor] in the

development of your Data Warehouse include:
Develop a Data Warehouse Architecture that covers Data, Application,

Technology and Support Architectures.
Proceed with a Data Warehouse Tool Selection to determine what tools will be
needed by the Data Warehouse.
Once the Architecture has been defined, develop the Iteration Implementation
Plan which will identify in detail what the first three to four iterations of the Data
Warehouse will include.
Proceed with the Detailed Design and Implementation of the first iteration of the
DW.
The Iterations or Population Projects identified over a 19 month period will include:
Iteration# BusinessCase Source History Numberof

TimeFrame Supported Systems Loaded Supported
Included Users
1.(34 WasteWater CRISP Sixmonths 12
months) Treatment LIMS
FIS
SOME
EXCELSS
2.(23 Operational CRISP Sixmonths 25
months) DataStoreand LIMS
Enterprise FIS
WideData +
Warehouse ADDITIONAL
Introduced EXCELSS
3.(34 Regulatory CRISP OneYear 50
months) Requirements LIMS
FIS
EXCELSS
DATA
+EPA/ECM
4.(34 Maintenance& CRISP Eighteen 75
months) Scheduling LIMS months
FIS
EXCELSS
DATA
EPA/ECM
+CMMS
5.(34 SourceControl CRISP TwoYears 75+
months) LIMS
FIS
EXCELSS
DATA
EPA/ECM
CMMS
+PERMIT
DATA
As the iterative process continues, additional elements will be selected from the
existing input systems, in addition to the new source systems added. The order of the
population projects above was determined from the analysis of the Business Cases
reviewed during the current phase.
The Operational Data Store (ODS) and Enterprise Wide Data Warehouse (EWDW) are
shown to be introduced after the first iteration. If [Project Customer/Sponsor] desires,
the ODS and EWDW could be held back one iteration to allow for a second data mart
to be constructed before them.
The order for the iterations is based on anticipated return on investment, overall
organizational knowledge gained, and ease of implementation. It would be possible to
change the order of implementation based on changing requirements from [Project
Customer/Sponsor].
4. Cost Benefit Analysis

4.1. Estimated DW Costs
Industry cost figures for the design and implementation of enterprise wide data
warehousing and data marts are difficult to obtain. Research indicates that some
organizations are spending more than $10 million over five years. Most organizations
are reluctant to release their budget and cost figures. Additionally, the uniqueness of
each organization's hardware, software and labor requirements results in very different
requirements and costs.
The figures presented below are considered by [Project Leader] to be representative

estimates of a [Project Customer/Sponsor] DW project over the first five years of the
project. The costs fall into three general categories: hardware, software and labor. The
actual costs greatly depend on the [Project Customer/Sponsor] resources, tool
selection, architecture and the detailed planning and design that will be accomplished
as the DW project progresses. Based on the information gained during the project, all
necessary features of a DW have been included in the comprehensive cost estimates.
As the DW architecture is developed and tools are evaluated and selected, the costs
will become firmer estimates.
The cost figures represent the initial design and construction of the enterprise wide DW
and several Data Marts, and the intended support of the entire project through the five
year point.
Total DW Cost of Ownership

Months 1-4 5-7 8-11 12-15 16-19 20-23 24-27 28-31 32-35 36-39 40-43 44-47 48-51 52-55 56-59
Iteration DM EWDW DM DM DM MAINT MAINT MAINT MAINT MAINT MAINT MAINT MAINT MAINT MAINT
Operational Support Costs

IT Support 50.0 37.5 50.0 50.0 50.0 150.0 150.0 150.0 150.0 150.0 150.0 150.0 150.0 150.0 150.0
User Training ($200 per user) 2.4 5.0 5.0 5.0 5.0 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5
User Mentoring (one FTE) 42.0 33.0 42.0 42.0 42.0 42.0 42.0 42.0 42.0 42.0 42.0 42.0 42.0 42.0 42.0
Sub-Total operational Costs 94.4 75.5 97.0 97.0 97.0 192.5 192.5 192.5 192.5 192.5 192.5 192.5 192.5 192.5 192.5
Capital Costs
Hardware Purchase/Upgrade 40.0 75.0 40.0 40.0 40.0 5.0 5.0 5.0 5.0 3.0 3.0 3.0 3.0 3.0 3.0
Extraction Software 50.0 50.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Cleansing Software 0.0 50.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
DW Manager Software 0.0 25.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
RDBMS 50.0 300.0 50.0 50.0 50.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Metadata 10.0 50.0 10.0 10.0 10.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Front End 20.0 20.0 40.0 40.0 40.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Software Maintenance (15%) 0.0 0.0 7.0 7.0 7.0 37.0 37.0 37.0 37.0 37.0 37.0 37.0 37.0 37.0 37.0
Design & Implementation 440.0 330.0 440.0 440.0 440.0 78.0 78.0 78.0 78.0 78.0 78.0 78.0 78.0 78.0 78.0
Sub-Total Capital Costs 610.0 900.0 587.0 587.0 587.0 120.0 120.0 120.0 120.0 118.0 118.0 118.0 118.0 118.0 118.0
Total per Iteration 704.4 975.5 684.0 684.0 684.0 312.5 312.5 312.5 312.5 310.5 310.5 310.5 310.5 310.5 310.5
EDW Cumulative Total to Date 1,679.9 2,363.9 3,047.9 3,731.9 4,044.4 4,356.9 4,669.4 4,981.9 5,292.4 5,602.9 5,913.4 6,223.9 6,534.4 6,844.9
Legend: * All costs represented in thousands of dollars

DM = Data Mart EDW = Enterprise Data W arehouse MAINT = Maintenance Mode
The costs reflected in the Total DW cost of Ownership chart are based on the following:
Hardware and software costs are based on current average costs. These
estimates are obtained from [Project Leader] worldwide DW engagements of
like construction, and [Project Leader] staff knowledge of our partner vendors
costs.
Software Maintenance is based on the industry norm 15% of the purchase

price, charged annually after the first 12 months of use.
Operational costs are based on industry norms.
Design and Implementation costs represent estimated costs for development

labor.
4.2. Business Case Value & Rankings
Selection and prioritization of the DW population projects includes two key dimensions:
business justifications and execution do-ability. These analysis areas were addressed
by two main activities: (i) focus sessions to determine business justification and data
requirements; and (ii) the source data analysis to determine the general availability of
the source data. Those two areas have been cross-analyzed to determine the relative
value of source data.
The first section below summarizes the relative rankings of the business cases, taking
into consideration the availability and quality of the source data and considering the
estimated DW project costs to address the business case.
The second section summarizes the relative rankings of the source data subject areas.
It takes into consideration costs as compared to relative values that are defined by the
supported business case justifications.
A pleasant, but not unanticipated conclusion is that the two subject areas (Lab and
CRISP) are highly valued by many areas of the organization. A DW project that
includes both subject areas will serve a wide range of business needs.
4.2.1. Business Case Rankings
Summary Version of Rankings including Value Estimates.

Category Category Detail Criteria 1. Waste Water 2. Regulatory 3. Maintenance &
Weighting Treatment Requirements Scheduling
Soft ROI - Non-Dollar based Quantifiable Business Benefits
Score Sub-Total 38 33 33
Score Ranking 6 1 1
Weighted Score 20 120 20 20
Soft ROI - Intangible Business Benefits
Score Ranking 6 1 3
Hard ROI - Doller Based Measurable Business Benefits

Hard ROI Total $ 1,001,250.00 $ 365,000.00 $ 1,430,250.00
Score Ranking 3 1 6
Total Weighted Scores 450 100 410
Over-all Ranking 1st place=6 points, 2nd place=3, 3rd place=1 6 1 3
LEGEND
Score Ranking Calulation: 1st place=6 points, 2nd place=3, 3rd place=1
Weighted Score Calculation Score Ranking * Category Weighting
Full Version of Rankings including Value Estimates with detail metrics.

Category Category Detail Criteria 1. Waste Water 2. Regulatory 3. Maintenance &
Weighting Treatment Requirements Scheduling
Soft ROI - Non-Dollar based Quantifiable Business Benefits
Number of Value Statements (Soft ROI Statements) 18 9 20
Strategy Ranking of Busiess Process in SIP (25-ranking) 16 20 11
Number of Departments supported by business case 4 4 2
Score Ranking 6 1 1
Soft ROI - Intangible Business Benefits
Note: Based on a scale from 1-3, 3 being highest
BUSINESS PROCESSES
Supports discovery of busiess process improvements 3 2 3
Improved ability to respond to research requests by board, EPA, audits 3 3 3
Improved ability to research for process improvements, efficiences, in-efficeinces 3 2 3

Improved ability to measure effectiveness of process changes 3 1 2
Improve Utilization or Efficeincy of Labor 1 2 2
Improve Utilization or Efficeincy of Materials 3 1 2
Improve Utilization or Efficeincy of Equipment 1 1 2
Improve Utilization or Efficeincy of Facilities/Fixed Assets 3 1 3
PERSONAL PRODUCTIVITY
Knowledge 3 1 2
Pro-active vs. reactive 3 1 2
Productivity 3 3 3
Quality of Data 3 3 3
Quality of Decisions 3 1 2
Reduce redundant processing, support and software required for multiple "stovepipe"
reporting systems 2 2 2
ORGANIZATIONAL / POLITICAL GOALS
Improve ability to support benchmarking 3 3 2
Improve Cost per Million Gallons Waste Water 3 1 2
Improve Environmental & Community Health 3 3 1
Improved ability to educate community and build good will 2 2 1
Avoid Privitization 3 3 3
Reduce Odor Complaints 3 1 1
Score Ranking 6 1 3
Hard ROI - Doller Based Measurable Business Benefits

1. Treat Waste Water
Chemicals - save 10% per year on $5,000,000 $ 500,000.00
Chemicals - save 5% by verifying deliveries & leveraging vendor relationships $ 250,000.00
Saved FTE Reporting Labor per year ($75,000 per FTE) $ 251,250.00
2. Regulatory Requirements
Estimated Value of Data Quality $ 50,000.00
Save possibly 3 late violations per year $ 75,000.00
3. Maintenance & Scheduling
Save 5% of Maintenance Costs $ 200,000.00
Extend useful life of capital equipment by 1% $ 1,000,000.00
Reduced Inventory Costs $ 110,250.00
Hard ROI Total $ 1,001,250.00 $ 365,000.00 $ 1,430,250.00
Score Ranking 3 1 6
Total Weighted Scores 450 100 410
Over-all Ranking 1st place=6 points, 2nd place=3, 3rd place=1 6 1 3
LEGEND
Score Ranking Calulation: 1st place=6 points, 2nd place=3, 3rd place=1
Weighted Score Calculation Score Ranking * Category Weighting
Value of Source Data Based on Business Questions

Business Case CRISP LIMS CMMS FIS Manual EPA HR Cen Gen
Logs
1. Treate Waste Water

FOCUS SESSION QUESTIONS
1. How can chemical inventory control be managed to improve their application? X X X
2. How much does it cost to control odors in the plant? X X X X
3. Are we capturing all the costs associated with the operation on a process? Often we
(operations) are asked and we have to substantiate (the cost of a treatment process).
X X X X
4. Do changes to the operations processes affect the final effluent over time? X X X
Can a relation be determined versus changes in the environment? X X
PROPOSED PROTOTYPE QUESTIONS
1. Pounds of Cationic Polymer per cubic feet of sludge produced. X X X X
2. Efficiency of Primary Treatment. X X X X
3. Efficiency of Trickle Filter (secondary). X X X X
4. Efficiency of Activated Sludge (secondary). X X X X
5. Efficiency of Digester. X X X X
6. Efficiency of entire plant. X X X X
7. Concentration of Bio-solids (liquid %). X X X X
8. Concentration of H2S Hydrogen Sulfide (STINKS!). X X X X
9. Chart Increase chemicals & results in response to specific problem. X X X X
10. Source Trunk Constituent concentrations vs. same period last year. Provides a basis for
decisions on plant 1 & plant 2 blend. X X X X
11. How much power was used over time: X X
? vs. flow. X X
? vs. power generated, exported, imported. X X
? vs. digester gas used, produced. X X
? vs. natural gas used. X X
12. Odor cost, (to prevent H2S release and related odors). X X X X
? Caustic Soda use & soda cost in scrubbers. X X X X
? Peroxide use & cost in influent. X X X X
2 Regulatory Requirements
1. Are we operating the waste water process according to the EPA requirements on a
continuous basis? Both in the operations and in maintenance. X X X X
2. ECM Are we reporting the information that we need to report in a timely fashion. X X
Are standard reports (Monthly, Quarterly, Yearly) on schedule according to permit to avoid
fines? X
Are violations and complaints reported within 3 days. X
3. Do changes to the Operations processes affect the final effluent over time? X X
Can a relation be determined versus changes in the environment? X X X
Effects that are caused could be in compliance but could cause a cumulative non-
compliance. X X X
4. We have a higher and increasing concentration of Cadmium but our influents dont reflect
that. Why? We asked Operations and they said it is because the Digesters have a longer
retention time than other agencies, which cause a build up of heavy metals. Can we
substantiate this type of thing with the trend analysis capabilities of a DW?
X X X X
5. Would like to be able to look for specific trends for identified constituents. X X
6. Would be useful to use the DW to trigger flags or warnings so that they know to document
something that has happened. X X X
7. We are sitting on a gold mine of information, but cant get the pieces (nuggets) out.
X X X X
3. Maintenance & Scheduling
1. How much is the total cost to control odors in the plant? X X X X X
2. What is my O&M cost trend been for a given area at an asset level (e.g. pump station).
Fixed asset cost, maintenance costs, utilities costs and replacement cost. X X
3. Notice of when process (meter) range is way out of range. The meter itself needs service.
X X
4. What are the people/equipment/material requirements for a planned maintenance activity?
X X
5. HR systems like safety/injury data for process areas to determine training costs and safety
impacts for our processes. X X
6. What is the electrical load and requirements for existing electrical equipment? X X
7. How much do we electricity to we generate? X
8. Trend analysis to determine actual Mean time between failure analysis would make PMs
more cost effective. X X
9. Trend analysis to predict when major problems will occur. The Board has cut staff, when
will the deferred maintenance cost more than the cost savings? X X X
10. Trend analysis of equipment usage to determine predictive maintenance needs based on
actual usage instead of scheduling interval/calendar based maintenance. X X
Totals by Source System 37 28 9 19 18 2 7
4.3. Hard ROI Cost Benefit Analysis
These graphs illustrate the hard return on investment (ROI) analysis.
The basis for these graphs are the DW costs by iteration as compared with the
Estimated Business Value (Hard ROI). The first graph uses the total cost for the
enterprise wide data warehouse. The subsequent three graphs include the costs for
building just one data mart, compared with the value of each business case.
These ROI justifications were based solely on estimated Hard ROI and do not reflect
the priceless value of the many Soft ROI justifications for each business case.
1. Enterprise Wide DW Return on Investment
The ROI point at 24 months after beginning implementation is based on the combined
value of all three business cases. The estimated yearly hard ROI values of each
business cases were prorated over the months. ROI begins to accumulate after an
iteration has been implemented into the production environment.
Enterprise Wide DW Cumulative Costs vs.

Total Value of all 3 Business Cases
Return on Investment
and Individual Business Case Values
14,000
Cumulative Value of
Enterprise Wide DW Cumulative Costs
all 3 Business Cases
12,000 Treat Waste Water Value
Maint. Scheduling Value
10,000 Regulatory Requirements Value
All 3 Business Cases Cumulative Value
Do
lla 8,000
rs Enterprise Wide DW Cumulative Costs
(0
00
6,000 Maintenance & Scheduling
)
ROI at
24 Months
Treat Waste Water
4,000
2,000 Regulatory Requirements
-
1 5 8 12 16 20 24 28 32 36 40 44 48 52 56
Months
2. Waste Water Treatment Analysis Return on Investment
Treat Waste Water Data Mart ROI
5,000
4,500
4,000
3,500
3,000
Dollars (000)
One Data Mart Costs

2,500
Treat Waste Water Value
2,000
1,500
1,000
500
-
1
8
s
12
16
32
36
40
52
56
20
24
28
44
48
th
on
M
The graph shows where the cumulative value of the Waste Water Treatment Analysis
DW exceeds the estimated cost of building the data mart.
3. Treatment Process Regulatory Requirements Analysis Return on Investment
The graph shows where the cumulative value of the Treatment Process Regulatory
Requirements Analysis DW exceeds the estimated cost of building the data mart.
Regulatory Requirements Data Mart ROI
1,800
1,600
1,400
1,200
Dollars (000)
1,000
One Data Mart Costs
Regulatory Requirements Value
800
600
400
200
-
s
12
16
48
52
56
20
24
28
32
36
40
44
th
on
M
The estimated $250,000,000 cost of building a full secondary treatment plant and the
$11,000,000 yearly operations costs were not included as part of this ROI analysis.
4. Maintenance Scheduling Decision Support Return on Investment
The graph shows where the cumulative value of the Maintenance Scheduling Decision
Support DW exceeds the estimated cost of building the data mart.
Maintenance Scheduling Data Mart ROI
8,000
7,000
6,000
5,000
Dollars (000)
One Data Mart Costs

4,000
Maint. Scheduling Value
3,000
2,000
1,000
-
s
16
24
32
44
52
12
20
28
36
40
48
56
th
on
M
5. Business Cases
The following sections contain the full detail of business justifications for each of the
three candidate data warehouse projects. Additionally, the focus session results are
summarized.
5.1. Waste Water Treatment Analysis
5.1.1. Business Case Summary
Treating wastewater is the primary function of [Project Customer/Sponsor] and

all operational expenses are in some way related to this process.
The Waste Water Treatment DW will provide timely access to integrated lab
sample data, CRISP operational metrics with FIS costing information to enable
analysis of the treatment process and plant operations.
5.1.2. Business Benefits / Justification
The following sections define the business justifications in terms of the business
needs expressed in the Strategic Information Plan (SIP) and the Focus
Sessions.
While hard dollars are difficult to determine there are clear return on
investment scenarios and business justifications.
Focus Session Business Questions
The business questions indicate the business value that the DW will provide to
the users. The following business questions were directly applicable to the
scope of the business case and were derived primarily from the specific focus
session. A few questions are from separate interviews and other focus
sessions.
How can chemical usage be managed to improve their application?

How can chemical inventory control be managed to improve their
application?
How much does it cost to control odors in the plant?
Are we capturing all the costs associated with the operation on a process?
Often we (operations) are asked and we have to substantiate (the cost of a
treatment process).
Do changes to the operations processes affect the final effluent over time?
Can a relation be determined versus changes in the environment?
Proposed Prototype Questions
[Project Customer/Sponsor] users (mostly operations) proposed the following

questions as possible prototype questions in the area of Waste Water
Treatment Analysis. Although all of these did not fit within the prototype scope,
they do all demonstrate the valuable knowledge a DW will provide to the
business.
These queries exemplify how a DW will provide quick and accurate decision
support for common business questions and business measures of success.
Currently some of these questions are being partially answered via redundant
manual data gathering, consolidation, validation and assembly.
1. Pounds of Cationic Polymer per cubic feet of sludge produced.

2. Efficiency of Primary Treatment.
3. Efficiency of Trickle Filter (secondary).
4. Efficiency of Activated Sludge (secondary).
5. Efficiency of Digester.
6. Efficiency of entire plant.
7. Concentration of Bio-solids (liquid %).
8. Concentration of H2S Hydrogen Sulfide (STINKS!).
9. Chart Increase chemicals & results in response to specific problem.
10. Source Trunk Constituent concentrations vs. same period last year.
Provides a basis for decisions on plant 1 & plant 2 blend.
11. How much power was used over time:
vs. flow.
vs. power generated, exported, imported.
vs. digester gas used, produced.

vs. natural gas used.
12. Odor cost, (to prevent H2S release and related odors).
Caustic Soda use & soda cost in scrubbers.
Peroxide use & cost in influent.
Benefits / Value Statements
The business justifications are substantiated and include cost savings in the
following areas:
Optimize the entire treatment process by closely tracking, trending and

benchmarking the efficiencies within each plant process and the over-all
cost efficiency of the entire plant.
accessible, integrated information which provides knowledge for timely
tactical adjustments to the treatment process.
Accurately track, measure and benchmark cost benefits in plant process or
chemical use adjustments.
Enable management to work smarter with historical trend analysis and
costing analysis providing strategic knowledge for medium to long term
tuning of the entire plant process which will lead to more cost effective
contracts for materials and capital projects.
Save chemical cost by reduced usage through discovery of efficiencies and
the narrowing of tolerances.
Save chemical costs by improving delivery schedules, improved tracking of
actual deliveries and actual quantities delivered by vendors and by using this
knowledge as leverage to improve vendor contracts.
Reduce or eliminate duplicate data entry of lab samples, plant process
information and other key data.
Reduce manual integration and reporting of lab and operational information.
Empower a more productive workforce with a DW providing the business
knowledge base and a tool for analysis, reporting and decision support.
The following are the focus session value statements (Soft ROI) which were
directly applicable to this business case.
1. No checks and balances covering deliveries of chemicals.

2. Bleach can go bad (perishable) if we do not use all that was purchased.
3. If too much chemical inventory on hand, there is an interest charge on
payables.
4. Time spent ordering chemicals.
5. Without the trending information we have situations where we may order too
much too little chemicals. This results in un-necessary delivery charges and
related labor.
6. Currently the various units of measure make validations impossible

manually. For example, the delivered quantity could be liquid weight, the
billing quantity dry weight and the meter usage in gallons.
7. Currently there is no information to evaluate if plant process changes
produced a true cost/benefit. To some extent results are seen, and
separately costs, but no study of the cost/benefits of different treatment
scenarios can be done.
8. A process improvement will have a ripple effect on cost savings. For
example; improved Digesters would increase methane for electricity, save
polymers, save truck hauling costs of Bio-Solids, and slightly increase the
value of Boi-Solids concentration as a denser fertilizer since it is sold based
on solids not tonnage. All of these cost saving process improvements also
results in cleaner effluent to the ocean!
9. Better usage of:
Consumables Chemicals
Energy
Oxygen Systems
10. Reduce our concentrate of solids and reduce shipping charge.
11. Reduce building of process facilities through efficiency.
12. Increase production of methane.
13. Improve EMC research capabilities.
14. Improve assembly of TPOD reports for Operation 820 group.
15. Reduce or eliminate recalculating information for various reports.
16. Reduce reconciliation time to validate billing.
17. Reduce reconciliation time to validate various reports to include in larger
reports (like the annual report).
18. In-plant air quality has a choice of processes they can employ. There is a
potential for a change of process resulting in additional ROI.
The following are the ROI scenarios expressed by the business users during the
focus sessions and related interviews. It is understood that these net benefits
will be very difficult to measure because both pre and post DW measurements
are elusive. After implementing the DW, many of the benefits will be noticeable
and quantifiable. A few of these ROIs are applicable to more than one business
case.
These numbers were directly used in the Ranking Spreadsheet to evaluate the
relative values of the candidate business cases.
See section 4, Cost Benefit Analysis.
Hard ROI Estimates from the business Users
Potential to save $ 500,000 on chemical usage. Assumes 10% savings on

$5 million/year on chemicals.
An additional 1 5% of chemical costs could be saved by more accurately
tracking and verifying vendors deliveries of chemicals. Some vendor
deliveries are not measured at all, while most deliveries are only eye-balled
for verification. Some deliveries are billed in different units of measure which
prevent manual verification.
Labor Cost Savings
o 15% of ECMs time is spent researching and collecting data.
o Estimated 1 Full Time Equivalent employee (FTE) organization wide to
do manual data collection, consolidation and validation.
o At least 2 FTE saved across the various operations groups due to
duplicate data entry, consolidation, verification and reviews of manually
constructed reports. The 2nd FTE is directly related to creating the MSO
report which includes about 7 sections, about 50 sheets, from all the
different divisions.
o 10% FTE spent re-keying lab results
o 10% FTE spent on Calculation Sheet which for the most part could be
printed from the DW, although a few manual steps and analysis remains.
o Total 3.35 FTEs
Strategic Impact
Treating waste water is [Project Customer/Sponsor]s core business function. All

expenditures and all operations are in some way directly related to this core
business function. The DW will provide a wealth of information on [Project
Customer/Sponsor]s core processes. The organizations goal is to operate the
treatment process in the most cost effective methods while preserving the
health of the community and environment.
The success of [Project Customer/Sponsor] in effectively meeting the needs of

the EPA regulations and the Orange County community and industries depends
on the processing running reliably. The DW will enable a large cross section of
the entire organization to have timely access to lab results and plant processing
information which is vital to their daily work load.
[Project Customer/Sponsor] faces the constant threat of privatization. The DW

will provide the analysis tools necessary to identify efficiencies and accurately
measure the effects of process improvements. Currently there is no tool for the
analysis or measurement of efficiencies.
[Project Customer/Sponsor]s Critical Goals for FY 1997 include the following

goals which are supported by the DW:
Competitiveness & Reorganization
Integrate computer system to link users and gain on-line access to data
bases
Monitoring & Compliance

Negotiate changes in monitoring requirements in our ocean discharge permit
to add flexibility, target data needs and improve cost effectiveness
Develop new information products to communicate Districts compliance
performance.
Success / Do able
The critical success factors of a DW include:
Management Sponsorship
User Sponsorship
Business Justification & Budget
Data Available, accessible. sufficient quality, appropriate level of detail and
can be transformed to a view consistent with the business questions
Data Warehouse process: methodology, project management and skilled
labor
The Business Case project assessed these success factors. The information
provided in the focus session and interviews indicates that there is sufficient
sponsorship and business justification for the DW. The users indicated that the
data does exist. It is sufficiently accurate and available at the right level of detail
to support a DW design, which transforms this information into a subject
oriented decision support system. While the evidence indicates that these
success factors are in place, they remain critical success factors to manage
during the DW process.
It appears that the data sets coming directly from LIMS and CRISP will be
accurate and can be directly transformed into a DW. The data from manual
sources will be data quality risks that must be mitigated through quality controls.
That data includes some chemical usage information and manually monitored
meter readings. The process of building the DW with quality controls will assist
the organization in improving the quality of the manually entered data.
Assuming the Critical Success Factors are met, the data quality risks are
managed, and an appropriate DW process is executed this DW implementation
will be successful.
Demand
The demand for the DW is supported by the following [Project

Customer/Sponsor] organizations and business needs.
All Operations Groups

Assists Source Control by providing improved access and quality of the lab
results data and the plant processing data.
Assists the Maintenance and Operations department by providing access
and quality data for plant processes.
Assists ECM group by providing the lab results and plant process
information.
Priority / Alignment with SIP Recommendations & Strategies
Below the SIP Investment Strategies and Recommendations which are directly
related to this DW business case or are partially met by the DW business case.
This analysis was based solely on the information contained in the SIP
document. The SIP summaries of the processes, applications, databases and
strategies led to these conclusions and it is possible omitted details of these
recommendations may have altered the conclusions.
Investment Strategy: 2, 5, 9, 10a, 12, 13, 18, 20a, 20b, 22
2. Manage Data Application This strategy is partially met by the DW

because for the data subject areas the DW includes (LIMS, CRISP) the
manage data requirements are met, such as providing historical data at
a finer level of detail to evaluate trends and generating reports.
5. Meet Regulatory Requirements The DW partially supports this strategy

by providing integrated LIMS and CRISP data which is used for
regulatory reporting.
9. Plant Process Database The DW partially covers this strategy by

providing the analysis and reporting needs that support the plant
process operation, yet the DW does not cover the operational
enhancements related to this strategy.
10a. Treat Wastewater Application The DW covers a majority of this

strategy by providing integrated accessible treatment process
information including both lab and CRISP data.
12. Discharge Wastewater Effluents Application Since the DW includes

the effluent lab results the DW covers all the basic requirements for this
strategy.
13. Sample/Analyze Wastewater Application Most of the requirements for

this strategy is related to the DW supplied functionality including:
analysis of large data sets, reporting, trending, graphs, etc.
18. Sample Database This strategy recommendation includes tracking and

making available lab and Source Control Samples. This need is met by
a combination of the three systems (LIMS, Source Control) and this DW.
20a. Control Influent Characteristics Application This strategy is partially met

by the DW in the area of making influent lab results at a detail
constituent level accessible to all areas of the organization.
20b Effluent Database This strategy is mostly met by the DW in the areas
. of combining effluent lab results with ODES and FIS to provide
integrated data with good reporting and analysis tools.
22. Influent Database This strategy is mostly met by the DW by integrating

lab influent samples with CRISP process flows information for
organization wide access and analysis.
Recommendation: 4, 9, 13, 14, 15, 18, 19 24, 26, 27, 28
These are the SIP recommendations which are at least partially addressed by
the DW or would be integral to the DW. Several recommendations were not
included in the list if they were simply data sources and not also integral to
related business processes.
Many of these recommendations are directly covered by this DW Business

Case. The Database Recommendations (13, 24, 27, 28) and Integrated
Database Recommendations (4, 15) appear to be included in the DW. The
analysis, reporting and decision support aspects of many process/application
recommendations (9, 14, 18, 19, 26) also appear to be met by the DW.
4. Data Warehouse The candidate DW is a proposed phase to this

recommendation.
9. Meet Regulatory Requirements Application The DW mostly covers this

recommendation by providing integrated information from LIMS and
CRISP which can be used to support regulatory analysis and reporting.
13. Plant Process Database The DW is integral to this recommendation.

The DW provides the trending analysis to determine the operational
enhancements and rules for automated adjustments. The DW covers a
majority of this recommendation since it will model a majority of the
relevant data.
14. Plant Automation (Treat Waste Water Process Application) Similar to

the prior recommendation: the DW will provide the trending analysis to
determine the operational enhancements and rules for automated
adjustments. The DW would not include the actual operational
automation which is covered by a system such as CRISP.
15. Integrating Plant, Maintenance, Lab and Financial Data The DW covers
a majority of this recommendation by providing the integrated data store
for these subject areas.
18. Discharge Wastewater Effluents Process Application The DW will likely

cover all the required functionality for this recommendation by providing
an accessible data store for all reporting and decision support on
effluents.
19. Sample/Analyze Wastewater Process Application The DW will likely

cover all of the required functionality for this recommendation by
providing a decision support system including source data from LIMS,
CRISP and ECM if necessary to meet this recommendation.
24. Sample Database Similarly to the prior recommendation, the DW will

provide the data store for analysis of lab samples.
26. Control Influent Characteristics Process Application The DW is integral

to the process of controlling influent. The DW will provide the analysis
and knowledge base for personnel to make influent decisions and
adjustments to operational systems.
27. Effluent Database The DW will likely cover all of the required
functionality for this information by providing a decision support data
store for effluents.
28. Influent Database The DW will likely cover all of the required
functionality for this information by providing a decision support data
store for influents.
Alignment with existing IT projects / applications / architecture
The DW will support (and not overlap) with the planned LIMS and CRISP
upgrades and implementations.
The LIMS system will continue to expand to address the operational needs of
the lab. The DW will use LIMS as a data source and integrate the lab data with
plant process data in a subject oriented manner to allow appropriate cross trend
analysis.
The SCADA/CRISP system will continually grow in the number of PLCs in place
to monitor and control the treatment process. Currently there are sufficient
PLCs in place to feed the DW operational metrics which will enable analysis and
optimization of maintenance schedules. As new PLCs are deployed, the DW
will expand to include analysis over a greater number of capital assets and a
finer level of detail.
5.1.3. Critical Success Factors and Inhibitors
Standard DW Critical Success Factors
Sponsorship at all levels of the organization is critical. Executives must

sponsor the budget and encourage the organization to participate to ensure
success. End users must be an integral part of the process to realize the
benefits.
The quality and the source data must be ensured. The focus sessions
determined that the data was perceived to be available and of the requisite
quality, but that has not been confirmed by detailed analysis.
The DW is a new process for the information technology department. For
the project to be successful they will need to augment their staff if DW
skilled resources. They will also need to execute the process using a tried
and true methodology and manage the process with sound project
management techniques.
There must be a DW architecture in place. The DW process must fall within
the guidelines of the organization's architecture. The DW architecture
provides the framework for new iterations or enhancements to be built
efficiently without re-inventing the wheel and without invalidating prior
designs.
The DW is a new information technology tool for the organization. The DW
process must include sufficient lead time for the users to become acclimated
to the decision support tools and experience the learning curve from simple
queries to true analysis.
The training costs on usage of DW DSS tools must be addressed. This can
also be a benefit since users can learn one reporting environment instead of
multiple systems with multiple views of the data and multiple
reports/spreadsheets.
Specific Critical Success Factors for the Treat Waste Water Analysis DW
The chemical usage information and some critical meter readings are currently
being obtained and tracked manually. As part of the DW process, these manual
tasks must be included as part of the over-all data quality method. The data
must be obtained, data entered on a timely basis, and validated.
Standard DW Inhibitors
Training costs on usage of DW DSS tools must be addressed. This can also
be a benefit since users can learn one reporting environment instead of
multiple systems, and multiple reports/spreadsheets.
Source system changes must be coordinated with the DW process. Source
system bugs and down-time will affect the data quality and data flow to the
DW. Source system design changes will affect data transformation /
mapping for the DW. These are standard DW risk that can be mitigated by
implementing DW methods such as data quality programs, enterprise wide
data modeling techniques and metadata. The various systems are kept
synchronized by utilizing an architected framework for the technology
lifecycle.
Specific Inhibitors for the Treat Waste Water Analysis DW
There is sufficient information being tracking in CRISP to begin providing

immediate benefits to data warehousing, but CRISP must be greatly
expanded to include all of the data points that the various departments
would like to analyze.
This DW is dependent on the LIMS application roll-out as a data source.
LIMS went live in early January and as such is likely to have some bugs to
be worked out. This could effect the quality and timely delivery of lab results
data to the DW.
Source Control enhancements will continue and must be coordinated with
the DW process by conforming with the DW architecture.
Manual Logs are included as a data source for the DW. These include
manual readings for chemical levels, chemical usage and manual meter
readings for meters that have not or can not be integrated with CRISP.
Some of this data is consolidated to the Monthly Summary of Operations
(MSO) reports and the MSO may be used as the direct source for the DW.
Some manual processes will be necessary to ensure consistency of the
format, quality of the content and possibly to manually trigger data loads
when the data is complete.
Portions of the MSO Reports may be required source data for the DW.
Since these are manually maintained, additional quality controls must be
implemented to ensure consistent and accurate data for the DW. Some
manual processes will be necessary to ensure consistency of the format,
quality of the content and possibly to manually trigger data loads when the
data is complete.
5.1.4. Assumptions
The information provided in the focus sessions and interviews is sufficiently

accurate for the conclusions documented in this deliverable. It is further
assumed that the estimated hard & soft return on investment statements
represent reasonable estimates sufficient for budgeting.
Continuing the DW process requires that the Critical Success Factors and
Inhibitors be addressed. This includes sponsorship, budget, data quality
and an effective DW process.
The Cost Benefit Analysis was based on industry standard metrics for DW
costing. The actual cost will vary based on actual scope, designs and
suitability of the current technology infrastructure. The DW architecture
process will refine these estimates through more detailed analysis of the
requirements, scope and technology infrastructure gaps.
5.1.5. Business Process Model
Water Water Treatment, Operations
Truck Load
Report
Chemical
Delivery Receipt
Accounting
820
Administration 17
1st of Month
Readings 18
Analysis
Plant Monthly 12
Report 15 TPOD
13
O&M
14
Department
Rough TPOD Monthly
MSO
6 (online) Report
5
3
Chemical
2 Daily Lab
Inventory Night
Results
Information
(online)
1 Operator's
Log 11
7 8 16
OCWD/
IRWD
Meter Reading
Sheets
9
870 Central
4
Generation
Lab
10
Chemi Ca CRISP VAX

Samples
Primary Trickle
Treatment Filter Daily Lab
Results
Digester
Activated
Sludge Belt Press
Bio Express Peroxide

Vendor
(radio monitor)
Processes:
1. Bio-solid transport truck is weighed after loading. The trucks unloaded

weight is subtracted to determine the load weight. The truck load report is
sent to Accounting to trigger a payment.
2. The chemical vendor truck arrives and pumps chemicals into holding tanks.
The delivery is visually confirmed by the level increase in the holding tank.
The driver has a receipt outlining the weight or volume of the delivery. The
Chemical Delivery Receipt is sent to Accounting and triggers an invoice
payment. These chemicals are purchased under a blanket agreement with
the vendor.
3. On the first of the month, staff in the plant take physical readings (flows,
pressures, chemical levels). These are reported on the First of Month
Readings Report. This report goes to the 820 group and is primarily used to
reconcile with Accounting.
4. There is more than one type of Operators Log. They are used to record log
information that is especially important to the process being monitored;
pressures, levels, chemicals, and temperatures. They also capture verbiage
about unique occurrences and abnormalities.
5. Verbiage from the Operators Logs provides information to the Plant Monthly
Report.
6. Readings from the Operators Logs provide information to the Monthly

Summary of Operations.
7. Manual Meter Readings that concern chemicals are recorded on the

Chemical Inventory and summarized from there on the MSO.
8. Manual Meter Readings from the plant are one of the main sources of data
for the MSO and the Night Report.
9. The Central Generation process gets information about digester gases

produced from the manual meter readings. From this information, data from
the gas company (imported natural gas), and internal instrumentation,
measurements is used to calculate and report on the MSO:
Internal gas consumed

External gas consumed
Power produced
Power imported
Power exported
10. CRISP takes real-time readings in the plant: flows, levels, pressures, and
temperatures. Some of these are recorded in CRISPs internal history files.
Reports of this history are sent to the Control Center and entered into the
MSO.
11. The Water District X and the Water District Y are contacted by phone to
report reclaimed water usage for the MSO.
12. The Plant Monthly Report is a paragraph-form report that describes

events in the plant. This is sent to the 820 group to eventually be
included in the Operations and Maintenance Department Monthly
Report.
13. The Monthly Summary of Operation is the assembled source of data

about the plants operation, production, consumption and performance.
The MSO includes LAB, CRISP, CenGen and manually compiled data.
Some information from the plant enters a computer for the first time in
this report. It is available online and is used throughout CSDOC. It is
also sent directly to the 820 group for further analysis and to be included
in the Operations and Maintenance Department Monthly Report.
14. The Night Information, also known as Process Information, is the

assembled readings of log data from the plant. It is available online and
is used as the first word in plant operations. Although it is mostly meter
readings and measurements, there is a Note space at the bottom of the
form that includes descriptive information about the plants condition
entered from the Operators Logs.
15. The Operations staff in the Control Center does some additional analysis
on the MSO numbers to illustrate efficiencies in the plant. This
information is sent to the 820 group to assist the development of the
TPOD (Treatment Plant Operational Data).
16. On regular intervals the lab takes samples from various areas in the
plant and runs tests on them. The results of these tests are summarized
on the Daily Lab Results Sheet and sent to the 820 group and plants
(this is currently an Excel spreadsheet and will be migrated on to the
LIMS system).
17. Using several of the reports that are sent from the plant operations, the
820 group reconciles usage information with payment information in
Accounting. This includes consumables, chemicals and power usage.
5.1.6. Source Data Analysis
The DW will include data from the following sources. During user interviews it
was confirmed that this data does exist at a sufficient level of detail and can be
joined with other data sets to create a consistent business view.
LIMS Lab Sample results (influents, in plant processing,

effluents)
CRISP Actual plant processes (run times, flows, meter readings)
FIS Costs of materials / Chemicals and fixed assets
Operations Non-CRISP readings come from manual data entry in

operations, for Chemical usage and various meters.
5.2. Treatment Process Regulatory Requirements Analysis
Meeting Environmental Protection Agency (EPA) regulatory requirements is a

must-do business process for [Project Customer/Sponsor].
The Regulatory Requirements Analysis DW will support a majority of the EPA

reporting requirements and enable analysis of wastewater treatment compliance
through the integration of lab sample data with regulatory information.
needs expressed in the Strategic Information Plan and the Focus Sessions.
While hard dollars are difficult to determine, there are clear return on
sessions.
1. Are we operating the waste water process according to the EPA

requirements on a continuous basis? Both in the operations and in
maintenance.
2. ECM Are we reporting the information that we need to report in a timely

fashion?
Are standard reports (Monthly, Quarterly, Yearly) on schedule according

to permit to avoid fines?
Are violations and complaints reported within 3 days?
3. Do changes to the Operations processes affect the final effluent over time?
Can a relation be determined versus changes in the environment?

Effects that are caused could be in compliance but could cause a
cumulative non-compliance.
4. We have a higher and increasing concentration of Cadmium but our
influents dont reflect that. Why? We asked Operations and were told that it
is because the Digesters have a longer retention time than other agencies,
which cause a build up of heavy metals. Can we substantiate this with the
trend analysis capabilities of a DW?
5. Would like to be able to look for specific trends for identified constituents.
6. Would be useful to use the DW to trigger flags or warnings so that they

know to document something that has happened.
7. We are sitting on a gold mine of information, but cant get the pieces
(nuggets) out.

areas:
Allow cost effective support of regulatory reporting, internal or external

research, audits, lawsuit inquires and source data for plume graphs.
Reduce or eliminate duplicate data entry and manual research of lab reports
which will automate the construct regulatory reports.
accessible integrated information which provides knowledge for timely
tactical adjustments to the treatment process where constituents approach
EPA thresholds.
Enable management to work smarter with historical trend analysis
providing strategic knowledge to tune the entire plant process to confidently
maintain compliance while reducing costs.
1. It is harder to negotiate each permit cycle (5 years) because the customer

base is increasing and therefore volumes increase to [Project
Customer/Sponsor]. The EPA wants to write the waiver based on volume
not concentration.
2. Need information to educate the EPA on how we effectively protect the

public health and environment even though total volume of waste water may
appear higher than what they perceive as being an issue.
3. Although direct fines may not be levied, providing the EPA with timely and
accurate reporting builds a better relationship that can assist [Project
Customer/Sponsor] with negotiating a more acceptable permit.
4. Effects that are caused could be in compliance but could cause a cumulative
non-compliance.
5. Currently have about 2 years of history on-line, but the permit requires 5
years. The DW is the perfect repository for historical information and allows
operational systems to run more efficiently by purging historical information
that can be more effectively accessed in the DW.
6. Source Control is audited every year or two, plus the EPA and regional
board call to question things on a regular basis. The DW would make these
audits smoother by providing easy access to consistent information.
7. The DW could trigger flags or warnings so that they know to document

something that has happened.
8. We are sitting on a gold mine but cant get the pieces out.
The following are the estimated return on investment scenarios expressed by

the business users during the focus sessions and related interviews. It is
understood that these net benefits will be very difficult to measure because both
pre and post DW measurements are elusive. After implementing the DW, many
of the benefits will be noticeable and quantifiable. A few of these ROIs are
applicable to more than one business case.
Hard ROI Estimates from the Business Users
Extensive labor costs in consolidating information.

o 15% of ECMs time is spent researching and collecting data.
o Estimated 1 FTE organization-wide to do manual data collection,
consolidation and validation.
o At least 2 FTE saved across the various operations groups due to
duplicate data entry, consolidation, verification and reviews of manually
constructed reports. This includes at least one FTE in operations across
employee name 1s group, employee name 2s area , employee name 3,
employee name 4, and others each spending 20-30% of their time
manually manipulation information that will be provided directly from the
DW. The 2nd FTE is directly related to creating the MSO report which
includes about 7 sections, about 50 sheets, from all the different
divisions.
o 5% FTE in manual labor searching for data for the annual reports.
=>Total 3.20 FTEs
Fines for incorrect information reported.

o Are consistently late but the EPA has been lenient and not levied the
potential daily fines of up to $25,000.
Potential Clean Water Act, Air Quality violations. There are penalties
defined.
Could lose waiver for discharge of secondary, which would cost the
construction of a full secondary treatment plant.
o Cost of full secondary treatment plant is $239,000,000 with an annual
operation cost of $11,500,000. This is all in 1989 dollars.
o Currently in the 8th year extension for the previous 5 year permit. Gave
the info to the EPA in 1990.
Strategic Impact
[Project Customer/Sponsor] must have a strategy to leverage its relationship

with the EPA (and related agencies). The EPA could have severe detrimental
revenue and political impact on [Project Customer/Sponsor] through late
reporting penalties, non-compliance penalties, raise poor political press, and the
ultimate trauma revoke [Project Customer/Sponsor]s permit.
The DW will provide [Project Customer/Sponsor] with a reporting and

knowledge base to pro-actively manage its relationship with the EPA. The DW
will assist [Project Customer/Sponsor] in avoiding penalties by providing timely
and accurate reporting. The DW will also provide a knowledge base for
research, evidence and justification showing that [Project Customer/Sponsor]
overachieves in compliance, protecting the environment, operating cost
effectively and is a model treatment facility.

Integrate computer system to link users and gain on-line access to data
bases.
Negotiate changes in monitoring requirements in the ocean discharge permit

to add flexibility, target data needs, and improve cost effectiveness.
performance.
Success / Do able
User Sponsorship
Data Available, accessible and of sufficient quality, appropriate level of
detail, and can be transformed to a view consistent with the business
questions.
Data Warehouse Process: methodology, project management, and skilled
labor.
data does exist, is sufficiently accurate and available at the right level of detail to
support a DW design. The DW transforms this information into a subject
oriented decision support system. While the evidence indicates that these
success factors are met, they remain critical success factors to manage during
the process.
It appears that the data sets coming directly from LIMS and CRISP will be
accurate and can be directly transformed into a DW. The data from manual
sources will be data quality risks that must be mitigated through quality controls.
That data includes some chemical usage information and manually monitored
meter readings. The process of building the DW with quality controls will assist
the organization in improving the quality of the manually entered data.
managed, and an appropriate DW process is executed, this DW implementation
will be successful.
Demand

Customer/Sponsor] organizations and business needs:
ECM regulatory reporting.

Lab trend analysis.
Source Control will use the DW as a source system for some information
they already track and can also use it for new trend analysis.
Portions of the MSO reports will be covered by the DW.
Below the SIP Investment Strategies and Recommendations which are directly
related to this DW business case or are partially met by the DW business case.
Investment Strategy: 1, 2, 5, 7b, 10, 12, 13, 18, 20a, 20b, 22
1. Document Database The Regulatory Documents could be cross referenced

from the DW.
2. Manage Data Application This strategy is partially met by the DW because

the data subject areas that the DW includes (LIMS, CRISP) to manage data
requirements are met. Examples include providing historical data at a finer
level of detail to evaluate trends and generating reports.
5. Meet Regulatory Requirements The DW directly supports regulatory

reporting and analysis
7b. Law/Regulation/Code Database The DW could include the strategy to

provide all districts with access to basic regulation information.
10a Treat Waste Water Application Since this DW will include both lab and
. CRISP information, the DW partially over-laps with this strategys
requirements.
12. Discharge Wastewater Effluents Application Since the DW includes the

effluent lab results the DW covers all the basic requirements for this strategy.
13. Sample/Analyze Wastewater Application Most of the requirements for this

strategy are related to the DW supplied functionality including: analysis of
large data sets, reporting, trending, graphs, etc.
18. Sample Database This strategy recommendation includes tracking and

making available lab and Source Control Samples. This need is met by a
combination of the three systems (LIMS, Source Control) and this DW.
20a Control Influent Characteristics Application This strategy is partially met by

. the DW in the area of making influent lab results at a detail constituent level
accessible to all areas of the organization.
20b Effluent Database This strategy is mostly met by the DW in the areas of
. combining effluent lab results with ODES and FIS to provide integrated data
with good reporting and analysis tools.
22. Influent Database This strategy is mostly met by the DW by integrating lab
influent samples with CRISP process flows information for organization wide
access and analysis.
Recommendation: 4, 9, 12, 13, 14, 15, 18, 19, 24, 26, 27, 28
These are the SIP Recommendations which are at least partially addressed by
the DW or would be integral to the DW. Several Recommendations were not
4. Data Warehouse The candidate DW is a proposed phase to this

recommendation.
9. Meet Regulatory Requirements Application The DW appears to cover all of

the requirements for this application by providing the knowledge base and
reporting needs for the regulatory requirements business process.
12. Law/Regulation/Code Database The DW covers a majority of this

recommendation by providing the database to track regulation/permit related
metrics. The DW would also reference any document images that may be
related to this recommendation.
13. Plant Process Database The DW compliments this recommendation by

providing the over-all plant effectiveness measures. Since the DW will include
plant process metrics from CRISP and LIMS results, the DW will directly
support the analysis and reporting portions of this recommendation.
14. Plant Automation (Treat Wastewater Process Application) Similar to the prior
recommendation, the DW will compliment this application by providing the
trending analysis to determine the operational enhancements, and measure
adjustments against regulatory goals and thresholds. The DW would not
include the actual operational automation which is covered by a system such
as CRISP.
15. Integrating Plant, Maintenance, Lab and Financial Data The DW partially
covers this recommendation by providing the LIMS and CRISP data.
18. Discharge Wastewater Effluents Process Application The DW will likely cover
all the required functionality for this recommendation by providing an

accessible data store for all reporting and decision support on effluents.
19. Sample/Analyze Wastewater Process Application The DW will likely cover all
of the required functionality for this recommendation by providing a decision
support system including source data from LIMS, CRISP and ECM if necessary
to meet this recommendation.
24. Sample Database Similarly to the prior recommendation, the DW will provide
the data store for analysis of lab samples.
26. Control Influent Characteristics Process Application The DW will be integral

to the process of controlling influent. The DW will provide the analysis and
knowledge base for personnel to make influent decisions and adjustments to
operational systems.
27. Effluent Database The DW will likely cover all of the required functionality for
this information by providing a decision support data store for effluents.
28. Influent Database The DW will likely cover all of the required functionality for
this information by providing a decision support data store for influents.
Alignment with Existing IT Projects / Applications / Architecture
The CRISP, LIMS and Source Control systems will continue to be enhanced to
meet the operational needs of those departments. The DW addresses a
separate need for an integrated view of the lab results and plant process
information. The DW serves a cross section of users for each department who
need to do cross-functional analysis.
The DW project is aligned with the following projects in ITs pipeline.
Data Integration
Strategic Information Architecture
FIS / Oracle Gateway
Data Extraction from Plant Control System
The DW will not overlap or interfere with the following projects in ITs pipeline.
All of these projects will improve the source data available to the DW.
CMMS upgrades
LIMS upgrades
Source Control Upgrades
CRISP upgrades.

benefits.
the project to be successful they will need to augment their staff with DW
skilled resources. They will also need to execute the process using a proven
methodology and manage the process with sound project management
techniques.
the guidelines of the organizations architecture. The DW architecture
designs.
Specific Critical Success Factors for the Meet Regulatory Requirements DW
The chemical usage information and some critical meter readings are
currently being obtained and tracked manually. As part of the DW process,
these manual tasks must be included as part of the over-all data quality
method. The data must be obtained, entered on a timely basis, and be
validated.
Training costs on usage of DW DSS tools. This can also be a benefit since
users can learn one reporting environment instead of multiple systems,
multiple reports/spreadsheets.
lifecycle.
Specific Inhibitors for the Meet Regulatory Requirements DW

This DW is dependent on the LIMS application roll-out as a data source.

LIMS went live in early January and as such is likely to have some bugs to
be worked out. This could effect the quality and timely delivery of lab results
data to the DW.
Source Control enhancements will continue and must be coordinated with
the DW process by conforming with the DW architecture.
Manual Logs are included as a data source for the DW. These include
manual readings for chemical levels, chemical usage and manual meter
readings for meters that have not or can not be integrated with CRISP.
Some of this data is consolidated to the Monthly Summary of Operations
(MSO) reports and the MSO may be used as the direct source for the DW.
Some manual processes will be necessary to ensure consistency of the
format, quality of the content and possibly to manually trigger data loads
data is complete.
5.2.4. Assumptions

The process models depict the business processes which interact with the DW
project.
[Model not available]
Processes:
1. Source Control sends the Source Control Monthly Report and the Source
Control Annual Report to both the EPA and Regional Water Quality Control
Board (RWQCB) as an NPDES requirement.
2. Source Controls permit holders send information about their effluent

performance. Depending on the type of monitoring that Source Control is
conducting for the permit holder, this could include:
Copy of current water bills.

Results of tests (self testing).
Information about business changes.
Other agencies that are users of the system are also monitored. This is done
with a Memorandum of Understanding (MOU). These documents are similar to
permits but are for agencies such as:
IRWD
SAWPA
XYZ Sanitation District.
3. The Lab provides most of the information to Source Control for reporting.
Specifically this includes the Discharge Monitoring Report (DMR) and the
Priority Pollutant Analysis Reports as well as test results for the Source
Control samples.
4. Source Control uses selected items (such as Ammonia levels) from

Operations for their reporting. This information arrives on the TPOD report.
5. Source Control tracks all trucking firms that move bio-solids from the plants.
The list is sent to the Health Department to verify that the trucking firms are
permitted to transport biological waste.
6. Operations, Maintenance and Solids Management Annual Report is an

NPDES requirement. It is sent to Environmental Compliance and Monitoring
(ECM) for distribution to the EPA and RWQCB.
7. Ocean samples gathered by ECM are sent to the Lab for analysis.
8. Outside contractors are also used to gather and analyze ocean samples.
These results are returned directly to ECM.
9. Biological solids removal firms report back to ECM with information about
the final destination and use of bio-solids. This is reported to the EPA.
10. When a spill occurs, it is generally reported to Operations from somewhere

in the community.
11. When a spill is reported to Operations, they dispatch the Collections group.
Collections go out into the field to gather information about the spill and
create an action plan to deal with it.
12. Spill information is routed to Collections for response action. Once

Collections has assessed the situation, the actions taken or help request is
communicated to Operations.
13. Collections are the response team for spills. As such, they must keep ECM
updated about the status of any spill that has occurred. This ranges from a
report on the action taken to a request for additional outside services (i.e.
Police, CalTrans, Office of Emergency Services).
14. Whenever a sewage spill occurs, the Orange County Health Department is
notified immediately. This is a requirement but it is not reported on an official
document. It is generally handled by phone.
15. Whenever a sewage spill occurs, the EPA is phoned for an initial report. The
spill will eventually be documented if it is over 1000 gallons. The EPA may
or may not request this report.
16. Whenever a sewage spill occurs, the RWQCB is notified immediately. This
will eventually be reported in writing if the spill is over 1000 gallons.
17. During any sewage spill, if outside assistance is needed the following
agencies are notified by phone:
Police: Safety, Public Assistance.

CalTrans: Roads.
PFRD: Storm Drains (Public Facilities and Resources Department).
OES: Emergency Service (Office of Emergency Services).
18. For every sewage spill over 1000 gallons, a spill report is written and sent to
the RWQCB.
19. All spills are summarized on a monthly spill report to the RWQCB.
20. Operations provide information for reporting to ECM on a regular basis. This
includes a variety of data such as:
Spill data.
Volumes of bio-solids produced.
Plant information.
21. There is a large amount of information that flows between the Lab and ECM.
The DMR goes to ECM for certification before the Lab publishes it to the
EPA or RWQCB.
Lab results
Reports
Bio-solids information.
22. The Lab releases the DMR report to the EPA and RWQCB monthly. The
report contains biological and chemistry data about influent and effluents for
CSDOC.
23. The Ocean Monitoring Report (OMR) containing information about ocean
impact is sent to the EPA annually.
24. The Ocean Monitoring Report (OMR) containing information about ocean
impact is sent to the RWQCB annually.
25. The EPA requires an annual Bio-Solids Report from ECM. This report deals
with the volume and disposition of the solid biological waste produced by the
CSDOC dewatering process.
26. The Operations, Maintenance and Solids Management Annual Report is

forwarded to the EPA by ECM. The report is constructed by Operations and
Maintenance.
27. The Operations, Maintenance and Solids Management Annual Report is

forwarded to the RWQCB by ECM. This report is constructed by Operations
and Maintenance.
28. The Ocean Data Evaluation System (ODES) is an electronic transmittal of

biological and chemical analysis to the EPA. This is similar to the
information contained in the DMR. ECM transmits the information to ODES,
but the data is fed by the Lab.
29. The Surveillance and Enforcement Monitoring System (SAEMS) is an

electronic transmittal of Chemistry information to the RWQCB. It is sent
directly from the Lab.
30. Although there are Air Quality Permit regulations (such as the release of
Sulfides) the reporting is currently exercised during the application for new
permits rather than on a fixed schedule.
31. The National Pollutant Discharge Elimination System (NPDES) is the vehicle
that the EPA uses to regulate [Project Customer/Sponsor]. The EPA is
unable to administer and enforce NPDES in every state so the
administration is often pushed down to the local level. In this state, this task
has been given to the State Water Resource Control Board who administers
it through the Regional Water Quality Control Board. This is why most of the
regulatory reports issued by [Project Customer/Sponsor] go to the RWQCB
and the EPA.
LIMS Lab Sample results (influents, in plant processing,

effluents)
CRISP Actual plant processes (run times, flows, meter readings)
Operations Non-CRISP readings come from manual data entry in

operations, for Chemical usage and various meters.
ECM The permit limits can be manually entered into the DW
5.3. Maintenance Scheduling Decision Support
Maintenance and Scheduling is a cross-functional process vital to ensuring

undisrupted and effective processing of waste water.
The Maintenance Scheduling Decision Support DW will provide integrated

CMMS maintenance information with SCADA/CRISP operational metrics such
as fixed asset related runtime, temperature and analog measurements. While
portions of the Maintenance & Scheduling process are addressed by specific
systems (such as CMMS) the entire knowledge base of information required to
plan efficient schedules does not exist. The DW will address the needs for
comprehensive, integrated and historical information to support appropriate
scheduling.
needs expressed in the Strategic Information Plan and the Focus Sessions.
While hard dollars are difficult to determine, there are clear return on
sessions.
1. How much is the total cost to control odors in the plant?
2. What is my O&M cost trend been for a given area at an asset level (e.g.
pump station). Fixed asset cost, maintenance costs, utilities costs and
replacement cost.
3. Notice of when process (meter) range is way out of range. The meter itself
needs service.
4. What are the people/equipment/material requirements for a planned

maintenance activity?
5. HR systems like safety/injury data for process areas to determine training

costs and safety impacts for our processes.
6. What is the electrical load and requirements for existing electrical

equipment?
7. How much do we electricity to we generate?
8. Trend analysis to determine actual Mean time between failure analysis will
make PMs more cost effective.
9. Trend analysis to predict when major problems will occur. The Board has cut
staff, when will the deferred maintenance cost more than the cost savings?

areas:
Eliminate ineffectual manual research of process flow information to

determine maintenance requirements.
Produce maintenance cost savings by moving from a calendar interval
based maintenance to an actual usage/need based maintenance.
o Save the costs of unneeded maintenance on equipment with
unexpected low usage.
o Save the investment in fixed assets by triggering timely preventative
maintenance on unexpected high usage equipment.
o Validate or tune optimal calendar/interval based maintenance where
actual usage is very close to expected planned usage.
Save near-end-of-life high maintenance costs by projecting optimal fixed
asset replacement schedules. An average of 60% of the maintenance costs
are incurred in the last 2 years. A majority of these cost will be saved by
using the DW to determine the replacement schedules.
Save cost by negotiating more cost effective capital expenditure contracts
by scheduling predictive replacement. Provide engineering and contractors
lead time to more cost effectively planning capital replacements, retrofits and
capital upgrades.
Save the costs of down-time, emergency fixes and potential permit violations
due to equipment malfunctions.
Enable maintenance and scheduling management to work smarter in
utilizing the labor, materials and equipment. Long term, the business can
only be effectively supported by an optimal down-sized labor force if the
labor is tasked with a right-sized work load. Empowered by the DW analysis
and planning tools, management can reduce labor, materials and equipment
costs while confidently meeting maintenance requirements.
1. Fixed assets and program expenditures are based on the cost analysis
research. The total odor management cost analysis will lead to savings in
labor, chemicals, instruments and materials.
2. Predictive maintenance analysis will enable identification of optimal

replacement time prior to the final 2 years where a majority of the
maintenance costs occur. See graphs below.
3. Enable management to schedule more effective maintenance providing the

following benefits:
Optimal workforce.
Optimal inventory usage.

Optimal inventory replenishment.
Optimal equipment utilization.
4. Predictive maintenance will enable lowering Inventory on hand.
5. Assigning of labor more efficiently while extending useful life of asset.
6. Mean time between failure analysis will make PMs more cost effective.
7. If the wrong type of meter malfunctions (such as process feeding or

chemical meter) then there is an additional cost during the time it is out of
service.
8. If out of scope trends are captured, larger fixed assets could be saved by
fixing problems before it is too late.
9. 60% of maintenance occurs in last 2 years of use for fixed assets. The
sample graphs that we drew last week. Currently dont track costs at
equipment level and dont track expected life of equipment.
10. Currently, O&M notices that maintenance has increased and then start a 3
year process to acquire the new equipment. End up keeping equipment 4 to
5 years too long.
11. It is estimated that [Project Customer/Sponsor] has about $1 billion in capital

equipment that is maintained. Predictive maintenance based on actual
usage together with predictive replacement will extend the useful life
possibly save 10% or more in capital investments.
12. A DW in this arena will probably solidify standards. Example: For a 100hp
pump, we need the following PLC TAG drops; vibration, temperature,
runtime. This could help feed Engineering planning.
13. Determining actual Mean Time Between Failures and expected Life will
provide knowledge for more cost effective negotiations with vendors.
14. Predictive maintenance will reduce stop and wait time and thus save on
labor costs.
15. Prevent specific types of injuries and the associated costs.
16. By doing electricity load shedding during peak and critical situations and with
proper maintenance, could possibly realize 10 25% savings of total energy
costs.
17. Trend analysis to predict when major problems will occur. The Board has cut
staff, when will the deferred maintenance cost more than the cost savings?
18. Increased efficiencies in cost per million gallons is good press / good politics
for the board.
19. Increased efficiencies will also compare well on the benchmark studies thus
reducing the chances of privatization.
20. The analysis will allow [Project Customer/Sponsor] to weed out equipment
models which are under performing and either negotiate more appropriate
pricing or replace them with higher quality models.
21. Maintenance & Scheduling IT Priorities (Scheduling and planning module for
CMMS. Operational changes.
Warehouse integration. Operational changes.

Cost at equipment level, decision support. Business and strategic
benefits. Major dollars here.
o Business practices first, then the better tools (like hand held devices).
o Restructure business practices, change work processes, then supply
the best tools.
The following are the estimated return on investment scenarios expressed by

the business users during the focus sessions and related interviews. It is
understood that these net benefits will be very difficult to measure because the
both pre and post DW measurements are elusive. After implementing the DW,
many of the benefits will be noticeable and quantifiable. A few of these ROIs
are applicable to more than one business case.
Hard ROI Estimates from the Business Users
Labor Cost Savings

o Estimated 30% of one FTE would be saved by replacing manual Q/A
phone calls and discussions with DW queries available to the
organization.
o Estimated 30% FTE would be saved across 6 people, 5% of their time
each, who manually collect and manipulate data coming from the lab,
operations and source control for use in making Maintenance &
Scheduling decisions.
o Estimated 100% FTE per year is wasted due to stop and wait time due
to fixed asset, inventory, equipment or skills not available to start a
maintenance job.
=>Total 1.6 FTEs
Estimated $1,000,000,000 in fixed assets equipment are maintained by

[Project Customer/Sponsor]. If the average useful life is extend by 1%, that
results in $10,000,000 in savings over the average useful life period (8 10
years), thus possibly saving $1,000,000 per year in fixed asset
expenditures.
Predictive maintenance supported by the DW will provide sufficient lead time
for the procurement process so inventory levels will be much lower. Only
high demand or high risk materials would need to have higher inventory
levels.
o Estimate 35% less inventory on hand over $4,500,000 worth of inventory
at 7% interest would result in a savings of $110,205.
The following pages illustrate persuasive ROI scenarios. Unfortunately, prior to
implementing the DW, these specific opportunities for cost savings can not be
identified or measured. It is intuitive to the maintenance and scheduling experts
that these cost savings opportunities definitely exist to some degree. The
business users gut feeling is that these exist to a significant degree to justify
the DW.
Usage Driven
Maintenance
Pump 3 is an
exposed fixed
Wasted labor, asset
materials, equipment
on pump 1
Runtime Hours
Pump 3
Recommended
Maintenance
Point
Pump 2
Pump 1
1 2 3 4
Current
Maintenance
Intervals
The graph is an example of two distinct Hard ROI examples.
Save the calendar maintenance costs on equipment that has met the
calendar time criteria but has not actually been utilized to the point where it
requires maintenance.
Save the exposed fixed asset from breakdown due to over-utilization
between interval maintenance.
The DW will enable management to determine the proper usage based
predictive maintenance by analyzing both maintenance trends and operations
actual process meters tracking usage.
Fixed Asset
Cost Analysis
$
New Acquisitio n
Price
Proje cted
Replacement
Deprecia ted Valu e

of Current Asset
Accumula ted
Main tenance
0 1 2 3 4
Years
The DW will combine FIS capital asset costs and current depreciated value with
actual total maintenance costs thus enabling management to determine when
continued maintenance is no longer cost effective. Currently there are large
maintenance efforts where this analysis is not possible. The HARD ROI clearly
exists, but cant be measured prior to having the DW in place.
Predictive
Replacement
Ideal Replacement
$ Point
Expected Life of
Fixed Asset
0 5 10 15 20
O&M Notices
Years Maintenance Trend
Current
Replacement Point
The DW analysis will enable management to forecast the end of useful life
when the cost to replace is less than the cost of near end of life high
maintenance costs. This also enable the business to appropriately time the 2-5
year procurement process for fixed assets. The Hard ROI would be the entire
cost difference between the ideal replacement point and the current
replacement point.
Strategic Impact
Optimizing maintenance and fixed assets supports [Project

Customer/Sponsor]s strategy to become a more efficient organization. The
results will be measured through the benchmarking initiative and the cost per
million gallons of waste water metric. The return on investment scenarios
support the organizations goals to reduce both operational expenses (labor,
materials and equipment) and reduce capital expenditures by extending the life
of fixed assets and efficiently scheduling the replacement of fixed assets. DW
also supports the notion of working smarter by providing the knowledge base
to manage an efficient and right-sized organization.
The success of [Project Customer/Sponsor] in effectively meeting the needs of

the EPA regulations and the county community and industries depends on the
processing running reliably. The DW will support the vital role of maintenance
and scheduling in providing un-interrupted waste water treatment processing.


Integrate computer system to link users and gain on-line access to data-
bases.
Negotiate changes in monitoring requirements in the ocean discharge permit

to add flexibility, target data needs, and improve cost effectiveness.
performance.
Success / Do able
User Sponsorship
Data Available and accessible, of sufficient quality, appropriate level of
detail, and can be transformed to a view consistent with the business
questions
Data Warehouse Process: methodology, project management, and skilled
labor.
data does exist, is sufficiently accurate and available at the right level of detail to
support a DW. The DW transforms this information into a subject oriented
decision support system. While the evidence indicates that these success
factors are met, they remain critical success factors to manage during the
process.
It appears that the data sets coming directly from CMMS and CRISP will be
accurate and can be directly transformed into a DW.
managed, and an appropriate DW process is executed, this DW implementation
will be successful.
Demand

Customer/Sponsor] organizations and business needs:
Operations Maintenance & Scheduling management to enable optimal

preventative maintenance and maximize the useful life of fixed assets.
Operations will benefit from having equipment with increased up-time and
increased Mean Time Between Failure which results from decreased down-
time and emergency reactive situations.
Below are the SIP Investment Strategies and Recommendations which are
directly related to this DW business case or are partially met by the DW
business case.
Investment Strategy: 1, 2, 4, 7a, 9, 10a, 10b, 14, 25
1. Document Database The engineering documents could be cross

referenced from the DW.
2. Manage Data Application This strategy is partially met by the DW

because the data subject areas that the DW includes (CMMS, CRISP) the
manage data requirements are met, such as providing historical data at a
finer level of detail to evaluate trends and generating reports.
4. Schedule Database This strategy would be partially fed by the DW

providing the trend analysis to create predictive maintenance schedules.
7a. Project Database - This strategy would be partially fed by the DW

providing the trend analysis to create predictive maintenance projects.
9. Plant Process Database The DW partially covers this strategy by

providing the integrated data from CMMS, CRISP and FIS for analysis and
reporting, yet the DW does not cover the operational enhancements
related to this strategy.
10a. Treat Wastewater Application The DW covers a part of this strategy by

providing integrated analysis of CMMS, CRISP and FIS data.
10b. Location/Facility/Process Area/ Building Database The relevant

documents could be cross referenced from the DW.
14. Fixed Asset/Equipment Database The DW partially covers this strategy

by providing trend and costing analysis to support appropriate
maintenance and predictive replacement of fixed assets.
25. Design and Construct Facilities Application The facilities documents

could be cross referenced from the DW.
Recommendation: 4, 8, 11, 15, 20
These are the SIP Recommendations which are at least partially addressed by
the DW or would be integral to the DW. Several Recommendations were not
4. Data Warehouse The candidate DW is a proposed phase to

this recommendation.
8. Scheduling System The DW is a necessary analysis and

decision support tool for management to use in producing the
maintenance schedules.
11. Project Management System This recommendation would be

partially fed by the DW providing the trend analysis to create
predictive maintenance projects.
15. Integrating Plant, Maintenance, Lab and Financial Data The

DW covers a majority of this recommendation by providing the
integrated data store for these subject areas.
20. Fixed Asset/Equipment Database The DW could cover this

recommendation by providing the repository for fixed asset data
(not images). The first iteration is targeted at the fixed asset
costing analysis not, but the general attributing of fixed
assets/equipment could be a straight forward added benefit.
Alignment with Existing IT Projects / Applications / Architecture
The DW will support (and not overlap) with the planned CMMS and CRISP
upgrades and implementations.
The CMMS system will be enhanced with a scheduling module. This module
requires input and optimal maintenance plan. The optimal plan will be derived
from analysis utilizing the DW tend analysis and the managements expert
knowledge.
The SCADA/CRISP system will continually grow in the number of PLCs in place
to monitor and control the treatment process. Currently there are sufficient
PLCs in place to feed the DW operational metrics which will enable analysis and
optimization of maintenance schedules. As new PLCs are deployed, the DW
will expand to include analysis over a greater number of capital assets and a
finer level of detail.

benefits.
the project to be successful they will need to augment their staff with DW
skilled resources. They will also need to execute the process using proven
methodology and manage the process with sound project management

techniques.
the guidelines of the organizations architecture. The DW architecture
designs.
Specific Critical Success Factors for the Maintenance & Scheduling DW
Some critical meter readings are currently being obtained and tracked
manually. As part of the DW process, these manual tasks must be included
as part of the over-all data quality method. The data must be obtained,
entered on a timely basis, and validated.
Training costs on usage of DW DSS tools. This can also be a benefit since
users can learn one reporting environment instead of multiple systems,
multiple reports/spreadsheets.
lifecycle.
Specific Inhibitors for the Maintenance & Scheduling DW

CMMS enhancements will continue, such as the scheduling module. The
upgrades must be coordinated with the DW process by conforming with the
DW architecture.
Manual Logs are included as a data source for the DW. Included are
manual meter readings for meters that have not or can not be integrated
with CRISP. Some of this data is consolidated to the Monthly Summary of
Operations (MSO) reports which may be used as the direct source for the
DW. Some manual processes will be necessary to ensure consistency of the
format, quality of the content, and possibly to manually trigger data loads
data is complete.
5.3.4. Assumptions

Electrical Instrumentation and Mechanical Maintenance
O&M
Department Mechanical Engineers
Monthly
Report
870 Central
11 820 Administration Generation
9
10
5 Preventative
Maintenance
Work Order
Corrective
CMMS Action
Support Work Order
Manage
Contractor
CMMS
Work Order
1
Construction
Work Order
Signed off
Work Order
Department Construction
8
Heads Job Request
3
4 6
Electrical
Clearing Mechanical
Instrumentation
House
7
Small
Projects
Request Manage Preventative
Contractor Maintenance 2
Work Order Work Order
Corrective
Construction
Action
Work Order
Work Order
Field Staff
Contractor Support
12
Facilities
Workstation
Equip
Plant
Processes:
1. CMMS Issues Work Orders directly to the Electrical and Mechanical

departments. These include:
Preventative Maintenance Work Orders generated based on interval

scheduling.
Corrective Action Work Orders generated in response to a manual
request from the plants or facilities.
Manage Contractor Work Orders generated for projects that require
the management of a subcontracted maintenance vendor.
Construction Work Orders generated for approved capital
construction projects.
2. All work orders are sent to the field staff via the respected maintenance
area.
3. Once the field staff completes the tasks for a work order, they sign off on the
work done and return it to the CMMS Support group for logging and
rescheduling.
4. If the field staff determine that other work needs to be performed in the field,
they can generate a manual work order directly in CMMS.
5. The CMMS Support group updates CMMS. This includes:
Logging work signed off on the incoming work orders from the field,
Rescheduling preventative maintenance,
Entering time, materials, and equipment usage.
Entering new equipment information and updating existing records.
6. Users of facilities and equipment can log a service request directly into
CMMS. A Corrective Action Work Order will be produced and sent to
Electrical or Mechanical for review. The Work Order will either be sent to the
field for corrective action or returned to the requester if deemed
unnecessary.
7. The Maintenance Department checks requested work to determine if it is a

capital project. This is necessary for all projects over $1000.00 that are not
considered preventative or corrective maintenance. Once a capital project is
identified, a Small Projects Request Form goes to a committee run by
Engineering for approval. This committee is called the Clearing House. If
approved, a completed and approved job plan is returned to Maintenance.
At this point Maintenance can enter it directly into CMMS as planned work.
8. Once a Construction Plan has been approved, the planners in Maintenance

can enter it directly into CMMS as planned work.
9. Central Generation and Mechanical Engineers enter runtime information

about their equipment into CMMS. This allows CMMS to issue preventative
maintenance work orders on a runtime versus interval basis.
10. The CMMS Support group produces the Monthly Summary of Maintenance
Activity for the Administrative O&M office. It includes a written information
about activities in Mechanical, Electrical Instrumentation, Support, and
Construction for the month.
11. The 820 Administration group publishes the O&M Department Monthly
Report which includes the Monthly Summary of Maintenance Activity as well
as several other reports.
12. Contractors are sometimes used for specialized maintenance such as meter
calibration. In these cases the contractors are managed by field staff under
a Manage Contractor Work Order.
CMMS Schedules and actual resources applied to maintenance

activities
CRISP Actual fixed asset usage (run times, flows, meter readings)
FIS Costs of resources (materials, personnel, equipment), fixed

assets and depreciation.
5.4. Other Notable Business Cases
The following business cases were discovered during the Business Case Deliverable
project. These are worthy of further analysis for possible future DW iteration projects.
As shown in the Data Value Matrix, these business cases share the source data needs
with other business cases. They provide additional business justifications to the DW
population projects because they can directly benefit from the accessibility of that
source data in the DW. The clearest example is Source Control analysis. Although
Source Control may not be the first DW and the entire data requirements for Source
Control may not be included in the first iteration of the DW, Source Control will still
benefit from any DW which includes lab samples or process flows.
5.4.1. Source Control
The source control department was a strong contender to be included as one of

the top three business cases to refine. The key reason for not including it as a
candidate for the 1st DW iteration is because the department already is
addressing its specific needs for data integration and reporting. Although they
have a large backlog of enhancements to their information technology, they are
significantly more advanced in their information management than the other
business case areas.
Once the Treat Waste Water business case is implemented in the DW, the
Source Control area will be able to utilize that DW as a data and reporting
source for a significant portion of their analysis and reporting requirements.
5.4.2. Power Generation
It was estimated that an additional 3 5 Megawatts of electricity could be sold if

the power generation capacity and usage could be optimally balanced. This is a
direct source of revenue that is waiting to be exploited. The approximate
electricity value (based on 3 cents per Kilowatt hour ) is $30 per megawatt hour
* 5 Megawatts * 24 hours = $3,600 per day = $ 1,296,000 per year.
The revenue could be realized by implementing improved power management.

To enable the efficient management of the resource would require additional
analysis of normal and peek usage needs, capacity, emergency scenarios, and
the deregulated electricity market. To enable that analysis requires additional
electricity monitoring meters feeding CRISP and then feeding a DW for analysis.
Currently, sufficient meters do not exist.
5.4.3. Odor Control
Total cost analysis for odor control would include the data requirements from
Treat Waste Water (lab samples, process metrics, chemical usage and
chemical costs) and some data from the Maintenance & Scheduling business
case specific to costs of ownership for odor facilities. Once the two business
cases (Treat Waste Water and Maintenance & scheduling) are completed, a
reasonably small DW iteration would cover the order control cost analysis
business case. The DW iteration would include odor complaint information,
additional chemical billing information and development of decision support
reporting specific to odor analysis.
6. Prototype Results
6.1. Prototype Overview
The selected prototype subject is waste water treatment. The selection was made by
first identifying the most likely subjects for data warehousing based on the SIP and then
examining the existing source systems to support those subject areas. The
requirement of the prototype was that it must be achievable in a short time frame, and
demonstrate proof of concept and purpose for data warehousing.
The waste water treatment prototype includes data about flows and concentrations in
different areas of the Plant 1 operation. This data is combined with chemical usage
and chemical costs for those process areas. In addition, the electrical power
production and use is examined along with gases used to produce power. This data
becomes information stored in an integrated database referenced against time, that is,
a data warehouse of Plant 1 Operations data by the day.
For purposes of the prototype, real data was used for October 1997 through December
1997. The prototype demonstrated no data extraction tools. This means that the data
was copied from [Project Customer/Sponsor]s existing systems directly to external
magnetic media and manually converted into the data warehouse database. A
production data warehouse would have the interaction with source systems automated
using extraction tools.
The prototype uses a commercial data warehouse database engine developed by Red
Brick Systems, Inc. It is the database software that contains the structure, or the
schema, of the data warehouse and allows for the high performance interrogation of
the data.
The prototype uses a user interface and query generation tool called DSS by
MicroStrategy, Inc. It is this tool that provides the user with the capability for ad hoc
querying, graphing and charting of the data in the data warehouse. The ability to
interrogate the data enables the user to use the data warehouse for decision support.
DSS is the acronym for Decision Support Solutions.
The prototype uses data from several sources:
The Lab: Data about total suspended solids and volatile suspended solids for
several Plant 1 process areas are used. The source was the Daily Lab Sheets
(Excel) and the Digester Solids Sheets (Excel). All of this data will be incorporated
in the new LIMS system in the Spring of 1998.
CRISP: Data about Plant 1 influent flows and Activated Sludge influent flows are
taken for the CRISP system. In the future, more log information will be within the
scope of the CRISP system and it will become the main source for log data.
FIS: The financial system tracks information about inventory purchases. Chemical
prices are extracted from this system. Currently, chemicals are not tracked as
inventory items and are purchased under blanket vendor agreements. Price per
volume and vendor payment by chemical information exists in FIS.
Operations Log Data: Some log data is not automated in any system, however it is
critical for Plant analysis. This data does get entered throughout the month into
different spreadsheets. The prototype uses data from the MSO spreadsheet.
Treat Waste Water Prototype
Monitor Chemical Usage.

Usage/ Flow over time.
Chemical concentrations/ lab results over time.
Cost/ time.
Reporting.
LAB
Excel spreadsheets available on network.
- daily results in plant.
- will reside in LIMS by
March 98.
No Common Key, Can be reconciled
- day
- process in plant
PLANT
available on network.
Excel spreadsheets
- actual chemicals fed
- log data (flows, volumes)
No Common Key, Can be reconciled

- day
- process in plant
SCADA
CRISP available on VMS.
- real-tim e data No Common Key,
- volumes Can be reconciled
- flows - chemical
FIS
J.D. Edwards
Data Warehouse - cost of chemicals fed
Prototype
available on AS400.
day 4
day 3
day 2
day 1
Lab
CRISP Chemical
Operations Results
Usage
Log Data Chemical
Costs
6.2. Business Benefits
The benefits of a data warehouse demonstrated by the prototype can be categorized in

four basic areas:
Subject Oriented: The information is oriented around the single subject of Plant 1
Performance. This is a relevant business subject to CSDOC, but too vague and broad
to be addressed by a single operational system.
For example, it is unlikely that the CRISP system would be set up to calculate the
efficiency of the Trickle Filters because this calculation requires log data and lab data.
The prototype is able to provide this information because it contains broad data
elements related to the subject and has a user tool that allows flexibility in data
interrogation.
Integrated: The information in the prototype is all in one database and is related to
common themes, specifically time (by day) and process area within the plant. In a
production environment this is accomplished by using extraction tools against the
source systems then cleansing and loading the data into the data warehouse. The
ability to integrate data from multiple sources allows the data warehouse to span
functional boundaries in the organizations structure to answer broader business
questions.
For example, by integrating lab and log data, the efficiency of a chemical can be
identified. By integrating financial information, a cost benefit of the chemical can be
calculated. Even if the source systems could communicate, it is unlikely that this
question could be answered without a data warehouse.
Time Dimension: The data in the prototype is loaded in layers, each representing a
day of information. With this structure, all the questions that are asked can be asked
for each day. More importantly, they can be asked for all days, creating a trend
analysis. This is critical because a question about Plant 1 performance may have little
meaning unless tracked over time.
For example, if the efficiency of the Primary Process was calculated and shown against
chemicals used, it may not result in a major gain in knowledge. If the question were to
be tracked over time, changes in chemical efficiencies or increases in influent
constituents could be isolated.
Non Volatile: The prototype tracks historical information about Plant 1. It does not
change. This is a benefit in that previous findings about performance can always be
reproduced. Even if a data element is discovered to be in error or historically measured
incorrectly, the fact that it remains in history can become valuable trend information as
time moves on.
For example, if it is determined that a process flow could be measured more accurately
in a different manor and the new measurements are consistently lower, then the
efficiency rating of the process would be consistently altered. This would be valuable
trend information that could be used to alter the process.
6.3. Data Elements
Dewatering Odor Chemical Prices

Cationic Polymer Sludge Produced Hydrogen Peroxide Sodium Hydroxide Cationic Polymer Sodium Hypo-
used in Belt Press used in preliminary (Caustic Soda) price per lbs. chlorite (bleach)
dewatering flow used in preliminary price per gallon
flow
Source: MSO Source: MSO Source: MSO Source: MSO Source: FIS Source: FIS
Sludge Cake Hydrogen Peroxide Sodium Hydroxide Sodium Hydroxide Hydrogen Peroxide
Concentration used in scrubbers (Caustic Soda) (Caustic Soda) price per gallon
used in scrubbers price per dry ton
Source: MSO Source: MSO Source: MSO Source: FIS Source: FIS
Power Ferric Chloride Anionic Polymer

(Ferric) price per active lbs.
Power Generated Power Used price per dry ton
by CenGen by Plant 1
Source: FIS Source: FIS
Source: MSO Source: MSO
Activated Sludge
Power Exported Digester Gas Used Natural Gas Used
by CenGen by CenGen by CenGen Activated Sludge A.S. Flow (OUT) DAF Sludge (OUT)
Flow (IN) Flow(IN) -
DAF Sludge(OUT) -
Source: MSO Source: MSO Source: MSO 80%(Plant Water)
Source: CRISP Source: Derived Source: MSO
Primary
A.S. TSS A.S. TSS
Total Primary Flow Total Primary Flow Primary Sludge Concentration (IN) Concentration
(IN) (OUT) (OUT) (OUT)
Flow(IN) - Sludge
(Out) Source: Lab Sheet Source: Lab Sheet
Source: CRISP Source: Derived Source: MSO
A.S. VSS A.S. VSS

Primary TSS Primary TSS Ferric Chloride Concentration (IN) Concentration
Concentration (IN) Concentration (Ferric) (OUT)
(OUT) used in primary
processing Source: Lab Sheet Source: Lab Sheet
Source: Lab Sheet Source: Lab Sheet Source: MSO
Plant Water (OUT)
Cationic Polymer Sodium Hypo-
Primary VSS Primary VSS Anionic Polymer used in Activated chlorite (bleach)
Concentration (IN) Concentration used in primary Sludge D.A.F.s used in Activated
(OUT) processing Sludge Source: MSO
Source: MSO Source: MSO
Source: Lab Sheet Source: Lab Sheet Source: MSO
Sodium Hypo-
chlorite (bleach)
Trickle Filter Digester used in Plant Water
Trickle Filter Flow Trickle Filter TSS Trickle Filters VSS Digester Average Digester Average Source: MSO
(IN) Concentration (IN) Concentration (IN) VS Concentration VS Concentration
30 MGD (IN) (OUT) Plant Water
Source: Constant Source: Lab Sheet Source: Lab Sheet Source: Solids Sheet Source: Solids Sheet
Trickle Filter Flow Trickle Filter TSS Trickle Filter VSS Sulfide (H 2 S) Digester Gas Ferric Chloride
(OUT) Concentration Concentration produced in Produced (Ferric)
30 MGD (OUT) (OUT) digesters by the digesters used in digesters
Source: Constant Source: Lab Sheet Source: Lab Sheet Source: MSO Source: MSO Source: MSO
7. Attachment A Source Data Descriptions

7.1. CMMS
7.1.1. System Overview
The Computerized Maintenance Management System is a software package

designed and supported by RJN.
It is an Oracle based system that currently uses a DOS user interface. It will be
upgraded to a Windows version in the spring of 1998. This upgrade includes
both user interface and database changes. CMMS continues to be supported
and upgraded by the source vendor at [Project Customer/Sponsor].
7.1.2. Functionality
CMMS is a maintenance work management system. It is used to schedule and

track the maintenance of assets at [Project Customer/Sponsor]. It is capable of
generating preventative maintenance work orders on an interval and runtime
basis as well as generating various types of work orders by request. Further,
CMMS is used to track the completed work history for maintained assets in the
plants.
Currently, the runtime (and out-of-range trigger) preventative maintenance

features of CMMS are largely unused. This is because it receives no source
data for this function. This means that preventative maintenance work orders
are generated based on a time interval basis. The Central Generation engines
are the exception as runtime data is entered into CMMS for these assets.
CMMS has a warehouse module that is designed to manage parts inventory for
maintenance purposes. This is not currently used because the financial and
accounting system controls the warehouse at [Project Customer/Sponsor].
There are plans to integrate the financial warehouse package and the CMMS
warehouse module to allow CMMS to do maintenance scheduling in more detail.
RJN is now developing the CMMS Scheduling and Planning module using input
from [Project Customer/Sponsor].
7.2. Source Control
The Source Control system is an application designed and supported by

[Project Customer/Sponsor] Information Technology. It is an Oracle based
application that continues to be upgraded based on user requirements.
The Source Control Department uses the Source Control System primarily to
track their permit holders, their permits, and the ongoing analytical information
about the permit holders effluent performance.
Each permit and permit holder is contained in the Source Control System.
Permit holder information includes profile type data as well as the monitoring
method that is being used. Permit information includes each constituent being
regulated by the permit and the constituent limits. A permit may contain from 1 to
40 different constituents but average about 7.
Based on the permit information, the Source Control System is used to develop
workload books for the inspectors that outline the monitoring tasks targeted for
the quarter.
Sampling is the main method for monitoring permit holders. This is

accomplished by taking physical samples and sending them to the [Project
Customer/Sponsor] Lab, or by having the permit holder have samples analyzed
and having the results sent in. In both cases the results are stored in the
Source Control System for verifying compliance and trend analysis. The Source
Control System is integrated with the lab system in that results can be
transferred and reconciled electronically using a unique identifier (called the
Greensheet Number).
7.3. LIMS
The Laboratory Information Management System is a purchased package

designed and supported by LabWare. It is an Oracle based package that is in
the process of being deployed. It replaces many independent PC based
databases (Excel and Dbase).
LIMS is used to track samples that come into the lab, the tests that are
requested for the samples, and the results of those tests. In fact, that describes
the three main tables in LIMS and their one to many relationships.
Samples come from several different sources:
Source Control Influent Samples.

In Process Plant Samples.
Surf Zone Beach Samples.
ECM Ocean Samples.
All samples are stored in a consistent way in the same tables. They are
identified with a unique code that has a constructed meaning. There is a
secondary identifier that allows for the integration with the Source Control
System.
Results from plant process samples are currently faxed to Operations every day.
This will be an automated process once LIMS is online with this data.
7.4. FIS
The Financial Information System is a package purchased and supported by

name.
FIS is an IBM AS/400 based package that is in the process of being deployed.
[Project Customer/Sponsor]s FIS includes the modules:
Payroll.
Inventory.
Purchasing.
Sales Order.
Accounts Receivable.
Accounts Payable.
General Ledger.
Fixed Assets.
The FIS is used to manage the functions of the Accounting Department

including all the accounting, inventory warehousing and procurement, as well as
the payroll functions for Human Resources.
7.5. CRISP
The CRISP (Copland Roland Interpreted Sequential Processor) system is a

purchased package designed by Square D and supported by CRISP Automation
Systems.
It is an ongoing project to install more data points and control points in the
plants. The CRISP software runs on separate Digital VAX computers in each of
the plants. The computers are networked and both systems can be accessed
from a single DEC workstation. The VAXs are also connected to the NT
network allowing files to be extracted and transferred via the File Transfer
Protocol (FTP).
CRISP is the Supervisor Control and Data Acquisition (SCADA) system. It is

designed to gather real-time data directly from the plant processes and allow the
automation and control of plant devices.
This is accomplished by connecting to the Programmable Logic Controllers

(PLCs) in the plants. The PLCs are logic devices that control process
machinery (such as valves and pumps) and monitor metering devices (such as
flow meters, thermometers, runtime clocks, and pressure sensors). The CRISP
system is new (i.e. 2-3 years old) and not fully implemented. The monitoring
capabilities are the first functions to be implemented.
CRISP is mainly used in the Control Center as a monitoring system for

Operations. Maintenance plans to use analogue and runtime data to improve
their method of initiating preventative maintenance. This will require integration
of the CMMS and CRISP systems. CRISP is supported by Information
Technology.
7.6. Operations
CRISP is an Operations system, however it does not cover the Operations

department as broadly as the other systems cover their respective areas. As a
result, there is a tremendous amount of data relevant to [Project
Customer/Sponsor]s operation that is mostly handled manually. This is
significant enough to be considered a source system for a data warehouse or
decision support system.
Operations staff members in the plants take manual meter readings and record
events in operators logs on a regular basis. Much of this information is real-
time data that will eventually be recorded by the SCADA (CRISP) System.
Currently, if it is relevant to the status or operation of the plant, its recorded in a
spreadsheet. The main depositories are the Night Information (Process
Information), the Monthly Summary of Operations (MSO), and the Chemical
Inventory.
These spreadsheets remain mostly up to date throughout the month and are the
source for operational data throughout [Project Customer/Sponsor]. The MSO
marries operation log data with laboratory data, making it one of the few
integrated reports concerning plant operations. It is integrated manually by
typing (or retyping) data directly into the spreadsheet.

(Project Name) Business Case: Yellow Highlighting Text To Be Replaced Blue Highlighting Example Text

Uploaded by

Copyright:

Available Formats

(Project Name) Business Case: Yellow Highlighting Text To Be Replaced Blue Highlighting Example Text

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

(Project Name) Business Case: Yellow Highlighting Text To Be Replaced Blue Highlighting Example Text

Uploaded by

Copyright:

Available Formats

[Project Name]

Yellow Highlighting = Text to be Replaced

Blue Highlighting = Example Text

Name Company ProjectRole

1.4. Intended Audience

This document is intended to be used by [Project Customer/Sponsor] and by [Project

[Project Customer/Sponsor] Strategic Information Plan (SIP) [Pub Date]

[Project Leader] Business Case Proposal [Pub Date]

In order for [Project Customer/Sponsor]s [Project Type] process to be fully successful it

Waste Water Treatment Analysis

Treatment Process Regulatory Requirements Analysis

Maintenance Scheduling Decision Support

[Project Customer/Sponsor]s mission is to protect the environment and public health by

Prior to the DW business case project, [Project Customer/Sponsor] completed the

2.1. Scope Statement

2.2. Business Case Summaries

2.2.1. Waste Water Treatment Analysis

Treating wastewater is the primary function of [Project Customer/Sponsor] and

Optimize the entire treatment process by closely tracking, trending and

Meeting Environmental Protection Agency (EPA) regulatory requirements is a

The Regulatory Requirements Analysis DW will support a majority of the EPA

The substantial business justifications include cost savings in the following

Allow cost effective support of regulatory reporting, internal or external

Enable management to work smarter with historical trend analysis

Maintenance and Scheduling is a cross-functional process vital to ensuring

The Maintenance Scheduling Decision Support DW will provide integrated

The substantial business justifications include cost savings in the following

Eliminate ineffectual manual research of process flow information to

The remaining recommended process steps for [Project Customer/Sponsor] in the

Develop a Data Warehouse Architecture that covers Data, Application,

Iteration# BusinessCase Source History Numberof

4. Cost Benefit Analysis

The figures presented below are considered by [Project Leader] to be representative

Total DW Cost of Ownership

Operational Support Costs

Legend: * All costs represented in thousands of dollars

Software Maintenance is based on the industry norm 15% of the purchase

Operational costs are based on industry norms.

Design and Implementation costs represent estimated costs for development

4.2. Business Case Value & Rankings

4.2.1. Business Case Rankings

Summary Version of Rankings including Value Estimates.

Hard ROI - Doller Based Measurable Business Benefits

Over-all Ranking 1st place=6 points, 2nd place=3, 3rd place=1 6 1 3

Full Version of Rankings including Value Estimates with detail metrics.

Improved ability to research for process improvements, efficiences, in-efficeinces 3 2 3

Hard ROI - Doller Based Measurable Business Benefits

Over-all Ranking 1st place=6 points, 2nd place=3, 3rd place=1 6 1 3

Value of Source Data Based on Business Questions

1. Treate Waste Water

Totals by Source System 37 28 9 19 18 2 7

4.3. Hard ROI Cost Benefit Analysis

These graphs illustrate the hard return on investment (ROI) analysis.

1. Enterprise Wide DW Return on Investment

Enterprise Wide DW Cumulative Costs vs.

Maint. Scheduling Value

10,000 Regulatory Requirements Value

All 3 Business Cases Cumulative Value

2,000 Regulatory Requirements