Home / Medicine & Technology / Data warehousing for improved decisions

Data warehousing for improved decisions

By Michael Cohen

There is a lot of data in medical practices, but is there meaningful information? The important step is turning data into meaningful information that is relevant, timely and allows for understanding and exploring relationships. Data warehousing and business intelligence has become an integral component to many business’ decision-support and analysis systems. Organizations are utilizing the data to identify and evaluate business opportunities, assess current operations and market new services or products. Physician practices are being required to address the same questions.

“Historically, the bulk of investment in corporate computing has been in systems that generate or capture data, such as accounting, order processing, manufacturing and customer information. Increasingly, organizations are investing in applications and technologies that deliver additional value from this collected data. Data warehousing is the process of collecting, cleansing and sifting data from a variety of operational systems and making the resultant information available to a wide audience of end users for analysis and reporting, according to Microsoft Corporation.

How many times have you tried to evaluate an issue but did not have the appropriate information to do so? Your assessment was based upon incomplete, old, subjective or non-specific information. What would be the impact of an HMO changing their payment methodology for a particular procedure? What is our cost for providing that service? Can you answer these questions? How many reports will you need to generate and manually load the data into spreadsheets to evaluate this? Or, will you base your assessment on anecdotal information? We know our revenue for that procedure and we know our overall practice overhead, so by multiplying those two numbers we can calculate our expense.

The need for meaningful and timely information for analysis and decision support has increased dramatically because of external pressures on your practice. These include the increased penetration of managed care and increased overhead. These changes are causing physicians and administrators to evaluate how they practice medicine and manage their practices. An important element that is often missing from the analysis is accurate and timely information. The only way to evaluate a potential change is with the support of meaningful data. Often, the only source of this data is the practice’s billing and management information system. The problem is that the reports available are more appropriate for billing and accounts receivable management.

In other industries, companies are transforming and utilizing data from transaction-based line-of-business systems to be the foundation for a data warehouse. Physician practices can load information from their current billing system into a data warehouse. This can be combined with clinical or financial data from other systems. Often when practices merge or upgrade their billing systems, access to historical data is no longer easily accessible. Converting this data into a single data warehouse is extremely beneficial to provide trend analysis and aggregate data from multiple systems. Data from your new system can be added to the warehouse to provide a comprehensive set of data about your practice or a combination of multiple practices. Data from multiple systems is integrated through consistent naming conventions, measurements, physical attributes and semantics. The technology has advanced and become less cost-prohibitive to make it feasible for all organizations to begin utilizing this technology.

Turning Operational Data into Meaningful Information

Operational data can come from a variety of sources including transaction data from a billing system, accounts payable and payroll from a financial system, and clinical data from various sources. The challenge is transforming and loading this data into a multidimensional model. Completing this requires a detailed understanding of the business questions the system will need to answer combined with programming skills.

The current trend is to utilize online analytical processing (OLAP) in which data is summarized and transformed into data cubes. Data cubes consist of descriptive categories (dimensions) and quantitative values (measures). Visually, the cube consists of multiple dimensions along the different sides that can be sliced or diced along each of its dimensions. The goal of this model is to allow users to easily complete complex analysis by viewing data that has been aggregated based upon user requirements. Users still have the ability to drill into the detail. For example, you want to review your average length of stay. Since operational data is at a detail level, the information is often summarized and computed in the loading and transformation phase. Performance is enhanced by precomputing values or aggregations. Every time you want to review your average length of stay you will not be required to calculate this data.

Continuing with our example, after reviewing this average length of stay (ALOS) for your practice you identify that your total has exceeded the target that you have established and you need to understand why. First you can drill down by diagnosis group, then by facility, then by insurance plan, and you can still view the individual patients and their hospital stays that have resulted in this ALOS. In this example, all of the ALOS will be calculated and stored in the data cubes, but the patient level detail will still be accessible. The dimensions of your cube will include date of admission and discharge, insurance plan, hospital and diagnosis group. The measures of the cube would include target ALOS, inpatient days, and average length of stay.

