Implementing Public Sector Performance ManagementA Balanced Scorecard Approach

Tim Calkins, SAS Institute Public Sector

EXECUTIVE SUMMARYParalysis by analysis is very common in executivemanagement today. Many organizations are experiencingdeath by details. They are measuring and tracking the rightinformation, but have no effective way to gather, organizeand easily view data and report on information that wouldhelp the corner office thinkers make decisions.

The balanced scorecard approach focuses on KPIs (keyperformance indicators). KPI’s are your organization's radarto the road ahead, acting as leading indicators to maneuveraway from using last month's actuals as your forecast tool.Using past performance data (lagging indicators) to makedecisions is like driving while looking in the rear view mirror.An effective balanced scorecard should have a mix of bothoutcome measures and performance drivers. Outcomemeasures without performance drivers do not communicatehow the outcomes are to be achieved. Conversely,performance drivers without outcome measures may resultin local improvements for the business unit, but are not likelyto result in expanded or enhanced financial performance forthe organization as a whole.

This paper will examine a methodology to improve theprocess of linking performance measures and costs directlyto programs through a balanced scorecard framework. It willalso demonstrate how the combination of a datawarehousing methodology can be coupled with the role ofour SAS® System to build a clear integrated performancesystem.

MANDATES ON PERFORMANCEPublic sector managers have been flooded by a sea ofmandates over the last ten years designed to monitoraccountability for a department or agency to meet specificgoals. This legislation was created to effect organizationaltowards greater efficiency. The most notable, is theGovernment Performance and Results Act of 1993 (GPRA)which created a standard planning and performancereporting process in federal agencies. GPRA requires eachdepartment to create a strategic plan, a performance plan,and a performance report. This act requires all agencies towrite the strategic plan by September 30, 1997, the annualperformance plan for fiscal year 1999, and a performancereport by March 31, 2000.

GPRA is the primary legislative framework through whichfederal agencies will be required to establish accountabilityfor improving strategic planning, budgeting, and theevaluation of programs.

GMRA (Government Management Performance Act of 1994)provides a more effective and responsive governmentthrough a series of management reforms primarily forFederal human resources and financial management. Theact, for FY 1996 and each year after, that all major Federaldepartments and agencies prepare a financial statementcovering all accounts and associated activities of each officeand bureau. The statement should conform to OMBguidance, and it should be audited by the agency InspectorGeneral. The statement should reflect (A) the overallfinancial position of the offices, bureaus, and activities

covered by the statement, including assets and liabilitiesthereof; and (B) results of operations of those offices,bureaus, and activities.

GMRA requires a government-wide financial statement, forFY 1997 and each year after, prepared by the Secretary ofthe Treasury in coordination with the Director of OMB,audited by the US Comptroller General reflects the overallfinancial position of the US government, including assetsand liabilities and results of operations.

Other mandates include the Clinger-Cohen Act, whichrelates IT to departmental mission and performance; theCFO Act, which establishes the need for performancemeasures; The National performance Review, directed atimproving customer service; and the InformationManagement Technology Reform Act to establishperformance-measurement analysis.

Public sector managers will fight for greater accountabilityand struggling on how to measure more accurately theoutcomes of their activities, not just the outputs.

There are essentially four components that a totalperformance management system requires to create a goodsolid performance report:1. A statement of appropriate objectives that are truly

measurable (and the available data to make thesemeasurements), and an overall strategic plan

2. Programs to achieve these objectives as well as themeans of linking measurements and objectives

3. Solid costing programs and the accounting means andmethods for getting good cost information

4. The ability to tie the three prior elements into thebudgeting system

Forward thinking and aggressive managers are in search ofmore than just compliance with mandates. Instead of justhanding in a performance report, they would ratherproactively manage the operations and services of theirbusiness on an on-going basis. One of the major challengesassociated with this reengineering effort is data capacity andmanagement. Managers are concerned about the ability oftheir systems to deliver data needed to report performanceand results measurements. Other concerns include theinability to deliver the data quick enough to produce ad-hocreports and short term solutions that solve immediatecompliance mandates, but fail to deliver what the managersneed most: a strategic management infrastructure that cangrow and be flexible as departmental requirements change.

