How To Do a Data Warehouse Assessment (And Why)
Published: July 1, 2000
Published in TDAN.com July 2000
Why Assessments and an Assessment Methodology are Needed – What an Assessment is
In the relative time scale of technology change, data warehousing has been around for a while. Discussion of “the mature data warehouse” and “second generation warehousing” is becoming increasingly common. Many, if not most, large organizations have something that they call a data warehouse, and they are likely to have some data marts tailored to the needs of specific work groups. A typical large enterprise today is most likely to be at the beginning of, or in the midst of, a data warehouse initiative. In some cases, there may be a history of several unsuccessful or partially successful data warehouse initiatives.
Regardless of actual or perceived maturity of data warehouse implementations, warehousing has yet to mature as a discipline. Data warehousing is still relatively young both in terms of proven methodologies, and in availability of experienced practitioners. In part, this is due to the inherent complexity of data warehousing. From identifying and extracting data, to providing the right access functions and information views, the data warehouse involves a wide range of processes, rapidly evolving tools, development methods, and required expertise. It’s not surprising, then, that many data warehousing initiatives have failed to meet expectations, deliver business value, or realize their full potential.
Nonetheless, the pressure for delivery of effective data warehousing solutions continues to grow. Facing a multitude of business drivers -- ever increasing competition, more sophisticated and better informed consumers, changing markets, changing regulatory environments, and many more pressures – organizations are driven to respond with better targeted products, improved customer relationship management, and greater operational efficiency. Responding effectively to the pressures demands more accurate, reliable, timely, complete, insightful, and useable information and analysis.
Well-informed business processes are essential, and failure is not an option, so organizations press ahead data warehousing initiatives. Successful data warehousing organizations may well be the successful business enterprises of the future. Yet the urgency of market pressures, along with pure financial considerations, make it crucial that: 1) past errors are not repeated, and 2) whatever is correct and useable out of past data warehousing efforts be identified and leveraged.
These introductory comments describe the overall state of affairs for many companies today. Current warehousing efforts have been initiated in and environment of previous attempts and existing components. There are needs to learn quickly from experience, to find the right road, to salvage what is good and useful, and to move forward. Meeting these needs is the purpose of a data warehouse assessment. The essence of data warehousing assessment – the what, why, and how of assessment – is directed at refining the warehousing process and revitalizing the warehousing initiative. Such assessments often represent the logical starting point of a renewed data warehouse life cycle.
When to Assess the Data Warehouse
Certainly any data warehousing initiative that is just beginning will benefit from a rigorous and comprehensive assessment. The results of such an assessment provide extensive information to position the initiative for success. Information about past efforts and current warehousing deployments describe the point at which the initiative is to begin. Information about business needs for, and expectations of, the warehouse describe the desired ending point of the effort. Assessment of the technical and organizational environments in which it will operate help to integrate the warehouse into existing business and IT processes. And understanding the readiness of the organization to build, operate, and use a warehouse, helps to plan the development and deployment projects.
Data warehousing assessment, however, is beyond the early stages. As needs, technologies, and environments change, reassessment has value throughout the life of the data warehouse. Assessment techniques can be effectively applied to data warehouses in various stages of maturity and completeness. A well-structured assessment is appropriate at any point where warehouse value and direction are uncertain, or at any time that the existing approach and infrastructure have become problematic. Typical times that a maturing data warehouse may benefit from reassessment include:
In these situations, or at any time when information is needed to increase or sustain the business value of the warehouse, assessment is the right approach. Clearly, then, data warehouse assessment is not a one-time event. Any of the situations just described may apply to a single warehouse at different points of implementation and maturity. Data warehousing represents a long-term commitment, and a key business enabler. Sustained value – given that the warehouse is deployed within a continuously changing landscape of technology, organizational structure, business priorities, and marketplace realities – demands a warehouse that evolves and adapts. Periodic assessment of the data warehouse may be necessary to ensure continued health, vitality, and value.
The Opportunities and the Challenges
Defining a successful data warehouse assessment approach, and using it effectively, require an understanding of the opportunities and challenges that a typical data warehouse may include. Although they vary widely in size and scope, data warehouses in general represent large and complex solutions to data integration and delivery problems. A typical data warehouse is challenged to:
Extract, consolidate, transform, and deliver volumes of disparate, often poorly understood and documented data …
for conceptually important, but often poorly specified reasons …
to a diverse group of users with varied levels of training and skill!
Even more challenging, the data warehouse is required not to perform this complex task once, but to repeatedly and reliably do so, in an ongoing, timely, robust, and extensible fashion.
Just as data warehouses are frequently complex and challenging, so too is the process of assessing them. The challenge is compounded by time constraints – there is normally not a lot of time to perform the assessment. Assessments, by their nature, are expected to be rapid, with six weeks a typical outer limit of patience for completion. Yet, much data – both business and technical – must be collected and evaluated to find out what is right, identify what went wrong, and determine how best to proceed. As anyone who has sifted through the artifacts of a major project can testify, review and repositioning is much more challenging than starting from scratch.
The challenge of data warehouse assessment, then, is that there is a lot of complexity to look at in a short period of time. A successful data warehouse assessment approach must provide a roadmap and sufficient structure to accomplish a breadth of analysis, at the right level of detail, in a limited time period. It should also provide a set of key artifacts and best practices to look for.
Complexity, itself, can be a barrier to success of data warehousing efforts. In troubled warehouse initiatives, it may be the case that many bright and capable people have simply been caught up in and overwhelmed by the complexity. Assessment represents an opportunity to step back, evaluate key gaps and dependencies, and restructure the direction of the warehousing effort. Assessment offers opportunity to re-establish a well-balanced and coordinated warehousing strategy that will leverage strengths, mitigate risks, and address weaknesses as the warehouse moves ahead. It also represents an opportunity to review initial design and technology decisions in light of current realities.
There are often significant organizational and methodological issues to be evaluated. The data warehouse and its evolution cross organizations and functions. Warehousing is tightly bound with business strategy and data stewardship on the one hand, and closely coupled with technology on the other. As a result, responsibility for definition, development and operation of the data warehouse often doesn’t readily integrate into either the current business or the current IT organizations, structures, methods, and processes. Additionally, the information management infrastructure required for ongoing success of the data warehouse of the data warehouse may be lacking and not well understood.
Warehousing assessment is further challenged by the need to maintain neutrality and objectivity. Regardless of who initiated the assessment, it must be performed from the perspective of information as an asset to the enterprise. In the ideal scenario, assessment is a joint effort undertaken in partnership by business and IT sponsors. In this scenario, the assessment provides an opportunity to objectively identify the roles, responsibilities, and quality metrics needed to successfully manage the delivery and analysis of business critical information. A comprehensive data warehouse assessment approach provides a framework of roles and responsibilities that may be quickly applied to the current environment. The framework serves to identify organizational and methodological gaps, and tailor a best organizational solution for the warehousing initiative under review. Organizational positioning of the warehouse is also important. Understanding of, and agreement upon the appropriate roles of the data warehouse within the broader context of enterprise knowledge management are crucial to managing and meeting expectations. Resolving the organizational issues may be among the most significant of an assessment’s contributions to progress with and long term viability of the data warehouse.
When a data warehouse assessment is initiated, it is frequently expected to produce much more than an identification of current weaknesses and recommendations of how address them. It is particularly common that the assessment is expected to produce a complete statement of business requirements – to provide a business context that was missing or incomplete when the warehousing initiative was started. While comprehensive requirements analysis is possible, it may be impractical within the scope and time constraints of an assessment. With the actual scope and overall state of affairs generally in question going into the assessment, it is difficult to estimate the time and effort of additional business requirements analysis. Business alignment analysis may be more appropriate to the scope of assessment.
A final challenge of data warehouse assessment is the need to establish clarity and consensus on the scope, exact deliverables, and expected outcome of the assessment. The assessment approach needs to include techniques for rapid assessment of warehouse business alignment. The framework should be designed to ensure stakeholder involvement and feedback, and to support rapid evaluation of overall business expectations. In many assessments this also represents one of the largest opportunities for improvement – by rapidly re-establishing the baseline, prioritizing business needs, and mapping needs to current data warehouse capabilities and directions. This realignment offers opportunity for tremendous improvement in the clarity of direction and the focus of implementation strategy.
The opportunities of data warehouse assessment are many and varied. The complexity and inherent challenges of data warehousing create a climate rich with opportunity. An assessment approach that supports rapid review and evaluation of a data warehouse, with attention to the challenges described above, provides a framework through which these opportunities may be realized. This framework, and the results of its application, help bring order to the detail and complexity inherent in the data warehouse, and assist the data warehousing team to make informed choices and move the warehouse into the future.
Decomposing the Assessment Problem
As already stated, data warehousing represents a large, complex undertaking with many, interdependent parts. The first step of a data warehouse assessment (as with a data warehouse itself) is determining where to begin, what to produce, and how to produce it. Complexity of the assessment is compounded by partial artifacts of previous projects, missing history, and multiple agendas. As with any complex undertaking, assessment is most successful when the large, complex problem is divided into smaller, more manageable pieces. Our experience has shown the following decomposition, depicted graphically in Figure 1, below of data warehouse investigation and to be most effective:
This decomposition also provides an excellent framework to specify and communicate the potential scope of the assessment project, and the range of its expected deliverables. Gaps, risks, constraints, opportunities, and resulting recommendations may be identified for each of these areas.
Figure 1: Decomposing the Data Warehouse
Business Needs Assessment includes an analysis of the underlying business drivers and objectives and overall context of business need that has been established for the data warehouse. In an assessment the objective is not to perform the analysis. It is to determine the degree of analysis that has been done, and to identify any business analysis gaps and their impacts. In some instances no business needs analysis has been done. In these cases some high level identification and ranking of business information needs is an essential part of the assessment; necessary for the assessment to have any meaningful context in which gap analysis is performed and recommendations are developed. When business needs have been defined, the assessment process examines the approach to capturing business requirements, their completeness and organization, the priorities of the requirements, and alignment of the data warehouse release strategy and deliverables to the needs. In conjunction with the Technical Architecture Assessment (see below) it considers how effectively front-end tools are applied.
The following key questions are among those that a business needs assessment may address:
Information Architecture Assessment includes an analysis of logical data structures, their feasibility, completeness, documentation, and fit to business requirements. Information architecture assessment also includes analysis of data sourcing and transformation, the methods and assumptions applied, and validation of mappings to business requirements. Metadata, as part of the information architecture, is examined with respect completeness of metadata being tracked, user metadata requirements, and approaches to management of the metadata. A review of metadata tools is undertaken in conjunction with the Technical Architecture Assessment (see below).
Key questions addressed by information architecture assessment include:
Technical Architecture Assessment looks at current hardware, software and network infrastructure, and examines physical database designs. Technical architecture assessment seeks to identify any technical risks or constraints with regard to performance, maintenance, scalability, data distribution, disaster recovery, and sizing. This assessment also seeks to identify opportunity to leverage the value of existing technical resources. Effective use of tools, and their overall fit to the business and technical environments is examined, including extraction and transformation, cleansing, database performance tuning, modeling, metadata management, querying, multidimensional analysis, web enabling tools.
Some of the key questions of technical architecture assessment include:
Organizational Assessment includes an examination of the existing organizational structure and identification of the roles and responsibilities of both IT and the business community that need to be addressed. In conjunction with the Project Planning and Methodology Assessment (below), this review considers alignment of the project organization with the overall business and IT environments. Organizational readiness for warehousing is examined, including readiness to assume responsibility for ongoing technical and business support, hardware and software configuration management, continuing business requirements definition, and front end applications enhancement. Organizational assessment strongly focuses upon the organization’s ability to fulfill many warehousing roles. Readiness is a major factor in planning and staging the overall implementation of the data warehouse.
Among the key questions that organizational assessment addresses are:
Project Planning and Methodology Assessment performs a review of the project plan, including its tasks, timing and resources. In addition to common project variables (time, resources, and results) the project assessment looks at extended factors such as project communication, decision making structures, change management and issue resolution processes, and business/IT collaboration (overlaps with organizational review). The data warehousing life cycle and the methodology being applied are assessed. Project team composition and skills are considered as a key factor in this part of the assessment. Finally, an assessment is made of the overall release and implementation strategy – this activity dependent upon and influenced by all of the preceding assessment perspectives.
Common questions answered by project planning and methodology assessment include (many are basic project management benchmarks):
These perspectives provide a very workable approach to specify the scope of an assessment, define its anticipated outcomes, and organize assessment activities. On completion of data gathering and analysis from individual perspectives, the individual findings must be synthesized and consolidated into an integrated action plan. The action plan should include a phased strategy to move ahead with data warehousing.
Partitioning the areas of analysis also offers a degree of “selectable component” approach to data warehouse assessments. The partitions provide a framework by which an assessment may be tailored to individual and specific needs. It may be apparent, for example, that one area clearly requires immediate attention and urgent action. An assessment focused on that perspective may be performed first to address immediate needs. When using such a selective approach, beware of too narrow a scope. Also consider the above list of perspectives and questions as a completeness test for data warehousing assessments.
How to tell if you need a Data Warehouse Assessment
A quick “self test” can help to identify areas of concern or need that may be the focus of your own data warehouse assessment. Use the self-test shown below as an aid to identifying your assessment needs:
Any question you are unable to answer positively, or any question to which you don’t know the answer, represents a potential gap and risk in your project that merits examination. Interestingly, this same checklist and associated benchmarks can be used to validate readiness and completeness of planning for a new data warehouse effort as well as need for assessment of an existing one.
Performing the Assessment
Data warehouse assessment is most effectively performed using a systematic and proven process. Figure 2 illustrates this process and the value derived from it. The following steps provide an overview of the process by which a proto-typical data warehouse assessment is executed using the multiple-perspectives method. The usual team size is three-to-four senior data warehousing analysts who collectively have expertise in all of the identified areas of assessment.
Figure 2: The Data Warehouse Assessment Process
Initial Parallel Investigation
Identify and prioritize executive and management reporting and analysis needs, priorities, constraints and expectations. As discussed, these are typically not well documented, if known at all. At minimum, known and documented business requirements will need to be validated. A series of structured interviews with primary stakeholders works well, followed by a group session to validate, synthesize and prioritize findings.
In parallel with the review of management needs to support the business assessment, an initial review of documents and identification of potential issues can be performed for each of the remaining perspectives. While findings are ultimately interdependent, and particularly dependent on business needs, this initial investigation is an essential data gathering step, and early comparison against benchmarks and best practices is informative. A discussion of the benchmarks is beyond the scope of this article. (Best practices and benchmarks could easily demand an entirely separate series of articles.) Good references for best practices are available in TDWI publications and other literature. Suffice it to state that you should investigate, identify, and have at hand a set of benchmarks against which your own projects and practices will be assessed.
Map the prioritized business information needs against the current warehouse in terms of data availability. This establishes a view of key gaps from the perspective of business need.
Map prioritized business information needs against identified architectural (and possibly organizational) problems. This is a mapping of information needs to architectural issues that inhibit meeting the business needs, and whose resolution would significantly improve delivery of required business information and analysis capabilities. This mapping provides a sense of which architectural issues have the greatest adverse impact on the business and are most urgent to address.
Methodology and project management gaps, and some of the organizational considerations, may not readily map to specific business priorities. These are more global in nature, and issues in this area are likely to have broad impact across all business needs and priorities. Issues from these perspectives are better suited to qualitative, rather than quantitative, analysis of impact.
Identify Solution Sets
Identify an initial list of potential solutions to addressing warehousing problems and correspondingly impacted business needs. This activity involves a subjective analysis of problem affinity analysis based on the earlier mapping. Evaluation of affinity leads to parsing of logically distinct solutions.
Refine and consolidate the set of logical, prioritized solutions based on
Package and Present Recommendations
As can be seen from this brief description, data warehouse assessments are not a rote process. Even with a more complete treatment of the steps than is possible in a brief article, judgement and insight based on professional experience are required. Data warehouse assessments are inevitably dynamic and mutable by nature. The assessment practitioner must be both experienced and agile. The assessment approach provides the framework and rigor necessary to apply the practitioner’s experience and knowledge for rapid and effective solutions to a complex problem set in a unique data-warehousing environment. The framework may also be applied by an organization to identify where additional expertise in conducting an assessment, and perhaps in implementing the warehouse, may be valuable.
Lessons Learned/Critical Success Factors
A logical conclusion for this article is a list of lessons learned and critical success factors identified over the course of conducting numerous data warehouse assessments. With these tips, the structured approach outlined above, and a little luck, your data warehouse initiative has a fighting chance of a providing essential business intelligence to management, and a leading a long life of positive contributions to the business. No system of relative importance is suggested by the sequence of this list. The items are not listed in any particular order.
Copyright © 2000 Knowledge Partners Inc.
Previously published in the Journal of Data Warehousing (DWI)
Recent articles by Dave Wells