Notes

Outline

IS660J: Data Warehousing “Building an Enterprise Data Warehouse” Lecture 7 Professor Burns

Enterprise Data Warehouse A planned, integrated, managed store of relevant corporate data optimized for analysis, query and reporting functions. Optimal design is a series of conformed dimension tables and transaction-grained Reasonable Staging, if necessary Clean (correct), consistent ( 1 version), relevant(data organized to answer business questions)

Several Approaches 3NF EDW – large, mainframe oriented, years 3NF EDW w/ dependent data marts – requires a 3NF EDW to exist Independent, unintegrated data marts - redundancy Integrated, dimensional enterprise data warehouse – cross process analyses without redundancy, deliver value early

Traditional Approach to EDW 3NF data warehouse precedes any and all dependent data marts (each addressing a single subject area). Building data marts in advance would only lead to redundancy

Incremental Approach to EDW Developed by Greg Jones and advocated by authors Consider two immutable laws of DW The most complex part of building a data warehouse is the loading of data, and It is impossible to evaluate any design until legacy data is loaded and shown to the users.

Incremental Approach Jones proposed 2 changes to traditional approach Build an EDW a subject area at a time, loading data into the first subject area design as soon as technically feasible, and iterate collection of feedback from users thereafter (weeks, not months). Abandon the mandate of building a large, comprehensive, data warehouse prior to building star schemas for individual subject areas. Prove value earlier, reconcile onerous issues early, leverage advances in tools and software. Dimensional EDW, done as prescribed, actually leads to less redundancy and improved consistency without  a 3NF data warehouse.

Dealing with Change 5 Business Elements Concept of Operations – organizational structure (products, divisions, geographies, ect) Business Model (how are $$$ generated and costs allocated) Corporate Strategies (forward looking) Competitive Environment (relative strengths and weaknesses of a business) Operational Environment (key operational processes in service of the business model) See Table 14.2

Planning a dimensional EDW Identify and conform primary dimensions for the entire business (Figure 14.4).  Core Operational Concepts. Identify key transaction grain fact tables for the entire business (Figure 14.5) Create a high-level dimensional EDW design expressing all relationships (Figure 14.6) Prioritize subject areas for development (business need) Identify source system changes (risk management) Select data storage, movement, and analysis sofotware Document objectives, assumptions and business requirements See also “A Practical Guide to Building Data Marts” on the text CD.

Build Plan an EDW, build a data mart (Figures 14.7 and 14.8)