INTEGRATED DECISION SUPPORTENVIRONMENTAn integrated decision support system has the ability toaccess, manage, organize and exploit your data frommultiple sources and different systems. Your Scorecard sitson top of your applications and assembles information frommany different areas and systems within your organization.All components of your balanced scorecard must be able tocommunicate, share data, read the same files and exportinformation with other components. Failure to communicateand share data will result in a system laden with manualprocesses, inaccurate data and glorified spreadsheets.

Applications Development

An Integrated DSS EnvironmentPublic Sector

Scorecard

Transactions Systems GeneralLedger

Data

BudgetData

Human Resource

Data

Access EngineTools Environment

OLAP WebData Warehouse

Data MiningArchitecture Environment

Business Metadata

FinancialConsolidation

Human ResourcesAnalysis

IT ResourceManagement

Other Data

Packaged DecisionSupport APPS

The first stage of communication is access to all youtransactional data. The ability to access transactionalsystems, to load data and to drill back in to that detailedinformation, is essential for good reporting. An access toolthat is flexible and can get to data on any hardware platformor in any just about any format will not limit your scorecardand allow easier updates.

After a flexible access tool, an environment of managing andexploiting the data is needed. A data warehouse is the heartand soul of your balance scorecard.

DATA WAREHOUSING METHODOLOGYThus, the methodology is a Performance Managementsolution wrapped around data warehousing technology. Thedata (budget, costs, and measures) comes from multipledata sources, multiple platforms, and stovepipe systems atdifferent times. The way to address this complexity is datawarehousing. By utilizing a central repository of information,managers have the ability to look at their data in a timely andaccurate manner and make proactive rather than reactivedecisions.

The major steps in creating a performance warehouse are:1. Data modeling2. Data extraction3. Data transformations4. Loading the warehouse5. Creation of business and technical metadata6. Exploitation of the data

DATA MODELINGThe first step to create a performance warehouse is datamodeling. By looking at the Strategic Plan and PerformancePlan, figure out the subject definitions to model against plusthe user requirements needed to view the data. Examples ofsubject definitions could be finance, human resources,customer feedback, measures, programs, or budgets. This isthe place where the designer needs to associate the costs,programs and measures in a logical representation to figureout the structure and the content.

Logical modeling results in the performance data model.Different techniques can be applied here including entity-relationship, star schema, and snowflake schema, or

multidimensional stores. The latter three are recommendedfor this approach since users will need dimensionality to lookat a program sliced by a certain cost, associated with ameasure compared against a goal. To help with the datamodeling, the ERwin modeling tool is integrated in with theSAS/Warehouse Administrator. Here, once the model iscreated, it can be populated into the metadata.

Physical data modeling is the process of mapping the logicaldata model to the warehouse repository structure. Physicalmodeling is where the data will come from, how often torefresh, and different types of storage.

DATA ACCESSSince data capacity is a significant issue, accessing the datais a major step in the process. Data required for this systemis never available in one place, but in scores of disparatestovepipe systems. For example, cost data needs to beextracted from an activity based accounting system, budgetdata out of the financial system, and measures out of varioussystems such as surveys being done via the web. Each oneof these systems probably resides on multiple platforms.

To help with this complexity, the enabling technology behindthe SAS data access strategy is the Multiple EngineArchitecture (MEA) and Multi Vendor Architecture (MVA).MEA provides more than 50 different access methods for avariety of file types found in hardware environments. Thesemethods include:

• Relational database management systems (Oracle,Sybase, Informix)• Hierarchical database management systems• Network database management systems• ODBC connectivity• External file formats, such as VSAM• Sequential files such as tapes• SAS tables

The engines translate SAS software read and write requestfor data into the appropriate calls for each databasemanagement system and file structure. Engines surfacedata into the SAS System in two forms, logical views to thenative data source or extracts of native data into SAS datatables. The SAS fourth generation language (4GL) is thedeployment of these extracts. To assist in the extraction, theSAS/Warehouse Administrator creates in a point and clickenvironment ODDs (Operational Data Definition). ODD is apointer to the data to extract, where it resides, and whatcolumns to import.

