No matter how much rigor a company may bring to it, building a data warehouse remains an act of faith. It can also entail a lot of pain.
So what prompts a company to do it? Pain.
That was the case at Impax Laboratories, which embarked on a data-warehousing project in 2006 after determining that printing out reports from two different ERP systems and manually entering the data into spreadsheets for analysis was impeding its ability to make smart business decisions.
Data warehouses synthesize data from disparate sources and present it in an easy-to-comprehend manner, often via a “dashboard” or similar data-visualization technique. This, at least in theory, promotes more-informed decision-making. They are often at the heart of business-intelligence (BI) and analytics systems, but many small and midsize companies believe that building a data warehouse is beyond their capabilities. After all, it entails acquiring technology, migrating databases, writing integration code, validating data accuracy, layering BI tools on top of the underlying warehouse, and cajoling and training employees to use the new system.
But, daunting as such projects may be, they can drive great — if difficult to quantify — value. At Impax Labs, a $358 million, publicly held specialty pharmaceutical company, the effort began as a relatively low-budget affair. It had just two dedicated employees, including project leader Jonathan Retano, a former Federal Reserve financial analyst whose five-year tenure at Impax had included stints in both sales operations and accounting. He moved to the IT department as associate director for business intelligence.
“I’m certainly not a traditional IT person, but with my background I had an understanding of the pain points for users,” Retano says. The goals of the program, he explains, were to make it easier for users to get access to data and to generate reports; establish a single, accurate version of the data; enable data sharing companywide; and combine data from different sources to provide a 360-degree view of the business.
Getting there has involved a long procession of small steps. Retano began by researching best practices as described in books by experts like Ralph Kimball and lining up an advisory firm, ISA Consulting. The first half of 2007 was devoted to buying and customizing IBM’s Cognos BI software suite for data extraction, modeling, and reporting, and then setting up the architecture (using Microsoft’s SQL Server as the platform).
That established a foundation that allowed Impax to cross a major hurdle: conducting a test run to make sure employees would embrace the concept. The test demonstrated how they could easily generate two reports they used often, on daily sales activity and back orders. Once employees compared the new reports with what they had been getting, they were enthusiastic. “It was a way to say, ‘Here’s what we can do. If you like it, we can continue and add more stuff,’” Retano says.
Indeed, “add more stuff” has become a rallying cry for the project. With an initial thumbs-up from employees, Impax marked 2008 by rolling out new BI capabilities one after the other: information on products, customers, shipments, and credit/debit memos because of their importance to many areas of the business, then the inclusion of manufacturing priorities such as inventory transactions and balances. Also added was market-share data from external sources, so that someone reviewing a product’s net sales and margins could factor in, say, new competitors entering the market. The system’s capabilities were expanded every quarter (see the chart at the end of this article), and Retano says that it continues to evolve and expand.