“Data management is a lot like the water business,” says Andrew Clarkson, Business Intelligence Lead at American Water. “Water always has a source, piping brings the water to various stops for cleansing, and tanks hold the finished water until it needs to be used. Data follows exactly the same process. You have a source for the data, networks function as the piping, applications process and filter the data, and the databases and warehouses act as the tanks.”
This analogy is being illustrated clearly as American Water transforms from a decentralized group of independent regional businesses to a more centralized organization with standardized business processes and enterprise-wide business intelligence (BI). As the first phase of this transformation, the business underwent an ERP implementation project, moving from various disparate IT systems to a single integrated software platform. The next phase in American Water’s data-driven transformation will be to integrate new customer information system (CIS) and enterprise asset management (EAM) systems with its ERP system and data warehouse. Part of this overall initiative involves a unique priority for the project team to communicate to users that they must take responsibility for data quality.
Low Flow Problems
Since its founding more than 125 years ago, American Water’s business has grown regionally and through acquisition. Today, American Water operates in more than 30 US states through fairly independent operations, which are subject to local state regulations as well as federal regulations. As a result, its business processes and IT landscape became very localized. Until recently, American Water ran its business using a variety of manual processes and non-integrated systems with a smaller central ERP system to house its financial data. And the company’s CIS system is still comprised of 15 different instances used by a central customer service center.
Gradually, the legacy IT environment became more difficult to manage. Because some business processes weren’t standardized or well documented and systems were not integrated, running any type of report beyond a single region was an extremely manual process. And the highly customized financial system was difficult to upgrade and provided limited functionality.
“We started falling behind on what was considered current ERP functionality,” says Clarkson. “We made acquisitions in a lot of areas, which added to the diversity of our IT landscape. Only 10% of our data could be shared across our legacy systems.”
American Water’s overly customized IT systems led to challenges in 2006, for example, when the company was preparing to hold an initial public offering (IPO) of its stock. Clarkson says that the amount of regulatory and compliance work leading up to the IPO exemplified how manual the processes had become. “The software system couldn’t handle the automated Sarbanes-Oxley (SOX) controls required for the IPO,” he says. “At the time of the IPO, roughly 80% of the SOX controls were manual. The business transformation project gave us the opportunity to automate many of the controls that were manual up until the SAP deployment.”
While SOX controls efforts helped standardize some processes, the company’s IT environment made it difficult to give the standardized processes any consistency on the back end. So as part of the business transformation program, American Water began to research its options for an ERP system that could support or even improve those processes.
Increasing Flow Capacity (Replacing the Pipes)
In 2009, the business issued a request for proposal to several ERP vendors and selected SAP as its provider. Clarkson attributes the functionality SAP provides and the tightness of its integration as key factors in the selection. American Water wanted to minimize the use of middleware and interfaces that connect the ERP, CIS, and EAM systems. Its entire business transformation was centered on driving more integration across the business — the ERP system had to be the backbone of that integration.
Filtering and Cleaning the Data
With the ERP blueprinting plan ready to begin, American Water turned its focus to data migration and data governance strategies. In the legacy environment, data resided in many different systems in various formats. So from the very beginning of the business transformation project, Andrew Twadelle, Vice President of Business Transformation, knew data migration would be an important step for the project’s success. Each regional business maintained some of its own data in different areas; rolling it all together was bound to create conflicts. For example, the materials master data had duplicate items because a material might be called one thing in the company’s New Jersey operation, and called something completely different in its Missouri business. Not only did those names need to be standardized for the migration, but those standardized names also had to be used by every business unit going forward or it would limit the BI benefits of the new system. This was a new, integrated view of data, and business users had to buy into it.
“We developed a data migration and governance strategy with user training, and the overall message we underscored was that business owns the data,” says Clarkson. “Historically, everyone thought of data as an IT responsibility. But we emphasized to them that the business needs to drive the rules and standards, and there are real implications if users don’t own the data strategy.”
“Historically, everyone thought of data as an IT responsibility. But we emphasized to them that the business needs to drive the rules and standards, and there are real implications if the users don’t own the data strategy.”
— Andrew Clarkson, Business Intelligence Lead, American Water
Data relevancy was one of the crucial areas that Clarkson emphasized to business leaders early in the data migration planning stage. When most business users hear “data migration,” they assume it means simply moving all of the old data onto the new system. But part of the project strategy was to question the business about the true relevancy of its data to find out what it really needed to migrate, and what data it could do without.
“The business user often don’t understand the magnitude of how much data we’ve already compiled across the company, so we worked with them to define what should be migrated over to the new system as is, what needed to be cleaned up before being migrated, and what could be left behind,” he says. “We didn’t want them to assume it was all coming over. In fact, we wanted them to make a case for everything they were bringing over.”
For example, with more than 70,000 vendors in the vendor master data, Clarkson pushed the business users to define what an active vendor was and to use that definition to decide what data to migrate. He also worked with the various functional groups to standardize how to present addresses in the data.
With such a complicated data landscape, American Water’s ERP project team understood it needed help developing and executing its data governance strategy. The team enlisted BackOffice Associates to provide that assistance. Clarkson explains that BackOffice’s experience in cleansing, collecting, and enriching data was very valuable in the project. The consultancy also provided the mantra that guided the data migration strategy: “Load Early, Load Often.” (See the sidebar to the right for more information about how BackOffice played a crucial role in American Water’s ERP implementation.)
“Once we figured out how much relevant data to bring over, we started a mock conversion process that included doing five mock conversions with SAP ERP and then two more dress rehearsals following those,” says Clarkson. “For us, that was eye-opening because we’d load data, find problems or realize something was missing, and then load it again. By the time we did the actual migration, the process was routine.”
Turning on the Tap
In August 2012, American Water went live with SAP ERP, including SAP ERP Financials, SAP ERP Human Capital Management, and SAP Supplier Relationship Management for supply chain management functionality. Data from those systems flows into SAP NetWeaver Business Warehouse to provide a single view of information in reports.
One goal of all the data migration and governance work, however, was to develop an enterprise-wide BI program that could provide a single view of the business. “Our vision from the start was to have an analytical system and a data warehouse that allowed us to pull data from our SAP ERP system at the beginning and later bring in other data sources to let us get business insight from whatever data we have,” says Clarkson. “So it was very important for us to think about what we needed on the back end of our data warehouse to deliver the reporting we wanted.”
American Water originally planned to have as many as 200 reports coming out of the system after all applications are deployed, but later cut that down to about 100 reports that would serve as the platform for future growth. Users are currently being trained on how to pull those reports and customize them based on other objects they have access to. Originally, most of the financial users were gravitating to Microsoft Excel-based reporting tools. “But as users see they can change views and merge queries, capabilities available in SAP BusinessObjects Web Intelligence, and other functionality, they are getting excited. They see the creativity this new reporting system allows for, and they understand the potential for it.”
Where It Flows from Here
In the near term, American Water is focused on keeping its SAP ERP environment stable and driving home the message that data must be clean to be effective. The company currently uses SAP Data Services and recently began working on implementing SAP Information Steward to ensure all the data it brings into the warehouse is useful.
The next steps in American Water’s data-driven transformation is to install SAP’s CIS and EAM systems and then integrate them into its data warehouse so more reporting in those areas can commence. That process is underway, and the plan is to use mock cutovers and focus on making sure the data is clean when it finally moves.
While an incredible amount of effort has gone into the data cleansing work at American Water, Clarkson easily justifies the value in it. “No one could ever build a water treatment plant so perfectly that the only time water needs to be monitored is at the end of the process,” he says. “There are meters and measurements to check water quality along the way, and data management is exactly the same. We have to ensure the quality of the data at every step to be confident that the final product is useful to our customers.”