Transforming Data into Business Intelligence

Discussion Topics § BI Overview § Questions to consider § BI Case Studies §

Business Intelligence Overview Confidential | June 2003 | © 2003 IBM Corporation

Aristotle Onassis said: "The secret of business is to know something that nobody else

Government organizations today are faced with an almost overwhelming set of questions. Legislation and constituents are driving a focus on accountability for dollars spent and results § What is the effectiveness of various government programs? § What is the cost effectiveness of various programs? § What is the breakdown of the client base by program, service, demographic makeup? § What is the adherence of the programs to state and federal regulations? § What are the administrative costs of running state, county, and community based programs? §. . . State of Louisiana | Confidential © 2003 IBM Corporation

Much of the data to answer these questions exists and more is available but there is a growing gap in the ability of organizations to analyze it State of Louisiana | Confidential © 2003 IBM Corporation

Challenges to accessing data to answer questions § Multiple sources of data and reporting systems § Difficulty in locating and addressing information § Unreliable information § Pressures to lower costs and improve services § Information requests take days or weeks to fulfill § Re-keyed data ensuring errors creep into results State of Louisiana | Confidential © 2003 IBM Corporation

The needed data exists in "silo" applications State of Louisiana | Confidential © 2003

The solution is to derive business intelligence from relevant consolidated data State of Louisiana

Breakout Discussion § Questions for discussion: ► As a business unit, what challenges you the most in your effort to make informed decisions? ► What roadblocks do you have to cross in order to get the information that you need to do your job? State of Louisiana | Confidential © 2003 IBM Corporation

What is business intelligence? § Business Intelligence means using your data assets to make better business decisions. § Business intelligence involves the gathering, management, and analysis of data for the purpose of turning that data into useful information which is then used to improve decision making. Organizations can then make more strategic decisions about how to administer clients and programs. These practices can also reduce operating costs through more effective financial analysis, risk management, and fraud management. State of Louisiana | Confidential © 2003 IBM Corporation

Business Intelligence represents a fundamental shift in the purpose, objective and use of information

Business Intelligence Solutions are. . . § ". . . solutions which enable an enduser to quickly and easily analyze organizational data to make intelligent decisions. . . " § ". . . systems that put information in the hands of end-users. . . " State of Louisiana | Confidential § ". . . the combination of data warehouse and end-user data access and analysis tools. . . " § ". . . an investment in information technology that can assist governments in managing limited fiscal resources. . . " © 2003 IBM Corporation

Business Intelligence solutions start with data warehouses and data marts State of Louisiana |

Industry leaders agree that data warehousing is a process, a journey and takes a

What is a data warehousing? "Data Warehousing is the process whereby organizations extract meaning from their informational assets through the use of special stores called data warehouses. " - Ramon Barquin, The Data Warehouse Institute, Bethesda State of Louisiana | Confidential © 2003 IBM Corporation

A data warehouse provides a logical single point where data from operational systems is brought together. It ultimately provides a single source of information needed by end users for decision-making State of Louisiana | Confidential © 2003 IBM Corporation

This conceptual view of an enterprise data warehouse integrates data that is transformed into

And this is the Required Transformation State of Louisiana | Confidential © 2003 IBM

Levels of Query Analysis Capability State of Louisiana | Confidential © 2003 IBM Corporation

Breakout Discussion § Questions for discussion: ► What impact do you think there might be on the work that you perform and the way you do business now versus in the future? ► What concerns do you have as a result of learning about this type of advanced technology? State of Louisiana | Confidential © 2003 IBM Corporation

