From deciphering business requirements to knocking down cultural silos, the IT department at USF Health – the University of South Florida’s medical organization – has faced down some major data warehousing challenges in its day.
Based in Tampa, USF Health comprises the USF schools of medicine, public health, nursing, physical therapy and the soon-to-be-opened school of pharmacy. It also boasts more than 300 practicing physician teachers, a medical practice plan, a clinic, a research organization and several partnerships with local teaching hospitals.
Every business user knows their pain points. Take five minutes and they will tell you what is painful.
Sidney Fernandes, assistant CIO and director of application development, USF Health
Back in 2006, USF Health’s IT team was given an assignment: Create a “single source of the truth” of data – a data warehouse – for financial reporting and other business intelligence (BI)-related activities, said Sidney Fernandes, USF Health’s assistant CIO and director of application development. But the team would have to overcome a chain of obstacles before making that happen.
For starters, the team needed to identify, gauge and figure out how to navigate the complexity of the data warehousing task at hand. Then they had to evaluate and choose the right vendors, get the system running and deliver results.
USF Health executives wanted fast and easy access to financial information and other reports they could use to evaluate faculty and distribute resources. This would require USF Health to use data from many disparate source systems related to clinical productivity, research productivity, educational productivity, expenses and payroll.
Fernandes said source systems for the data warehouse implementation include two financial applications, Oracle-PeopleSoft Financials and CODA Financial Management Software; two human resources applications, Oracle-PeopleSoft and Cyborg Systems Ltd. software; an electronic medical records repository; General Electric Practice Management systems; ambulatory surgery support applications; picture archiving systems; a radiology information system; and various spreadsheets.
“We wanted to get all these source systems into a single place so that decision makers could see exactly what a researcher or physician was producing, what grants they had applied for, what articles they had published, how many patients they saw […] and what USF was spending on them,” he explained. “That was the genesis of the data warehouse initially.”
Business users present data warehousing challenges
Two of the biggest hurdles that USF Health’s IT team faced in its quest included gaining access to the various information sources and getting data warehouse system requirements from business users.
“There were some political hurdles that we had to overcome to allow people to trust us and open up their data to us so we could pull it into the data warehouse,” said Swapna Chackravarthy, USF Health’s data warehouse architect and assistant director of application development. “Because these [source systems] were so disparate, they did not really lend themselves very easily to an integrated view of the data.”
After meeting with department heads and gaining access to source systems, Chackravarthy and Fernandes ran into more problems because USF Health’s various departments had never standardized file naming conventions and other practices.
“We found that department codes were nonstandardized and varied across the source systems,” Chackravarthy said. "We had to overcome that by building an architecture that allowed us to group all these varying structures under common umbrellas. That was the only way that we could do integrated reporting and there were some in-house applications, built for this purpose.”
Meanwhile, work had begun on getting system requirements from the business users that would ultimately be doing the actual BI reporting. IT professionals have reported in the past that business users generally do not know what they want from a technological standpoint. But Fernandes believes that assertion is a bunch of baloney.
“The business users always know what they want,” he said. “They just don’t know how it is that they want it.”
Fernandes said the key to getting proper the proper requirements for any data warehousing project is to focus on the business users’ pain points. This involves working with them to learn which of their tasks are overly time-consuming and which they just plain hate.
“Every business user knows their pain points,” he said. “Take five minutes and they will tell you what is painful.”
USF Health picks BusinessObjects over Cognos
After getting a good idea of what it needed from a business standpoint, USF Health looked at several software vendors. But the two that looked most likely to meet USF Health’s needs were BusinessObjects and Cognos.
Fernandes said the two products were closely matched in terms of features and functionality. But BusinessObjects ultimately emerged the winner, mainly because Fernandes found it to be more flexible with regard to applying business rules.
“Cognos uses a lot of direct cubes, so we had to be much more stringent on the business rules,” he said. “BusinessObjects relies more on the Universe Builder, so we could we wouldn’t have to be recombining cubes and things like that. So we felt more comfortable in that realm because our data and our metrics and things like that weren’t firmly defined at the time.”
Chackravarthy added that she believed BusinessObjects offered better capabilities around ad hoc reporting.
“When it came to ad hoc reporting and basically drilling and slicing and dicing the data from an end-user interface perspective, we found that BusinessObjects seemed a lot more intuitive and easy to use,” she said.
Fernandes added that the data warehouse itself resides in an Oracle Database 10g database management system.
Overcoming data warehousing challenges: Advice from a professional
The new data warehouse at USF Health is currently up and running and delivering the required reports in a fraction of the time it used to take, Fernandes said.
“With the data warehouse, all of the data is exposed directly as it existed in the source systems,” he said. “This immediately led to business processes being changed [because] the CFO had almost instantaneous access to things like salary, schedule and research metrics or clinical metrics.”
Fernandes said that organizations considering a warehouse project should make it a point to gain a detailed understanding of the scope of the initiative before investing in any new technology.
“Make sure that you have a plan for the data architecture and make sure that the plan is not inflexible,” he said, “because the data is going to tell you a lot of things that you may have not thought of during the business-scoping process.”