By Tom Maher, Hayes Management Consulting
Twitter: @HayesManagement
For years, we have seen discussions about Business Intelligence (BI), critical indicator dashboards and other data tools for decision making. It all sounds good in theory, but I don’t see a lot of effective BI in the real world. So what is the problem? The largest problem is and always has been a lack of structure in the underlying data.
Nearly every large health network has made an attempt to consolidate data from multiple sources into some type of data warehouse. However, having the data in one place does not make it useful or trusted as a source for business intelligence. There is usually an assumption made that if the data is together in one place (data repository or data warehouse), it can be used to perform a lot of wonderful analysis and become the basis for some great BI tools. The problem with this approach is that it lacks structure. You would not build a boat by strapping together anything that floats and hoping that you can figure out how to put them work together later. So why is there a tendency to think that it will work with business intelligence?
The real truth is that building an effective data repository or data warehouse requires a great deal of design work. This involves data analysis, data design and conversion, and data reconciliation in order for the data to be useful and trusted as a BI source.
So, what are the steps for building an effective data repository or data warehouse?
- Decide and list which areas of your business could benefit from BI reporting
- Evaluate available data sources and determine whether there are data sources to support identified BI needs
- Evaluate the data sources in detail to determine how many sources need to be combined and how consistent the data from the various sources will be
- Select the data sources that you want to utilize
- Perform detailed data analysis on your BI needs and on the data sources
- Design a data model that supports your BI needs
- Analyze the individual data sources and assess how much data conversion work will be needed to transform the various data sources to fit your data model. This may mean converting free text data to codified data or mapping various sources of codified data to a single codified definition.
- Perform a gap analysis to determine areas where your data model cannot be supported by the available data. Evaluate alternatives and impacts on your BI effort.
- Perform a trust analysis on your data sources
- Are your sources comprehensive or will there be data missing?
- Are you data sources verifiable and reconcilable?
- If there are data gaps or trust gaps, now is the time to assess what can be done to close these gaps. If you do not trust the source data, then how can you trust the resulting BI reports?
- It may take some iterative analysis with alternate data sources to the point where it looks like you can realistically meet your BI needs
- Since data repositories and data warehouses are usually built as relational databases, you will need to do a formal relational design. The end result is a data model for your BI system.
- Enlist a qualified DBA for the design
- The first design pass should produce a fully normalized relational design. Fully normalized means that the same data should only be stored once. Nothing should be duplicated.
- Estimate volumes over time in order to give your DBA a basis for performance estimates
- The second design pass should assess the performance implications of the fully normalized design and make some judgments about de-normalizing the table designs and adding summary tables to the design in order to boost performance and limit resource consumption
- Create a conceptual design of your BI and Key indicator Reports
- From a business perspective, make sure your data and database designs support all of your critical BI needs
- Have your DBA assess how well the conceptual reporting designs will perform based on the proposed technical design of the database
- If there is any indication that critical BI functions may not be met or may be subject to poor performance, cycle back and iteratively re-visit all of your decisions and design assumptions
After all of that foundation work, you are ready to start developing build specifications. Do not lose sight of the conceptual design and consider building in phases. Start with your highest priorities. Populate your data model and build your BI reporting in a sequence to get the best return for your investment as early as possible.
Remember, you would not build a boat by strapping together anything that floats and hoping you can figure out how to put them work together later. Avoid doing the same with your business intelligence system.
This article was originally published in Hayes’ Healthcare Blog and is republished here with permission.