Mississippi Department of Transportation State Transportation Agency Speeds Access to Business Intelligence for Better Reporting

The Mississippi Department of Transportation (MDOT) is responsible for maintaining the state’s highways and other transportation infrastructure. The agency wanted to improve its data management system so it could use external storage for huge data files and enhance the maps it includes in its reports. MDOT capitalized on an opportunity to install a beta version of Microsoft SQL Server 2008 R2 Enterprise to accomplish this goal. With the new software, MDOT can now provide fast access to large files stored externally and easily generate reports featuring maps that take advantage of existing geospatial information. Furthermore, the new solution provides high availability and scalability and reduces the burden on the agency’s IT staff. MDOT also plans to take advantage of PowerPivot technologies in SQL Server 2008 R2 to help employees better analyze and share business intelligence.

The Mississippi Department of Transportation (MDOT) plans, maintains, and improves the highway, rail, maritime, public transportation, and aviation infrastructure in the state. Based in Jackson, it has 100 field offices, more than 3,000 employees, and an annual budget of U.S.$900 million.

With SQL Server PowerPivot for Excel, our safety engineers can mine data in SQL Server 2008 R2 to help prevent road accidents.  John Simpson Chief Technology Officer Mississippi Department of Transportation

In late 2009, MDOT was managing its data using Microsoft SQL Server 2008 Enterprise data management software, and although MDOT was pleased with it, the agency’s needs were about to change. In conjunction with the development of a new portal system to be powered by Microsoft SharePoint Server 2010, MDOT planned to move many large engineering documents into its database. “These documents are several hundred megabytes apiece in size, totaling two terabytes of data all together,” says Ken Slay, Project Manager at MDOT. “We could see that storage was going to become an issue.”

MDOT was also interested in increasing its business intelligence (BI) capabilities. The agency wanted to take advantage of the vast amounts of content it had in reports, spreadsheets, documents, presentations, and other types of communications. For example, it was particularly interested in mapping functionality. “Certainly, with money from the federal stimulus program—the American Recovery and Reinvestment Act of 2009—it’s very important for us to be transparent about where and how funds are being spent on road projects,” says Slay. “We had some internal tools, but it was difficult for our business analysts to show geospatial data within a public report in a meaningful way.”

MDOT wanted new data management software that could store and quickly search large amounts of data while improving its capabilities to analyze business intelligence and report on geospatial data.

In January 2010, MDOT learned about Microsoft SQL Server 2008 R2 Enterprise, which includes powerful new data analysis and reporting features. The agency’s BI analysts were impressed, and MDOT decided to implement a beta version of the software. Meanwhile, it was also implementing the new portal system, a content management and search solution based on SharePoint Server 2010 and Microsoft FAST Search Server 2010. MDOT found that the software packages worked well together.

With SQL Server 2008 R2 Reporting Services, our business analysts can now quickly draw up a report and drop in a map that takes advantage of existing geospatial information.  Ken Slay Project Manager Mississippi Department of Transportation

The second feature, FILESTREAM, takes advantage of RBS so that BLOB columns can be managed inside the database. This gives the remote data the same manageability and security capabilities of the rest of the database, eliminating the need to manage files separately.

Additionally, Microsoft SQL Server 2008 R2 Reporting Services introduces new features and enhancements that help users create reports and make those reports more comprehensive. Among the new features is support for reports with visual geographic mapping. The new features also give MDOT BI users the ability to add analytical data to maps.

SQL Server 2008 R2 also works well with Microsoft System Center Data Protection Manager 2010, a backup and recovery solution that MDOT is currently implementing as its primary backup for all of its servers.

To implement SQL Server 2008 R2, MDOT worked with consultants from Microsoft Services and with LifeCycle Solutions, a Microsoft Gold Certified Partner based in Jackson. “LifeCycle provided us with the expertise and tools to make this not just a completed project, but a successful one,” says Slay. “We’re deeply appreciative of LifeCycle, and we look forward to continuing to work with its staff and with the world-class technology from Microsoft.”

Daniel Root, Director of Software Development at LifeCycle, who served as technical lead on the project, says that he was impressed with the attitude of MDOT employees. “I frequently hear people at MDOT talking about trying to save the taxpayer money. When they can save money or show value with the money they are spending, it’s a huge win for them. And that’s what most excited them about SQL Server 2008 R2.”

