Part of:Maximizing the potential of the data warehouse
How to evaluate and optimize data warehouse performance
Organizations build data warehouses to satisfy their information management needs. Data warehouse optimization can help ensure that these warehouses achieve their full potential.
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 they provide a poor cost-benefit ratio when they aren't.
The key to optimizing data warehouses for success is choosing the right combination of products and processes and effectively administering them by 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 daily business objectives.
When combining that high level of care with increasingly constrained staffing levels, there is often little time to optimize the environment to achieve its maximum potential.
As the system matures and expands in data volumes, data sources, processing complexity and end users, IT shops are unable to step back and analyze the environment. The more mature the data warehouse system becomes, the more challenging 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 the needs of the business. Although some of the processes are similar, there are significant differences between evaluating an environment's potential to achieve its business objectives and conducting problem analysis to fix something, for example.
The process to optimize data warehouse performance
Beginning data warehouse analysis
Staffing a data warehouse optimization team is typically fluid. A core team of players should consistently participate throughout the entire optimization project lifecycle, but other personnel will be added and removed as necessary. Team roles such as executive sponsor, project sponsor, project manager, technical evaluation team member and business analyst are consistent with other analysis projects.
The evaluation begins by identifying and reaffirming the data warehouse's goals. Much like an organization's corporate mission statement, an action-based statement declares the purpose of the warehouse, what it does and its business objectives.
The team reviews or documents the system's data sources, dataflows, technologies, people, processes, products and customers. Having a visual depiction of the data warehouse environment greatly enhances discussions and improves the overall analysis process.
Optimize the process
Analysis projects excel when the process begins with a stated objective. From strategic high-level assessments to more granular subject area evaluations, a stated objective helps define a project's participants, timelines and costs.
The project team collects information at a high level and evaluates the findings to narrow the scope. This iterative process continues until the team documents and presents the findings to the project stakeholders.
Another best practice is not attempting to evaluate and optimize every single issue the analysis identifies. Pick data warehouse optimization and modernization battles by prioritizing the issues.
One-on-one meetings, focus groups and surveys
From the corporate office to technical support, everyone who uses or administers the warehouse is a source of information. The key to finding which areas are not reaching their full potential is to identify and interview people that interact with the data warehouse environment.
Here's a noninclusive list to jumpstart the identification process:
data governance and security team members;
technical architects;
system administrators;
database or data administrators and modelers;
data and business analysts;
ETL developers; and
C-level to ground-level end users.
There is a common set of questions that can apply to all participants. Some of the more obvious examples are what they like and dislike about the data warehouse. Is it meeting their needs and is the environment getting better, stagnating or getting worse? An important topic to address is comparing the organization's current needs to the data warehouse's initial goals and success factors.
Phrase the questions in a way that allows participants to easily respond as precisely as possible. If asking how well a data warehouse is meeting their needs, provide multiple choice answers allowing them to say if it is fully meeting their needs, meeting most of their needs or not meeting them at all. Follow up the question with an open-ended text box that asks them to further explain their response.
Having a visual depiction of the data warehouse environment greatly enhances discussions and improves the overall analysis process.
Tailor additional role-based questions to the participant's subject area. For technicians, questions should focus on ease of administration, monitoring capabilities, performance, system reliability and so on. A very short list of examples for business user discussion topics include the system providing the insights they need, ease of access, system performance, visualizations and information sharing capabilities. The last question should always be, "What else should we be asking?"
One-on-one meetings, group discussions and surveys are great for feedback. Almost all the Q&A discussions and survey information will result in the data warehouse optimization team scheduling follow-up meetings to clarify past responses and facilitate additional fact-finding.
The fact-finding and analysis process is iterative and becomes more granular as the project matures. The investigation begins at a global level and the questions and technical statistics become more detailed until the optimization team identifies why a system isn't fully meeting its objectives. The team continues to identify and interact with subject matter experts and end users throughout the analysis process.
By nature of the investigative process, organizations can better understand how big of an effect an issue has on the data warehouse's ability to meet business objectives. They also gain insight into who it affects and everything required to remediate it. The team uses the information collected to prioritize each issue and identify a cost-benefit ratio. Then the team begins to organize their findings and document recommendations.
Presenting the findings
Formal evaluation documents and scheduled visual presentations can communicate findings to stakeholders. The documentation provided to business and IT management should include:
an executive summary;
the areas of evaluation;
a table of summary findings;
issue description;
level of impact and priority;
cost-benefit ranking; and
a list of findings with detailed analyses and potential solutions.
Solutions could range from additional user education to system redesigns and new platform development. It can help decision-makers prescribe solutions by providing information that explains the effect an issue has on an organization, as well as the costs and benefits of fixing it.
