Data Warehousing 101

1

• Data Warehousing 101
• Howard Sherman
• Director Business Intelligence
• xwave

2
Agenda

• Introduction
• Definitions
• Why Create a Data Warehouse
• Complexities You Will Encounter
• Best Practices
• Questions

3
xwave Overview

• Full services IT solutions provider - we fulfill
  the complete range in enterprise system
  requirements.
• Our legacy is as a high quality systems
  integration company with deep infrastructure and
  product fulfillment capabilities.
• Possess extensive COTS and custom development
  experience leveraging the best of breed in
  applications and business processes.
• Focused on key industries in which we have
  relevant experience.
• xwave is a 346M division of Bell Aliant Regional
  Communicationsan ICT provider with more than
  10,000 employees, 100-plus years of customer
  service and an international client list.

4
The BI Practice at xwave

• Over 65 BI Professionals with Access to Many More
• Specialized and Certified BI Consultants
• End to End Capabilities
• Experienced in a Full Range of Tools/Products
  Including Cognos, Business Objects, CA, Oracle,
  Microsoft and Trillium
• Over 10 Years of Experience Delivering Industry
  Leading BI Solutions

5
Definitions

Data Warehouse n. A collection of corporate
information, derived directly from operational
systems and some external data sources. Its
specific purpose is to support business
decisions, not business operations.
Business Intelligence n. Process of assembling
disparate data, transforming it to a consistent
state for business decision making, and
empowering users by providing them with access to
this information in multiple views.
6
Why Create a Data Warehouse?

• To perform server/disk bound tasks associated
  with querying and reporting on servers/disks not
  used by transaction processing systems.
• To use data models and/or server technologies
  that speed up querying and reporting and that are
  not appropriate for transaction processing.
• To provide an environment where a relatively
  small amount of knowledge of the technical
  aspects of database technology is required to
  write and maintain queries and reports and/or to
  provide a means to speed up the writing and
  maintaining of queries and reports by technical
  personnel.
• To provide a repository of "cleaned up"
  transaction processing systems data that can be
  reported against and that does not necessarily
  require fixing the transaction processing systems.

7
Why Create a Data Warehouse?

• To make it easier, on a regular basis, to query
  and report data from multiple transaction
  processing systems and/or from external data
  sources and/or from data that must be stored for
  query/report purposes only.
• To provide a repository of transaction processing
  system data that contains data from a longer span
  of time than can efficiently be held in a
  transaction processing system and/or to be able
  to generate reports "as was" as of a previous
  point in time.
• To prevent persons who only need to query and
  report transaction processing system data from
  having any access whatsoever to transaction
  processing system databases and logic used to
  maintain those databases.
• To perform complex joins, transformations and
  business logic once and not every time a new
  report is created.

8
Why Create a Data Warehouse?

• Performance - Operational and Data Warehouse
  Systems
• Simplify - Make Complex Data from Many
  Systems Available in One
• Accuracy - Standardize and Cleanse
• Business Value - Provide the Foundation for the
  Business to Have Access to Information to
  Make Timely, Informed Decisions

9
Complexities of Creating a Data Warehouse

• Incomplete errors
• Missing Fields
• Records or Fields That, by Design, are not Being
  Recorded
• Incorrect errors
• Wrong Calculations, Aggregations
• Duplicate Records
• Wrong Information Entered into Source System

10
Complexities of creating a Data Warehouse

• Incomprehensibility errors
• Multiple Fields Within One Field
• Inconsistency errors
• Inconsistent Use of Different Codes
• Overlapping Codes
• Inconsistent Grain of the Most Atomic Information

11
Best Practices

• Data Warehousing is a process and not a project
• Complete requirements and design
• Prototyping is key to business understanding
• Utilizing proper aggregations and detailed data
• A full iterative approach is essential
• Training is an on-going process
• Build data integrity checks into your system

12
Questions or Comments?

Thank You