] How Sedgwick County Implemented 821 KPIs Renfeng Ma, Sedgwick County

Renfeng Ma, Sedgwick County
Dr. Bjarne Berg, Comerit Inc.
]
How Sedgwick County
Implemented 821 KPIs
[ JUERGEN LINDNER
SAP POINT OF CONTACT
MEMBER SINCE: 1998
[ LINDA WILSON
ASUG INSTALLATION MEMBER
MEMBER SINCE: 1999
[ ATUL PATANKAR
ASUG INSTALLATION MEMBER
MEMBER SINCE: 2000
Learning Points
 We will look at lessons learned from the implementation of a
very large SAP NetWeaver® Business Intelligence (SAP
NetWeaver BI) cockpit
 Learn how to achieve government transparency using SAP
technology
 Review the change management process of developing 800+
KPIs in a public agency
 We will explore how applications and systems can be integrated,
how data collection can be automated, and how cockpits are
developed in a standardized format using SAP’s Strategic
Enterprise Management’s (SEM) Corporate Performance
Monitor, SAP NetWeaver BI, and SAP NetWeaver Portal
combined
2
Agenda
 Background
 Data collection from non-SAP sources
 A real example of a very large cockpit
 Seasonal thresholds and graphing options
 Change management
 Wrap-up
3
Background
Sedgwick county has 21 cities, including the city of
Wichita with 354,000 people. It also has 27
townships and an area of 1,008 square miles.
Sedgwick County is home to a number of aviationrelated industries and is known as the “Air Capital
of the World.”
4
Background (cont.)
 The county has many organizations, such as public health,
fire, emergency medical services (ambulances), sheriff and
jail operation, forensic center, code enforcement, registrar
of deeds, county clerk, criminal justice (district attorney,
district courts), highways, mental health services, parks,
solid waste, housing, election, HR, treasurer, tax collection,
county zoo and coliseums, and much more …
 A major challenge was to measure these many services
effectively to improve accountability and achieve
transparency.
5
Determine where you need to go and the steps to get there…
1. Develop Management Story
2. Develop Primary KPI and
Secondary KPIs
3. Group KPIs into KPI Themes
How Do You Manage? - Three Components of a Management Story
•Primary Outcome: KPI
•Identify Service Requirements (accuracy, timeliness, quality, volume)
•Manage Resources to Meet Service Requirements (people, tools, process,
communicate with funding source)
6
Reducing Resistance to Change
Hint: Use testimonials from peers to reduce psychological
resistance to the Management Story – “How I Came Up With
My Management Story”. Testimonials by…
•
•
•
•
•
Director of Fleet Management
Director of Emergency Medical Services (EMS)
Mark Masterson, Corrections
Colin McKenney, CDDO
Tim Kaufman, Human Services
Educate KPI Owners - The Three Dimensions of KPI
• What to Measure
• How to Measure
• How to Interpret
7
Encourage Wide Array of Measures - The Three Types of Indicators
Lagging - historical look at past performance (quarterly revenue, hours off
due to job related injury, employee turnover)
Leading - predictive of future results (safety training completion rate,
scheduled maintenance compliance rate, grievances)
Real-Time – where things are right now (inventory levels, employee count,
contractual obligations outstanding)
4 Signs of a Good Indicator:
• Tracks mission critical outcomes and activities
• Is influenced by productive behavior but can’t be easily manipulated by
your team
• Something that all team members can focus their efforts on
• Value of measuring exceeds cost of measuring
8
Do change management before changes actually occur.
If something has been used by my peer for a long time, maybe it’s not
too risky after all. Dashboard is not new to Sedgwick County, EMS has
been using one for years…
9
Roles and Responsibilities
Roles of a Division Director
Roles of a Department Head
Schedule 4 hours each week for
Division Director and Department
Heads to develop KPI with the help
of KPI consultants
Be the champion for your
departmental KPI
Provide quality control before KPIs
go in front of County Manager for
approval
Be the champion for KPI
development and ensure timely
completion of KPI in the division
Ensure that your KPI is clear to and
accepted by your employees, and
your employees are committed to
improving your KPI
Allocate 4 hours each week to
develop KPI with the help of KPI
consultants
Managing by KPI is everybody’s
job, involve all the employees in
your department
Measuring performance takes time and effort. Clear responsibility
and well understood timeline are essential to success.
10
11
What Is SEM-CPM?
 The Corporate Performance Monitor (SEM-CPM) is part of the
Strategic Enterprise Management BI tool suite. It consists of the
Performance Management (PM) area and the Strategy Management
area (SM).
 The Performance Management area consists of four distinct areas:
1.
2.
3.
4.
12
Management Cockpit
Measure Builder
Benchmarking
Delivery of business content
Source: J. Lombard, 2006
Strategy Management and Performance Measurement – CPM
The presentation layer in SAP
CPM cockpits has
four layers:
 Cockpit
High-level overview (consists of walls)
 Wall
A logical grouping of measures
 Logical view The display level of the cockpit; consists of frames
 Frame
Lowest level of individual measure display
These presentation layer objects are already Webenabled and integrated with the Portal. They also
provide built-in drill-downs and navigation, based
on how you constructed your measures.
13
13
13
SEM-CPM Walls
14
The color codes
(red, yellow, and
green) of the
indicators are set
based on some
thresholds that we
will explore later …
SEM-CPM Navigation and Measures
 The frames can be based on a set of composite measures that are
weighted; i.e., six KPIs can we weighted differently to create an
overall measure.
 You can create the hierarchies of measures in the Measure Builder
tool (transaction UMK_MB_DISP_PARA)
By clicking on
each of the
frames, you
can access
more details in
graphical
cockpits
15
Agenda
 Background
 Data collection from non-SAP sources
 A real example of a very large cockpit
 Seasonal thresholds and graphing options
 Change management
 Wrap-up
16
Our System Architecture and External data extraction
Legacy
Legacy
Reporting
Reporting
Legacy
systems
systems
systems
Users
Users
Users
Users
Users
1
SQL
Server
ASP page
2
3
SAP R/3
4
SAP BW
1. Flatfile updates and Manual
Updates through ASP pages.
Gradually being automated.
2. Periodic updates through DB
connect from SAP BI
17
6
3. Periodic updates through BI
extractors (ALE)
5
SAP
SEM-CPM
SAP
Portal
4. SAP BI Queries executed through
ODBO
5. Cockpits hosted in SAP Portal, which
also provides single sign-on (SSO)
6. Users via standard web browsers on
intra or internet.
External data extraction and updates
By creating a SQL
Server database, we
could standardize the
user input and
customize the web
pages in ASP so that is
was easy to do manual
inputs.
18
All input are flagged as
periodical (monthly, qtr
or annual) and are open
for edits only for a
specific period by a few
individuals in the
department that has
access.
Legacy
systems
Users
Users
Users
Users
Users
SQL
Server
ASP page
SAP R/3
SAP BW
SAP
SEM-CPM
SAP
Portal
The extraction from the SQL server
database to SAP BW/BI is done through a
single job that extract all KPI information
for the period through the use of SAP BI’s
DB connect feature.
Query Performance and Simplification in Infocubes
Legacy
Legacy
Reporting
Reporting
Legacy
systems
systems
systems
For Non-SAP data
Since single KPI values for a
given department and period
are stored in the SQL Server
and transmitted to BW. The
Queries takes on average 0.2
seconds !!!!
Users
Users
Users
Users
Users
SQL
Server
ASP page
SAP R/3
SAP BW
SAP
SEM-CPM
SAP
Portal
For SAP data
KPI_ID Period
1
2007-10
2
2007-11
3
2007-12
4
2007-07
5
2007-10
6
2008-01
…
…
…
…
19
Dept_ID
201
201
201
202
202
204
…
…
Value
0.65
0.64
0.68
386
394
16794
…
…
Period_type
Monthly
Monthly
Monthly
Qtr
Qtr
Annual
…
…
Locked
Y
Y
N
Y
Y
N
…
…
The details are kept in the
DSO and the infoCube has
only the single value for the
department for the period.
Query speed is therefore only
0.2 seconds on average…
Agenda
 Background
 Data collection from non-SAP sources
 A real example of a very large cockpit
 Seasonal thresholds and graphing options
 Change management
 Wrap-up
20
A User Experience Example
Our first step is to navigate to the departmental KPIs
21
21
Accessing Frames
Now we want to explore the summary cockpit for Public Safety
22
22
Accessing Summary Cockpits
These KPIs are weighted measures of six KPIs for the emergency
management services. The index is color coded for acceptable
performance levels.
23
We can click on any graph to enlarge it …
23
Drilling Down to Details
Here we can see that the index has turned red. That indicates that further
research is warranted. We added a new drill-down button to make user
navigation easier …
Note: This is sample data from the test system and has not been
validated (system went live a few months later)
24
24
Changing Indicator Graphs on the Cockpits
In the cockpit, the colors are set by the underlying KPI performance. When
there are no benchmarks, you can change the image …
Some KPIs do not have
benchmarks, they are not color
coded red, yellow, or green.
In those cases, SAP codes them
with a “beautiful” X.
25
We did not like that and created our
own image and replaced the “X”
with a customized image instead.
25
Exploring the Emergency Management Profile
The profile does not contain benchmark data, but is informative about the
activities. Here we explore the number of EMS 911 calls.
From the EMS profile, users can access:
1. Primary KPIs for the organization
2. Program outcome KPIs
3. Tertiary KPIs to manage the operations
26
KPIs are for all levels of the organization
26
Program Outcomes for EMS
Program outcomes include detailed benchmarks for monthly performance
and are coded red, yellow, and green based on actual data. Here we see
ambulance performance for one month.
27
- Building hierarchies of
measures gives everyone, at all
levels of the organization, some
benefits of using the cockpit.
- Getting the management to use
the same tool is a major benefit
of SAP NetWeaver BI.
27
Tertiary Operational Indicators for EMS
The tertiary indicators are most important for the director of Emergency
Management Services. It may be combination of survey data taken
periodically and system data loaded monthly.
Don’t be afraid of merging periodical data, such as customer
satisfaction and employee ratings taken every six months,
28
with operational data from source systems
28
Tertiary Financial Indicators for EMS
By making financial data easily accessible with the operational data, the
department managers see a greater benefit of using the cockpits.
The data can be annual, monthly, or weekly, as long as it
is used for trend lining and management decisions.
29
Be careful about adding daily information. When doing
so, you are leaving management cockpits and have
started building dashboards which have different usage
communities and a variety of different tools
29
Dashboard, Scorecards, and Cockpits
Purpose
Usage
Updates
Data
Measures
Context
Source
Dashboard
Displays performance
Scorecard
Displays progress
Cockpits
Displays status and events
Performance monitoring
Performance management
Performance management
Real-time feeds
Monthly snapshots
Daily snapshots
Events
Summaries
Summaries and events
Metrics
KPIs
Metrics & KPIs
Exceptions/alerts
Targets and thresholds
Trends
Linked to systems
Linked to plans
Linked to BI systems
Sources: Wayne Eckerson, 2005;
Bjarne Berg 2006
 Many companies and people confuse the concepts of dashboards,
scorecards, and cockpits. They vary in terms of purpose, usage,
source, data, etc.
 Most SAP NetWeaver BI systems fall into the cockpits category
These are typical parameters, and some may be slightly different
(e.g., some metrics in a cockpit may only be updated monthly).
30
Back to the Walls
This case study has 821 KPIs for management and operations –
we only looked at one small area with 18 KPIs so far. Let us look
at the senior management’s view of the cockpit and the
community profile …
For senior management, the KPIs tend to have more broad
applications in terms of scope and level of aggregation. Policies
and budgets are based on overall performance and not the
day-to-day operations which may fluctuate by season.
31
Dashboards – not cockpits, are used for operational management31
The Senior Management Community Profile
The Community Profile is a great way to summarize the
organization in terms of descriptive statistics. It is a very highlevel overview of the status of the organization.
For commercial enterprises, this is an area that may contain a
summary of all employees, locations by demographic information
and organizational model, and/or summary production volumes or
sales volumes for last quarter, or year, and trend lines.
32
Keep the information very summarized!
32
The Community Profile of an Organization
The community profile should be organized in sub-categories
that are informative, interesting, and have long-term trend line
value.
This example has six sub-groupings of measures and a total of 34 KPIs.
For example purposes, we will explore the long-term Economic trend lines
33
Community Profile – Economic Indicators
For long-term indicators, don’t be afraid to use external data and data that
has annual updates. The collection of this data is simple, low cost, and
provides everyone with a shared knowledgebase.
34
In the community profile, the data should be long-term
trends and relevant to strategic decision making
Keep the Cockpit Useful for a Large Community
By keeping the cockpit “wide” and for a large user community,
we were able to provide a shared view of a highly diverse
government organization
35
Build a “wide” cockpit with shared measures that is widely available to all users
35
Agenda
 Background
 Data collection from non-SAP sources
 A real example of a very large cockpit
 Seasonal thresholds and graphing options
 Change management
 Wrap-up
36
Automating Thresholds and the KPI Data from Non-SAP Systems
KPI_ID Period
1
2007-10
2
2007-11
3
2007-12
4
2007-07
5
2007-10
6
2008-01
…
…
…
…
Dept_ID
201
201
201
202
202
204
…
…
Value
0.65
0.64
0.68
386
394
16794
…
…
Period_type
Monthly
Monthly
Monthly
Qtr
Qtr
Annual
…
…
Locked
Y
Y
N
Y
Y
N
…
…
The data table in SQL
server provides a set of
columns that capture the
summary of the results for
the period for a
department.
This allows the department to update the KPI
values for a short time period to correct any errors
(done in a simple ASP page)
Threshold values (Red,
Yellow and Green) are
captured for each period,
so that the history of
historical thresholds can
be preserved.
37
KPI_ID Period
1
2007-10
2
2007-11
3
2007-12
4
2007-07
5
2007-10
6
2008-01
…
…
…
…
RThreashold YThreashold GThreashold
0.8000
0.7500
0.7499
0.8000
0.7500
0.7499
0.8000
0.7500
0.7499
450.0000
425.0000
424.9999
450.0000
425.0000
424.9999
21000.0000
17500.0000
17499.9999
…
…
…
…
…
…
SEM-CPM Rules for Graphs and Real Estate
 Try to avoid empty spaces, but don’t force unrelated measures
into the cockpit view — it confuses users
Don’t use as many types of graphs as possible,
and never mix more than 3 types of graphs. If you
use more, users have to interpret the pictures as well as the data.
 Missing data
points add
irritation.
(it is hard to hide
bad data in a
cockpit)
38
38
38
SEM-CPM Rules for Graphs and Real Estate (cont.)
There is nothing wrong with using the same type of graphs on a
cockpit (e.g., tachometers or line charts). Users adapt quickly
and can absorb the information faster.
39
39
Tracking Changes Over Time – Annual Measures in Context
For an organization to act strategically, you need annual performance
measures to see the big picture. You often have to merge measures also.
This is of little value to the operational managers, but of great value to the
CFO, CEO, CxO, and upper-level executives.
40
When the executives are looking at their organization, they
are frequently examining external data at an annual level
40
Use of Complex Graphing of Annual Measures
Sometimes measures have little value unless they are seen in
context of other indicators. You can use complex graphing
with different scales to address this.
Example: Number of fatalities in
road accidents have limited
value in assessing vehicle
safety without seeing the
context of total number of
accidents.
We can click on the graph to examine
this closer
41
41
Use of Complex Graphing of Annual Measures (cont.)
We now have two measures over five years of different scales.
However, SAP-CPM allows you to merge these in a single graph
with two scales.
42
42
Agenda
 Background
 Data collection from non-SAP sources
 A real example of a very large cockpit
 Seasonal thresholds and graphing options
 Change management
 Wrap-up
43
Change Management Process
 Since a CPM system is going to track performance over
time, it is important that the benchmarks and thresholds do
not fluctuate frequently
 At the same time, the organizations and people being
measured have an inherent interest in moving the
thresholds if they turn yellow or red
 Therefore you will need a formal change management
process for how to get approvals for changes to the
cockpits
 Senior management should be the decider, not the IT
department …
44
Change Management Process (cont.)
IT responsible
Change
Request form
Integration tested
Business responsible
QA environment
No
Approved?
Sr. mgmt. responsible
Yes
Approved?
Submission
No
No
Complete?
No
Approved?
Yes
Yes
Moved to
production
System tested
Scheduled
Devl. environment
Yes
Review
recommended?
No
Change
Request form
Unit Tested
Developed
Devl. environment
Yes
No
45
Approved?
Yes
The Change Management Form – Page 1
 To make this process work, you need a formal instrument.
 The instrument can be online (i.e., a Web page), electronic (Word
document), or a paper-based system.
 The form should contain at least these fields:
Change Request Form
Requestor Name:
Department
Phone number / email
The front page
that the
requestor fills
out
Describe the change requested, be
detailed
Why is it needed
How important is it that the
change occur? (how would you
manage if this is not done)
TBD
46
When is the change needed
When
possible
Future
release
Date
Break-fix
(right now)
The Change Management Form – Page 2
 This page is used by the system administrator or the
project team
 The purpose is to have controlled changes that are
scheduled and tested appropriately
For internal use only
Received date:
Reviewed by:
Comments/recommendation
The back page
that the system
admin and
approver fill out
Pending
Not-Approved
Future
release
Approved
Break-fix
(right now)
Pending
Prototyped
In QA
Tested
In Production
Approval status:
Approved by:
Approved date:
Assigned to:
Due date:
47
Development status:
47
Agenda
 Background
 Data collection from non-SAP sources
 A real example of a very large cockpit
 Seasonal thresholds and graphing options
 Change management
 Wrap-up
48
When to Consider CPM — Management Cockpits
 The management cockpits and Web rendering closely integrate with
the pre-delivered measures in SAP Measure Catalog. You also can
add new measures through SAP Measure Builder.
 By linking the higher-level measures using Measure Trees, you can
create true performance measures that are consistent throughout
your cockpit and which also take care of most of the Web navigation
and drill downs in the cockpit. Navigation is built into the
management cockpit and you do not have to build customized links.
Consider SAP CPM when you have limited in-house Web skills, or
when measure consistency and rollups are very important.
49
Drawback: Users often complain that there are too many steps
that they have to follow when drilling up and down within the
measures (rigid navigation that is hard to customize).
What SAP Tool Should I Use? – Other Options
BW 3.x
Web AD
SAP NetWeaver 7.0
Web AD
SAP
NetWeaver
Visual
Composer
SAP
SEMCPM
Thirdparty
cockpits
Java SDK
tools
Portal
iViews
Multidimensional Analysis (full)
J
J
-
-
-
-
J
Portal integration
J
J
J
J
J
-
J
Information Broadcaster
J
J
-
-
-
-
-
Formatted reporting
-
J
-
-
-
-
-
Graphical options (delivered)
-
J
J
J
J
-
J
Automatic code generation
-
J
J
-
-
-
-
J
J
J
J
J
J
J
Direct support for non-BW data
-
-
J
-
J
-
J
Simplicity to use and learn
-
-
J
J
-
-
J
SAP integration
50
There are many options and sometimes the choice is based on what you are
familiar with and whether you enjoy using new tools
Resources
 COMERIT (SAP BI presentations and articles)
 http://www.comerit.net
 Patrick Dixon, “Optimize Your Portal Implementation with These
Undocumented Security and Performance Techniques” (Admin and
Infrastructure, 2007).
 www.sapinsideronline.com/downloads/Las_Vegas_2007/presentations/Track9
_session9.pdf
 Dr. Bjarne Berg, “Building Cockpits and Dashboards: Shortcuts,
Design Best Practices, and Guidelines to Ensure You Pick the Right
SAP Tool for the Job” (Reporting and Analytics conference, 2007)
 http://csc-studentweb.lrc.edu/swp/Berg/articles/R&A_2007_Berg_building
_cockpits_dashboards_v3.ppt
51
Learning Points
 Don’t underestimate the time it takes to get management
buy-in to measures, thresholds, and performance graphs
 Get very senior management sponsorship. If you cannot
get it, go with OLAP instead.
 Instill the tool into the organization’s decision-making
process, i.e., create a periodic (monthly) meeting with KPI
reviews.
 Formalize a process for when and how KPIs can be
changed. Approvals should be done by senior executives
and not very often (seek measure stability).
52
How to contact us:
Dr. Bjarne Berg, [email protected]
Refreng Ma, [email protected]
53
]
 Thank you for participating.
Please remember to complete and return your
evaluation form following this session.
For ongoing education on this area of focus, visit the
Year-Round Community page at www.asug.com/yrc
[SESSION CODE:
303
54