Customer Solution Case Study
/ / Weyerhaeuser Improves Processing Time by 50 Percent with Database Solution Upgrade
Overview
Country or Region:United States
Industry:Manufacturing
Customer Profile
Federal Way, Washington–based Weyerhaeuser Company manages millions of acres of commercial forestland; grows and harvests timber; and manufactures, distributes, and sells forest products.
Business Situation
Weyerhaeuser must regularly deliver information to comply with U.S. government requirements, but it needed to improve its database solution to reduce the time required to produce accurate data.
Solution
Weyerhaeuser upgraded from a Microsoft® SQL Server™ 2000–based solution to one based on SQL Server 2005 to boost performance and have more time to refine the information for delivery.
Benefits
More than 25 percent faster processing
Higher-quality information
Integration for streamlined development / “We now see the composite process average run timeof 4 or 5 hours, down from 10 to 12 hours—andwe’re running half again as much data on SQL Server 2005.”
Brad Chapman, Application Team Lead, Weyerhaeuser Company
To comply with a U.S. federal directive to prove that its pricing forlumber was fair, Weyerhaeuser Company needed a way to quickly gather and analyze information from various technology systems. The company’s Microsoft® SQL Server™ 2000–based solution worked well, but its processing speed left little time for thecompany to comply with the government’s tight timelines. Weyerhaeuser upgraded to SQL Server 2005, tuned existing processes, and now is able to complete processing 50 percent faster. The time gained leaves more room for analyzing the data, which is important to Weyerhaeuser because delivering accurate, defendable data is critical for compliance. The upgraded solution also takes advantage of integration between SQL Server 2005 andMicrosoft Visual Studio® 2005 for streamlined development, which saves steps every time Weyerhaeuser wants to change or add to its solution.
Situation
Based in Federal Way, Washington, Weyerhaeuser Company is principally engaged in the growing and harvesting oftimber; the manufacture, distribution, andsale of forest products; and real estate development and construction. The company employs 55,000 people in 18 countries andregions.
Like others in the wood products industry, Weyerhaeuser is mandated by the United States Department of Commerce to prove that it is not offering Canadian wood at unfairly low prices in the U.S. market. The Department of Commerce routinely asks Weyerhaeuser for information about the quantities and prices of wood sold, but the specific information that the company must supply varies from request to request. The time between the request and the submission date can be as little as two weeks.
Weyerhaeuser faces many challenges in responding to the Department of Commerce.One such problem is segregating Weyerhaeuser-produced lumber from thatpurchased from other manufacturers. Solving these problems requires aggregating and analyzing millions of invoices in a variety of ways.
Another difficulty is the degree of detail that the Department of Commerce requires. Within each submission, Weyerhaeuser must provide master data, such as customer number, shipping location, channel, pricing, and delivery terms, along with the segregation model, plus vendor information and the amount of all purchases on each invoice—not just the totals. To locate this data, Weyerhaeuser runs a composite process tocombine all iterations of an invoice family,which entails loading the raw, transaction-level data from millions of invoices—including thousands of pricing and quantity adjustments—into the database. (Adjustments are slight changes made by Weyerhaeuser or its customers during the sales process. For instance, a single invoice may contain five products, each of which underwent multiple adjustments.) The Department of Commerce does not want to receive every sales iteration—just the total final state of each sale. Therefore, the company must fine-tune the information through a series of time-consuming steps totry to accurately calculate the final price per product. The information must be as accurate as possible so that it matches the company’s general ledger and other accounting documentation.
To gather and process data from its varied technology environment, Weyerhaeuser used a database solution based on Microsoft® SQL Server™ 2000 running on Windows Server™ 2003, both of which arepart of Microsoft Windows Server System™ integrated server software. Although the system processed data accurately, each composite took about 14 hours because SQL Server 2000 had to retrieve data from all over the Weyerhaeuser environment, including a mainframe computer, numerous business applications, and a variety of Oracle databases.
Weyerhaeuser needed better database performance because the processes, and the subsequent refinement of the data, were happening within a short, strict timeframe; waiting 12 to 14 hours to see the effects of each small change to the data frustrated staff members and compromised their ability to accomplish their task. Processing required so much time that it affected IT staff members’ decisions as to how many changes they could make and how accurate they could make the data within the time constraints.
In addition to requiring a great deal of time for data processing and analysis, the database solution varied widely in its availability.“We couldn’t anticipate when a process would be completed,” says Brad Chapman, Application Team Lead for Weyerhaeuser Company. “We wanted to find a database solution with better system performance so that there would be less aggravation and more predictability during each submittal process. That way, we would have a greater opportunity to deliver verifiable, accurate data.”
Solution
For Weyerhaeuser, a suitable solution was easy to find. “SQL Server 2005 addressed the performance issues that we faced with our previous solution,” says Chapman. Weyerhaeuser also chose a development environment including the Microsoft Visual Studio® 2005 development system, the Microsoft .NET Framework (an integral component of the Windows® operating system that provides a programming model and runtime for Web services, Web applications, and smart client applications), and SQL Server 2005 Reporting Services. The integration between the development environment and the database also influenced Weyerhaeuser’s decision to adopt SQL Server 2005 Enterprise Edition.
Prerelease Solution Deployment
In preparation for implementing the new solution, 10Weyerhaeuser database administrators participated in a four-day, intensive, on-site training session for SQL Server 2005 given by Scalability Experts, a Microsoft Gold Certified Partner. Staff from Weyerhaeuser’sBusiness Intelligence Services group participated in business intelligence development classes offered at Microsoft facilities; they then helped educate the other group members. “The initial training was very helpful to our effort,” says Judy Williams, Manager of theDatabase Administration Team for Weyerhaeuser Company.
In May 2005, key members of Weyerhaeuser technical staff tested the SQL Server 2005 solution by taking its data to the SQL Server labs on the Microsoft campus in Redmond, Washington, and comparing test results with SQL Server 2000 benchmarks. The lab tests revealed significant performance improvements over SQL Server 2000, so Weyerhaeuser determined that it made sense to upgrade to the June 2005 prerelease version of SQL Server 2005.
In June 2005, Weyerhaeuser developers tackled the significant task of data conversion using the SQL Server 2005 Integration Services. Next, as part of an effort to improve processing time, the company completely redesigned the layouts for its disks and logical unit numbers. The new disk layout reflected the SQL Server 2005 test environment, which helped the company move from the script load process to a restore process. A consultant from Microsoft Services contributed guidance when it came time to write scripts for a number of functions. “Our Microsoft consultant was a technically astute and helpful resource for our troubleshooting and coordination efforts,” says Williams.
The company conducted a full restore of its June data load and began package conversions. Within one week, Weyerhaeuser delivered several process runs from its lab environment to outside trade consultants for data analysis.
The consultants deemed the output acceptable, and in July 2005, Weyerhaeuser moved its SQL Server 2005 server computer from the lab to one of the company’s production data centers. Weyerhaeuser restored the data from backup and ran all processes, including its July data load. Since then, the company has successfully loaded data and executed its processes numerous times. “There were no significant problems in the entire process,” says Chapman.
The System at Work
Although the size of the database changes from reporting period to reporting period, the largest volume of data has approached half a terabyte. To run the system, the SQL Server 2005 database server acquiresclose to 1.7 terabytes, including a large allocation of temporary database files and transaction logs.
At Weyerhaeuser, interaction with the SQL Server 2005 database solution is done through Web applications, query windows in the SQL Server 2005 Management Studio feature, and the Microsoft Office Excel® 2003 spreadsheet software. Controlling the application processes involves using a Web-based front end in which Weyerhaeuser can schedule jobs or run them as needed. Although the company currently is running SQL Server 2005 on 32-bit architecture, it will move to 64-bit computing in the near future.
Weyerhaeuser usesSQL Server 2005 Integration Services andSQL Server 2005 Analysis Services to collect and load vast amounts of transactional data from a range of sources, build data cubes, and refine the data on an ongoing basis. The U.S. government tends to ask Weyerhaeuser for the reports once per quarter, but it has permission to ask at any time, which means that the company must always be ready to respond.
Benefits
For Weyerhaeuser, upgrading its database solution has meant having enough time to properly analyze and modify data to accurately reflect the company’s sales. “Anytime we improve our processes, performance, and the management of our application, we can focus on the data quality,” says Chapman. “When there is trust in the data and the process, the steps we take are all steps forward. SQL Server 2005 is helping us take those steps.”
More Than 25 Percent Faster Performance
The primary benefit for Weyerhaeuser in upgrading to SQL Server 2005 is the enhanced performance. Weyerhaeuser estimates that the software itself performs 25 percent faster than its previous solution. The company realized an additional 25 percent performance improvement by tuning existing procedures. For example, it used to take the company 18 hours to produce one particular report. Now, because of the software’s cube-building process improvements, SQL Server 2005 helps Weyerhaeuser produce the same report in just seven hours.
SQL Server 2005—in particular, SQL Server 2005 Integration Services—is delivering sizable performance improvements even with a marked increase in the amount of data it is handling. “We now see the composite process average run time of 4 or 5 hours, down from 10 to 12 hours—and we’re running half again as much data on SQL Server 2005,” says Chapman. “We’ve even added three new processes since we ran our lab tests, and we’ve still cut more than an hour from our testing performance times.”
The speed of the new solution helps Weyerhaeuser perform much more detailed tracking of all the changes it makes over thecourse of compiling and refining the data. “Thanks to SQL Server 2005, we’re ableto recover from a mistake much more quickly because it’s so much easier to go back and locate the troublesome issue,”saysChapman.
Weyerhaeuser keeps all the data reports that it produces, and each new sale generates more data on a daily basis. Therefore, the company anticipates steady growth in the data volumes that the system will have to handle. “Scalability was a huge factor in our decision to move to SQL Server 2005,” says Chapman. “Our data volumes are flexible, but we see that our total database volume will only grow. It’s good to know that SQL Server 2005 will be able to support our very real expectation of long-term growth.”
Higher-Quality Information
The SQL Server 2005 solution is so much faster that it gives Weyerhaeuser more time to analyze and assure the accuracy of its data. “The SQL Server 2005 optimizer produces more consistent execution query plans. It helps developers accurately predict the processing and analysis times, which means that they can make more process runs and still meet the deadlines,” says Lydia Luk, Senior Database Administrator for Weyerhaeuser Company. “It’s certainly improved our outlook on the whole process.”
And meeting those deadlines is crucial because the financial ramifications of missing one are huge. Says Chapman, “If Weyerhaeuser cannot provide the information that the Department of Commerce requests and defend that information, we’re at the mercy of arbitrary rulings, where percentage points are in the millions of dollars.”
To help it maintain better control overitsdata, Weyerhaeuser has begun using SQLServer Reporting Services to produce “snapshot” reports and data-related notifications. “Generating snapshot reportswith the AddEvent function help us integrate Reporting Services into our data-cleansing and data-preparation processes,” says Chapman.
Integration for Streamlined Development
Having implemented the .NET Framework and Visual Studio 2005, Weyerhaeuser now can perform ongoing development for SQL Server 2005 in a more connected, manageable environment. “We don’t take the value of the integration lightly,” says Chapman. “Our internally developed front end directs the management of jobs and queries that extract data from the back end. We couldn’t have made such seamless connections without the integration between SQL Server 2005 and Visual Studio 2005. Everything works together, so we save steps every time we want to add a feature or make a change.”
Future Steps
The company’s database administrators now are working on best practices to make SQL Server 2005the company’s standard relational database management system, replacing SQL Server 2000. Once best practices have been created, the company plans to establish a shared SQL Server 2005 server environment in which developers companywide can begin working on new applications.
In the meantime, Weyerhaeuser is experimenting with having other groups in the company use SQL Server 2005, such as thedevelopment group, which is working onimplementing XML features within SQL Server 2005. New applications are being planned for deployment in the near future on SQL Server 2005 within the company’s shared data mart. Concludes Williams,“We see much potential for growth with SQL Server 2005 and look forward to it becoming our standard.”
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:
Microsoft Windows Server System
Microsoft Windows Server System is a line ofintegrated and manageable server software designed to reduce the complexity and cost of IT. Windows Server System enables you to spend less time and budget on managing your systems so that you can focus your resources on other priorities for you and your business.
For more information about Windows Server System, go to:
Microsoft Visual Studio 2005
Microsoft Visual Studio 2005 is the world’s most popular development environment for designing, developing, and testing next-generation Windows-based solutions and Web applications and services. By improving the development experience for Windows, the Web, mobile devices, and Microsoft Office, Visual Studio 2005 helps organizations deliver a variety of solutions more productively than ever before. Visual Studio Team System expands the product line with new software tools that enable greater communication and collaboration throughout the development life cycle. With Visual Studio 2005, businesses can deliver modern service-oriented solutions more efficiently.
For more information on Visual Studio 2005, go to:
msdn.microsoft.com/vstudio
Acquire Visual Studio:
msdn.microsoft.com/vstudio/howtobuy