Customer Solution Case Study
/ / The Illinois Department of Transportation Saves Lives with Microsoft Business Intelligence Solution
Overview
The organization: The 5,000 employees of the Illinois Department of Transportation work to provide safe, cost-effective transportation to the residents of Illinois. Among the Illinois Department of Transportation’s guiding principles is a dedication to providing quality programs through innovation. The organization continually endeavours to “do it right the first time, every time.”The challenge: While tasked to “do it right”, Illinois Department of Transportation employees faced a significant obstacle to making the right decisions: limited access to data residing in many disparate, siloed data systems. Employees — from managers down to front-line workers – had to request information from the IT team when they needed to make decisions. IT personnel had to manually run reports from the mainframe, and the reports often required numerous iterations and up to several weeks to perfect. In addition, employees were unable to analyze data and identify opportunities to improve programs and save more lives.
The solution: Using Microsoft PerformancePoint 2007, as part of a comprehensive BI platform, the Illinois Department of Transportation has created several crucially important dashboards. These dashboards integrate data from many of the organization’s systems and provide employees with the tools they need to analyze information for decision making. The fatalities dashboard the Illinois Department of Transportation developed shows state-wide targets for decreasing traffic fatalities, and highlights progress towards the goals. It also enables managers to easily assess where and why fatalities happen. Now, they have the information needed to invest in programs that can reduce fatal accidents. / “With PerformancePoint Server 2007, IDOT can allocate resources more effectively and confidently quantify the decisions we’re making.”
Mark Kinkade, Chief Information Officer, Illinois Department of Transportation
The Illinois Department of Transportation (IDOT) is dedicated to improving traffic safety, reducing fatalities, and boosting process efficiency. Central to these efforts is a belief that informed workers are better equipped to make better and faster decisions, and to complete tasks correctly and quickly the first time. But distributing information proved an uphill battle in many cases. Employees simply couldn’t access information existing in legacy system ‘silos’, and critical reports had to be manually generated by IDOT’s IT team.
IDOT’s vision has sought to free up its IT team by giving employees direct access to information. Early attempts involved building custom dashboards from scratch, but this approach failed to scale well. Accordingly, IDOT sought to implement a comprehensive business intelligence (BI) platform—a one-stop information shop -- upon which additional dashboards and user-friendly information portals might be quickly implemented. In MicrosoftÒ Office PerformancePoint™ Server 2007, SQL Server® 2005, and Microsoft Office SharePoint Server 2007, IDOT found a solution its users could easily use at a price that it could afford.
With PerformancePoint as a key part of its BI platform, IDOT plans to implement several invaluable dashboards that will help save lives and money. For example, a fatalities dashboard will track fatal crashes and help to identify high-crash locations and root cause. This will optimize the development of effective crash mitigation programs. Another useful dashboard will monitor road-salt usage by location, essential to the effective management of a high cost item. And these dashboards are only the beginning. IDOT plans to build many more, all aiming to provide it’s employees with direct and easy access to the information and analytics they’ll need to make better and faster decisions, drive efficiencies, and improve safety.
Information Roadblocks
With 5,500 employees, IDOT serves 12.8 million Illinois residents. From building roads and bridges to executing advertising campaigns about traffic safety, IDOT is focused on providing safe, cost-effective transportation for the residents of Illinois. Central to its work is a commitment to delivering quality. A key tenant for the organization is to “do it right the first time, every time.”
But working efficiently wasn’t always easy for IDOT. “We had many disparate, disconnected systems,” explains Mark Kinkade, CIO at IDOT. ”The lifecycle of planning road construction through the culmination of the ribbon cutting is represented by many silo databases, on heterogeneous platforms. Further, crashes occurring on these same roads were also in a separate database.”
This lack of visibility into organization-wide data proved problematic in several ways. First, it limited the organization’s ability to make critical decisions about programs, because it was difficult to know what was working, and what wasn’t. For example, IDOT wanted to understand what caused crashes to occur in certain locations. “We wanted to see if we could tie accidents to how the road was built, what materials were used, or even what contractors were involved,” explains Kinkade. “But doing that required finding a way to integrate data from several of IDOT’s many disparate systems.”
This limited insight owing to fragmented data residing in multiple systems can make maintenance planning difficult, and errors in judgement expensive. For example, each year IDOT purchases huge quantities of road salt to melt winter snow and ice. Ordering the right quantities involves calculations based on data from various roadway, inventory and financial systems. Without ready access to this data, order quantities were all too often the result of guesswork. “Historically, we would either invest too much or run out of salt before the end of the season,” according to Kinkade.
Finally, this lack of easy data access put excessive strain on IDOT’s IT team. “Whenever someone needed information, they turned to our IT people to create a custom report, often manual reports from our mainframes”, says Kinkade. He adds. “Usually reports went through several time-consuming iterations before the requester was satisfied – some reports needed three or four attempts to get it right.”
Not only was acquiring the data time and resource intensive, managers still couldn’t analyze it easily. “Usually, we delivered hard copy reports, but sometimes managers asked us to put the data in a spreadsheet so they could do simple analysis,” says Kinkade. “Because it is neither a self-serve nor repeatable process, report requests of this type consume our resources, and represent extremely high actual and opportunity costs.”
Building Bridges to Data
For some time, IDOT’s vision has embraced a “self-service” approach to information access. Accordingly, an initial proof-of-concept project was undertaken and IDOT’s IT team turned to Microsoft®. Using Microsoft SQL Server® 2005, IDOT’s IT team created a construction data mart coupled with a custom ASP.NET dashboard providing end-user access. This combination gave IDOT’s construction group direct access to data previously held captive in the mainframe. When the project was completed, Kinkade confirmed its success. “We gave back two months worth of report requests to someone in the construction group to see if she could run the equivalent reports herself,” says Kinkade. “She did. What would have once taken us three to four weeks, she completed in a few hours.”
But while the project was a success, it was also laborious. “Manually building the dashboard solution from scratch was painful,” says Kinkade. So, IDOT began to consider a comprehensive BI platform in support of rapid dashboard creation and deployment, in the context of subject-area portals. “Portals are where the one-stop information shop takes form”, says Kinkade. At IDOT, portals can blend dashboards with all types of data including relational and multi-dimensional, images, multi-media, documents and maps. With the vision clear, IDOT quickly settled on Microsoft. “The other BI vendors are too expensive,” explains Kinkade. “With Microsoft, we get to experience the power of BI without investing a fortune.”
PerformancePoint Delivers Data
PerformancePoint™ Server 2007 leverages investments in Microsoft SQL Server 2005 and Microsoft Office SharePointÒ Server 2007 to deliver integrated data to users. PerformancePoint also integrates with Microsoft Office Excel 2007 for easy and familiar data analysis. At IDOT, for example, dashboards utilize several key features from Microsoft Office SharePoint Server 2007 to create a full featured solution. These features include Excel Services, Business Data Catalog, and mapping solutions. These features, together with PerformancePoint dashboards, create the core of the end-to-end BI environment.
Increasingly, IDOT plans rely on PerformancePoint’s capabilities to monitor critical processes and to provide timely and useful analysis to workers. Potentially hundreds of managers and staff will benefit from these insights. PerformancePoint analytical features fit hand-in-glove with the concept of self-serve data.
One of the first of IDOT’s dashboards supports its mission of delivering safe transportation; a fatalities dashboard to help the organization track and analyze fatal accidents. “We are building key performance indicators around the different causes of fatal accidents – such as speed, alcohol consumption, weather, improper lane use, and so on,” explains Kinkade. “By conducting analysis using PerformancePoint’s decomposition tree, we noted instantly that causes contributing to a significant number of fatal crashes were labelled as “other”, a catch all group providing little insight. Thus it was discovered a significant percentage of fatal accidents were caused by factors not being tracked. Kinkade notes, “We realized that we need to do some training with police about tracking these other causes.”
Armed with information on fatal accident causes, IDOT can now more effectively achieve goals set by the National Highway Traffic Safety Administration. IDOT officials can see actual versus target with red, yellow, and green indicators. The problem areas are highly visible making the Department’s measures much easier to achieve.
In addition, IDOT employees can run reports supporting analysis without burdening the IT team. “With tools provided by PerformancePoint, workers can analyze data along many dimensions,” says Kinkade. “For example, it’s now much easier to identify where fatalities happen and why they happen.”
Tracking winter salt usage and cost is also easier with IDOT’s new salt usage dashboard. This dashboard allows analysis by geographic location to provide a rich and insightful view into IDOT’s salt usage. Salt represents a major cost for an agency managing one of the nation’s largest highway systems.
Driving the Benefits
IDOT’s PerformancePoint solution is already paying dividends. “Instead of doing three iterations of a custom report over several days or even weeks, reports now take just a few hours or less to produce,” says Kinkade. “Even better, IDOT staff can run reports themselves. This solution allows users to access data that has never been available to them like this before.”
PerformancePoint not only contributes to IDOT’s improved data access, it’s giving workers the ability to analyze data in new ways. “Ad-hoc data analysis was simply nonexistent before,” explains Kinkade. “The Microsoft BI solution is now driving critical decisions by providing users with unprecedented data mining and analysis tools. With the Microsoft solution, IDOT can allocate resources more effectively and quantify the decisions we’re making. It’ll also allow our staff to make better decisions quicker.”
Looking forward, Kinkade predicts that PerformancePoint will become standard in many Illinois government agencies. “PerformancePoint is going to prove very valuable in a lot of areas,” says Kinkade. “For example, we could deliver a dashboard to the Governor’s Office of Management and Budget so that they can keep close tabs on fund balances and state finances. With PerformancePoint’s short learning curve, familiar interface, and affordability, I think many State of Illinois agencies will adopt it to support better decision making.”
Concludes Kinkade: “When people at other agencies see the BI solution we’ve built with Microsoft technologies, they quickly conclude that their IT teams can provide a similar solution, ‘PerformancePoint is going to take off fast in our state.”
Microsoft Business Intelligence
Microsoft provides an end-to-end, enterprise-grade, and cost-effective BI solution utilizing the intuitive Microsoft Office 2007 suite and robust BI components of SQL Server 2005 to enable decision makers throughout the organization to drive increased performance.
For more information about Microsoft Business Intelligence solutions, go to:
www.microsoft.com/bi
Microsoft Office PerformancePoint Server 2007
Office PerformancePoint Server 2007 provides all of the functionality that is needed for performance management including scorecards, dashboards, management reporting, analytics, planning, budgeting, forecasting, and consolidation. The application reaches all employees, across all business functions (finance, operations, marketing, sales, and human resources).