Design Introduction

WhereScape RED can be used to build data warehouses based on any number of design philosophies from EDW 3NF enterprise data warehouses with consumer data marts through to federated or conformed star schema-based warehouses. In the absence of another approach, the following methodology can be used for the design of data warehouses.

Design Approach

The concepts behind the WhereScape Pragmatic Data Warehouse Methodology are as follows:

1. Building an enterprise-wide data warehouse is a process—an evolution rather than a big bang. Start small and grow the warehouse in manageable chunks until all the pieces are in place. Once you reach that stage, changes and new source systems will continue the process.
2. You need to understand the big picture, but not get lost in it. Talk to all the various departments, business units, and companies within the organization. Do so at a relatively high level and try to understand how the information from each area impacts or affects the others. Identify commonalities and areas where the same information is handled in different ways. This process should take days or weeks not months.
3. Identify the high value, high return, and possibly easiest areas of the business. Drill down in these areas and break down the workload into small manageable chunks of work, for example, one to two analysis areas. Agree on the first component of the data warehouse and do that.
4. Get an understanding of the source system for this first component or analysis area. If possible, get an entity relationship diagram and talk to the people who built or supported the application. Identify the tables that contain the key information you will need. The goal is a quick and initial view, a detailed specification is not required.
5. Design the first component. This design should be a first draft and can be written rather than using a design tool. Remember at this stage what the end users want is not known, so don't set the design in concrete, or spend a large amount of time in this area. 
   

   Note

   Experienced users of WhereScape RED often dispense with a design and go straight to building a prototype.

6. Build a prototype. In most cases, this should not take more than one or two weeks—experienced WhereScape RED developers can expect to build prototypes in hours or days. Concentrate on the detailed and descriptive data, unless you have a clear picture of the summarized requirements. Do as much as possible in terms of validating the data back to the source system. If dealing with a large or complex source system, then only deliver a segment in this prototype, e.g. one branch, one store, one product group, etc. Keep It Simple.
7. Demonstrate the prototype to a group of key users. Then drill down to a subset of key users (we recommend no more than three) who will help you go forward with the design. If possible, give these users access to the prototype and get them to use the data. Stress that data accuracy is not the issue at this stage, but rather the look and feel.
8. Enhance the prototype with the feedback provided by the users. Again a quick process. If complicated requirements evolve, then create a plan to implement, doing the highest value parts first. The goal is to get quick buy-in and support from the two or three key users.
9. Provide key users access to the reworked prototype and get them to use the data. Have them define the business names for all the measures and attributes and to define any pre-calculated measures that they frequently use. Get them to define the hierarchies in the data. Ascertain the commonly utilized queries and reports, and see if there would be a better way of presenting these.
10. From the user feedback look at the need or possibility of using higher-level fact tables, such as summaries, aggregates, snapshots, or composite rollup tables.

The concepts and methodologies for designing and building a data warehouse are beyond the scope of this manual.
It is assumed that the reader understands the basic concepts of a data warehouse, and is familiar with modeling, EDW 3NF, star and snowflake schemas, dimensions, fact tables, etc.
Refer to the WhereScape website for a basic overview of data warehouse design if required.