The Real Scoop on Data Warehouses

Specialty Pharmacy Times, March/April 2015, Volume 6, Issue 2

The implementation of a data warehouse, a relational database for query and analysis, can help a specialty pharmacy utilize its data to the fullest.

The implementation of a data warehouse, a relational database for query and analysis, can help a specialty pharmacy utilize its data to the fullest.

There has been much discussion, and some confusion, about data warehouses. What is a data warehouse? What can it do for a specialty pharmacy? What does it cost? Let’s break it down.

What is a Data Warehouse?

Technically speaking, a data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can also include data from multiple sources.

From a business perspective, a data warehouse can really help a pharmacy use data to its fullest potential—from providing operations performance visibility to forecasting and fulfilling reporting needs.

What Can a Data Warehouse Do for a Specialty Pharmacy?

It can generate revenue opportunities by enabling fee-for-service and fee-for-data alliances with pharmaceutical companies and pharmacy benefit managers that require reporting. If a specialty pharmacy can deliver on the specific patient support programs that pharma is interested in—and report back on that activity—there are many potential revenue opportunities available.

It can greatly reduce the cost of reporting. With the ability to both build logic for each data point one time and to reuse the logic for each consecutive report built, there are significant savings available. Some good examples of this are calculations for medication possession ratio, proportion of days covered, turnaround time, and custom prescription status fields.

It delivers enhanced business intelligence and visibility. With all of the data stored and staged in 1 location with easy-to-use query building tools, the user can tee-up reports that offer insight into sales, market segmentation, inventory management, financial management, and operations performance.

It frees up the user’s time. The report-building query tools, which can be set up, saved for reuse, or even automated by noninformation technology staff, make the process of generating useful reports much faster. This also leads to better informed and quicker business decisions.

It centralizes and cleanses data to increase data quality and consistency. Rules are set up to ensure all of the data that flows into the data warehouse are standardized, which make the data trends and analysis culled from the reports accurate and trustworthy.

It lends itself to future forecasting and trending. A data warehouse stores large amounts of historical data so that different time periods and trends can be analyzed in order to make future predictions. Such data cannot typically be stored in a transactional database or used to generate reports from a transactional system.

What Do They Cost?

Many businesses wait until their systems, data, and reporting needs are very complex before realizing the need for a data warehouse. If the data warehouse is built while the systems and data are still manageable, the data warehouse can grow with the rest of the business at a much lower cost.

Regardless of the complexity of the data of a business, there are options to scale the rollout of a data warehouse to fit a range of budgets and timeframes:

Level 1 solutions can start at a cost of $70,000 and include features such as extraction-transformation-load (ETL), automated report scheduling, and an ad hoc reporting interface (see Table 1, Figure 1).

Level 2 solutions include, in addition to all level 1 features, enhanced ETL monitoring and reporting and the inclusion of additional data sources. The cost for these additional features ranges from $25,000 to $50,000. Conversion of existing reports costs from $4000 to $12,000 each, depending on their complexity (see Table 2, Online Figure 2).

{Click image to enlarge}

Figure 2: Level 2 Solution Diagram

Level 3 solutions add a quality engine, data firewall, and analysis services, which can range from $50,000 to $80,000 (see Table 3, Online Figure 3).

{Click image to enlarge}

Figure 3: Level 3 Solution Diagram

It’s important to say it again: while data warehouses can take a business to another level of competitiveness and growth, one of the keys is to start as soon as possible to control the project’s cost and complexity. SPT

About the Author

Paul Sipes serves as vice president of Spud Software in Grand Blanc, Michigan. He and his team have designed several specialty pharmacy work flow and reporting applications. Paul spent 20 years as a regional director with MSX International, a global provider of technical services, where he was on the cutting edge of digital solutions for US Customs and for automotive and retail clients. He transitioned this experience to Spud Software and the health care sector in 2007, where he has been providing digital solutions for improving patient outcomes and patient processing efficiency.