WorkflowOne

Printing Services Firm Creating 1 Terabyte Data Warehouse with SQL Server 2005

WorkflowOne helps organizations as diverse as the American Red Cross, Roto-Rooter, and Toys “R” Us save on their printing costs by providing end-to-end solutions for printing projects, with services including design, sourcing, production, warehousing, marketing, fulfilment, and delivery. After acquiring another company, Relizon, WorkflowOne IT needed to create a centralized data store that would provide richer and more flexible reporting than what was available from its AS/400 systems. The company created an operational data store (ODS) using Microsoft® SQL Server™ 2005 Enterprise Edition (64-bit) and SQL Server 2005 Reporting Services running on an AMD Opteron™ based Egenera® BladeFrame™ system. The solution has worked so well that WorkflowOne plans to add multidimensional analytics using SQL Server 2005 Analysis Services, and expand the ODS into a 1 terabyte data warehouse.

Situation
WorkflowOne provides its customers with complete printing solutions—from design, sourcing, and production through to warehousing, marketing, fulfillment, and delivery—to help reduce the cost of documents, print, promotional products, and office consumables. The company’s comprehensive, end-to-end approach helps eliminate inefficiencies, streamline processes, and unlock hidden savings opportunities.

Everything was already there in SQL Server 2005—great ETL tools, reporting, and analytics. Jeff Noffsinger Director of Solutions Development WorkflowOne

The company, which recently acquired Relizon, has an impressive customer base, providing its services to leading organizations including the American Red Cross, Mellon Bank, Northwest Airlines, Roto-Rooter, Shell Canada, Toys “R” Us, and Wegman’s Food Markets. WorkflowOne has more than 3,000 employees, including sales representatives in more than 100 cities. The combined companies, operating as WorkflowOne, generate annual revenue of U.S.$900 million of parent company WorkflowOne Management’s $1.1 billion portfolio.

The acquisition accentuated the desire of WorkflowOne IT to create a centralized data store and a better reporting solution. The company operates with a heterogeneous IT environment. Its warehouse and inventory system, billing, and other key operations are hosted on an IBM AS/400 iSeries computer using the DB2 database. The company’s online transaction processing (OLTP), including its online shopping cart infrastructure, is hosted by an application service provider that uses Microsoft® SQL Server™ database. The company’s in-house enterprise resource planning (ERP) application is hosted on a Sun UNIX system using an Oracle database.

With such a diverse IT environment, WorkflowOne needed to create a centralized data store and reporting solution.

“As we looked at integrating our backend operations after the acquisition, we knew that we needed to create a consolidated data source to run reports against in order to provide a single version of the truth,” says Jeff Noffsinger, Director of Solutions Development at WorkflowOne. “We wanted a reporting environment that would be more flexible and timely than the green-screen reports generated from the AS/400.”

Solution
WorkflowOne deployed an operational data store (ODS) hosted on a single instance of SQL Server 2005 Enterprise Edition (64-bit) database running on the Microsoft Windows Server® 2003 Enterprise Edition (64-Bit) operating system. The ODS is hosted on an Egenera® BladeFrame™ system with four. 64-bit AMD Opteron™ processors and 16 gigabytes (GB) of RAM. Storage is on a Xiotech SAN with six terabytes of raw storage.

SQL Server, BizTalk Server, and SharePoint Server have been so easy to work with that we see this as being the foundation for our future e-commerce initiatives. Jeff Noffsinger Director of Solutions Development WorkflowOne

The multi-tier architecture includes:
ETL Tier. The company uses SQL Server 2005 Integration Services to perform extract, transform, and load (ETL) processes on some 20 tables from the iSeries – DB2 and custom VAX – Ingres systems.
Data Tier. The ODS database, which currently holds 33 million records totaling about 200 GB is expected to exceed 1 terabyte within a year as the ODS is expanded into a full data warehouse, using SQL Server Integration Services to load additional data from the company’s ERP system and other key applications.
Reporting Tier. SQL Server 2005 Reporting Services, a comprehensive, server-based solution for creating, managing, and delivering real-time information to support daily operations and decisions, is used to run reports against the ODS. Two instances of Reporting Services are used, one to support the internal employee portal, the second to support an extranet portal for partners and customers. SQL Server 2005 Report Builder will be used for ad hoc reporting accessed through the internal portal.
Analytics Tier. WorkflowOne IT plans to add an analytics tier, using SQL Server 2005 Analysis Services to create multidimensional cubes for online analytical processing (OLAP). Analysis Services organizes data from a data warehouse into cubes with precalculated aggregation data to provide rapid answers to complex analytical queries.
Presentation Tier. Users access reporting and analytics through portals created using the beta edition of Microsoft Office SharePoint® Server 2007. One portal supports internal users. A second portal supports partners and customers.

Benefits
Creating an ODS to centralize data for reporting and analytics has produced important benefits for WorkflowOne, including ease of integration, flexible reporting, and a unified view into the business. The company has also benefited from the powerful development environment from its adoption of Visual Studio® 2005 development system and the Microsoft .NET Framework.

Ease of Integration
Although WorkflowOne has some Oracle databases deployed in its environment, it chose to build its ODS using SQL Server 2005 because SQL Server provides an enterprise-grade database that is easy to integrate with other Microsoft servers and technologies. Noffsinger says the decision was easy because of its experience with other Microsoft solutions. 

