The Data Warehousing Development Lifecycle

The Data Warehousing Development Lifecycle I.

Planning A.

B.

C.

D.

E.

F.

Select the Implementation Strategy 1. Top Down Approach 2. Bottom Up Approach 3. Combination Approach Select the Development Methodology 1. Structured Analysis & Design (Waterfall) Method 2. Spiral Development Method Develop the Business Objectives 1. What is the target market for the data warehouse? 2. What are currently used or planned platforms? 3. What are planned capabilities in terms of features and functions? 4. What are the various data sources that can and/or must be integrated into the data warehouse? 5. When should the data warehouse become operational? Select an Initial Implementation Scope 1. Typical dimensions for restricting scope: a) Number & Types of Department Served b) Number of Data Sources Selected c) Subset of Enterprise Model Selected d) Budget Allocated e) Time Allocated for 1st Project 2. Dimensions can be broadly divided into two categories: a) Scope determined from the perspective of the business user of the warehouse (1) Who are the departments that need to use the data warehouse initially and for what purpose? (2) What are the range of business queries that must be initially answered by the data warehouse? b) Scope determination based on technology considerations (1) What is the size of the data warehouse metamodel? (2) What is the size of data inside the data warehouse? (3) What are the input sources of data and how many are they? (4) How usable is the data from the data sources? (5) How well are the data sources documented? (6) What is the level of Integrated Management Facilities? (7) What is the availability of logical models and CASE tools? Is there an Enterprise Data Model available? (8) Can existing skills and human resources be used? Will the data warehouse be implemented on existing platforms or on platforms similar to existing ones? Select an Architectural Approach 1. Operational storage versus using copies of operational data 2. Data warehouse only 3. Data marts only 4. Data warehouse and data marts 5. Platform and infrastructure partitioning 6. Two-tier client/server architecture 7. Three-tier client/server architecture Build a Program and Project Plans Budget

1. 2.

G.

H.

II.

Articulate both a program plan and a set of project plans Reserve an adequate budget for the program while committing the expense for specific projects a) Cost estimation, based on the organization’s history with software development b) Cost estimation, based on the Reference Architecture 3. Provide measures for estimating the payback of the data warehouse a) Measures of cost recovery or cost savings b) Measures of opportunity creation c) Measures of revenue creation d) Measures of market growth e) Measures of competitive advantage f) Measures of customer satisfaction Develop Business Usage Scenarios to clarify end user expectations 1. Scenario consists of: a) A clearly identified business user with clearly identified business role b) A functional area that is sponsoring the data warehouse or data mart and will use it when it is delivered c) One or more business queries of crucial interest to the functional area that is not currently satisfied by existing information systems 2. Are helpful in the following instances: a) Building acceptance criteria for the data warehouse b) Identifying the data warehouse metamodel c) Identifying the amount of historical information needed d) Identifying the dimensions of interest to end users e) Identifying the need for data marts/data warehouse Collect the Metadata

Requirements A.

Define the Owner’s Requirements 1. Potential requirements gathering areas: a) Business objectives b) Data warehouse/data mart scope & objectives, customers, & stakeholders c) Customer requirements d) Sources of data e) Plan, such as budget, schedule, resources f) Impact on current investments, such as people, technology, & training 2. Part of the business requirements are also scope specifications for the data warehouse in terms of the following: a) Subject Areas - topics of interest to various business functions (1) Example: the marketing department may have interest in one or more of the following topics: (a) Market research (b) Competitive analysis (c) Buyer behavior (d) Market segmentation product (market matching) (e) Pricing and budgeting decisions (f) Product decisions (g) Promotion decisions (h) Channel decisions (i) Forecasting trends (j) Benchmarking

(2)

B.

C.

Analysis of the selected topics yields specific subject areas for the marketing department: (a) ORDERS (b) PROMOTIONS (c) MARKETS (d) SALES (e) TIME CYCLE b) Granularity - lower the granularity, the higher the amount of detail c) Dimensions (1) Time (a) Day (b) Week (c) Month (d) Quarter (e) Year (2) Customer groups (a) Customer (b) Market segment (c) Market (d) Industry (3) Product families (a) Product (b) Product family (c) Product line (4) Geography and location (a) Sales rep (b) Territory (c) District (d) Country region (e) Country (f) International region (g) Corporate (5) Organization structure (a) Department (b) Business unit (c) Division (d) Strategic business unit (e) Subsidiary (f) Corporation (6) Organization specifics (7) Industry specifics Define the Architect’s Requirements 1. Enterprise Architecture Planning a) A Data Architecture (1) E-R Models, etc. b) An Application Architecture (1) Catalog of applications & function each provides (2) C-R-U-D Matrix c) A Technology Architecture Define the Developer’s Requirements 1. Specific applications, interfaces, computers, databases, communications, and user-interface screens. 2. Technology requirements 3. Deployment requirements

D.

III.

Analysis A. B.

IV.

Develop logical data warehouse/data mart models Define the processes needed to connect the data sources, the data warehouse, the data marts, and the end user access tools together

Design A.

B.

V.

4. Data warehouse production readiness requirements 5. Requirements for development and deployment personnel and their skills Define the End-user’s Requirement 1. Workflow - how does the functionality offered by the data warehouse fit into the end user’s daily workflow? 2. Query requirements 3. Reporting requirements 4. Data analysis requirements a) Types of activities (1) Slice and dice - separate data items in various ways (2) Drill-down - expose progressively more detail (3) Data mining - look for hidden patterns (4) Datasurfing - browse in an undirected manner (5) Download and make local modifications (6) Build business models, for example, using spreadsheets b) Data viewing (1) Two-dimensional - spreadsheets, relational (2) Multi-dimensional (3) Reports and charts (4) Living sample database

Detailed design of the data architecture 1. Develop physical data models for the data warehouse and data mart storage databases. 2. Map the physical data models of the data sources into the physical models of the data warehouse/data marts. Detailed design of the application architecture 1. Processes that are internal to data sources and relate to cleanups or partial extractions of information and processes that connect the data sources to the data warehouse/data marts 2. Processes that are internal to the data warehouse and are used for housekeeping purposes 3. Processes that connect the data warehouse to the data marts (if used) 4. Processes that are internal to the data marts (if used) and are for housekeeping purposes 5. Processes that connect the data warehouse/data marts to end user tools 6. Processes that are internal to the end user workstations and are used for establishing connectivity to the data warehouse and data marts and for launching analysis tools 7. Processes that support management and administration and housekeeping task for the data warehouse as a system

Construction A. B.

Programs that create and modify the databases for the data warehouse/data mart Programs that extract data from data sources

C. D. E.

VI.

Programs that perform data transformations such as integration, summarization, and aggregation Programs that perform updates of relational databases Programs that search very large databases

Deployment A. B. C. D. E. F. G. H. I. J. K. L. M.

Provide initial installation including facilities for initial data connections to sources and for data update and synchronization Plan and deliver a staged implementation Plan and provide training and orientation for all classes of users Plan and implement platform upgrades and maintenance needed by the data warehouse solution when necessary Provide user and systems administration Provide archive and backup capabilities Provide recovery capabilities Ensure integration into existing infrastructure Provide access controls and security Ensure full availability and process for handling breakdowns of the systems and its infrastructure components Plan and provide for information merchandising Plan and provide information directory/catalog Plan and provide for information browsers