Data Warehouse Methodology

1
Data Warehouse Methodology

• Part 3

2
Methodology Per Development Increment
Business Needs
Delivery
Storage
Engineering
Acquisition
Access

• Define business requirements, with focus on
  information requirements
• Define dimensional data model for the data mart
  facts and dimensions
• Define data model for the DW
• Identify authoritative source of required data
  from operational systems and analyze source data
• Map source data, define transformation
  requirements
• Design build and test extract, transformation and
  load mechanisms
• Design and build end user application
• Demonstrate and refine user application via
  prototyping techniques
• Train and conduct user acceptance

3
Step 5 Mapping and Transformation Requirements

• This step defines
• How data from source system(s) will map to the
  data warehouse and data marts
• The process and logic that will be used to
  extract data from source systems
• The requirements for transformation of source
  data into a form that is ready to load into the
  data warehouse

4
Example Process Overview
5
Example Extraction requirements
6
Transformation Requirements
Stage Table
Rules
Target
7
Design, Build and Test Extract and Transformation
Routines

• In this step, the programs required to extract,
  transform and load data into the data warehouse
  are developed.
• This step is very similar to any information
  system project (design, code and test)
• In order to perform technical design, must take
  into consideration the programing language (C,
  COBAL PL/SQL) or ETL tool (Informatica,
  DataStage, ETI) that will be used.

8
Design and Build End User Application(s)

• Can begin once data mart data model has been
  designed
• Can be done in parallel with Back Office
  design, code and test (we can load our data mart
  data model with fake data)
• There are many types of end user applications.
• Examples
• Standard reports
• Parameterized reports
• Web pages/portals
• Customized (virtual) OLAP cubes

9
Prototype/Refine Applications

• Demonstrate preliminary version of application to
  end users
• Often, end users cant tell the designer
  specifically what is required prototyping is a
  great way to obtain specific requirements

10
Train and Conduct Acceptance Testing

• Similar to any other information system project
• Often, an initial load of warehouse and mart is
  required. Often, will perform acceptance test of
  initial load, then of first incremental load.

11
Establishing Success Criteria

• This is key. Need to
• Establish what success criteria are
• Design measures for determining if success
  criteria met
• Publish results show the organization how the
  data warehouse has helped the organization

12
Success Criteria

• Return on Investment
• The return that can be had through investing in a
  data warehouse. There are a number of ways ROI
  can be realized
• Lower cost (better inventory management
  Walmart!)
• Improved productivity both from IT and end user
  staff. Most common example staff spend much
  less time assembling data and much more time
  analyzing
• Increased revenue improved targeting,
  acquisition of customers, greater share of
  wallet of customers, reduced attrition, etc.

13
Success Criteria

• The Data Warehouse is Used!
• Number of users is increasing
• Queries run regularly
• Number of queries is increasing

14
Data Warehouse is Useful

• Warehouse may be used, but users may perceive
  that value is marginal.
• Need to consult with users to determine if users
  perceive that the warehouse delivers value.
• Often used in place of quantitative results

15
Success Criteria

• Project is delivered on-time
• Project is delivered within budgets

16
Success Criteria

• Improved User Satisfaction
• Qualitative
• May apply to internal users or external
  (sometimes these are customers).
• Determines if users are happy with features and
  capabilities of end user applications and that
  performance (query) is acceptable

17
Success Criteria

• Additional Requests for data and functions
• If you find that new users and groups are
  beating on your door for their own data marts,
  etc. you have a good indication you are
  successful with existing groups
• Means warehouse has a good reputation among users
• If existing users are asking for enhancements,
  then they are actively engaged and using the
  warehouse

18
Success Criteria

• Business Performance Benchmarks
• Show improvement vs industry benchmarks
  (productivity, etc.) on after implementing
• Very subjective and difficult to prove
• Goals and objectives are met
• The specific goals and objectives outlined in a
  project charter have been met

19
Success Criteria

• Business Problems are solved
• New Business Opportunities are identified
• Implement initiatives that previously could not
  be done due to lack of data
• Warehouse is change agent
• Warehouse provides ability for organization to
  change due to enhanced access to information, and
  changes to the way decisions are made.