The company was impressed with the SQL Server OLTP service provided by its ASP partner. It appreciated the ease with which it could deploy Microsoft BizTalk® Server to integrate its transaction data with its AS/400 backend. The company had also found it easy to work with SharePoint Server in creating portals, and had adopted Microsoft Visual Studio 2005 and the Microsoft .NET Framework version 2.0 as its internal development platform. Microsoft Visual Studio is the rapid application development tool for building next-generation Web applications and Web services. Microsoft Visual Studio .NET is the rapid application development tool for building next-generation Web applications and Web services.

“Our experience has been that it is easy to create solutions with Microsoft servers because all of the hooks you need are already built in,” says Noffsinger. “Ease of integration proved true when creating our ODS because we didn’t have to purchase and integrate any third-party applications. Everything was already there in SQL Server 2005—great ETL tools, reporting, and analytics.”

With Analysis Services and Report Builder we’re giving users the tools to let them go exploring, with drag-and-drop simplicity. Jeff Noffsinger Director of Solutions Development WorkflowOne

The ease of integration figures into the company’s future projects. “SQL Server, BizTalk Server, and SharePoint Server, have been so easy to work with that we see this as being the foundation for our future e-commerce initiatives,” Noffsinger says. “Ease of integration reduces project costs and development time. Instead of creating custom code for reporting, for example, we were able to download some Web parts to gain an interface into the Reporting Services world.  All of this combines to enhance the speed-to-value proposition.”

Flexible Reporting
The green screen reporting from the legacy system provided valuable information, but WorkflowOne IT wanted to give users a more flexible reporting system that better integrated with their work. “With the old system, the information wasn’t available within the context of the user’s working experience,” says Noffsinger. “You had to interrupt what you were doing and work with a completely different interface. With our SQL Server ODB and Reporting Services, accessed through our SharePoint Server portal, the data is completely integrated with the user’s normal productivity tools such as [Microsoft Office] Excel and [Office] Word.”

Reporting Services makes it easy to create custom reports with built-in parameterizing features that let users highly customize the scope of data returned. Integration with SharePoint Server gives users a Web-based dashboard for monitoring the data they need.

“Our users love the flexibility of generating custom reports by choosing parameters from drop-down lists,” says David Lawton, Manager, Enterprise Reporting and Analytics at WorkflowOne. “The user feedback has been very positive. They also really appreciate having so many output options. They can display their data using Excel, Word, PowerPoint, and a range of other options.”

When multidimensional cubes are deployed using Analysis Services, access to data will be even more enhanced as users gain the ability to create their own ad hoc queries using the Report  Builder function of SQL Server 2005.

“It will be easier for us, and much more valuable to our users, to give them the ability to generate their information, rather than us trying to imagine and build every kind of fixed report that someone might need,” says Noffsinger. “With Analysis Services and Report Builder we’re giving users the tools to let them go exploring, with drag-and-drop simplicity.”

Unified View Into the Business
The ODS is proving to be the unifying tool that WorkflowOne IT hoped it would be as the group  looked at unifying the newly merged companies. As the ODS is expanded into a data warehouse with the addition of ERP data, the database—and the reports and analytics that can be run against it—becomes an even more important resource.

“Bringing information from across our operations into a central SQL Server database with powerful and flexible reporting and analytics—accessible from a portal—is giving us the unified view of our business that we hoped we would achieve,” says Ian O’Brien, Vice President and Chief Technology Officer at WorkflowOne. “We think this technology is going a long way toward helping people gain a common understanding of what is happening within the company. That was definitely one of our fundamental goals:  To provide an internal and extranet portal that would provide a common mechanism for accessing data. This is giving us a common view of the truth that can be accessed by users and utilized by multiple applications.”

Our users love the flexibility of generating custom reports by choosing parameters from drop-down lists. David Lawton Manager, Enterprise Reporting and Analytics WorkflowOne

Powerful Development Environment
WorkflowOne has long developed applications internally, some of which were created using Java and other development tools and languages. The company has since adopted Visual Studio 2005 and the .NET Framework for its integrated development environment (IDE). Noffsinger says the developers appreciate the powerful development tools and the efficiency with which they can work. ASP.NET, a set of technologies in the.NET Framework for building Web applications and Web services, has been especially helpful in creating and integrating applications.

“SharePoint Server 2007 supports native code, which has made it easier for us to create management interfaces in native ASP.NET pages and then deploy these within the SharePoint framework,” says Noffsinger. “We’re very interested in using the new Workflow Foundation Runtime Components. Writing reusable activities and composing those as workflows will really help our business processes. We see this as a huge area of opportunity.”

The development environment is making it easier for the company to keep up with customer requests for customizations. “Especially in the B-to-B [business-to-business world, you see a lot of customization requests,” says Noffsinger. “Our development environment, combined with the integration built into BizTalk, SharePoint, SQL Server and the other components we’re using makes it much easier to respond to customer needs.”
 
Microsoft Server Product Portfolio
For more information about the Microsoft server product portfolio, go to:
www.microsoft.com/servers/default.mspx

Microsoft SQL Server 2005
Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. By providing high availability, security enhancements, and embedded reporting and data analysis tools, SQL Server 2005 helps companies gain greater insight from their business information and achieve faster results for a competitive advantage. And, because it’s part of Windows Server System, SQL Server 2005 is designed to integrate seamlessly with your other server infrastructure investments.

For more information about SQL Server 2005, go to:
www.microsoft.com/sqlserver  

© 2006 Microsoft Corporation. All rights reserved. This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, BizTalk, SharePoint, Visual Studio, Windows, the Windows logo, and Windows Server, are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners.

Document published December 2006