HomeMy WebLinkAboutStaff Report 9748
CITY OF PALO ALTO OFFICE OF THE CITY AUDITOR
October 23, 2018
The Honorable City Council
Palo Alto, California
Policy and Services Committee Recommends the City Council Accept
the ERP Planning: Data Standardization Audit
In accordance with the Fiscal Year 2018 Annual Audit Work Plan, the Office of the City Auditor
has completed the ERP Planning: Data Standardization audit. The audit report presents one
finding and four recommendations. The Office of the City Auditor recommends that the Policy
and Services Committee review and recommend to the City Council acceptance of the ERP
Planning: Data Standardization audit.
Respectfully submitted,
Harriet Richardson
City Auditor
ATTACHMENTS:
• Attachment A: Data Standardization Audit (PDF)
Department Head: Harriet Richardson, City Auditor
Page 2
ERP Planning: Data Standardization
October 17, 2018
Office of the City Auditor
Harriet Richardson, City Auditor
Mimi Nguyen, Senior Performance Auditor
Jordan Christenson, Performance Auditor
Attachment A
Page intentionally left blank for double-sided printing
Attachment A
Office of the City Auditor ● 250 Hamilton Avenue, 7th Floor ● Palo Alto, CA 94301 ● 650.329.2667
Copies of the full report are available on the Office of the City Auditor website at:
http://www.cityofpaloalto.org/gov/depts/aud/reports/performance/default.asp
OFFICE OF THE CITY AUDITOR
EXECUTIVE SUMMARY
ERP Planning: Data Standardization
October 17, 2018
PURPOSE OF THE AUDIT
The purpose of this audit was to determine if the City currently has data standardization in place in
ERP master data and to give examples for types of standardization recommended prior to transferring
data to the new ERP system.
REPORT HIGHLIGHTS
Finding:
Implementing data
standardization will
increase data accuracy
and uniformity in the
future ERP system.
The City can benefit from formalizing data standardization. Within the City’s
current ERP system, SAP, we identified examples for improving data quality,
compatibility, and consistency. Implementing data standardization will offer
a structure for facilitating the input of data in a more accurate and uniform
manner. It will also improve data output and data analytics.
The five main benefits of data standardization:
• Improved data quality
• Increased data compatibility
• Improved consistency and efficiency of data collection
• Reduced data redundancy
• Improved data access.
Key Recommendations:
The City should review city data and implement data cleansing through
manual or automated methods for data standardization prior to transfer to
the new ERP system. The Information Technology Department should:
• Provide governance over data standardization, such as who is
responsible for data standardization, what data is subject to
standardization, what is the data standardization, when does
standardization change, etc.
• Work with Departments to review the data within SAP and determine
what will benefit most by standardizing data.
• Review other systems and implement data standardization, where
feasible and beneficial; especially in circumstances when the data feeds
into SAP.
• Require Departments to implement data standardization requirements
during data cleansing in the ERP transition.
Attachment A
Page intentionally left blank for double-sided printing
Attachment A
TABLE OF CONTENTS
Objective ................................................................................................................................................. 1
Background ............................................................................................................................................. 1
Scope ...................................................................................................................................................... 2
Methodology .......................................................................................................................................... 2
Finding:
Implementing data standardization will ensure increased data accuracy and uniformity in the
future ERP system .............................................................................................................................. 4
Recommendations ........................................................................................................................... 11
Appendix 1: City Manager’s Response ................................................................................................ 12
ABBREVIATIONS
DAMA Data Management Association
DMBOK Data Management Body of Knowledge
EPA Environmental Protection Agency
Attachment A
Page intentionally left blank for double-sided printing
Attachment A
ERP Planning: Data Standardization 1
INTRODUCTION
Objective The purpose of this audit was to determine if the city has data
procedures that govern standardization of master data in SAP and
to give examples for types of standardization that would be
beneficial when implementing the new ERP system.
Background In the ERP Planning: Data and System Security Governance audit,
we identified areas of concern for the lack of data governance in
the City. The audit states that successful business processes
require accurate, consistent, and complete master data.
Master data Master data is the source for the most accurate data on business
practices within the City and is used to inform transactional data. It
pertains to business entity information regarding employees,
customers, vendors, and the organization. Management provides
control over how master data is entered in a system to enable
consistent, shared, and contextual use across systems.
Data Standardization Data standardization is the model, examples, or rules to increase
reliability and effectiveness of common data elements. The two
main components to data standardization are implementing
policies and automated processes to reduce errors as data is being
entered in the database and making corrections to data in the
database.
Policies and procedures should define an organization’s data
standards, including controlling input structures within the
database, such as format, naming conventions, standard
abbreviations, and allowable ranges of master data values;
organizational policies, such as data cleansing standards and
procedures; or greater data governance structures. Once data is
entered into the database, automatic or manual tools can be used
to standardize data.
Data quality rules for master data should include elements of both
strategies. It is considered a best practice to control data entry on
the front end through policies and procedures rather than relying
primarily on downstream data cleansing tools.
City’s Commitment to Accurate
Data
The City instituted an open data web portal in 2012 to offer
residents and interested parties access to city data and proclaimed
in 2014 that it would institute “open data by default” to make city
Attachment A
2 ERP Planning: Data Standardization
functions more “transparent, participatory, and accountable.”
Adopting a new ERP system is a moment of opportunity for the
City to ensure the reliability and transparency of city data and to
allow for greater investment in data in the future.
Past Audits The City Auditor’s Office has encountered data standardization
and data integrity issues in several past audits, including:
• 2017 Continuous Monitoring Audit: Payments
• 2017 Accuracy of Water Meter Billing Audit
• 2015 Utility Meter Audit: Procurement, Inventory, and
Retirement
• 2013 Employee Health Benefits Administration Audit
Scope This audit gives guidance on data standardization policies and
consistency issues that should be addressed through data
cleansing prior to transferring data to the new ERP system. It
provides general examples of non-standardized data as currently
entered in SAP to provide context for the type of data
standardization concerns. Companion audits will explore the
accuracy and completeness of specific city data sets and give
recommendations to specific departments for improving the
reliability and integrity of those data sets.
It was beyond the scope of this audit to look at standardization of
data from sources other than SAP that may filter into the Open
Data platform. However, instituting organization-wide data
standardization and governance policies will allow for more
accurate outfacing data to the public through the Open Data
platform, which will increase the reliability and transparency of
data for city residents.
Methodology To accomplish our objective, we:
• Identified standardization issues in city master data sets in SAP,
which is the City’s current enterprise resource planning system.
• Identified any existing data policies in place (data dictionaries,
data input rules in SAP).
• Analyzed a sample of data sets for inconsistency and data
standardization issues.
• Displayed these examples based on the potential benefits that
Attachment A
ERP Planning: Data Standardization 3
would come from standardizing the data.
• Provided examples on how to improve data input processes and
data cleansing for more standardized data.
• Used the Environmental Protection Agency’s (EPA) data
standards, developed in partnership with the Environmental
Data Standards Council for information collection and exchange,
to identify potential benefits for data standardization.1
• Referenced and used as guidance the Data Management Body
of Knowledge (DMBOK), published in 2010 by the Data
Management Association (DAMA). We used this guidance to
define data standardization and to outline practices for
evaluating the extent to which the City’s data is standardized.
We selected data that was entered into SAP from January 1, 2016
through April 13, 2018. We chose this date range to concentrate
examples on recent rather than historical data while still having a
large enough range to demonstrate patterns in the data. The
examples we used represent typical issues in SAP that could
benefit from standardization, rather than specific high-risk areas.
These types of issues occur throughout SAP, as evidenced by the
number of past audits that have identified data standardization
and integrity issues. As such, no specific department or area is
intended to be singled out. Rather, the audit discusses the greater
issue with data standardization across City master data.
Compliance with government
auditing standards
We conducted this audit of ERP Planning: Data Standardization in
accordance with our FY 2018 Annual Audit Work Plan and
generally accepted government auditing standards. Those
standards require that we plan and perform the audit to obtain
sufficient, appropriate evidence to provide a reasonable basis for
our findings and conclusions based on our audit objectives. We
believe that the evidence obtained provides a reasonable basis for
our findings and conclusions based on our audit objectives.
We would like to thank management and staff in the Administrative Services Department
and the Information Technology Department for their time, cooperation, and assistance during
the audit process.
1 “Data Standards Briefing Paper,” Environmental Protection Agency, available at
https://www3.epa.gov/ttnchie1/conference/ei12/panel/kohn.pdf
Attachment A
4 ERP Planning: Data Standardization
Finding Implementing data standardization will ensure increased data
accuracy and uniformity in the future ERP system.
Summary The City can benefit from formalizing data standardization. Within
the City’s current ERP system, SAP, we identified examples for
improving data quality, compatibility, and consistency based on
our assessment of data in different working areas. Implementing
data standardization will offer a structure for facilitating the input
of data in a more accurate and uniform manner. It will also
improve data output and data analytics.
Benefits of Data Standardization The EPA Data Standards Briefing Paper explains five main benefits
of data standardization:
• Improved data quality
• Increased data compatibility
• Improved consistency and efficiency of data collection
• Reduced data redundancy
• Improved data access.
The sections below give examples of the City’s SAP master data
that is not standardized and explain benefits that would potentially
be gained by standardizing data.
Improved Data Quality Improving data quality would ensure that a common language is
established for data entered in SAP, which can facilitate an easier
and more accurate exchange of information. Exhibit 1 shows
examples of data quality issues in one type of SAP master data.
Exhibit 1: Inventory Master – Inconsistencies in Data Table
Attachment A
ERP Planning: Data Standardization 5
Exhibit 1 shows that the material description does not have a
consistent structure. Although all of the items include similar
information for lamps, there are discrepancies in the item details,
which could cause ordering errors if the description does not
include all needed information or potentially result in mistakes
when ordering parts and/or reordering items. For example, there
are:
• inconsistencies on what is included as lamp specifications
(watts, voltage, or both)
• inconsistencies in the technical description (e.g.,
inclusion/exclusion of the base size)
• inconsistencies in the use of “W,” ”Watts,” or neither to denote
wattage
• inconsistencies in the use of commas and/or periods to
separate the item type from its description
• spelling errors
In addition, some columns are not consistently filled out, such as
the manufacturer part number that is often left blank. Omitting
key information in the master data could cause ordering issues for
items that require, for example, a specific manufacturer or energy
rating to comply with city policy.
Although the description field has some structure to the input,
creating standardization rules would be more useful both for those
familiar and unfamiliar with any specific item:
• Having a consistent format would makes it easier to find a
specific item and help ensure that the correct item is
purchased.
• Including all key information in appropriate field would allow
searches on other details, such as the manufacturer part
number, rather than relying only on information entered into
the material description field.
• Exhibit 2 shows how stricter standardization rules would assist
in the consistency of the light bulb data for the Material
Description field.
Attachment A
6 ERP Planning: Data Standardization
Exhibit 2: Inventory Master – Current vs. Improved Description Example
The current data entry rule:
ITEM and TYPE and other information
An example standardization rule of:
ITEM, TYPE, WATTAGE AND SHAPE, BASE SIZE
These two changes, standardizing in data the material description
column and including manufacturer part numbers in the
manufacturer part number column, are examples of how to
increase the overall quality of the dataset. Information in these
columns could better direct inventory and purchasing staff
regarding what to buy.
Increased data compatibility Data standardization can improve the compatibility of data across
modules in SAP. This can be accomplished by establishing data
dictionaries or building policies in the system to require that all
data in having the same field names contain identical data
throughout the system. This can be accomplished by requiring a
single point of entry for any particular data field. Data that is
inconsistently entered across the system can create errors that
could lead to financial and business losses, as well as an inability to
provide accurate reporting for decision-making purposes. It can
also create a need for additional data cleansing if data used as the
primary field for migration into the new ERP system is selected
from an incorrect data source. It may not be possible to combine
data from nonmatching fields, but if done, can lead to general
incompatibility, redundancy, or other errors.
Attachment A
ERP Planning: Data Standardization 7
Two examples of data incompatibility are shown in Exhibit 3. First,
there are two fields in SAP that identify a Division, which specifies
a type of service provided (i.e., electric, gas, water, etc.). One field
is Division and the other is Division Category, both representing
the service type; however, they are inconsistent with each other.
Second, the two fields Account Determination (AD) and Type of
Premise specify the type of property serviced (i.e., residential,
commercial, industrial, etc.). Although both fields may be used
slightly differently, the numbering, sequencing, naming, and
detailed breakdown would likely benefit from standardization.
These two examples show nonmatching and incompatible data
that should be reviewed for structural inconsistency in the new
ERP system.
Exhibit 3: Utility Service and Property Type Inconsistency and Incompatibility Examples
• The division type (under Dv and
Division category) and description
(under Name and Short Description)
do not contain the same dropdown
list/choices.
• The naming, Electric and Electricity,
are inconsistent.
• The naming and use of Division
number 06 are different from each
other.
• There are gaps in the numbering
within each.
• The property type categories, Account
determination and Type of premise, do
not contain the same dropdown
choices.
• The naming descriptions and detail of
description are inconsistent.
• The numbering and sequencing are
incompatible.
Attachment A
8 ERP Planning: Data Standardization
Improved consistency and
efficiency of data collection
As stated in the data governance audit, the City does not currently
have data standardization policies. These policies can improve the
consistency of data by outlining the information that should be
included within a field and requiring a consistent format. Some
policies can be built into an ERP system through drop-down menus
that limit input to standardized choices and requiring that open-
text inputs be in a certain format. Doing this provides the
additional benefit of increasing efficiency by reducing the amount
of typing needed for data collection.
Examples of inconsistency issues that can be controlled by data
standardization policies include:
• Formatting (0.11 vs. 0.1, (XXX) XXX-XXXX vs. XXXXXXXXXX vs.
XXX.XXX.XXXX)
• Inputs (Street vs. St. vs. st)
• Type (12 vs. twelve)
In addition to formatting consistency, standardization policies can
require input of fields that are deemed important to be included in
the database, thereby reducing the number of blank or missing
fields. This type of policy can also be built into an ERP system by
not allowing the person entering the data to move on to the next
screen or action unless certain data has been entered.
City data currently has both omissions and inconsistent formatting,
which demonstrate the effect of not having strong data
standardization policies. Exhibit 4 shows an example of City vendor
data that has inconsistent formatting of the vendor name, city
name, postal code, and address. Additionally, although there is a
field for a PO Box, it was not used in in the time period reviewed in
favor of being included in the street field. This could possibly cause
issues with mailings or contacting the vendor.
Attachment A
ERP Planning: Data Standardization 9
Exhibit 4: Vendor Master - Formatting Inconsistencies
Implementing a data standardization policy either within the ERP
system or through a data governance policy requires input of fields
that are deemed important for consistency.
Reduced data redundancy Data redundancy can be reduced through data standardization.
Reducing data redundancy makes it easier to locate existing
records rather than creating new records for the same data.
We observed redundancy of data in master vendor files. For
example, Exhibit 5 shows that the same vendor name was
associated with several vendor numbers rather than only one
vendor number being associated with each unique vendor. The
only difference between the vendor files is the formatting of the
street address. Redundancy in this case could cause issues with the
history of purchases from the vendor and cause other data issues.
Exhibit 5: Vendor Master - Name and Search Term
Exhibit 6 shows that there are two records for what are likely
identical items. They have different material numbers due to
inconsistency in inputting information. Adding to the issue, the
material part number was not entered, which could make it
difficult to find the right item when ordering (see above discussion
regarding improved data quality). Not having consistent rules for
entering data can lead to multiple records for the same items.
Attachment A
10 ERP Planning: Data Standardization
Redundancy in this example could lead to purchasing and purchase
history errors.
Exhibit 6: Inventory Master – Material Description
These two examples demonstrate how redundant data may be a
result of formatting inconsistency and result in search errors.
Standardizing the structure of data inputted into the ERP system
would help minimize redundancy.
Improved data access Standardized data can improve data access by making it easier to
retrieve information. For example, it can be difficult to retrieve
complete and accurate information if search terms are not
standardized. Exhibit 7 shows an example shows that the search
terms have several standardization issues:
• Search term does not match name of vendor
• One search term for multiple vendors
• First and/or last name used for search term inconsistently
These inconsistencies could lead to issues of data access.
Exhibit 7: Vendor Master Search Terms
Attachment A
ERP Planning: Data Standardization 11
Other data feeding into the ERP
system
The standardization examples we identified are applicable to data
in other systems outside of SAP. It is important to also consider
and apply standardizations to these data sets because the data
may feed into the ERP system. Exhibit 8 shows an example of some
of the same inconsistencies and redundancies in other systems.
Palo Alto 311 and Accela are an external facing, multi-platform
solution for providing Palo Alto residents, businesses, and visitors
access to a set of local government-provided services and online
requests. The Issue Types and the Case Types contain similarities
but lists different descriptions. Standardization can assist with data
quality, consistency, compatibility, redundancy, and access.
Exhibit 7: Other systems with data inconsistencies
PaloAlto311 Code Enforcement Issue Types: Accela/Building Eye Case Types:
• Building Construction (Unpermitted)
• Property Maintenance (Weeds, Vegetation,
Encroachment, Junk/Debris in Public View, etc.)
• Fences
• Zoning Compliance (AirBnB, Vacation Rentals, Non-
Conforming Use, etc.)
• Signs (Non Permitted, Signs in Public Property, etc.)
• Leaf Blower (Gas Powered)
• Other
• Building
• Encroachment
• Fences
• Home Occupation
• Long Term Condition Monitoring
• Property Maintenance
• Short Term Rental
• Signs (ARB)
• Signs (Public Property)
• Vehicles
• Zoning
• Leaf Blower
Recommendations To help ensure that the City adopts best practices for data
standardization when transitioning to the City’s new ERP system,
we recommend that the Information Technology Department
adopt practices for standardizing data, specifically including:
1. Provide governance over data standardization, such as who is
responsible for data standardization, what data is subject to
standardization, what is the data standardization, when does
standardization change, etc.
2. Work with Departments to review the data within SAP and
determine what will benefit most by standardizing data.
3. Review other systems and implement data standardization,
where feasible and beneficial; especially in circumstances
when the data feeds into SAP.
4. Require Departments to implement data standardization
requirements during data cleansing in the ERP transition.
Attachment A
12 ERP Planning: Data Standardization
APPENDIX 1 – City Manager’s Response
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report
progress on implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been
implemented.
Recommendation
Responsible
Department(s)
Agree, Partially Agree, or Do
Not Agree and Target Date and
Corrective Action Plan
To be completed 6 months after Council acceptance and every 6 months
thereafter until all recommendations are implemented
Current Status
Implementation Update and
Expected Completion Date
Finding 1: Implementing data standardization will ensure increased data accuracy and uniformity in the future ERP system.
To help ensure that the City adopts best practices for data standardization when transitioning to the City’s new ERP system, we recommend that the
Information Technology Department adopt practices for standardizing data, including:
1. Provide governance over data
standardization, such as who is
responsible for data
standardization, what data is
subject to standardization, what
is the data standardization, when
does standardization change, etc.
Information
Technology
Agree.
Target Date: Dec 31, 2019
Corrective Action Plan:
Data standardization and
governance are both already
priorities of the draft data
strategy plan document that is
being developed.
2. Review other systems and
implement data standardization,
where feasible and beneficial;
especially in circumstances when
the data feeds into SAP.
Information
Technology
Agree.
Target Date: Dec 31, 2019
(For standardization guidance
only. Remediation may take
significantly longer and will be
established once an assessment
is made).
Attachment A
ERP Planning: Data Standardization 13
Recommendation
Responsible
Department(s)
Agree, Partially Agree, or Do
Not Agree and Target Date and
Corrective Action Plan
To be completed 6 months after Council acceptance and every 6 months
thereafter until all recommendations are implemented
Current Status
Implementation Update and
Expected Completion Date
Corrective Action Plan:
The plan to implement data
standardization across systems
beyond SAP will be covered in
the City’s upcoming data
strategy plan.
3. Work with Departments to review
the data within SAP and
determine what will benefit most
by standardizing data.
Information
Technology
Agree.
Target Date: Dec 31, 2019.
Corrective Action Plan:
The plan to identify data and
data stewards for SAP to
determine standardization
benefits will be covered in the
City’s upcoming data strategy
plan.
4. Require Departments to
implement data standardization
requirements during data
cleansing in the ERP transition.
Information
Technology
Agree.
Target Date: Dec 31, 2019.
Corrective Action Plan:
This is already a mandatory
component of the design phase
of implementing the new ERP
system.
Attachment A