Informatica Drives Data Integration for Upsher-Smith

by Alex Woodie

The planning and analysis group at drug-maker Upsher-Smith Laboratories was in a special kind of Excel hell. With a monthly data feed from a pharmaceutical information clearinghouse topping 50 million rows, technicians had to do some fancy summarizing or risk breaking the celebrated spreadsheet program, which bottoms out at 64,000 rows. So the company decided to implement a data warehouse, and when it came to connecting to its core OS/400 systems, including SAP, it found success with Informatica's data integration tools.

Upsher-Smith is an established pharmaceutical company based in Plymouth, Minnesota. When the company was founded, in 1919, its main product was a standardized preparation of digitalis tincture, which is used to treat heart disorders. Today, Upsher-Smith sells more than 45 products, which it researches, manufactures, and markets itself. The privately held company employs about 600 people, and brought in about $250 million last year. The company plans to reach $1 billion in revenues by 2010.

Ever since Ken Olson, sales and marketing systems manager, joined Upsher-Smith, in 1990, it has relied on the OS/400 server. "We are an AS/400 shop," he says matter-of-factly. In 2003 the company became an SAP shop, too, when it installed the ERP software giant's production planning, formulations, packaging, procurement, order entry, distribution, inventory, charge-back processing, finance, and sales and marketing software on a four-way iSeries Model 830 server. The company also uses the Telemar contact management system, which resides on an iSeries Model 810.

While the company doesn't sell directly to physicians, it employs a sizable field sales force that is responsible for building relationships with doctors and advocating the Upsher-Smith products. In support of this field sales force is a planning and analysis team, headed by planning and analysis director Ann Prosser. This group uses Excel to build reports for the field force, including sales targeting and market share reports based on information about doctors' prescription patterns, purchased every month from IMS Global, a Plymouth Meeting, Pennsylvania, pharmaceutical market research firm.

IT TAKES A WAREHOUSE

When loaded straight into spreadsheets, however, the IMS data, as well as data from the SAP and Telemar systems, tested the limits of Excel. "You're talking millions of rows, so trying to use Excel as a business intelligence tool is overwhelming," Olson says. "That was the impetus to move to a data warehouse."

As a longtime IBM shop, Upsher-Smith chose to implement its data warehouse on IBM's DB2 UDB database management system. The company still needed a business intelligence platform on which to build and distribute reports from the data warehouse, and the contenders for this component were Business Objects and MicroStrategy. Both are good tools, and Upsher-Smith settled on the MicroStrategy platform, which the company installed on a Windows-based xSeries server. (Olson says the company would have preferred to run the data warehouse on the iSeries, but that this would have required purchasing the Enterprise version of the MicroStrategy product, which was considerably more expensive.)

Since Upsher-Smith had historically been an AS/400 shop, and most of its data resides on OS/400 servers, the company set out to create its own data transformation programs using the language that is native to OS/400, which is RPG. "But as we got more into this and learned more about data warehouses, we came to understand that procedural languages like RPG weren't the best approach," Olson says. "So we started to look at extract, transform, and load tools."

The company tested three or four ETL tools, Olson says, and the one that performed the fastest was Informatica's PowerCenter. He then interviewed several local PowerCenter users that had similar setups--namely, they were pulling data from DB2/400 databases--before recommending the tool. (Speed, however, was not a crucial factor for Upsher-Smith. Informatica offers a native DB2/400 connector, called PowerExchange for DB2/400, which is an extra $35,000 or so. But ODBC access was fast enough for the company's data warehouse, which is about 10 GB in size.)

Upsher-Smith is using PowerCenter to load standardized data into the warehouse from three SAP systems, the Telemar contact management system, and the IMS data feed. PowerCenter lets Upsher-Smith bring together various bits of information, including historical sales information, physicians' addresses, and physicians' prescription habits, into a single, unified structure on the data warehouse. Then Prosser and her team use the MicroStrategy analysis tools to build various reports against the DB2 data warehouse. The field force can receive the reports in any number of ways through MicroStrategy.

Prosser is appreciative of the work PowerCenter saves her team. "On the IT side, PowerCenter provides us with the features, flexibility, and performance necessary for very complex data transformations and integrations, so that we are able to quickly deliver new combinations of data to help field personnel measure their effectiveness in different ways," she says.

Olson, who spent four days learning PowerCenter with Informatica, likens PowerCenter to a graphical programming language. "You build your mappings graphically, and then you take your mappings and build workflows," he says. "It's all graphical and pretty easy to use. It's certainly more sophisticated [than Informatica's competitors' tools]. It's very complex, from the standpoint of having a lot of capabilities. I haven't used all of the capabilities, but I've never run into something we couldn't do with Informatica."

RETURN-ON-INVESTMENT

Before installing the data warehouse, building the Excel reports for the field force was consuming the resources of one full-time IT staffer, Olson says. Since installing the data warehouse and PowerCenter, the company's field force has almost tripled in size, to 120 people. However, the amount of work required by the IT department to prepare the Excel reports has dropped to a quarter of what it was, thanks to PowerCenter, Olson says. "We're very satisfied with Informatica," he says.

"In the pharmaceutical business, information is critical," Olson says. And getting good, actionable information into the hands of its field force is a key to driving growth. Since the field force is happy with its new Excel spreadsheets, the company appears well positioned to continue to grow.