Data Warehouse Check-Ups

It's human nature to resist visiting a doctor for periodic check-ups. Generally, we'd prefer to avoid being poked and prodded, having our vital signs taken, and then being told that we need to quit smoking, exercise more, or change our lifestyle habits in some fashion. However, most of us are mature enough to realize that these regular check-ups are critical to monitor our health against conventional and personal norms.

Similarly, it's critical to conduct regular check-ups on your data warehouse and business intelligence (DW/BI) environment. It's less invasive to just keep doing what you've grown accustomed to in your DW/BI environment, but it pays to regularly check its weight and blood pressure, as well as the pulse of the business community.

Just like the assortment of health guides available in your doctor's office, we'll describe the most common disorders encountered when performing DW/BI check-ups. For each malady, we'll then discuss the telltale symptoms to watch for, as well as prescribed treatment plans.

This column is pertinent to anyone with a maturing (dare we say "graying"?) data warehouse. Those of you just getting started should also watch for these warning signs to nip any disorder in the bud before it takes hold and spreads within your environment.

Business Sponsor Disorder

One of the most common, yet potentially fatal disorders involves the sponsorship of the DW/BI environment. A business sponsor disorder is often the contributing factor to data warehouse stagnation.

Symptoms. Organizations are most vulnerable to this disorder when the original sponsor moves on either internally or externally. Even though someone will ultimately fill the job or assume the title, the new person likely isn't as zealous for the data warehouse effort as the original sponsor. If the original sponsor left under somewhat negative circumstances, many of the assumptions concerning the data warehouse effort may be at risk, including tool selection, identity of trusted vendors, and choice of application development topics. The political winds at the moment of such a transition can be especially turbulent.

Even if the sponsor hasn't changed jobs, he or she may mentally abdicate sponsorship duties. Newly formed data warehouse teams are especially susceptible. Once the team gets approval to proceed with the DW/BI initiative, it turns its complete attention to building the new environment as promised. In the meantime, another hot issue distracts the business sponsor (who potentially suffers from attention deficit disorder).

Another warning sign is if IT is the primary sponsor of the DW/BI program, establishing priorities and driving the development plan. Finally, if you find DW/BI funding suddenly coming under intense scrutiny, you're likely suffering from sponsorship disorder.

Treatment plans. The first step to treat this disorder is to identify and recruit a business sponsor. An ideal business sponsor visualizes the potential impact of the DW/BI environment on the business, which empowers the sponsor with enthusiasm to ensure that the larger business community embraces the DW/BI deliverable. If the sponsor isn't engaged and passionate about the cause, then it's tough to convey that to the rank and file. Effective sponsors often face a compelling problem that they're trying to address. Good sponsors are able to leverage this compelling problem to provide the project with momentum by insisting the organization can't afford not to act.

We look for business sponsors who are influential within their organizations, both in terms of hierarchical and personal power. DW/BI sponsors need to be politically astute and understand the culture, players, and processes. Because neither the business nor IT communities can effectively construct the DW/BI world independently, business sponsors should be realistic and willing to partner with IT.

The most obvious way to find a business sponsor is to conduct a high-level assessment of the business requirements. Likely sponsor candidates will rise to the top of the surface as a result of this process. Another approach is to conduct a demonstration proof-of-concept, presuming viewer expectations can be realistically managed.

If no one emerges as a potential willing and able sponsor, then the project team should seriously reconsider moving ahead. You absolutely need someone high in the business to champion the cause. Otherwise, you'll suffer from chronic business sponsor disorder. The estimated lifespan of a DW/BI environment plummets without strong business sponsorship.

Your work isn't done once you've identified and recruited a single sponsor. Given the never-ending nature of the DW/BI program, sponsorship needs to be institutionalized with a steering committee or governance group of senior business and IT representatives. Clearly, you don't want to put all your sponsorship eggs in one basket.

One of our favorite tools for working with business sponsors is the prioritization quadrant, as described in "The Bottom-Up Misnomer" (Sept. 17, 2003). This technique is used in the early stages of a data warehouse to align business and IT priorities, resulting in a program roadmap for the enterprise. On an ongoing basis, perhaps every six months to a year, the DW/BI sponsorship or governance group should review progress to date and revisit the prioritization quadrant to queue up subsequent projects balancing business value and feasibility.

You need to establish a "care and feeding" program for the DW/BI sponsors. Business sponsors are highly experienced and respected businesspeople, however, they may not be highly experienced in the organizational culture change often required for an analytic initiative. They may need some coaching on their new roles and responsibilities. Take a look at our column, "Habits of Highly Effective Sponsors" (Sept. 1, 2003) for more details.

