Data Warehouse Design

Problem Definition: The definition and recording of the problem to be solved is one of the most often overlooked steps of any development effort. A problem needs to be solved, so the tendency is to jump right in a solve it. For small, negligible cost efforts this is fine. For Data Warehousing design, ignoring this step can lead to disaster. Write down and widely publish the answers to the following questions and all other questions that are appropriate for your specific effort.

Remember to keep the questions targeted to DEFINING the problem NOT solving it.

Requirements Analysis: Performing a requirements analysis is critical to the success of any project. Without a clear goal in mind, success is dubious. There are a number of different philosophies about requirements analysis: top down, bottom up, inside out, etc. The method I have found to work the best is as follows:

• Clearly state the problem(s) you wish to solve
• Identify all data sources and formats
• Identify the users of the completed system
• Formulate a specific budget - time, money, personnel
• Ask identified users to specifically state what they expect the system to do
• Ask management to specifically state their success criteria
• Separate their requirements from their "desirements." Only design to requirements. The enhancement phase is where you address the "desirements."
• Group and "bubble-up" requirements.
• Generate a prioritized requirements table listing the requirement, where it came from, the success criteria, and priority. Keep this table high-level. A table with a dozen requirements will be much easier to manage than one with hundreds.
• Produce a detailed development schedule including hardware, software, personnel, documentation, and reviews. Include outsourcing requirements and long lead-time items.
• Get a sign-off of the requirements, resource allocation, and schedule from top management before you go any further.

Design & Prototyping: There are many design methodologies. The most successful design methodologies are:

1. Rapid Prototyping (for small to medium projects)
2. Structured Development (for large or very complex projects).

Rapid Prototyping: The rapid prototyping method is illustrated in figure X. There are 5 keys to a successful rapid prototyping methodology:

• Assemble a small, very bright team of database programmers, hardware technicians, designers, quality assurance technicians, documentation and decision support specialists, and a single manager.
• Define and involve a small "focus group" consisting of users (both novice and experienced) and managers (both line and upper). These are the people who will provide the feedback necessary to drive the prototyping cycle. Listen to them.
• Generate a user's manual and user interface first. You will be amazed at what you will find out by producing a user's manual first!
• Use tools specifically designed for rapid prototyping. Stay away from C, C++, COBOL, SQL, etc. Instead use the visual development tools included with the database.
• Remember a prototype is NOT the final application. Prototypes are meant to be copied into production models. Once the prototypes are successful, and then begin the development processing using development tools, such as C, C++, Java, SQL, etc. Structured Development When a project has more than 10 people involved or when multiple companies are performing the development, a more structured development management approach is required. Note that rapid prototyping can be a subset of the structured development approach. This approach applies a more disciplined approach to the data warehouse development. Documentation requirements are larger, quality control is critical, and the number of reviews increases. While some parts may seem like overkill at the time, they can save a project from problems, especially late in the development cycle.

Development & Documentation: Once the requirements analysis is well underway, the prototypes are working, and the focus groups are becoming happy, it's time to begin the development. Coordinating hardware and software purchases and upgrades, server and hardware installation, software and database development, documentation guides and manuals, reviews, and testing can become a full-time job. The key to keeping a handle on all of this to maintain a good written schedule that everyone can view and to have periodic "all-hands" reviews.

Test & Review: Testing and Reviews take place throughout the development cycle, including prototyping, development, deployment, operations, and enhancements. It never ends. It's wise to place a single individual in charge of testing and reviews. This is not a popular job, but it is critical for developing a system that works and meets each of the requirements. Be sure to empower this person (usually a quality assurance engineer) with the appropriate authority.

Deployment & Training: Scheduling training sessions concurrently with the installation can be an effective use of time. Don't skimp on the training. Make sure you have training in the budget from the beginning and don't dip into it.

The best way to ensure success is to effectively train the users so that they will actually use the system and possibly sing its praises.

Operation: Data warehouses usually contain two or more servers. Tasks such as backups, bug fixes, software updates, hardware maintenance and upgrades, media services, account maintenance, security patches, and other similar tasks must be performed regularly. Operation and maintenance of such services requires an operations staff.

Enhancement: There is always one thing you can count on: "Requirements Creep." The more successful the data warehouse, the faster requirements creep will occur. As your users become more sophisticated they will want more and more capabilities. If you can respond quickly and efficiently, your users will again sing your praises

Help Desk: You might think that good manuals and good training would be sufficient to effectively use your data warehouse. Not so. A knowledgeable, available, responsive help desk is critical to the overall success of the project.

Users will always find new uses for a well-designed system and problems will inevitably occur. Without a help desk, a data warehouse can become dated and under-utilized.