There are key critical success factors for the successful implementation of a BI initiative § High-degree of executive sponsorship ► Strong executive sponsorship is needed to demonstrate commitment to the project, provide resources, and reinforce the linkage of the data warehouse to business issues and strategy. § Data warehouse is linked to a well defined business information problem ► Without clear linkage to a well defined business problem, the value and benefits of the project to the business will be difficult to quantify and the project will be very hard to justify. § User involvement throughout the process ► Users must be involved in every step of the project to ensure that the effort remains focused on delivering business value and that the system can and will be utilized by the user community. § An appreciation of the significant effort involved for legacy transformation/data management tasks ► The difficulty of extracting, transforming, and combining data from disparate operational systems is one of the most difficult aspects of the data warehouse project and complicates the testing and validation of the data warehouse system. § Focus on proper database design for effective end user access and performance ► Good database design leads to performance that will encourage use. § Start small and plan for continued growth ► Developing the warehouse in small increments allows the business to begin receiving benefit from the project earlier and maintains project momentum. § Commitment to technical and user education and training ► Technical education is needed to allow IT staff to maintain, manage, and enhance the data warehouse. User education is required to develop expertise with the user interface, tools, and the data contents of the warehouse. State of Louisiana | Confidential © 2003 IBM Corporation

IBM has a proven approach for implementing data warehouses State of Louisiana | Confidential

IBM's experience and data warehouse best practices indicate projects with manageable scope, and then growing the EDW iteratively are most successful § Threefold projects ► Business function ► Enterprise data modeling ► Infrastructure development § Organic growth ► Each project expands the existing infrastructure § Outlook ► First deliverable in 4 to 5 months ► After 12 -24 months: – Many business deliverables – Modeling & Infrastructure continue to grow with each release – Multiple releases that deliver value to the business with each iteration § A complex, interdependent, long-term process ► Differs from most current I/T development efforts State of Louisiana | Confidential © 2003 IBM Corporation

Top "5" Ways to ensure that a Business Intelligence initiative will Fail. . . 5. Build a data warehouse to "fix" all of the data quality issues in your operational data. State of Louisiana | Confidential © 2003 IBM Corporation

Top "5" Ways to ensure that a Business Intelligence initiative will Fail. . . 4. Aggregate data, but keep all of the codes from the operational system -- because "everyone" knows the codes. 5. Build a data warehouse to "fix" all of the data quality issues in your operational data. State of Louisiana | Confidential © 2003 IBM Corporation

Top "5" Ways to ensure that a Business Intelligence initiative will Fail. . . 3. Try to do everything in a single project. 4. Aggregate data, but keep all of the codes from the operational system -- because "everyone" knows the codes. 5. Build a data warehouse to "fix" all of the data quality issues in your operational data. State of Louisiana | Confidential © 2003 IBM Corporation

Top "5" Ways to ensure that a Business Intelligence initiative will Fail. . . 2. Build a data warehouse and/or perform data mining with no business requirements or business involvement -- also known as the "build it and they will come" method. 3. Try to do everything in a single project. 4. Aggregate data, but keep all of the codes from the operational system -- because "everyone" knows the codes. 5. Build a data warehouse to "fix" all of the data quality issues in your operational data. State of Louisiana | Confidential © 2003 IBM Corporation

Top "5" Ways to ensure that a Business Intelligence initiative will Fail. . . 1. Build a data warehouse without Executive Sponsorship from the functional side of the house. 2. Build a data warehouse and/or perform data mining with no business requirements or business involvement -- also known as the "build it and they will come" method. 3. Try to do everything in a single project. 4. Aggregate data, but keep all of the codes from the operational system -- because "everyone" knows the codes. 5. Build a data warehouse to "fix" all of the data quality issues in your operational data. State of Louisiana | Confidential © 2003 IBM Corporation

Metadata (data dictionary) - or business rules - is a very important part of a data warehouse. IBM recognizes this and includes metadata strategy in our approach § Metadata is the term used to describe information about data, (e. g. data name, types, business definitions, or business rules) § Metadata enables administrators and business users to better understand the data environment and how to use the data in that environment for analytical purposes § Without metadata, business users are like tourists in a new city without any information or roadmap, and data warehouse administrators are like city planners who have no idea about the size of the city or how fast it is growing § This is VERY important in a fluid and changing environment driven by legislation State of Louisiana | Confidential © 2003 IBM Corporation

