How To Do a Data Warehouse Assessment (And Why)

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:

• Revitalize an existing data warehouse – The data warehouse functions, but use and returned business value are lagging.
• Make a transition from warehouse “builders” to warehouse “operators” – The warehousing organization needs to ensure ongoing data quality, enhancement and evolution
  in parallel with changing business needs, continuous management of a sound technical infrastructure, and continuously effective delivery of business information.
• Move from “first generation” to “second generation” warehousing – The warehouse needs to migrate from loosely structured pooling of data to an improved structure
  with high levels of data integration, data accessibility, and customized information delivery.
• Position warehousing as a core technology, and extend their leading edge to include knowledge management.

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:

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:

• Business Needs
• Information Architecture
• Technical Architecture
• Methodology and Project Management
• Organization

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.

 

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:

• Have business drivers and objectives been identified?
• Have business requirements been documented?
• Do the requirements align with business drivers and objectives? Do they focus on strategies that respond to the drivers?
• Do the information needs identify and target the enabling of specific business processes and tactics?
• Do the information needs identify the key performance indicators (business metrics) and business perspectives (dimensions, descriptive attributes) needed to measure, analyze, and optimize the
  targeted business processes?
• Do they identify the roles to be supported, their number and distribution?
• Do they identify the frequency and volume of reporting and analytical needs?

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:

• Are the information requirements modeled?
• Do they map to and support the identified business requirements?
• Have they been considered in context of the broader enterprise?
• Have key data life cycles have been traced and documented? (to identify correct points and periodicity of data capture and associated business rules)
• Are data transformations fully documented?
• Have user and operational metadata needs been identified? Is a metadata strategy to meet these needs identified and implemented?
• Have data quality issues have been identified and addressed? Do users trust the data being delivered?
• Do the users understand what data is available, what the data is, and how to use it?
• Can users get to, manipulate, and analyze the data when and in form needed?(overlaps with technical architecture benchmarks)
• Does data access and analysis clearly add business value and meet business needs?
• Is the underlying data architecture flexible and extensible? Can it support multiple analytical needs? Does it readily allow for integration of new data?

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:

• Does the technical architecture provide for the right range of data delivery services? (e.g. report publishing; canned queries; ad hoc querying; analytical models, etc.)
• Do the data and information services reach the right people? The right business processes? Do data warehouse services integrate with other business processes where needed?
• Is current access and query performance adequate? What about performance of data acquisition and warehouse refresh processes?
• Will the technical architecture scale to increasing demands to support more processing power, more users, more data, more frequent and higher volume queries, new applications and front ends,
  and new technology components?
• Is the need to secure sensitive data from unauthorized access and alteration being met? Is a proper balance of security and accessibility being achieved?
• Are the right tools implemented, deployed, and effectively used to support environment specific needs? Consider tools for data extraction, transformation, and loading (ETL), performance and
  usage monitoring, environment tuning; metadata management, metadata access and delivery; and information access, delivery, and analysis.

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:

• Have roles and responsibilities for the data warehouse been identified and documented?
• Has ownership of the data warehouse been addressed from the perspective of strategic business objectives and direction setting? From the perspective of tactical enhancement and ongoing business
  needs? From perspectives of information management and technical support and responsibilities?
• Has the key issue of business/IT collaboration been directly articulated and addressed?
• Has overall organizational readiness (in terms of business and IT skills) been considered in the data warehouse deployment strategy?
• Has the need for ongoing operational support and tuning, in parallel with continuing development, been considered?
• Has tactical support to the business community been planned for? Are support roles and responsibilities identified to help current business users understand how to leverage data warehouse
  capabilities and to identify new requirements?
• Have data stewardship roles, data quality practices, and metadata management responsibilities been implemented? Do both business and IT organizations have appropriate responsibilities here?
• Has the data warehouse been positioned organizationally within a broader context of enterprise information and knowledge management?
• Has a plan, process, and structure been established for ongoing training of users and enhancement of technical skills?
• Has any structure been put in place for ongoing monitoring of data warehouse quality, and for periodic assessments, as needed?

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):

