Historical Data Takes a Backseat

ATD gets better insight and performance with smart data archiving.

by Fred Sandsmark, November 2010

Can a company have too much of a good thing? The answer was yes at American Tire Distributors (ATD), a fast-growing distributor of tires, custom wheels, service equipment, and shop supplies based in Huntersville, North Carolina. The good thing, in ATD’s case, was data.

“We are a business that starves for information, especially in sales where we’re trying to look at projections for growth and how we properly position ourselves for the future,” says Tony Vaden, CIO at ATD. “We have a huge amount of data that we need to turn into information, and that’s where the struggle is.”

ATD’s challenge—too much data yielding not enough information—is common, according to Jack Olson, author of Database Archiving: How to Keep Lots of Data for a Very Long Time (Morgan Kaufmann, 2008) and CEO of data archiving consultancy SvalTech. Olson reports that studies show that 70 percent of the data in operational databases is inactive. That inactive data, typically retained for regulatory reasons, takes up valuable space in the databases that support enterprise IT, degrading the performance of enterprise applications. Olson points out that inactive data has no intended business use, but CIOs have little choice but to keep the data in the system. “You can see what’s happening here: we’re slowly grinding these databases to a halt,” says Olson.

That was certainly so at ATD, where performance issues made business applications less effective than bald tires on a rainy street. In 2010 ATD IT staff began using database archiving to pare down years of data into a useful, manageable pool while still retaining required records. The archiving project, which leveraged Oracle partner Solix’ Enterprise Database Archiving (part of the Solix Enterprise Data Management Suite), was coupled with a hardware refresh. Together, archiving and updated hardware improved performance of ATD’s Oracle E-Business Suite implementation, made application development and testing and disaster recovery operations easier, and paved the way for future IT initiatives. 

Working Around the System

ATD’s data growth was driven by several factors. First, the company has expanded rapidly through acquisitions. ATD was #13 on Inc. Magazine’s 2009 list of fastest-growing private companies in America as ranked by revenue; its 2008 revenue of US$2 billion was up 30.3 percent over three years.

The sheer number of products that ATD distributes was a second contributor to the data growth. The company’s catalog contains approximately 40,000 SKUs, ranging from tires and wheels for a variety of vehicles to electronic tire pressure sensors and specialized tools. Many of these products are relatively new offerings for ATD, added to the company’s catalog in recent years as cars have grown more sophisticated.

A third factor was the expansion of ATD’s Oracle E-Business Suite environment, which launched in 2005. “Oracle E-Business Suite is our core,” Vaden says. “A lot of our processes are built around the Oracle product suite.”

The dozen Oracle E-Business Suite modules ATD currently runs have been phased in over several years, and ATD’s Oracle applications environment continues to grow in size and complexity. The next planned milestone is a staged rollout of Oracle Warehouse Management to ATD’s 85 distribution centers across the U.S. When this move is complete at the end of 2011, ATD’s Oracle user base will have quadrupled from 600 users to 2,500.

With so many changes, maintaining application performance has become difficult. “You start off at 300 GB, and your database keeps growing year after year,” recalls Angelic Gibson, ATD’s director of IT operations. “We got to a point where we were constantly tuning SQL, just struggling with the volume of data in our primary tables.” At its peak, ATD’s Oracle environment consumed 3 TB of storage.

Data growth caused ripple effects throughout the company. Complete backups took 20 hours. Oracle Recovery Manager clones took as long as four days. Nightly batch processes were encroaching on the workday. (An extreme example: when one ATD customer acquired another, merging the two companies’ inventory data could take seven hours.)

When overnight processes extended into the next business day, employee productivity suffered. Employees at ATD’s Field Support Services office were sometimes forced to wait until as late as 11 a.m. to start work. Delivery route planning was delayed, which affected customer satisfaction. In some cases, daily processing was being held over until weekends. “We were really starting to make the business work around the system,” Gibson says. “That’s where it became problematic.” 

Archiving Shifts into High Gear

The size of ATD’s database also threatened to stall a planned hardware upgrade. ATD executives wanted to upgrade old UNIX-based servers to newer hardware running Linux. “The first time we did our trial run, it took more than seven days to migrate,” Gibson recalls. “That was pretty daunting.” That time frame was shaved down to 60 hours with some additional effort, but that was still too long for ATD to be out of operation.

So ATD tapped the brakes on the hardware upgrade and shifted gears to its database archiving project. Though archiving was initially intended to improve application performance, Gibson realized that a smaller database would speed up migration to the new hardware. “Definitely the driver to get [archiving] done more quickly was our hardware refresh,” she recalls.

ATD selected archiving technology from Oracle partner and independent software vendor Solix Technologies after evaluating three different products. Vaden liked Solix’ established partnership with Oracle and its products’ validated integration with Oracle E-Business Suite.

“Long-term support was critical to us,” adds Gibson. “We felt like Solix wasn’t a company that was going to disappear from the marketplace. We got to meet their support team and got a very good understanding of how their support cycle works. We also had confidence that they would meet our needs for customization.”