MDOT began installing SQL Server 2008 R2 Enterprise in early February 2010 and plans to go live with the solution in late April. It runs on HP ProLiant DL380 G6 server computers. The agency intends to use Microsoft SQL Server PowerPivot for Microsoft Excel, an in-memory BI technology that employees can use to analyze and combine data. With PowerPivot for Excel, MDOT users can create workbooks that assemble and relate hundreds of millions of rows of data from virtually any source. Furthermore, they can do this work using the familiar Microsoft Excel 2010 spreadsheet software interface.

Now users can easily access traffic safety information and stimulus project spending data. In addition, the SQL Server 2008 R2 architecture enforces data and access security.  Ken Slay Project Manager Mississippi Department of Transportation

MDOT uses SQL Server 2008 R2 to provide fast access to large documents stored externally and to generate reports featuring geospatial maps. With SQL Server 2008 R2, MDOT provides a highly available and scalable solution, reduces the burden on its IT staff, and enhances its users’ data analysis capabilities.

MDOT uses the RBS and FILESTREAM features of SQL Server 2008 R2 to provide users with fast access to large documents stored in a reliable, secure, cost-effective external storage solution. “We have already heard anecdotal stories of MDOT employees who have saved hours because they have much faster access to documents through SQL Server 2008 R2,” says Slay. “For example, one of our executives needed to quickly find specific information related to 24 stimulus projects. By entering a simple phrase into FAST Search Server 2010 to search documents stored in SQL Server 2008 R2, in minutes he found the information that previously would have taken him a couple of days of digging through files.”

MDOT uses the geospatial reporting capabilities of SQL Server 2008 R2 to easily add analytical mapping information to reports. “With SQL Server 2008 R2 Reporting Services, our business analysts can now quickly draw up a report and drop in a map that takes advantage of existing geospatial information,” says Slay. “They can now produce publicly available reports that highlight, for example, the exact road that’s being repaved.”

MDOT can also retrieve data based on geospatial queries and can use other capabilities of SQL Server 2008 R2 Reporting Services to export reports in a variety of formats, including Excel 2010, PDF, or Microsoft Word 2010 documents. “Our reporting specialists are able to do more, faster,” says Slay.

MDOT uses SQL Server 2008 R2 to provide high availability and scalability for its databases. “Because SQL Server 2008 R2 powers both public Internet content and the day-to-day business of thousands of employees, the service level we had to provide on this project was as high as any application in the organization. And that includes our financial, construction management, and engineering systems,” says Slay. “We know that SQL Server 2008 R2 provides that high availability.”

The solution is also scalable to meet future expansions in data storage. “If at any point we need to, we can add a third node to our SQL Server 2008 R2 cluster to store additional data,” Slay says. “Using SQL Server 2008 R2, we have a scalable business platform that not only meets the needs of the taxpayers of Mississippi today, but also provides us with the ability to grow to meet future departmental requirements while maximizing the value of the investment.”

The solution also reduces time spent on several common IT tasks. For example, with RBS and FILESTREAM, IT staff can manage data more efficiently. With System Center Data Protection Manager 2010, database administrators will save time by gaining granular control over backups and restores. And reporting staff is already saving time by using the expanded features of SQL Server 2008 R2 Reporting Services.

MDOT employees will take advantage of PowerPivot for Excel to do more self-service BI analysis. “Now users can easily access traffic safety information and stimulus project spending data,” says Slay. “In addition, the SQL Server 2008 R2 architecture enforces data and access security, while the SQL Server PowerPivot for Excel provides an intuitive interface in a familiar tool.”

“With SQL Server PowerPivot for Excel, our safety engineers can mine data in SQL Server 2008 R2 to help prevent road accidents,” says John Simpson, Chief Technology Officer at MDOT. “They can see whether certain road features, lighting, or incidents of impaired driving are causing accidents at specific locations and can design new safety strategies based on those insights. We expect to use SQL Server PowerPivot for Excel and SQL Server 2008 R2 to save lives.”

Microsoft SQL Server 2008 R2 delivers higher scalability for mission-critical environments, more efficient IT, and expanded reporting and analytics through self-service business intelligence.

For more information about Microsoft SQL Server 2008 R2, go to:
www.microsoft.com/sqlserver/2008/en/us/R2-editions.aspx

For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers in the United States and Canada who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to:
www.microsoft.com

For more information about LifeCycle Solutions products and services, call (601) 914-2906 or visit the Web site at:
www.lifecycle-solutions.com

For more information about Mississippi Department of Transportation products and services, call (601) 359-7001 or visit the Web site at:
www.gomdot.com