Affiliated with:

Staffing a Data Warehouse or Analytics Project – Part III – Best Practices

image 63

Best practices for staffing any data warehouse / business intelligence /analytics initiative should be followed to ensure success and sustainability.

Every major program or initiative has some important best practices, critical success factors, and basic activities that may help to start the effort and become successful more easily and efficiently, and the realm of data warehousing / business intelligence / analytics is no different.  To cover the most common factors, most researchers define criteria by the sizes of development staff and limit suggested points to four or five, so an organization can manage the review of each point and implement them with few challenges.

Best Practices

For organizations with sizable analytics needs (over 20 total staff working on analytics efforts across the organization):

  1. Centralize the data warehousing / business intelligence resources.  The skills required to design and develop data warehousing solutions are not inherent in all programmers, since these skills are not common for all programmers and analysts.  Allowing anyone without the proper skill set to develop the DW/BI/analytics solutions risks having developers spend one year on what should be a three month solution.  Then those resources return to other projects for some time, and revisit the DW/BI environment after six months or a year.  At that point, they cannot remember how to navigate the tools and language.  In addition, they may lack best practices knowledge around DW/BI/analytics development processes and techniques.  Successful organizations have a separate team for DW/BI development / programming staff, focused on that environment, the best practices and tools used there.
  2. Separate resources by area of focus: data vs. usage.  Large organizations have the challenge of serving many users.  Usually, this means many types of access tools and broad collections of data from many areas.  These tools and technologies can require a high learning curve.  By separating skill sets, it becomes possible to build experience by component, within the team, rather than using generalists.  This provides a best practice-based foundation of well designed architecture, consistent development, effective support, and continual maintenance.  Additionally, processes are refined and followed according to proven practices and standards.
  3. Install one or two DW/BI architects with an overall understanding of the various technical layers of the solution.  This person must have prior successful data warehouse architecture experience similar to the proposed solution.  Since that skill can be difficult to find, it may be necessary to hire an experienced consultant with several examples of successful DW/BI architecture and then having that individual work with a senior level internal person who has strong design skills for knowledge transfer.  This resource will focus on component layers, approaches, techniques, tools, and infrastructure.
  4. Build a small team of database experts, who work with the data modelers.  They will handle system and application related database design, development, support, and auditing/monitoring.  These resources are crucial to effective organizations.  While Extract, Transform, Load (ETL) developers are expected to deliver programs, the database administrators (DBAs) help ensure that all programs and processes are efficient and that the data required for each program / application / extract / etc. is available and conforms to the organization’s database and usage standards.

For organizations with smaller analytics needs (fewer than 20 total staff working on analytics efforts across the organization):

  1. Develop up to a quarter of the staff to be specialists who understand most aspects of a technology – like Business Intelligence (BI) or ETL.  While only 1 person may be focused on ETL all the time, that will give them the ability to learn some of the more complex design techniques and improve the overall team skills and progress.  Choose someone that is strong at design and has good teaching / mentoring skills.
  2. Hire at least one resource with significant prior data warehousing / business intelligence experience or use a consultant.  With a smaller team, it is especially critical to have someone who can start the effort effectively, with the right with tools and processes, and in a reasonable time.
  3. Get training for all technical resources on processes, professional skills, and techniques.  Let them learn tool syntax via limited training, but place more focus on hands-on learning and leveraging knowledge bases.
  4. Don’t ignore the business analyst position.  It is common for smaller organizations to think that they just have technical resources meet with the business users and build exactly what they ask for.  A business analyst will help outline the right solutions that will provide much more value at less cost for the user community.  This is especially important for BI and analytics solutions.