Geographic Information Systems (GIS) Confidential | June 2003 | © 2003 IBM Corporation

Business Intelligence with Spatial Data: GIS Solutions Enable Visualization of Analysis Results Source of

Many government organizations are faced with questions that, at their root, are geographic in whole or in part § Where are my geographic based assets? (e. g. , fire hydrants, facilities, state-maintained roads) § What is happening in the geographic areas under my jurisdiction? (e. g. , crime, crashes, demographics, traffic volumes/flow, disease) § Where should I intervene during regular circumstances? (e. g. , enforcement, maintenance, construction) § How can I plan for special circumstances? (e. g. , emergency situations, extreme weather, future growth) State of Louisiana | Confidential © 2003 IBM Corporation

Components of a GIS Solution Applications Data Layers Base Maps State of Louisiana |

Data Warehouses/Marts As The Repository for Spatially. Referenced Data Layers: Potential Subject Areas Roadway Attributes: Railroad Crossings Bridges Historical Traffic Volume Grade Functional Class Others…. Place/Other Attributes: Zoning Demographics Jurisdiction Funding Levels Restrictions Others… State of Louisiana | Confidential Roadway Events: Crashes/Crash Attributes Maintenance Projects Construction Projects Traffic Flow/Volume Flooding Others…. Nature of Data: Granular vs Summary Real time vs Historical Hierarchies for Drill Up/Down Others… © 2003 IBM Corporation

Business Intelligence with Spatial Data: Bi-Directional Source of maps: www. esri. com State of

Application Start Up • Integration of Crash, Roadway, and Base Map Data • Query

Scale-Based display • Zoom in: Reference Marker layer is configured to be rendered automatically

Street labels • Zoom in to automatic street labelling State of Louisiana | Confidential

Query for a subset of crashes State of Louisiana | Confidential • Crashes denoted

Bi-directional selection State of Louisiana | Confidential • Select crash on map, see data

Business Intelligence Case Studies Confidential | June 2003 | © 2003 IBM Corporation

Cleveland Municipal School District - making progress on a work in progress § Large urban district - 77, 000 students, with many superintendents over the last two decades § The Cleveland Public School District, placed in state receivership in 1995 restructured to a Municipal School District governance in 1998 CEO Barbara Byrd-Bennett signs on in November of 1998 § The Data Warehouse is a key focus for enhancing decision-making: ► ► ► Release 1 (Mid-2000) – Focus on Student, Test and School data for three academic years – Trained 20 "power" users over the summer Release 2 (December, 2000) – Add Curriculum, Certified Staff, more attendance and discipline with additional academic school year – Trained 120 new users that have "browser" access – Continued work on data quality – Focusing on establishing organizational roles to support the warehouse – Mentoring and promoting training in the proper use of data for data-driven decisions Release 3 (current) – Assessing data interchange with other public agencies, such as Juvenile Justice – More current refreshes of attendance data- (weekly) & most recent school year all subjects – Structured skills transfer sessions State of Louisiana | Confidential © 2003 IBM Corporation

Education data warehouses focus on strategic stakeholder questions OPERATIONAL (closed-ended questions) INFORMATIONAL (open-ended questions) üHow many knives were in school today? üWhat trends regarding weapons in school exist? üWhich students in which schools were absent üHow do absences affect student achievement? today? Of those who were absent today, which were absent yesterday? üWhat professional development activities have üWhat effect does professional development have certified reading teachers taken this year to date? on the reading portion of the proficiency test? üWhat are all the reading initiatives in the district? üWhich are the most effective reading programs? What best practices exist for reading programs? üHow many students have 4 consecutive üWhat are social factors contribute to unexcused absences this month? absences (e. g. , childcare, pregnancy)? What interventions can the county help with? üHow many teachers are teaching math and üWhat effect on student achievement does science? By school? multiple teachers teaching a single subject have? State of Louisiana | Confidential © 2003 IBM Corporation

