BOSTON -- Three years ago, pharmaceutical giant Pfizer Inc. was doing quite well financially. Its financial data on the other hand, was a mess.
The New York-based company had 10 different technology systems housing its financial data. That forced Pfizer's financial professionals to either become experts in each system or to spend time on the phone with peers seeking the data they needed, then downloading it, putting it into Excel spreadsheets and ultimately weaving it back together in the right configuration.
"It's a fairly standard problem for lots of companies, but for us it was an epidemic," said Danny Siegel, Pfizer's senior manager of business technology who was brought in to build a centralized data warehouse for financial data. "People were spending a disproportionate amount of time doing data collection, reconciliation and integrations by hand."
At The Data Warehouse Institute (TDWI) conference Tuesday, Siegel told SearchCRM.com that the answer to Pfizer's problems was not, as some had suggested, putting all the data in one central repository. Siegel said that would likely cause more problems than it would fix. Rather, Pfizer assembled a team who knew its financial business and processes and could map out the data connections.
The effort involved a significant investment of time. Seventeen people in director-level positions and above spent nine months sorting through the business logic and divisional systems that were used for everything from project accounting to consolidation to planning and forecasting. It was a massive undertaking with different systems catering to each business process and, in some cases, steps within the processes, Siegel said.
"It's all table driven," Siegel said. "It may as well be a spreadsheet. It's not a technical problem. The technologies that are here are pretty mature. It's to a point where you don't need throngs of Unix guys, script writers and SQL programmers to come up with a very robust -- and complex in some circumstances -- data warehouse."
Siegel declined to say just how much Pfizer spent on the project, but called the budget for creating consistencies across the myriad systems "moderate" given the extent of the work. Some problems, however, have not gone away. People continue to send out spreadsheets because, while there are now data standards, there's no mechanism for forcing people to follow them.
Pfizer wanted to avoid making changes to a source system that would create siloed information in systems downstream, Siegel said. For example, if a change were made to information in the general ledger, it wouldn't necessarily flow to a divisional system. Pfizer built what it calls the financial data hub, which manages every piece of information, not just the meta data information.
"We basically take the changes in the data standards, propagate them, commit them to an Oracle database and then. . .serve up those changes as a Web service," Siegel said. "So you can have all these systems simply be consumers of the information in a pull fashion. When I make a modification to a piece of business logic, a downstream system could then say 'Aha, there's been a change; I'd like to know what that change is.' The hub says, 'here's the change.' It updates its logic at whatever frequency is appropriate. That way, a report coming out of one end looks like a report coming out of the other end."
That, said Siegel, is the Holy Grail -- an audit trail that leads from the transactional level all the way up to the statutory level, providing consistency and helping comply with regulations like the Sarbanes-Oxley Act.
Similar projects ahead
Pfizer began the process with just its financial data but plans to create data marts for other areas, like sales and marketing, as people within the company transfer departments and want the same capabilities they had before. Similar architecture should make the process easier with the next group of systems.
Siegel said he is just now beginning to determine ways to measure the success of the project. Roughly 20,000 employees touch the data from the warehouse, but only a couple hundred are core users and, of those, the vast majority are running simple ad hoc query reports, Siegel said. Probably 15 or 20 users are writing their own complex reports using Pfizer's BI software, Business Objects 6.1.
"They don't know they're looking at the warehouse, but they are," said Siegel. "In my opinion, that's success. It would be far fetched to think I could teach everyone to use the Business Objects clients. Alternatively, if I can get 20,000 people to say the definitive source of my financial data comes from x and that x is sourced ultimately from the data mart, we're going in the right direction."
Pfizer is now looking at a tool to measure which segments of the database are being used by which users, which will help not only with fine tuning, but also with security, Siegel said. It's easy to track workers who are looking at data they have access to but really shouldn't be interested in. Additionally, Siegel is creating his own reports that monitor who is refreshing reports, who is running recalculates and who is running ad hoc queries, which ultimately provide a glimpse not only at who is using the data mart but to what extent. As of this month, Siegel determined that roughly three-quarters of the U.S.-based finance organization, roughly 100 employees, are using it. "That's a pretty big number," Siegel said.