Chapter 15: Operations and Maintenance

Trust but verify

Overview

Weve seen too many DW/BI teams postpone thinking about how to operate their new system until its nearly in production. When deadlines are looming and users are clamoring for data and reports , its too late to start designing your operating procedures. Youll be making stuff up as you go along, and Mistakes Will Be Made.

There are two major sets of issues to think about with respect to the ongoing operations of your system. The first set of issues revolves around communicating with, training, and supporting the users. Of course youll be publishing reports to them about the business, but you also need to communicate with them about the DW/BI system itself. What kind of information or metadata will you share? How will you publish it?

Weve already talked about training the user community, but how do you keep that training fresh? What plans do you have for training new users? Will you need to extend the training? In terms of support, how will you answer user questions, both simple questions like how to connect, and very complex analytic questions like how to categorize customers.

The second set of issues focuses on technical systems management. You need to think, long before you go into production, about a host of issues. Your decisions about these operational issues will affect your system configuration and design. These issues include how youre going to monitor resource usage and business usage, and how youre going to report on this same usage. How are you going to automate operations? How will your database administrators kill users queries?

At launch your systems performance might be great, but with increased data volumes and user load, performance might degrade. How will you identify and solve bottlenecks? How can you be proactive about tuning the system to prevent bottlenecks from forming?

One of your most important tools for improving performance is to implement a partitioning strategy. But partitioning requires a complex data management process during incremental loads. How do you need to modify your ongoing ETL process to accommodate data partitioning?

Finallybut very importantyou need to plan for, implement, and test your backup and recovery strategy.

The now-familiar Business Dimensional Lifecycle diagram (see Figure 15.1) places operational issues at the end of the Lifecycle where you loop back around on the next iteration. Operationally thats accurate, but as we discuss throughout this chapter, you need to be planning for safe operations from the outset.

Figure 15.1: The Business Dimensional Lifecycle

In this chapter youll find answers to the following questions:

• What do you need to worry about with respect to maintaining and extending the BI portal and BI applications?

• How do you execute Integration Services packages in production?

• How do you monitor the system? What kinds of counters and events should you track? How can you see what users are doing right now, and kill bad queries?

• How do you conduct performance tuning of a DW/BI system?

• What data do you need to back up and how often? How should you perform backup and recovery?