Data within each dimension can be organized into levels of a hierarchy. The levels represent the level of detail that would be available in reporting. In our example, the dimension for date of admission will include levels for year, month and day. This will allow users to view more or less detail by moving up and down between levels.

A common design for a data warehouse is a star schema. In this design, a central fact table is linked to related dimension tables. The central fact table includes all of the dimensions. The hierarchy levels for the dimensions are in the dimension tables. The benefit of this model is it improves user understanding of the data and performance by presenting the data in an intuitive model. The performance enhancement comes from summarizing and calculating data to meet reporting requirements. Going back to our example, the central fact table will have the hospital key number and the dimension table will include the name, address and any other information. As opposed to duplicating the address every time the hospital is referenced in the central fact table, you include the hospital key number with every admission, but the hospital address is stored only once in the dimension table.

One of the most important steps in the process is to validate that all of the transaction data is loaded into the warehouse. Operational data must pass through a cleansing and transformation stage before being loaded into the data warehouse. This will ensure that the information is accurate, reliable, consistent, does not include invalid data and conforms to the structure. This is accomplished using loading packages which include a validation process to verify that all of the data is accurately loaded.

The final step is to deploy the data warehouse to end users utilizing a business intelligence, analytic or decision support tool. These tools hide the complexities of the data warehouse to the end user. The goal of these tools is to expedite reporting and improve business analysis. They include intuitive models to assist users to visualize relationships utilizing graphs, decomposition trees and statistical reporting. For example, you can look at a bar chart that compares your average length of stay to a target. By pointing to the actual ALOS bar, you can drill down into a bar chart that shows ALOS by diagnosis group. Then you notice one diagnosis group is above budget, you can point the bar and see the ALOS for this diagnosis by facility. The end point can be a list of patients that meet the requirements defined through your data warehouse structure.

Users can be granted and revoked privileges for access to selected information within a data warehouse. A user name and password is authenticated before a user is permitted to view information or run queries against the warehouse. So, the president of a practice may have access to all the information, but another physician may only be able to view information for patients at a particular hospital in which they routinely round.

The business intelligence tools can be deployed as a desk-top application or a web-based application. Web-deployment requires firewalls and secure data transmission techniques. A firewall is utilized to protect the warehouse and all computers from unauthorized access via the Internet. In addition, data needs to be securely transmitted over the Internet so that only the authorized person who requested it can view it.

Applying Data Warehouse Technology

The key to succeeding in today’s health care environment is making better decisions quickly. A data warehouse can provide accurate and timely information, both financial and clinical, as well as business analysis tools that will become a key competitive advantage and valuable asset for decision support. Compiling and analyzing data from single or multiple sources can help identify ways to better utilize resources, evaluate business lines, reveal health patterns, understand the financial performance of an area and lead to better understanding and performance in risk-based contracts. Allowing users to have timely access and reporting capability will assist in identifying issues and developing appropriate solutions. True integration of data will allow physicians to better understand the cost-effectiveness and quality of care or services being provided.

Many information technology initiatives are not successful because inadequate planning and analysis is done before getting started. Organizations need to define and document their requirements for a project and continuously review them to assure that they have not changed. Any project should take an iterative approach allowing for ongoing user usage and evaluation to refine the project and ensure that the outcome for the project will support the cost. All solutions need to be evaluated as to their ability to produce a quick return on investment and provide high value to the organization.

Now that you have created your data warehouse and deployed a business intelligence tool, you are prepared to address questions and complete analysis with increased knowledge of your practice. Next time that you face the following questions, you will have the necessary information to make an informed decision.

Should I continue to provide a diagnostic test based upon changes in reimbursement? Does it enhance the quality of care that I provide and is it profitable? Is there a trend in my practice that may impact profitability in the future? What is the average length of stay or inpatient days per 1000 members for my practice and what is the reason for it?

Michael Cohen is a Project Leader at Actium@Modis, a leading provider of information technology consulting, education services and products.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.