• Does a resource leveled project plan exist?
• Are deliverables clearly identified?
• Are deliverable artifacts properly managed?
• Are frequent checkpoints and walkthroughs included?
• Are there long stretches with no deliverables?
• Are there clear issue resolution and change management processes? Are they effective and are they used?
• Are project roles and responsibilities well defined?
• Have effective lines and forms of project communication been defined and established?
• Does the project plan implement a good data warehouse methodology? (include reference to previously published Journal articles on methodology selection and evaluation)
• Does the plan provide for incremental delivery of business value according to business priorities, budget, and technical feasibility?
• Is the project adequately staffed with the right skills?
• Does the project have access to the necessary business stakeholders, subject matter experts, technical assets and technical support?

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:

 

Business Need	yes	no
	Do we have a clear statement of why are we building a data warehouse?
Will we know when we succeeded?
Do we know what to do we first?
Do we understand how data warehousing is related to other organization initiatives?
Do we have an identified, active, and committed sponsor?
Have we identified the users of and stakeholders in the data warehouse?
Do we understand how the warehouse will be accessed?
Have we identified the users of and stakeholders in the data warehouse?
Have we identified how the information will be used?
Information Architecture	yes	no
	Will we be able to make sense of the data once it’s “delivered”?
	Have we identified data quality needs? And do we know how to meet them?
	Will it be the right information?
	Can we integrate with other warehouses/marts?
	Can we track and adjust to changes in source systems?
	Can we add to it over time?
Technical Architecture	yes	no
	Will the technology work?
	Will it perform OK?
	Will it scale up?
	Do we have the knowledge, skills, and resources to maintain it?
	Is it cost effective?
	Are we making good use of tools?
	Are the tools useable by the people who need to use them?
	Will they provide the reporting and analytical functionality required?
	Will they reach and support all the right users?
Organization	yes	no
	Does the warehouse have an owner?
	Does it have a defined place in the business organization?
	Does it have a defined place in the IT organization?
	Is maintenance responsibility known and accepted?
	Have we realistically identified what is needed to manage & operate the warehouse over time?
	Do we know what to do next? Do we know how to decide what’s next?
	Do we have the people, skills, and resources to operate and sustain it?
Project Planning and Methodology	yes	no
	Are we able to “see into” the project?
	Is a structured, best practice approach being followed?
	Do we have the right people and skills?
	Can we get it done in the planned and desired timeframe?
	Does release planning align with business need?
	Are we able to resolve issues quickly and effectively?
	Can we sustain a project without drag and loss of focus?
	Are business and IT collaborating effectively?

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.

 

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.

Initial Outputs:

• List of prioritized business information needs
• Understanding of the information, technology, organization, methodology and project management context.
• Initial identification of key problems in each area.
• List of what data is available is available in the current DW and any limitations on its completeness, accessibility, data quality.

Gap Analysis

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

• Contributions to addressing prioritized business needs.
• Contributions to addressing highest impact architectural problems.
• Logical groupings of functionality based on architectural feasibility and technical cohesion.
• Organizational capability to implement.
• High level estimates of time and cost.

Package and Present Recommendations

This involves:

• Compilation and final documentation of analysis.
• Presentation of findings.
• Validation of recommendations with management.
• Decisions on which solution set(s) to pursue.

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.

• In the assessment reports and presentations, hide the details of analysis. Focus on key issues and their business impacts. Include detail in appendices.
• Take a major validation checkpoint with business stakeholders after identification of business priorities. This ensures a valid context for the remaining analysis activities.
• Look for other checkpoint opportunities, e.g. after initial identification of key warehousing issues – debriefing with subject matter experts initial interviewed. The general value of
  checkpoints is to prevent surprises, validate the analysis as you go, and sustain stakeholder involvement in the assessment.
• Avoid the political mine field as much as possible. You will go round in circles, and may become totally confused about what is and isn’t important. Don’t ignore cultural and
  organizational realities, but don’t become a participant in political and organizational dynamics that may distort objectivity of the results.
• Focus on business need, not building the perfect warehouse.
• Do constant internal team debriefing and collaboration. Assessments are inevitably dynamic and mutable. The different perspectives inherently interdependent, and the knowledge and discoveries
  of assessment team analysts must be shared.
• Establish scope of assessment and expected outputs at initiation. Define the deliverables of the assessment, and begin building toward them from the start.
• Early identification of key stakeholders and subject matter experts, scheduling of interviews and work sessions, and gathering the relevant documentation, while seemingly mundane points, are
  critical to maintaining a predictable schedule and producing meaningful assessment results.
• Don’t rely too heavily on surveys. Surveys are useful to get a general sense of perceived issues and problem areas. Objective data gathering and analysis, however, are essential to move
  beyond perceptions to core issues and their root causes.