ATD Article_Profit Magazine (ATD gets better insight and performance with smart data archiving)

1
By Fred Sandsmark
American Tire DistributorsTony Vaden, CIO,
andAngelic Gibson, Director ofIT Operations
Historical Data Takes a Backseat
ATD gets better insight and performance with
smart data archiving.
an 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
inactive data, typically retained for regulatory
reasons, takesup valuable space in the databases
that support enterprise IT,degrading the
performance of enterprise applications.
Olsonpoints out that inactive data has no
intended business use, but
equipment, and shop supplies based in
Huntersville, NorthCarolina. The good thing, in
ATDs case, was data.
CIOs have little choice but to keep the data in
the system. Youcan see whats happening here
were slowly grinding these
We are a business that starves for information,
especially insales where were trying to look at
projections for growth andhow we properly
position ourselves for the future, says
TonyVaden, CIO at ATD. We have a huge amount of
data that weneed to turn into information, and
thats where the struggle is.
databases to a halt, says Olson.
That was certainly so at ATD, where performance
issuesmade business applications less effective
than bald tires ona rainy street. In 2010 ATD IT
staff began using database
ATDs challengetoo much data yielding not
enoughinformationis common, according to Jack
Olson, author of
archiving to pare down years of data into a
useful, manageablepool while still retaining
required records. The archiving project,which
leveraged Oracle partner Solix Enterprise
Database
Database Archiving How to Keep Lots of Data for
a Very LongTime (Morgan Kaufmann, 2008) and CEO
of data archiving
Archiving (part of the Solix Enterprise Data
Management Suite,was coupled with a hardware
refresh. Together, archiving andupdated hardware
improved performance of ATDs Oracle
consultancy SvalTech. Olson reports that studies
show that 70percent of the data in operational
databases is inactive. That
p r o f i t t h e e x e c u t i v e s g u i
d e t o o r a c l e a p p l i c a t i o n s
45
2
gtgtSNAPSHOT
E-Business Suite implementation,made application
development andtesting and disaster recovery
opera-tions easier, and paved the way forfuture
IT initiatives.
ARCHIVING SHIFTS INTO HIGH GEAR
American Tire Distributors (ATD)atd-us.com
The size of ATDs database alsothreatened to
stall a planned hard-ware upgrade. ATD
executiveswanted to upgrade old
UNIX-basedservers to newer hardware
runningLinux. The first time we did ourtrial
run, it took more than sevendays to migrate,
Gibson recalls.That was pretty daunting.
Thattime frame was shaved down to 60hours with
some additional effort,but that was still too
long for ATD tobe out of operation.
Location Huntersville, North CarolinaIndustry
Tire wholesaler/distributorEmployees 2,300
Customers 71,000
WORKING AROUND THE SYSTEM
Annual delivery miles 28 million
Oracle products Oracle E-Business Suite
11i,Oracle Real Application Clusters 11g,
OracleDatabase 11g
ATDs data growth was driven byseveral factors.
First, the companyhas expanded rapidly through
Partner products Solix Enterprise
DatabaseArchiving, part of the Solix Enterprise
DataManagement Suite
acquisitions. ATD was 13 on Inc.Magazines 2009
list of fastest-growingprivate companies in
America asranked by revenue its 2008 revenue
of US2 billion was up 30.3 percent over three
years. The sheer number of products that ATD
distributes was asecond contributor to the data
growth. The companys catalogcontains
approximately 40,000 SKUs, ranging from tires
andwheels for a variety of vehicles to
electronic tire pressuresensors and specialized
tools. Many of these products are rela-tively
new offerings for ATD, added to the companys
catalog inrecent years as cars have grown more
sophisticated.
So ATD tapped the brakes on the hardware upgrade
andshifted gears to its database archiving
project. Though archivingwas initially intended
to improve application performance,Gibson
realized that a smaller database would speed up
migra-tion to the new hardware. Definitely the
driver to get archivingdone more quickly was
our hardware refresh, she recalls.
ATD selected archiving technology from Oracle
partner andindependent software vendor Solix
Technologies after evaluat-ing three different
products. Vaden liked Solix established
part-nership with Oracle and its products
validated integration withOracle E-Business
Suite.
A third factor was the expansion of ATDs Oracle
E-BusinessSuite environment, which launched in
2005. Oracle
E-Business Suite is our core, Vaden says. A lot
of our pro-cesses are built around the Oracle
product suite.
Long-term support was critical to us, adds
Gibson. Wefelt like Solix wasnt a company that
was going to disappearfrom the marketplace. We
got to meet their support team andgot a very
good understanding of how their support cycle
The dozen Oracle E-Business Suite modules ATD
cur-rently runs have been phased in over several
years, and ATDsOracle applications environment
continues to grow in sizeand complexity. The
next planned milestone is a staged rolloutof
Oracle Warehouse Management to ATDs 85
distributioncenters across the U.S. When this
move is complete at the endof 2011, ATDs Oracle
user base will have quadrupled from600 users to
2,500.
works. We also had confidence that they would
meet our needsfor customization.
Solix technological underpinnings also appealed
to theATD team. We were comfortable with their
approach toward
archiving, Gibson says. There are many
different technologi-cal ways of archiving in an
Oracle E-Business Suite environ-ment. We much
preferred Solix approach, using the logic
thatOracle has built into their application and
certified.
With so many changes, maintaining application
perfor-mance has become difficult. You start
off at 300 GB, and yourdatabase keeps growing
year after year, recalls Angelic Gibson,ATDs
director of IT operations. We got to a point
where wewere constantly tuning SQL, just
struggling with the volume ofdata in our primary
tables. At its peak, ATDs Oracle environ-ment
consumed 3 TB of storage.
Raghu Kodali, Solix vice president of product
managementand strategy, says Solix Oracle
Validated Integration and flex-ible 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
Data growth caused ripple effects throughout the
company.Complete backups took 20 hours. Oracle
Recovery Managerclones took as long as four
days. Nightly batch processes wereencroaching on
the workday. (An extreme example when oneATD
customer acquired another, merging the two
companiesinventory data could take seven
hours.)
both, he explains. This helps customers like
ATD archive theright set of transactional
business objects based on user-definedbusiness
rules.
With help from a Solix team, ATD first ran the
archive in itsdevelopment environment. We
worked out the kinks there,and then we went
through full user acceptance testing,
Gibsonrecalls. Business users signed on and
verified that the data waspresent and viewable
in the archive according to the users spe-cific
responsibilities as established in Oracle
E-Business Suite.
When overnight processes extended into the next
businessday, employee productivity suffered.
Employees at ATDs FieldSupport Services office
were sometimes forced to wait untilas late as 11
a.m. to start work. Delivery route planning
wasdelayed, which affected customer
satisfaction. In some cases,
Solix Kodali says that the product extends the
OracleE-Business Suite responsibilities
framework so the privilegesassociated with
users Oracle E-Business Suite logins can be
daily processing was being held over until
weekends. We werereally starting to make the
business work around the system,Gibson says.
Thats where it became problematic.
used to access data in the Solix archive. This
simplifies security
46 November 2010
3
and eliminates the need for users to learn an
additional tool. As each Oracle E-Business Suite
modules archive was
it now runs in one minute.
The smaller operational database also paves the
way forfuture IT initiatives. ATD is migrating
some of its modulesto Oracle E-Business Suite
12, and in 2012 executives planto adopt Oracle
Fusion Middleware. Vaden says database
approved and documented, it was migrated into
produc-tion. General ledger, accounts
receivable, inventory, and
pricing modules were moved over the course of
three months,from January to March 2010.
Archiving those four modulesdecreased the size
of the Oracle E-Business Suite database by
archiving will make those transitions easier.
The more stream-lined our current products are,
the better our ability to makedecisions about
them, he says. From my perspective, this
25 percent, Gibson says, and when the accounts
payable, pur-chasing, and costing modules are
completelater this yearthe database will be 40
percent smaller.
helps put us on the right path for upgrades.
Vaden also notes that disaster recovery at ATD
works betterwith a smaller database. I hope
well never need to recoverfrom a failure or a
disaster, he says, but when were setting
arecovery point objective and a recovery time
objective, the lessdata we have to recover, the
faster were back in production.
With this database reduction in place, ATD
returned toits hardware refresh, completing it
in September 2010. Thesmaller database migrated
to the new platform in about 40
hours. Had we not archived, we would have to be
completelydown for more than 60 hours, Gibson
says. Thats a loss ofbusiness for us. Solix
was a huge success factor in our ability
tomigrate over a weekend.
Having a smaller database also helps with new
applica-tion development. Were constantly in a
development cycle,
Gibson explains. Were developing and pushing
code quickly,so we need to have fresh data in
our testing and developmentinstances. When it
takes four days to do a clone, its
impracticalto meet the demands the business puts
on us. So by decreasingthe time in a cloning
scenariowere down to about a day anda half
nowwe can get clones done on the weekend.
BENEFITS DOWN THE ROAD
ATD got an expected performance boost in Oracle
E-BusinessSuite from database archiving. We see
a 50 to 60 percent
performance improvement in our nightly batch
process-ing, with no SQL tuning at all, Gibson
says. Its huge.
Revisiting one extreme example the seven hours
of process-ing formerly required to merge two
customers inventory
SET FOR THE LONG HAUL
Database archiving has also helped ATD move
toward a lifecyclemanagement approach to data,
meaning that the company
data was reduced to one hour, and with further
SQL tuning
Establishing Retention Policies
E
very organization that deploysdatabase archiving
needs to estab-
to collect in the production database. It works
like this Archiving of data
one of the reasons we went with Solix,Gibson
says. Solix allows us to view
lish two data retention policies howlong (or
how much) data will be keptin the active
database, and how longarchived data will be
retained before itis deleted altogether.
from two years prior is done at theend of
January, after the close of thecompanys fiscal
year. (For example,data from calendar year 2008
was
the data through one single system, asopposed to
our users having to pull itup somewhere else.
Raghu Kodali, vice president ofproduct
management and strategy atSolix, says ATDs
three-tier arrange-ment is typical for Solix
users. As thedata ages out of active archive,
it canbe moved into a highly compressedand
immutable mode, he explains.Even then, it can
still be searched,reported, and audited, and can
onlybe deleted when the retention
periodexpires. This helps customers
mitigaterisk from a compliance point of view.
archived beginning in February 2010.)The
archiving and purging process,which happens
during evening and
American Tire Distributors (ATD)initially
established different lengthsof time for active
data in each of its
weekend maintenance windows, takesabout eight
weeks and removes a
Oracle E-Business Suite modules, butthis proved
impractical. A lot of ourusers have to go
between modules
terabyte of data when it is complete,
15 months worth of data remains inthe active
production database.
when they do their analyses, explainsAngelic
Gibson, ATDs director of IToperations. They
would find two
The next annual archive, in February2011, will
remove data from calendaryear 2009. Gibson notes
that, becauseof upgraded hardware, the
archivingprocess will probably take four
weeksinstead of eight.
years worth of data in one modulebut only nine
months in another. Theywere getting really
confused.
Some companies will need to keeptheir archived
data much longer thanATD does, says Jack Olson,
author ofDatabase Archiving (Morgan
Kaufmann,2008). It varies by application, by
So ATD established that a minimumof 13 months of
historical data wouldbe kept active for all
modules. But
Data will spend seven years in ATDsarchive and
then be removed to a disk-based backup system.
While its in thearchive, that data remains
available tobusiness users who need it. Thats
because archiving is done annuallyrather than on
an ongoing basis, asmuch as 24 months of data is
allowed
industry, and by country, he explains.You need
to find all that apply to you,and then pick
the longest line.
p r o f i t t h e e x e c u t i v e s g u i
d e t o o r a c l e a p p l i c a t i o n s
47
4
Oracle E-Business Suite Moving Up
L
ike thousands of organizationsworldwide,
American Tire Distribu-
and master data management mod-ules.
Enhancements in Oracle E-
attachments, look-ahead searching,and the
ability to embed rich content
tors (ATD) has built its business prac-tices
around Oracle E-Business Suite.And like many
Oracle E-Business Suitecustomers, ATD is taking
advantage ofsignificant new features and
functional-ity by upgrading to Oracle
E-BusinessSuite 12.
Business Suite 12.1 include supportfor
high-volume warehouse operations,including
demand-based forward pick
such as business intelligence reportsand
dashboards.
We continue to add new function-ality to Oracle
E-Business Suite 12.1on a regular basis through
easy-to-consume release update packs,
saysOgnjen Pavlovic, vice president of
replenishment sourcing enhancementsto
streamline sourcing processes,
enforce compliance, reduce risk, andachieve
savings and the ability to viewfully loaded
estimated costs (includ-ing transportation,
duties, and taxes)
Oracle E-Business Suite 12, firstavailable in
January 2007, includedarchitectural improvements
to OracleFinancials to support global and
Oracle E-Business Suite productstrategy. Oracle
E-Business Suite
across the complete supply chain usingOracle
Landed Cost Management.
12.1.3, available in August 2010, com-bines
statutory and regulatory updateswith other
enhancements across all
shared-service operations, improveoperational
efficiencies, and reducerisk. Specifically, it
introduced a newcentralized architecture that
standard-izes and simplifies the financial
infra-structure across an entire enterprise.Its
subledger accounting, ledgers,
Organizations with field serviceoperations can
leverage improved dis-patch center operations,
more-effective scheduling of technicians,and
streamlined service requestcreation and
management in OracleE-Business Suite 12.1.
Supply,demand, and design chains wereoptimized
with the new value chainplanning capabilities in
Oracles sup-ply chain management solutions,
anddeduplication and merging were addedto
Oracles master data managementsolutions.
Usability was improvedacross the board, with
mouse-overand pop-up selection, inline editing
of
product areas.
Pavlovic notes that Oracle E-Business Suite 11i
Release 11.5.10,the terminal release of Oracle
E-Business Suite 11i, will transition
fromPremier Support to Extended Supportin
November 2010. After three years inExtended
Support, Oracle E-BusinessSuite 11i Release
11.5.10 will enterSustaining Support.
and ledger sets ensure adherence toa single set
of accounting rules acrossan enterprise while
supporting faster,simpler period-end closings.
Oracle E-Business Suite 12.1,released in May
2009, enhanced otherareas including the human
resources,projects, service management,
pro-curement, supply chain management,
If you are still on Oracle E-BusinessSuite 11i,
Oracle recommends that youplan and execute your
Oracle E-
Business Suite 12.1 upgrade within thesupport
window, Pavlovic says.
is able to exercise more control over datas
creation, storage,and deletion. Going through
that lifecyclefrom productionto archiving to
eliminationis definitely what were
puttingtogether as a long-term strategy, Vaden
says.
base archiving, Olson says.
He adds that database archiving vendors are
steadilyimproving their products, although none
is perfect yet. I thinkthe best choice is to
pick a vendor whose solution is closestto the
problem you have, and then work with that vendor
tomake up the difference, Olson advises. If
you do that today,youll help the vendor get to
the better product faster.
Such long-term thinking is important, Olson says,
becausethe problem of data growth is only going
to get worse. Sayyoure keeping data for seven
years, and one day the govern-ment says you have
to keep that data for 25 years, Olson
Which is exactly the route that ATD chose. Im
confidentthat we picked the right product and
have the right solutionfor our customers, Vaden
says. Thats just the right amount ofa good
thing. ltgt
explains. From that point forward, for the next
18 years, youwill never throw a single record
away. Its all growth.
While such data growth is a common motivator for
databasearchiving, Olson says that the
technology has less-obvious usesas well. There
is no doubt that database archiving came
aboutbecause of operational performance problems
of overloaded
FRED SANDSMARK is a freelance writer in the San
Francisco Bay Area and a regularcontributor to
Profit.
databases, he says. But people are discovering
a lot of othermotivators, and theyre using it
in places where they dont havea data glut
problem.
gtgt For more information
Those places include retiring inactive
applications (thuseliminating expensive legacy
hardware), maintaining real-timesnapshots of
financial transactions for regulatory
purposes,and easing mergers and acquisitions.
Good technologies tendto find new uses, and
this is definitely happening with data-
Oracle E-Business Suite
oracle.com/us/products/applications/ebusinessSoli
x Enterprise Data Management Solutions
solix.com
48 November 2010