Another method for extraction is Structured Query Language(SQL). The SAS System supports standard ANSI SQL.When the SAS System’s internal SQL is unable to optimizequeries for the DBMS, the user can then pass native SQLstatements to the DBMS. This method is also supported inthe Warehouse Administrator for single point of control.

DATA TRANSFORMATIONSSince the data for the performance warehouse is coming in avariety of elements, data needs to be cleansed forconsistencies and data validation. The two steps intransformation are conversion and summarization.

Conversion is aimed at resolving data inconsistencies invalue definitions and formats among data. It is anopportunity also to check for missing data and determinewhether to take out the missing data or set it to a certainvalue. Also, at this point in the process, new columns canbe derived from the data or calculations. For example, ifextracting data from a customer satisfaction survey, howwould you take an “Agree” answer and turn it into a numeric

Applications Development

value. In addition, the calculation has to define whether ornot the survey meets objectives of the goal. Resolving datainconsistencies can be as simple as a state defined as “NewJersey” in one source and in another source defined as “NJ.”This part of the process is extremely important when enduser is trying to run a report against the data. In order to linkthe programs, costs, and measures you must have an objectcode that will allow the data to merge.

Another part of transformation is summarization. Each levelof summary reduces detail, improving performance andminimizing storage overhead. Summaries can be at thenumeric level as well as groupings.

The whole transformation process is managed easily insidethe SAS/Warehouse Administrator with point and clickcapabilities. SAS/Warehouse Administrator can perform oneto one mappings, one to many mappings plus differentfunctions and calculations with minimal coding.Transformation is a critical piece to ensure the data integrity.

LOADING THE PERFORMANCE WAREHOUSEOnce the transformation has occurred, the performancewarehouse can be loaded. The three different approaches inloading the warehouse include the load of data alreadyarchived, complete data refresh, and change data capture.One issue with completing data refresh is with the continualreloading of the data warehouse from scratch which will notbe feasible because of increasing volumes of data andconsuming resources.

To change data capture is to load the warehouse with onlythe changed data (after the initial load) from the operationalsystems. The issue here is how to capture the change,which could include options such as scanning internal dates,audit files, journals or logs. A combination of these methodsare available depending on the data.

A part of loading the warehouse is scheduling the process.Measure data could be refreshed every week, where asfinancial information only refreshes monthly.SAS/Warehouse Administrator schedules these processesat a specific chosen time and a chosen interface withdifferent operating system schedulers or with the SASDomain Scheduler.

Different components can create the performancewarehouse. The repository could contain detail tables,summary tables, multidimensional databases (MDDB),information marts (canned queries, graphs, or worddocuments). Multidimensional databases will be a commontheme in most performance warehouses with multipledimensions: geography, time, programs, lines of business.This cube will allow users to slice and dice on themeasurement versus program during a certain period oftime. Below is an example of the process editor ofSAS/Warehouse Administrator showing the steps to build anMDDB in a performance warehouse.

METADATA FOR PERFORMANCE WAREHOUSEA crucial piece of the performance warehouse is themetadata, data about data. Metadata is the component thatkeeps track of the process of defining, creating, andpopulating the warehouse. Two types of metadata aretechnical metadata and business metadata. Technicalmetadata is the physical attributes of the data including inputsources, columns, source code and the actual processes.

Examples of technical metadata:

• Sources of operational data• Targets for warehouse data• Aggregation methods and rules• Availability of summarization• Logical and physical data mapping• Frequency of downloads• Exception conditions

Technical metadata assists IT when changes in thewarehouse need to occur. Instead of searching through linesof code, IT can perform a search on the metadata and seewhere in the process the change needs to be performed.

Business metadata contains information for end users toknow how to navigate through the warehouse and know onwhat to report. Business metadata consists of datacoverage, business rules, targets, goals, etc. StrategicPlans and Performance Plans should be a part of thebusiness metadata. Thus, when a manager is looking at acrucial measure, the user will know the link to the goal andobjective it is associated with. Examples of metadata are:

• Subject oriented content of the data warehouse• Critical success factors- targets• Goals• When the data was refreshed• Information on how the columns are used• Dimensions• Analysis variables• Summary statistics

The SAS/Warehouse Administrator generates metadata thatcan be imported from other data sources and exported.Metadata can be exported out to the Web for end users. Thiswill eliminate the black box syndrome where users view datafrom the warehouse of which they have no knowledge.