Critical Success Factors

  1. Process training is much more important than tool training.  Language or tool syntax is important for developers to be able to deliver their solutions.  However, it pales in importance when compared to setting up the right processes and techniques.  Spend at least three times as much energy on process training as on syntax training.  Once a developer knows the process of “how to code,” switching languages is as easy as learning syntax.  However, if a developer is the best Java developer in the corporation but now is expected to excel in the design of data integration, without significant training and guidance, they will likely have several failures or very poor designs before they ever start building effective solutions.
  2. Spend a little extra time at the start to define an architecture and roadmap that outlines the path for DW/BI/analytics as described by the business needs.  Data warehouse architectures DO NOT evolve.  Imagine a star schema data mart meant for a pilot project turning into a 3rd normal form atomic data warehouse after 5 years.  It is possible to define an incremental approach that will allow the architect to build a solution by component, but architectures just do not evolve.
  3. Ensure the training and travel budget is two to three times the normal IT training budget.  While building an organization, it is imperative to recognize the difference in skill sets and the training required to develop them into an effective DW/BI/analytics organization.  Include training in areas such as metadata management, data quality, data governance for DW/BI, and other areas not usually included in regular DW/BI training.  Consider certification training for senior resources.
  4. Buy infrastructure periodically, not constantly.  Right sizing the infrastructure will help to avoid having constant infrastructure projects that conflict and constrain the data and usage projects.  Growth in DW/BI/analytics is imperative, to demonstrate success.  Thus, try to make it a practice to buy 100 – 300% more processing and storage power than estimated anticipate, to enable additional growth without every project having a separate dependency on adding infrastructure.  There is also a significant cost savings in building infrastructure periodically.
  5. Operations – separate infrastructure support resources consistently and leverage these resources across the applications.  Every organization has an approach for infrastructure and it is more important to follow that than start something new.

Tips and Tricks

For sizable analytics needs:

  1. Encourage resources to switch between areas of expertise every other year.  This helps to build the next senior analysts, architects, and team leads while strengthening the overall team.  Any performance issues must be addressed and should be held back from moving or they will slow down all efforts.
  2. Conduct formal walk throughs of all ETL design, and code by DBAs, Data Modelers, and Data Quality resources.  This will help identify problems in architecture or design before the users encounter them.  Too often, the DBAs and Data Modelers are not invited to these meetings, but their specific expertise can be critical to the process.
  3. Build success before considering outsourcing.  Outsourcing the right components at the right time can be a key part of your strategy.  However, developing proven processes and solution design templates greatly enhances the probability of success when dealing with outsourcing.
  4. Focus ETL and BI tool support within those groups, and do not put responsibility for tool configuration into the hands of operations.  Since operations staff does not work directly with the customer, their priorities and experiences are not business-driven.  Typically, they are not in-depth users of the tools.  This can result in restrictions or controls that limit the effectiveness of the tools and the effectiveness of the support.

For smaller analytics needs:

  1. Don’t go overboard with tools – have only one or two options for ETL and only one or two options for BI.  If every user gets to pick their own tool, it becomes a practice of resources constantly learning new tools, but never mastering any of them.
  2. Utilize user groups for seeing examples of what other organizations have done and getting contacts that can help with specific technical challenges.  Almost all of these user groups are free and only meet for a half day to a full day once a quarter.  This is a significant potential source of building experience and might even lead to connecting with resources that will help with a design or development challenge through a phone call.
  3. Encourage resources to build on existing work.  This is especially pertinent in ETL work.  Good developers love to write complex code.  Unfortunately, many of them should be coached to leverage work that has already completed 95% of what they need for the next job.  Leveraging this could lead to producing much more work with less effort.
  4. Stay away from the big vendors.  For smaller, more focused organizations, the big vendors will generally consume funds and define approaches that are beyond the organization’s requirements.  Most smaller analytics needs do not require the use of the top of the line hardware/software that is invariably recommended.  Big vendors also tend to bring in a small army of consultants and do not train the client’s staff well.


Best practices and proven critical success factors can lead any organization to an effective data warehouse / business intelligence / analytics environment.  Leveraging some of the best practices, critical success factors, and tips above may help improve effectiveness or speed development in a complex endeavor.


Sid Adelman

Sid Adelman founded Sid Adelman & Associates, an organization specializing in planning and implementing Data Warehouses. He has consulted and written exclusively on data warehouse topics and the management of decision support environments.

© Since 1997 to the present РEnterprise Warehousing Solutions, Inc. (EWSolutions). All Rights Reserved

Subscribe To DMU

Be the first to hear about articles, tips, and opportunities for improving your data management career.