records loaded with Automate Studio
Automate Studio scripts developed
global business users
In the competitive industry of outdoor apparel, Columbia Sportswear is constantly striving to develop new technologies and advancements that can help people more comfortably enjoy being outdoors. Among its many products, the OutDry Extreme ECO brand1 — the company’s breathable, waterproof, eco-conscious rainwear technology — has repeatedly received top industry awards2 since its launch in 2017. To continue to excel in this market, the business needs to not only create high-tech products, but also maintain cutting-edge technology internally, so it can deliver products faster to the outdoor enthusiasts who depend on them.
To achieve these goals, in 2010, Columbia Sportswear embarked on the largest initiative in its more than 80-year history: the Global Business Transformation (GBT) project. This project was a framework of processes and systems designed to drive the company’s business practices on the path to operational leadership. This transformation primarily involved moving the wholesale infrastructure — along with pieces of its retail infrastructure — into the organization’s new SAP ERP-based landscape, which is currently the SAP Apparel and Footwear industry solution (version 6.08). Additionally, the initiative included major upgrades to the company’s non-SAP systems, including systems for planning, product lifecycle management, and warehouse management.
“The GBT initiative advanced our system landscape to that next level and put Columbia Sportswear in a position to better service our partners and customers into the future,” says Holly Day, Data Conversion Manager at Columbia Sportswear, who managed all the SAP data conversions during the initiative. “The more efficiently we can do our work, the faster we can get our product out there to the consumer, and that’s really what we were striving to do through this project.”
Outdoor apparel & footwear
- 7,800+ employees, $2,800 million+ in revenue (2018)
- Brands include flagship Columbia Sportswear, Mountain Hardwear, SOREL, prana, and Pacific Trail
- Operations divided into four segments: the US, Latin America and Asia Pacific, EMEA, and Canada
- Manufacture in 20+ countries, sell to 7,300+ wholesale customers, and operate 530+ outlet and retail stores
- SAP Apparel and Footwear
- SAP Business Warehouse
“Anyplace where we are going in the direction of an agile methodology — where we need to be nimbler and solve business problems faster — Automate Studio is a good solution to have in our arsenal.”Holly Day Data conversion manager
Putting clean data in the new SAP system, quickly
The GBT project involved five phased “releases” of regional rollouts of SAP Apparel and Footwear. Each release was tied to a geographic region or area of the business, and collectively involved legacy master and transactional data conversions spanning 19 countries, 13 languages, and multiple time zones. Because the legacy systems were outdated and highly customized, a large amount of the data in these systems were in formats that didn’t easily integrate with the new SAP system, according to Day. “A lot of data needed to be transformed to work in our new landscape, which requires more specific data requirements than our legacy systems did,” she says. “Our data conversion work supported data cleansing within the business, providing an opportunity to clean up our data across all our regions — and not only cleanse the data, but also delete old transactions and purge inactive data elements. We didn’t want a lot of old, unusable data in our pristine new system.”
This level of data cleansing would not only help the business obtain higher data quality and better visibility into its financial and supply chain data, for example, but also trickle down to benefit the end user, since the data cleanup also involved consolidating or eliminating duplicate records.
While the SAP rollouts and data conversions were underway, the IT organization concurrently began an effort to adopt more agile methodologies for team collaboration, data gathering, and data cleansing, and to utilize that framework to accomplish more in less time. As a result, the timelines of the releases became more compressed as the project progressed. “The first release took a little longer than one year, which is normal, but after that, our go-lives were roughly a year apart or less,” says Day. “The timeline for our final release was nine months, and instead of having our traditional number of mock conversions (three or four), we had only two. We weren’t afforded the extra runway we normally have in the beginning to help the business get up to speed.”
In response to the business and IT demand of doing more faster, the data conversion team had a ready answer. “No matter what the data conversion task was, Columbia Sportswear’s overarching strategy was always at the forefront of our minds — so our data conversion team was always looking for the best ways to connect active people with their passions,” says David Flock, Senior Data Conversion Analyst at Columbia Sportswear, whose primary role in the GBT project was converting legacy transactional and master data into the new SAP landscape. “Having tools that can move high volumes of data in a timely manner so that we can accomplish our tasks in time was paramount.”
To achieve this quick alignment with the new agile philosophies and compressed project timelines, the data conversion team relied on an efficient and accurate automated solution that could upload and download data to and from SAP software. The team used Automate Studio, which not only provides for SAP integration, but also integrates with any other systems that can generate data to be captured in a spreadsheet and uploaded to the SAP system. (See the sidebar at the end of the article for more on the solution.)
At a glance
Goal: Convert data from legacy systems to SAP Apparel and Footwear throughout a global five-release initiative, while moving to an agile methodology with condensed timelines
Strategy: Implemented an automation solution by Precisely Automate to speed the data conversion process
Outcome: Automated repetitive manual tasks, supported data cleansing via Automate Studio log output, allowed heavy volumes of SAP data to load faster and easier, improved audit processes, and enabled more efficient SAP security testing
“When a user transcends to a Automate Studio developer and is a resource for the business in developing transaction scripts or queries, what a great value-added and empowered contributor that user becomes.”
Senior data conversion analyst, Columbia Sportswear
Choosing an Automation Solution
While previously employed at another apparel and footwear company, Day had worked on SAP data conversions using an automation solution that had some similarities to Automate Studio. For this reason, the concept was familiar to her when a business analyst at Columbia Sportswear recommended that she look at the more modern Precisely Automate application, which could allow for rapid data loading without a lot of programming. “The team of data conversion analysts I was putting together would wear both technical and functional hats — with business analyst and SAP skills — but I didn’t want them mired in writing ABAP code,” she says. “We needed a tool that would allow us to work within the SAP framework and make manual SAP tasks go faster. We saw Automate Studio as a good fit and a great bridge between IT and the business because the team could use it at a technically advanced level to quickly turn around solutions that would solve problems for the business.”
To introduce the data conversion team to the solution’s capabilities, Precisely consultants came to the Columbia Sportswear office to demonstrate how quickly the solution could automate the most common SAP transactions. “The demo was really impressive and gave us confidence that this software would do what we needed it to do,” Day says. “Knowing the number of conversions that would be involved for the SAP rollouts, we didn’t have time to overengineer and write a ton of code. We needed something that would give us a quick turnaround out of the box and could provide easy wins for a large number of our conversions. This way, we could spend our design time on the more complicated problems and in-depth conversions that would require more design work.”
According to Flock, the sweet spot for Automate Studio is best for automating high volumes of low- to midcomplexity transactions, and as the complexity rises, it is better suited for lower volumes of data.
To educate the six members of the data conversion team on how to best use the solution, Precisely provided initial training sessions to this core group of analysts. “The whole suite has a very intuitive user interface,” says Flock. “Minimal hands-on training and mentorship were all that was required to get new Automate Studio runners up to speed.”
Once this core team was ready to hit the ground running, the data conversion process began.
Converting data for SAP Apparel and Footwear
The data conversion process at Columbia Sportswear involved the team using an extract, transform, and load (ETL) process to extract data from the source legacy systems to a database (SQL Server) — where the data was transformed into spreadsheet format — and then load that data into the target SAP Apparel and Footwear system via Automate Studio.
Throughout the five releases of the SAP rollout, the data conversion team developed 26 Automate Studio transaction scripts to convert data from the legacy systems to SAP Apparel and Footwear. These scripts included converting records for customer master, vendor master, general ledger accounts and balances, pricing conditions, determination tables, value-added service (VAS) conditions, cost centers, and accounts receivable, among other transactions. The total number of records loaded using these Automate Studio scripts for all five releases was more than 5.4 million transactions, and the final release involved many countries, different sales organizations and statutory requirements, and multiple languages. So in addition to having a more compressed go-live to complete, this release also involved an increased level of complexity, according to Day. “However, because of Automate Studio, we were able to accomplish this very complex release within a very accelerated timeline,” she says.
In total, two thirds of the converted transactions were completed with Automate Studio scripts, which involved varying levels of complexity for each release, according to Day. “Some scripts were basic and had fewer than 10 fields to load for each of the 100,000 records, while others — customer master in particular — had close to 100 fields,” she says. “Even if the number of records wasn’t that high, there may have been a ton of fields to be loaded — such as 5,000 sold to or 50,000 ship to — so there was a big difference in complexity.”
For the remaining third — which involved the more high complexity, high-volume transactions, such as sales orders, purchase orders, and material master data conversions — the team used SAP intermediate document (IDoc) technology and the SAP Legacy System Migration Workbench tool. “With very data- heavy and complex conversions, there are lots of places where errors can occur because there’s a ton of SAP validation required,” said Day.
In addition to the challenge of having some high-complexity and customized transactions to work with throughout the project, the team was also faced with the transition of a core team member to another department — the finance expert — after the completion of the third release. “This was an area where the Automate Studio solution allowed us to continue to be effective even without deep business knowledge,” Day says. “Because our team had already developed baseline scripts, we were able to move forward and run those without being finance experts ourselves, just making minor modifications as needed. We didn’t have to be functional experts in all of the business areas to be effective with the software. With a business analyst simply walking us through any unfamiliar transactions, we could effectively create scripts that could save a large amount of time.”
Flexibility, speed, and empowerment
Timesaving is just one of the many benefits that Automate Studio has provided to Columbia Sportswear. It helps business users manage heavy loads of SAP data effectively and with ease, according to Day. “Most business users are very familiar with working in spreadsheets and so the solution is intuitive, comfortable, easy-to-adopt, and not intimidating for them to use,” she says. “Its core functionality has a lot of flexibility, which most business users could effectively run with very quickly.”
Currently, the Automate Studio user base has reached upward of 125 global business users, including those in the areas of master data, account logistics, buying, and finance, all of whom were able to quickly get up to speed on how to use the solution. “Some teams do their own development — like the master data team — and for others, our team supports whatever scriptwriting they need, providing our conversion scripts or developing new scripts for ongoing business transactions,” Day says. “We have had some big wins recently providing solutions to teams that thought they always had to do their updates manually — for example, we provided a script to automate purchase order line item updates, which is saving an enormous amount of time.”
Manually performing high-volume SAP tasks like these is not the sort of workload a typical business user is excited about tackling on a Monday morning, according to Flock. “With Automate Studio, users can look forward to getting a lot of work done in a timely manner and perhaps receive corresponding acknowledgement from management,” he says. “To take it a step further, when a user transcends to a Automate Studio developer and is a resource for the business in developing transaction scripts or queries, what a great value-added and empowered contributor that user becomes.”
The benefits of the solution’s speed and flexibility also translated to the data conversion analysts during the GBT project. “Because we could accommodate last-minute changes from the business and turn around any additional fields in a quick time frame using Automate Studio — which probably would not have worked using a more traditional programming method of conversion — we were able to mitigate scope creep,” says Day.
Before each release, the data conversion team ran mock data conversions to help provide business users with support for their data cleansing. Automate Studio run logs captured SAP error messages that the team then shared with the business. These run logs were also helpful for ensuring the project met all the compliance criteria for the corporate audit team. “We were required to have records of what we did during those releases, and we were always compliant with our audit processes in the conversion space because Automate Studio made it so easy,” says Day. “With its logging mechanism, those files are stored and referred to within our data validation forms. So for the two thirds of our conversions completed with the solution, that mechanism made the data validators’ jobs much easier.”
Another area that benefited from Automate Studio was SAP security testing, which saw significant efficiency improvements. Columbia Sportswear’s security team was revamping the SAP security roles and authorizations in the finance area, and in configuring these new roles and authorizations, SAP security users were manually going into a given SAP transaction and testing whether the role or authorization was allowing inappropriate access. Flock created a chain of 148 individual Automate Studio transaction scripts and then had the log messages captured and summarized on a single worksheet for all 148 scripts. “That way, SAP security users could launch the Automate Studio script at the push of a button, and within minutes, they would have their negative testing test performed and have a log created for whether or not they had a successful or failed result on all 148 scripts,” he says. “That was a great opportunity to support them with a challenge that they were encountering because without this Automate Studio script, it could have potentially taken days for them to log into 148 separate transactions.”
Expanding Automation use
Columbia Sportswear is looking to automate more of its business processes and is evaluating other areas where automation can be implemented for the SAP user base. “There are parts of our business that are just discovering what Automate Studio can do for them,” says Day. “For instance, inventory control is an area that we have just started venturing into building scripts for, and there are other opportunities that we haven’t tapped into yet.”
There are specific capabilities that Precisely Automate portfolio offers that Columbia Sportswear can take advantage of down the road. “In the future, I’m interested in adopting and utilizing the Automate Evolve workflow and central library functionality as a business practice, as well as discovering what opportunities lie with Automate Studio’s Direct capabilities,” Flock says. “Had I been familiar with Direct when we were working on the conversions, it could have been a way to fill the gap of some of the high-volume and complex transformations. That’s an area I’d like to explore going forward to see where we can get more benefit to the business.”
The company is currently in the process of looking at how other generic automation solutions can interface with Automate Studio software to perform separate automated tasks. For example, one part of a business process might run on a non-SAP system and be automated with a different solution that then integrates with Automate Studio. “We are just beginning to look at how these interactions could take place, but it’s something we are excited about starting,” says Day. “But because Precisely Automate is written to work optimally with SAP software, this software gives us the best of both worlds. Basically, anyplace where we are going in the direction of an agile methodology — where we need to be nimbler and solve business problems faster — Automate Studio is a good solution to have in our arsenal.”