In collaboration with Gwinnett County Public Schools (GCPS), IBM met budget & scope for each phase § Gwinnett County Public Schools (GCPS) § Largest school district in Georgia with over 100, 000 students § Subject Areas: School, Student, Test, Courses/Grades, Staff and Finance § Multi-phased project: ► Phase 0: Enterprise Information Strategy - 10/98 -12/98 ► Release 1: Student, Test & School - 01/99 -06/99 ► Release 2: Student, Test, School (detail), Courses/Grades, HR - 07/99 -10/99 ► Release 3: Programs & Services, Curriculum - 11/99 -05/00 ► Release 4: Additional Programs & Services, Grants, Classified Staff - 05/00 - 12/00 ► Release 5: Finance & Additional Programs & Services - 03/01 - 08/01 ► Release 6: Additional Finance, Programs & Teacher Data Mart - 09/01 current State of Louisiana | Confidential © 2003 IBM Corporation

Miami Dade Police is using integrated data and information to affect change § Miami-Dade Police Department supports the largest municipality in Florida. § IBM provided technical and project leadership in requirements gathering and development of the warehouse and led the development of the web enabled front end to the reporting environment § Deployed to the police department and local government officials, the warehouse supports analysis of crime and operational trends § Enables easy to use source of statistical information that allows executives to create crime-reducing strategies and identify crime patterns. § Allows more effective use of scarce resource to meet juvenile rehabilitative needs. § Align policy more effectively and plan for emerging community trends § Evaluate risk factors and improve interventions for at-risk juveniles State of Louisiana | Confidential © 2003 IBM Corporation

The goal of the First of a Kind at Farmers Insurance was to improve risk management in their property & casualty lines § Identify relevant risk factors § Segment insured population into homogenous groups with distinct risk characteristics § If (driver is male) and (age under 25) and (product is sports-car) --> claim frequency is 25% § Price segments profitably § Identify sub-niches § If (driver is male) and (product is EXPENSIVE sports-car) --> claim frequency is 5% § Manage business with underwriting rules that pre-identify segments and therefore risk § First-of-a-kind results, for 1 state, 1 year 6 out of 43 discoveries ► Policy gain of +7, 600 ► Profit gain of +$2 Million ► State of Louisiana | Confidential © 2003 IBM Corporation

IBM has worked with the State of Mississippi in building a multiphase, multiagency data warehouse, focusing on their self-sufficiency § Environment and Requirements ► Formulation of less than optimal public policy – No comprehensive view of the state's own operations – Personnel-related info not linked to financial, economic and statistical systems ► Multiple data sources - a lot of data manipulation, difficult to locate ► Delayed, inaccurate, inefficient reporting - internal and interagency § Solution Description ► Highly successful partnership with IBM with deliverables across four phases ► Consulting services for design through implementation, hardware and software, warehouse model ► Side-by-side methodology and skills transfer, affording the state's staff to enhance their warehouse § Benefits ► Provide better financial management ► ► – Current financial and five years of history readily available Executives summary reports with referrals to the data sources Business analysts access to detail data with deep drill down capabilities Fast, reliable information, exceptional data integrity Improved from several hours to immediate & several weeks to same day State of Louisiana | Confidential © 2003 IBM Corporation

Breakout Discussion § Questions for discussion: ► As a result of learning about BI, what are the top 3 areas of concern most prevalent on the minds of the business community in your business function? State of Louisiana | Confidential © 2003 IBM Corporation

IBM Business Intelligence for Governments Thank you. State of Louisiana | Confidential deeper insight deeper accountability deeper expertise deeper technology deeper experience deeper resources deeper answers deeper capabilities deeper research deeper knowledge deeper solutions deeper integration deeper value deeper e-business deeper thinking deeper transformation deeper innovation deeper government © 2003 IBM Corporation

Transforming Data into Business Intelligence — An Overview