Never take your sponsors for granted. It's certainly not safe to rest on your laurels. You need to constantly communicate, feeding constructive, realistic, and solution-oriented feedback to the sponsors, while listening to stay abreast of sponsors' hot buttons. Communication is also critical to continually build bridges with other business leaders in the organization. Lastly, you should actively convey what the DW/BI world has done for them lately. You can't afford to wait until someone's scrutinizing expenditures to inform them of your successes. As uncomfortable as it may seem, you need to market the DW/BI environment. Enthusiastic business users typically deliver the most effective commercials.

Data Disorder

Accessing good data is one of the two pillars of the data warehouse. (The other is addressing the right business problems). The most serious and common data disorders are poor quality data, incomplete data, and late data.

Symptoms. One of the key indicators of a data disorder is the degree of data reconciliation happening across the organization because the data's inconsistent or not trusted. Data disorders are often blurred with business acceptance shortfalls when the real underlying issue is the data is irrelevant or overly complex.

Treatment plans. The initial treatment for data disorders requires drafting an enterprise data warehouse bus matrix. This technique is thoroughly discussed in the Sept. 17th column referenced earlier. The matrix establishes a blueprint for enterprise integration by identifying the core business processes and common, conformed dimensions. After the matrix is developed, it should be communicated and "sold" up, down, and across the organization to establish enterprise buy-in. If you already have a slew of disconnected analytic data repositories, you can embellish the bus matrix by cataloging the "as-is" environment prior to developing action plans for your longer-term data strategy.

While the bus matrix identifies the links between core subject areas in the data warehouse, it also highlights "opportunities" for extract-transform-load (ETL) processing. Like the overall technical architecture, the back room ETL architecture is often created implicitly rather than explicitly, evolving as data profiling, quality, and integration needs grow. You may need to rethink the staging and ETL architecture to ensure consistency and throughput at acceptable costs.

You should preface the DW/BI project with a comprehensive data profiling task to confirm that the data is what it's advertised to be. During the production phase, you must continuously monitor the data for quality glitches and omissions. Finally, you should carefully examine whether you need to go to a streaming real-time architecture to deliver the data to decision makers within their "sweet spot" time window for affecting the business.

Data disorders often result when data is irrelevant, noncomprehensible, or otherwise difficult to use. Review your existing data schemas for potential improvements. We identified guidelines for reviewing presentation dimensional models in "Fistful of Flaws" (Oct. 10, 2003).

Finally, if you've invested a lot of time and resources to develop an atomic, normalized data warehouse but the business users complain about ease-of-use issues, you can leverage that existing investment by creating complementary dimensional models to address ease of use and query performance, while also boosting your chances of business acceptance.

Business Acceptance Disorder

Here's another critical disorder affecting data warehouse mortality rates. If the business community doesn't accept the DW/BI environment to support decision-making processes, then you've failed. Sorry to be so blunt, but it's a harsh reality. The business must be engaged if you're to stand any chance of DW/BI acceptance. Unfortunately, business engagement is often outside our comfort zones; we may be unsure about techniques for ensuring their engagement, plus there are typically no incentives in place for mastering this domain.

Symptoms. There are some strong indications of business acceptance disorder. Are the business users simply not using the data warehouse like you think they should? Do the number of BI tool licenses greatly exceed the number of active users? Do the number of trained users greatly exceed the number of active users? Are the prime targeted beneficiaries of the DW/BI environment turning their attention to a different analytic platform, independent of the DW? Do the business users make requests like "just give me a report with these three numbers on it" because they're loading the report into Excel where they're building their own personal data warehouse? Does the business community perceive a legacy of disappointment when it comes to IT's ability to address their requirements? Did the DW/BI project team focus on data and technology, presuming they understand the business's requirements better than the business does?

Treatment plans. Your mission is to engage, or reengage, the business. Talking to users about their requirements is an obvious place to start. The DW/BI environment is supposed to support and turbo-charge their decision-making. Given this mission, distributing surveys or reviewing entity/relationship diagrams are ineffective tools for gathering business requirements. Put yourself in their shoes to understand how they currently make decisions and how they hope to make decisions in the future. Obviously, you need to have the right attitude, listen intently, and strive to capture their domain expertise.

It's important that you engage business folks across a vertical span of the organization. It's not enough to merely speak with pseudo-IT power analysts that can make data jump through hoops. We need to talk to their peers who aren't so empowered, plus speak with middle and upper management to better understand where the organization is going. You're vulnerable to falling into the trap of creating a better mousetrap for today's problems if you spend all your time in the trenches. It's highly beneficial to have relationships with all levels of the business organization — executives, middle management, and individual contributors.

Of course, you're more likely to successfully engage the business when you have a strong business sponsor with a powerful ability to influence the organization. A strong, committed business sponsor can significantly affect the organization's culture. Conversely, if you suffer from a sponsorship disorder, it's even harder to obtain business acceptance. These two disorders often go hand in hand.