THE PERFORMANCE DATAThe exploitation of the warehouse is key to all users.Managers need to quickly and clearly and see performanceindicators through critical success factors, reports, andgraphs. There are multiple ways to exploit the datawarehouse through query and reporting tools, statisticalanalysis, and applications development. For theperformance warehouse, exploitation will be through anExecutive Information System (EIS) and a Web Enabledapplication.

An EIS system provides a user friendly, interactive, out ofthe box desktop. Managers click on icons and see therelevant information. The data is dynamically driven sousers are always viewing the latest information. Features ofan EIS include multidimensional viewing, graphs,comparison reports, variance reporting, critical successfactors, and forecasting. Multidimensional viewing isreferred to as Online Analytical Processing (OLAP) which

Applications Development

enables users to slice and dice the data. OLAP exploits themultidimensional database created in the warehouse to lookat different dimensions crossed with analysis variables.OLAP gives the end users the ability to drill through the dataat different levels. SAS OLAP tools allow the users to lookat the summarized information in the cube, and reachthrough to the detailed level information that has made upthat particular crossing.

PUBLIC SECTOR SCORECARDSAS Institute combines all the elements needed to track andreport on KPIs in the Public Sector Scorecard ©. Thisbalanced scorecard approach has all the data access, datawarehouse, OLAP, EIS, Internet and reporting built into onesolution.

An easy point and click desktop provides a powerfulinterface to all your reports, administration and balancedscorecard options, and is customized to allow the mostflexibility and ease of use. The Public Sector Scorecard hasthe built in balanced scorecard intelligence to help youquickly deploy both static and adhoc reporting and a quicksnapshot on how balanced your organization is.

Reporting is handled via an EIS or you can disseminate theinformation via the web. The Web as a thin client, givesusers quick access to the information. Dynamic reportscould include query and reporting, critical success factors,multidimensional analysis, forecasting, graphing, etc. Withdynamic reports, the end user selects certain criteria on thebrowser, submits, and the information is retrieved from theserver and relayed back to the browser. Web applicationsimplement an efficient, scalable solution.

A single version of the truth is accessible through an EIS orWeb application without having to wait for an IT request.Exploitation is the finishing piece to the process, the endresult. The creation of the performance report(s) should bea part of the EIS/Web application. The user’s requirementswill predict the finished application.

ROIPerformance measures, once tuned to give you clearperspective of your organization, will make a big impact.

Reduction of costs, more informed decisions and improvedcustomer service. The demand for information and accessto it is growing. The web has changed everything and thegeneral public expects more information and faster turnaround time. Not only will performance measures andbalanced scorecard help you manage your organization atall levels, but will help assist in meeting the growing demandfor information delivery.

SUMMARYThe process of building a performance measure warehouseis complex. The methodology proposed is a step by stepapproach. Often times, agencies try to start at the enterpriselevel and failure is inevitable. To start building aPerformance Management Warehouse, organizations mustchoose a goal, objectives, and measures with two differentsources, and build an application. The next logicalsuccession will be to put the application out to the users, andachieve success. By taking an iterative approach,organizations will then add new goals, measures, andsources of data.

Four Key Steps:

1. Build it2. Test it3. Try it4. Adjust it

A Performance Management Information System should bea clear integrated, scalable solution for managers to makecritical decisions before turning in their performance reports.Whether your view of a balance scorecard for yourorganization is at 30,000 feet or you are at ground zero,resist death by details. Access, Organize, Manage andexploit your data with an integrated approach that helpseveryone in your organization maintain better reporting andaccountability.

ACKNOWLEDGEMENTSThanks to Jennifer Torrisi Hill who wrote the first version ofthis paper.

ABOUT THE AUTHORTim Calkins is manager of Financial Solutions for the PublicSector Technology Center at SAS Institute Inc. The PublicSector Technology Center (PSTC) is dedicated toempowering and creating solutions for the public sectorneeds. He provides expertise is in financial performancemanagement and application development. He has workedwith SAS Institute Inc. for four years in Financial Solutionsand worked for 12 years in financial reporting before joiningSAS. Tim can be contacted at tim.calkins@sas.com.

Applications Development