Data Warehouse projects are on fire. These projects are normally driven by the information technology department and are often responding to the need for aggregating data across systems. What is making this appealing is that the cost of hardware has come down over the years. Storage arrays are easier to maintain and setting up a data repository is normally something that some IT staffs have within their skill set.
The project starts off easily enough, building the various interfaces maybe some crude extracts that populate a SQL or Oracle database. Some organizations already have report writers and analytics software that they use for previous projects and they overlay them on top of the data warehouse. The CIO then steps back, arms spread and announces that they have built a data warehouse. The problem is that no one really knows how to use it. At least in the movie “Field of Dreams,” they build a baseball field for people that already knew how to play. By building it the players came and you can just sit back and enjoy the show. This does not happen with a data warehouse.
If you think of your data warehouse as a software development project, instead of a database repository, then at least you start moving in the right direction. All these years’ database administrators have told us that you build a database from the ground up. But is this really true for a data warehouse? Here is where I’m going to flip things upside down. You start your data warehouse with the user interface:
- What is the experience you want your users to have?
- What level of training will they have when they access the user interface screen?
- What roles and rights will you be able to develop for the various users?
- Interview and survey users to determine what the key requirements will be.
- Understand what the static views are, and what data details will they have to drill down into.
- Work with the leadership team to develop a comprehensive list of dashboard views.
- Understand what key performance indicators are for the organization and develop email alerts.
- Develop a change management system with at least two scheduled updates every six months. These are similar to software upgrades and patches.
- Work with senior leadership to develop policies and procedures that define the source of truth for reports. This is a demarcation line when all reports are generated through the data warehouse and not through individual legacy systems.
- Have quick views for users that do not have to drill down.
- Establish intuitive drill down tools (that requires minimum training) for users that have to preform root cause analysis.
In order to accomplish all of these initiatives you have to step back and take inventory. Survey and interview all the users. This gives you an opportunity to collect sample reports and a full inventory of all reports generated in the organization. For large systems this is a daunting task. However one of the most important questions to ask when collecting the report samples is “how often do you look at this report and what exactly are you looking for?”
I conducted a large reports inventory for an academic medical center once and asked the same question to the managers working in the business office. The data center was delivering a large quantity of stacked bar graphs to each manager on Monday morning. If the managers did not receive the bar graph by 10 AM, a flurry of emails would be sent causing everyone to go into crisis mode. So with this bar graph in my hand I asked each manager what data points are they analyzing on the report. After some lengthy tap dancing I finally got them to admit that they gave the report a five second glance and threw it in the trash.
When we completed our reports inventory we were able to determine that more than half of the reports produced were obsolete. 75% of the managers did not know how to read the graphs, and most just needed two or three key data points from the massive amount of data that they were receiving. These are all typical issues found in organizations that have been doing business the same way for years and have many homegrown managers.
The key to building the right environment is having a robust roles and rights access. Each user has a different list of requirements. Different data elements, different views, and different metrics. This was never an issue when they were using their own legacy systems, niche software, and best-of-breed applications. But enterprise solutions and consolidated data warehouses are forcing users to look at generic reports which may not have value for their department. They deserve better.
If you continue down the path of building a data warehouse without clearly developing a report strategy, then you will be in a constant state of build. Your data warehouse will be a proverbial money pit that you continually feed naïvely thinking that this is the year that you’re finally done. When you build it, they will come…and say “I want my old bar graphs back!”
