How to Assess and Optimize Data Warehouse Performance


Data warehouses are complex architectures that require a wide range of strategies, technologies, products, workflows, skill sets, and best practices. They can be cost-effective if implemented and administered correctly, but offer poor value for money when not.

The key to successful data warehouse optimization is choosing the right mix of products and processes and administering them efficiently using internal and industry standard best practices. The challenge is that most data warehouses require a high level of administrative attention to successfully achieve their day-to-day business goals.

Combining this high level of care with ever-shrinking manpower, there is often little time left to optimize the environment to reach its maximum potential.

As the system matures and grows in data volumes, data sources, processing complexity and end users, IT workshops are unable to step back and analyze the environment. The more mature the data warehouse system becomes, the more difficult it is to ensure that it continues to provide maximum business value.

A few tips can help organizations analyze a data warehouse to optimize it to meet business needs. Although some processes are similar, there are significant differences between assessing the potential of an environment to achieve business goals and performing a problem analysis to solve something, for example.

The Data Warehouse Performance Tuning Process

Start of data warehouse analysis

Staffing a data warehouse optimization team is usually fluid. A core team of stakeholders should participate consistently throughout the optimization project life cycle, but other staff will be added and removed as needed. Team roles such as executive sponsor, project sponsor, project manager, technical review team member, and business analyst are consistent with other analytics projects.

The assessment begins by identifying and reaffirming the goals of the data warehouse. Much like an organization’s mission statement, an action-based statement declares the warehouse’s purpose, what it does, and its business goals.

The team reviews or documents data sources, data flows, technologies, people, processes, products, and system customers. Having a visual representation of the data warehouse environment greatly enhances discussions and improves the overall analysis process.

Optimize the process

Analytics projects excel when the process begins with a stated goal. From high-level strategic assessments to more granular domain assessments, a stated objective helps define a project’s participants, timelines, and costs.

The project team collects information at a high level and evaluates the results to narrow the scope. This iterative process continues until the team documents and presents the results to project stakeholders.

Another best practice is to not attempt to assess and optimize every problem identified by the analysis. Choose data warehouse optimization and modernization battles by prioritizing issues.

Individual meetings, focus groups and surveys

From corporate headquarters to technical support, everyone who uses or administers the warehouse is a source of information. The key to identifying areas that are not reaching their full potential is to identify and interview the people who interact with the data warehouse environment.

Here is a non-exhaustive list to start the identification process:

  • members of the data governance and security team;
  • technical architects;
  • system administrators;
  • database or data administrators and modelers;
  • data and business analysts;
  • ETL developers; and
  • C-level to basic level end users.

There is a set of common questions that may apply to all participants. Some of the most obvious examples are what they like and dislike about the data warehouse. Is it meeting their needs and is the environment improving, stagnating or deteriorating? An important topic to discuss is comparing the current needs of the organization to the initial goals and success factors of the data warehouse.

Phrase questions in a way that makes it easy for participants to answer as precisely as possible. If you ask them how well a data warehouse meets their needs, provide multiple-choice answers that let them say whether it fully meets their needs, meets most of their needs, or doesn’t meet them at all. Follow the question with an open text box that asks them to explain their answer further.

Having a visual representation of the data warehouse environment greatly enhances discussions and improves the overall analysis process.

Tailor additional role-based questions to the participant’s domain. For technicians, questions should relate to ease of administration, monitoring capabilities, performance, system reliability, and more. A very short list of sample discussion topics for business users includes the system providing the information they need, ease of access, system performance, visualizations, and information sharing capabilities. The last question should always be, “What else should we ask for?” »

One-on-one meetings, group discussions, and polls are great for getting feedback. Almost any question-and-answer discussion and survey information will allow the data warehouse optimization team to schedule follow-up meetings to clarify past responses and facilitate additional fact finding.

The investigation and analysis process is iterative and becomes more granular as the project matures. The investigation begins at the global level and technical questions and statistics become more detailed until the optimization team identifies why a system is not fully achieving its goals. The team continues to identify and interact with subject matter experts and end users throughout the analysis process.

By the nature of the investigation process, organizations can better understand the extent to which an issue is affecting the data warehouse’s ability to meet business goals. They also have an overview of who it affects and what is needed to fix it. The team uses the information gathered to prioritize each issue and identify a cost-benefit ratio. Next, the team begins to organize its findings and document its recommendations.

Presentation of conclusions

Formal evaluation documents and scheduled visual presentations can communicate results to stakeholders. Documentation provided to business and IT management should include:

  • an executive summary;
  • areas of assessment;
  • a table summarizing the conclusions;
  • Description of the problem;
  • level of impact and priority;
  • cost-benefit ranking; and
  • a list of results with detailed analyzes and potential solutions.

Solutions could range from additional user training to system overhauls and new platform development. It can help decision makers prescribe solutions by providing information that explains the effect of a problem on an organization, and the costs and benefits of solving it.


Comments are closed.