Implementing a Data Warehouse: A methodology that worked

An essential aspect of DW project implementation (and other IT projects as well) is using established business practices to manage development and implementation.  Discussion of use of these “due diligence” practices in Step 1 establishes the foundation for starting the DW project with the proper levels of management oversight.  Step 2 presents examples of business models necessary for the DW developer to understand the needs of the business that the DW will serve.  Other DW books describe the data modeling process but neglect to provide modeling instruction and actual examples to insure that the DW is properly aligned with business needs.  An elegant data warehouse that doesn’t meet the needs of the business is wasted effort.

Step 3 documents and displays the level of detail needed to define CSF’s (Critical Success Factors) and KPI’s (Key Performance Indicators). If calculations for these important metrics are not defined in detail, and consensus to use them is not reached, then again, the most elegant data warehouse implementation is a wasted effort. In addition, developing and documenting functional requirements is essential in identifying legacy system reporting deficiencies.

Step 4 describes how to access and display field level information on the iSeries platform.  Actual shots of the resulting screens are shown.

Step 5 presents the functional contents of an Request for Proposal (RFP) for a Data Warehousing tool-set. 

Step 6 presents the progression of work required to build a data warehouse.  Step 6 also:

• ·        Describes and displays a hybrid dimensional to flat file data model that may be, in reality, the best data organizational model for a typical data warehouse.  Also, a table is included showing examples of data file field cryptic names and their corresponding metadata name.
• ·        Shows examples of data cleansing problems encountered at the authors company and describes how the problems were resolved. 
• ·        Describes and displays how the data warehouse libraries are organized on the iSeries.
• ·        Shows examples of actual Load function selection criteria. The examples are shown to document the level of selection criteria explanation needed to coach users.

Step 7 presents a method to select data mart contents. Not elegant, but workable. 

Step 9 presents a necessary discussion on how adults learn to use technology tools. An actual example of how Data Warehouse user training documentation can be formatted is included. 

Again, the purpose of this book is to provide actual examples of processes, work papers, technical documentation, pitfalls, and measures of success that led to implementation of our successful data warehouse.  If a reader wants to understand an implementation methodology that worked (we saw over 12,000 data warehouse accesses last year) – this book will provide that view.

Step 1.  Practice the due diligence of our profession!

Our business partners in the academic and software provisioning communities have given us a set of tools to perform the due diligence of our profession. We need to use these tools. A number of tools are essential for our purposes here:

• ·        System Development Life Cycle Methodology (SDLC)
• ·        Statement of Work (SOW)
• ·        Request for Proposal (RFP)
• ·        Formal Method of Project Management

Step 2.  Build business models

A building is not constructed without a blueprint for the builder to follow. Similarly, business and methodology models are the beginnings of the blueprint for a data warehouse. Different types of models are useful. These models depict areas such as operational processes, management levels, software interfaces, and data file key relationships.

Step 3.  Define business requirements based upon the business models

It is essential that business performance metrics be documented and available. KPIs (Key Performance Indicators) and CSFs (Critical Success Factors) used to monitor business performance must be provided to data warehouse developers. A primary goal of the data warehouse is to present the KPIs and CSFs to data warehouse users.

Step 4.  Identify data sources based upon defined requirements

Essential questions must be answered. For example:

• ·        Where does raw data that will be used in the data warehouse reside?
• ·        What raw data is necessary to generate required derived data?

Analysis of the lowest level of data available in each system is required to answer these questions.

Step 5.  Select a data warehouse toolset based upon defined requirements

Once functional requirements are documented, we will need to find and install a data warehouse and business intelligence toolset that is appropriate for our business. Using the work tools defined in step 1, we will organize our requirements and start our search for the correct vendor.

Step 6.  Build the data warehouse

The work of designing and building the physical data warehouse is generally divided into three categories: COPY (sometimes called Extract), BUILD (sometimes called Transform), and LOAD. These general categories will define the following:

• ·        Data to be selected for the data warehouse
• ·        Processes to prepare selected data
• ·        Processes to build files for the data warehouse
• ·        Processes to allow data warehouse users to extract and view information

Step 7.  Build the data marts

Data marts contain subsets of data warehouse information. These data subsets are usually developed for specific data warehouse users – for example, Operations, Finance, and Human Resources – or any other specific departmental needs. Data marts eliminate the need for users to access the entire data warehouse when all they want to see is their own information.

Step 8.  Build the initial OLAP templates

Online Analytical Processor (OLAP) templates provide views into a data warehouse and data marts. These templates allow users to select and filter information within the data warehouse. Data warehouse developers provide initial OLAP templates.

Step 9.  Train the data warehouse users

It isn’t enough to develop an innovative and robust data warehouse. Success with the data warehouse is measured by use, not by technical prowess! Users need to be trained and trained and trained….

Step 10.  Deploy the data warehouse

Data warehouse deployment is both a functional and political issue. Which users will have what access to the data warehouse? What security will be in place? Is the infrastructure prepared for deployment? Do users have the correct workstation hardware?

Step 11.  Begin data warehouse support

Poor post deployment support will cause the implementation to fail. Period.