As previously discussed, one size doesn't fit all when it comes to analytic capabilities. You need to acknowledge the range of usage requirements and institutionalize a strategy to address the spectrum, as discussed in "Beyond Paving the Cowpaths" (Nov. 18, 2003).

Similar to our discussion about business sponsor care and feeding, you need to establish a comparable program for the rest of the business community. Care and feeding commonly occurs with the initial deployment, but then we often quickly turn our attention to the next project iteration. You need to proactively conduct ongoing checkpoint reviews to remain engaged with the business. In addition, you should help the business users understand their shared responsibility for a healthy DW/BI program.

Education is a key component of deployment, but it isn't a one-time event. You need to consider ongoing needs for tool, data, and analysis education. We've worked with some organizations that include DW/BI training as part of their new hire orientation because information is a fundamental part of their culture. Not surprisingly, the DW/BI environment is broadly accepted in these companies; it's part of the way they do business.

Finally, as we described with the sponsorship disorder, communication is critical. You can't rely on the project documentation tools to communicate with all your constituencies. You need to concentrate on what's in it for them, marketing successes while managing expectations.

Infrastructure Disorder

Contrary to popular opinion, infrastructure disorders are seldom fatal. There's often room for improvement, but it's usually an elective procedure. Despite our personal interests in this disorder, it usually doesn't warrant the attention due the others.

Symptoms. Are the DW/BI systems slow or is the data late? Is the DW/BI environment commonly described as a bundle of technical bells and whistles? Are there tool overlaps and/or voids? What about performance concerns? Performance covers a gamut of potential underlying problems: ETL processing time to get the data loaded, query result time lags, and the DW/BI development cycle time to deliver new functionality.

Treatment plans. Every DW/BI environment is based on an architectural foundation; however, it may be time to revisit your overall architecture plan. The question is whether your plan was explicitly developed, or whether it just implicitly occurred. A well thought out plan facilitates communication, minimizes surprises, and coordinates efforts.

Revisiting your technical architecture doesn't mean going out and buying all the latest, greatest technology. You need to understand the business's needs and determine the associated implications on the technical architecture in terms of the required ETL services, access/analysis services, infrastructure, and metadata. The drive toward more real-time data warehousing is a prime example of the translation from business needs into architectural requirements. Some organizations have made poor technology choices in the past. It takes courage to unload that baggage to enhance the DW/BI environment going forward.

Cultural/Political Disorder

Unfortunately, DW/BI environments aren't immune to cultural or political disorders, and there's no vaccine in development on the research horizon.

Symptoms. Symptoms of this disorder are fuzzier to articulate, especially because they typically transcend more than the DW/BI environment. Organizations with cultural/political disorders may be stymied by conflicting priorities of "doing it fast" vs. "doing it right." Similarly, they often struggle to reach consensus on tough issues, such as data standardization and process changes. Finally, more specifically related to DW/BI, many organizational cultures aren't poised to embrace analytic decision-making, especially when decisions have traditionally been based on gut feelings or intuition. Do the business users currently manage by the numbers? There's often a lack of recognition and/or willingness to champion a culture shift to more fact-based decision-making.

Treatment plans. When dealing with cultural and political disorders, you can't just duck them, much as we would like. You need to be courageous, while understanding that these disorders are difficult to overcome with trench warfare. Now is the time to call in your support group: IT management, business sponsors, and the business community. If the support group doesn't recognize the need and assume accountability for treating these disorders, then the DW/BI team is in for a long, uphill struggle. Senior business and IT management must accept its fiduciary responsibility for handling information and analytics as corporate assets. Finally, actions speak louder than words. The organization will easily see through a veil of verbal commitment if management doesn't exhibit reinforcing behaviors.

Early Detection

As touted by cancer experts worldwide, early detection is the best prevention. Proactively monitoring your data warehouse and business intelligence environment is the best method of ensuring its health. It's tough to prescribe a remedy if you don't know what you're suffering from. As a student commented recently, thinking about common disorders and alternative treatment plans is a "shot in the arm" for anyone who's trying to rescue a failing data warehousing project. The metaphor lives on.

Finally, remember there's nothing wrong with having a check-up and learning that you're in perfect health. In fact, that's the optimal outcome!

Ralph Kimball founder of the Kimball Group, teaches dimensional data warehouse design through Kimball University and critically reviews large data warehouse projects. He has three best-selling data warehousing books in print, including The Data Warehouse Toolkit, 2nd Edition (Wiley, 2002).

Margy Ross is president of the Kimball Group and instructor with Kimball University. She cowrote The Data Warehouse Lifecycle Toolkit (Wiley, 1998) and The Data Warehouse Toolkit, 2nd Edition.