Solix’ technological underpinnings also appealed to the ATD team. “We were comfortable with their approach toward archiving,” Gibson says. “There are many different technological ways of archiving in an Oracle E-Business Suite environment. We much preferred Solix’ approach, using the logic that Oracle has built into their application and certified.”

Raghu Kodali, Solix vice president of product management and strategy, says Solix’ Oracle Validated Integration and flexible archiving technology appeal to many customers. “Our approach allows customers to use Oracle E-Business Suite-provided routines, custom-built rules, or a combination of both,” he explains. “This helps customers like ATD archive the right set of transactional business objects based on user-defined business rules.”

With help from a Solix team, ATD first ran the archive in its development environment. “We worked out the kinks there, and then we went through full user acceptance testing,” Gibson recalls. Business users signed on and verified that the data was present and viewable in the archive according to the users’ specific responsibilities as established in Oracle E-Business Suite.

Solix’ Kodali says that the product extends the Oracle E-Business Suite responsibilities framework so the privileges associated with users’ Oracle E-Business Suite logins can be used to access data in the Solix archive. This simplifies security and eliminates the need for users to learn an additional tool.

As each Oracle E-Business Suite module’s archive was approved and documented, it was migrated into production. General ledger, accounts receivable, inventory, and pricing modules were moved over the course of three months, from January to March 2010. Archiving those four modules decreased the size of the Oracle E-Business Suite database by 25 percent, Gibson says, and when the accounts payable, purchasing, and costing modules are complete—later this year—the database will be 40 percent smaller.

With this database reduction in place, ATD returned to its hardware refresh, completing it in September 2010. The smaller database migrated to the new platform in about 40 hours. “Had we not archived, we would have to be completely down for more than 60 hours,” Gibson says. “That’s a loss of business for us. Solix was a huge success factor in our ability to migrate over a weekend.” 

Benefits Down the Road

ATD got an expected performance boost in Oracle E-Business Suite from database archiving. “We see a 50 to 60 percent performance improvement in our nightly batch processing, with no SQL tuning at all,” Gibson says. “It’s huge.” Revisiting one extreme example: the seven hours of processing formerly required to merge two customers’ inventory data was reduced to one hour, and with further SQL tuning it now runs in one minute.

The smaller operational database also paves the way for future IT initiatives. ATD is migrating some of its modules to Oracle E-Business Suite 12, and in 2012 executives plan to adopt Oracle Fusion Middleware. Vaden says database archiving will make those transitions easier. “The more streamlined our current products are, the better our ability to make decisions about them,” he says. “From my perspective, this helps put us on the right path for upgrades.”

Vaden also notes that disaster recovery at ATD works better with a smaller database. “I hope we’ll never need to recover from a failure or a disaster,” he says, “but when we’re setting a recovery point objective and a recovery time objective, the less data we have to recover, the faster we’re back in production.”

Having a smaller database also helps with new application development. “We’re constantly in a development cycle,” Gibson explains. “We’re developing and pushing code quickly, so we need to have fresh data in our testing and development instances. When it takes four days to do a clone, it’s impractical to meet the demands the business puts on us. So by decreasing the time in a cloning scenario—we’re down to about a day and a half now—we can get clones done on the weekend.” 

Set for the Long Haul

Database archiving has also helped ATD move toward a lifecycle management approach to data, meaning that the company is able to exercise more control over data’s creation, storage, and deletion. “Going through that lifecycle—from production to archiving to elimination—is definitely what we’re putting together as a long-term strategy,” Vaden says.

Such long-term thinking is important, Olson says, because the problem of data growth is only going to get worse. “Say you’re keeping data for seven years, and one day the government says you have to keep that data for 25 years,” Olson explains. “From that point forward, for the next 18 years, you will never throw a single record away. It’s all growth.”

While such data growth is a common motivator for database archiving, Olson says that the technology has less-obvious uses as well. “There is no doubt that database archiving came about because of operational performance problems of overloaded databases,” he says. “But people are discovering a lot of other motivators, and they’re using it in places where they don’t have a data glut problem.”

Those places include “retiring” inactive applications (thus eliminating expensive legacy hardware), maintaining real-time snapshots of financial transactions for regulatory purposes, and easing mergers and acquisitions. “Good technologies tend to find new uses, and this is definitely happening with database archiving,” Olson says.

He adds that database archiving vendors are steadily improving their products, although none is perfect yet. “I think the best choice is to pick a vendor whose solution is closest to the problem you have, and then work with that vendor to make up the difference,” Olson advises. “If you do that today, you’ll help the vendor get to the better product faster.”

Which is exactly the route that ATD chose. “I’m confident that we picked the right product and have the right solution for our customers,” Vaden says. That’s just the right amount of a good thing.

For More Information
Oracle E-Business Suite: Moving Up
Establishing Retention Policies
Oracle E-Business Suite
Solix Enterprise Data Management Solutions

 


Fred Sandsmark is a freelance writer in the San Francisco Bay Area and a regular contributor to Profit.

 

 
Oracle 1-800-633-0738