Developing and Deploying Data Warehouse and Business Intelligence Solutions

1
Developing and Deploying Data Warehouse and
Business Intelligence Solutions Kerr-McGee
Information Management Group
Skye Brannon Jeff Bridgwater Sarena Sherrard
DW Analyst DW Manager Sr. DW Analyst
2
Who is Kerr-McGee?

• Kerr-McGee is an Oklahoma City-based energy and
  inorganic chemical company with worldwide
  operations and assets of approximately 10
  billion.
• http//www.kerr-mcgee.com/

3
Agenda

• Introduction to DW/BI Concepts
• Extract, Transform Load (ETL)
• Business Intelligence / Reporting
• A Day in the Life

4
DW / BIConcepts
5
Information Management Strategy

• Structure the systems and data relationships to
  provide user-friendly customer access to data in
  order to provide decision-making information.

6
Adding Value to Data
7
Information Pyramid
8
What is a Data Warehouse?

• A copy of data from one or more On-line
  Transaction Processing (OLTP) systems
  specifically structured for Query, Reporting and
  Analysis (QRA).
• Data is typically at a summarized level to limit
  the size and complexity of the data warehouse
• Data is usually cleansed and merged to create an
  apples to apples comparisons

End-User Reporting
OLTP Systems
Data Warehouse
9
The Idea Behind Data Warehousing
Meaningful Easy Access
Extract Transform
Information
Data
10
Framework Architecture
11
Business Intelligence
Information
Data
12
Corp.
K-M As-Is
Oracle Financials
Passport
InPower
HR Data Warehouse
Chemical
Domestic Oracle Financials
Production Operations
Passport
Financial Operations
Adage
European Oracle Financials
Maximo
Data Warehouses
Chemhouse
TOLAS
Existing Reporting Systems
OG
OG Data Warehouse
Novistar Energy Financials
Tobin

• Issues
• Multiple Versions of the Truth
• Different definitions for similar data
• Multiple Reporting Tools and Reports
• Impacts Performance of Oracle Financials

DFW
PREMAS
P2000
Aberdeen Oracle Financials
Merak
Intl. Systems
Aberdeen Data Warehouse
Others
13
Corp.
HR Data Warehouse
K-M To-Be
Oracle Financials
Phased Out
Passport?
Peoplesoft
Budget Forecasting Application
Production Operations
Chemical
Centralized Data Warehouse(s)
Domestic Oracle Financials
Passport
Financial Operations
Consolidated Analysis Reporting
Solution (Cognos Business Intelligence)
Adage
Data Warehouses
European Oracle Financials
Maximo
Existing Reporting Systems
Dashboards KPI Mgmt
TOLAS
Chemhouse
Consolidated Reporting Systems
OG
Novistar Energy
Tobin

• Benefits
• Single Version of the
• Truth
• Business Intelligence environment leads to
  Timely Analysis
• Reporting
• Consolidated Tool
• Enables Multiple
• Economies of Scale
• - Massaging Distribution
• - Desktop/Maintenance
• - Transaction System Load

OG Data Warehouse
DFW
Possibly Phased Out or Integrated
PREMAS
P2000
Aberdeen Oracle Financials
Aberdeen Data Warehouse
Merak
Possibly Phased Out or Integrated
Intl. Systems
STANDARDIZE, CONSOLIDATE, MINIMIZE, SIMPLIFY
Others
14
Data Warehouse Roles

• Manager Planning and management of entire product
  or project lifecycle May assist in ETL BI
  Interface design and development
• Data Warehouse Architect Applies knowledge of
  technology options, platforms, and design
  techniques across product and project lifecycle
  responsible for design of overall warehouse
  process
• ETL Specialist Analysis and design of
  extraction, transformation, and loading strategy
  development of ETL scripts and procedures
• Business Intelligence Specialist Design and
  development of multidimensional-cubes reports
  performance and tuning of chosen technologies
• Web Interface Specialist Design and development
  of application interface elements coordinates
  interfaces between application components

15
ETL
16
Data Extraction and Transformation
Metadata Management

• Data Extraction and Transformation
• Applying business rules to turn data into useable
  information
• Clean up and standardization of consumers,
  vendors, products, etc.
• Integration of disparate internal and external
  data
• Can be 70 - 80 of effort
• Issues
• - Can be difficult and time consuming to
  define business rules
• - Extraction tools automate only the more simple
  tasks

Plan/Forecast/ Analysis
Data Extract Cleansing and Integration Process
External Data
Global / Dept/ Business UnitSummary and Analysis
OLAP Query
Data Visualization
Datamarts
Data Mining
Executive Information Systems
Data Transformation
Operational Data Store
Data Warehouse
Information Analysis
Source Systems
17
ETL Challenges
Data Volume
Source Inclusion
ExtractTiming
Warehouse Complexity

• Warehouse Complexity
• Destructive vs. Incremental Loads
• Integration (mergers/acquisitions)
• Data integration (consolidation of information)
• Process integration (consolidation of processes)
• Smaller windows of opportunity
• Make decision in a shorter period of time due to
  competitive, global market
• Global marketplace (DW timing updates)
• High-profile e-Business initiatives
• Satisfying requirements

18
ETL - The Heavy Lifting

• Challenge to develop efficient, consistent
  methods of gathering and cleansing heterogeneous
  data
• Capture and load of data from multiple source
  systems (both internal and external)
• Integrates data into a single source
• Cross-system mapping to standard identifiers
  (surrogate keys)
• Aggregation for information delivery and BI
  initiatives

19
ETL Tools - Only Half the Story

• Half the story ETL Tools Extract, Transform, and
  Load data
• Transport data between sources and targets
• Document data element changes (metadata)
• Administer run-time processes and operations
• Scheduling
• Error management
• Audit logs
• Statistics

20
ETL Tools Core Components
Databases/Files
MetadataImport/Export
Target Adapters
Extract
RuntimeMetadataServices
Transform
Load
DesignManager
Source Adapters
Databases/Files/Legacy Apps
21
ETL - The Options

• Software Products
• Homegrown Solutions
• SQLLOADER, PL/SQL, GATEWAYS LINKS
• Developer-bound
• Little cohesion between components
• Niche Players (Oracle Warehouse Builder /
  DataJunction)
• Enhanced Scheduling Logging
• Not Multi-Warehouse Oriented
• Informatica Powermart
• Great UI
• Powerful Scheduling Logging
• High Price
• Proprietary Transform Language

22
ETL - The Reality
23
Informatica Powermart

• Repository Manger

• Designer

Workflow Manager
Workflow Monitor
24
Business Intelligence / Reporting
25
What is Business Intelligence?

• Business Intelligence is the transformation of
  data into information you can use to drive your
  business.
• There are a number of vendors that have developed
  Business Intelligence software. Kerr-McGee uses
  Cognos.

26
Business Intelligence Tools
Metadata Management

• Business Intelligence Tools
• Combination of applications and tools
• Provide analysis, presentation and reporting
  facilities for users
• Tailored to meet diverse needs of executives,
  mgrs, analysts
• Data may reside in ODS, data warehouse or data
  mart
• Issues
• How do you choose the right tool or tools?

Plan/Forecast/ Analysis
Data Extract Cleansing and Integration Process
External Data
Global / Dept/ Business UnitSummary and Analysis
OLAP Query
Data Visualization
Datamarts
Data Mining
Executive Information Systems
Data Transformation
Data Warehouse
Operational Data Store
Information Analysis
Source Systems
Project Management Quality Assurance
27
Categorize Information Needs
28
Information Delivery Mechanisms
Predefined Summaries
Specialized Algorithms
Directed Analysis
Standardized Operational Reporting
Ad-hoc Queries
29
B.I. Infrastructure
30
All things Cognos

• Cognos is a vendor. The suite of applications
  weve bought and use from Cognos are
• Access Manager (Security)
• Upfront (Portal http/intranet/kmbi)
• PowerPlay (reports/cubes)
• Impromptu (Web based PDF reports)
• NoticeCast (conditional report notifications)
• Visualizer (graphic depictions of data
  warehouse/cube information)
• What we will cover.

31
Terminology

• Cube - A multidimensional way to analyze
  information, designed to provide quick answers to
  the who, what, why, when, and where business
  questions.
• Drill Down - Going from a summarized view to a
  more detailed view of information within the same
  cube
• Drill Across - Linking data from One Subject
  Area to Another (General Ledger to Accounts
  Payable)
• Drill Through - Linking to source data using
  selected filters
• Powerplay Web - On-Line Analysis Tool for cubes
  (slice/dice, drill down, drill across drill
  through)
• Newsbox -A web based folder used to store views
  of data (reports). Every KMBI user has their own
  personal newsbox.

32
Cognos - Upfront

• Upfront - Portal Management

33
Cognos - PowerPlay

• PowerPlay web reports/slicing and dicing/data
  analysis, based on cubes.

More Information on Cognos website
http//www.cognos.com/products/businessintelligenc
e/analysis/
34
Cognos - Impromptu

• Impromptu printable reports (in PDF) that
  may/or may not be produced with prompts for
  filtered information.

35
Cognos - Visualizer

• Visualizer interactive graphic depictions of
  data warehouse/cube information

36
Developing Visualizations

• Initial Project meeting should include
• Client - gives input on look and feel, data
  requirements, timelines
• Project Manager ensures project is feasible
  within budget and time restraints at the onset
  and through out the project.
• Data Warehouse Architect ensures all the needed
  data is in the data warehouse.
• Business Intelligence Specialist (cube builder)
  ensures all the needed data is in the cube, in
  the correct format
• Web Interface Specialist (visualization builder)
  works with cube builder and client to
  established look and feel, navigation, chart
  styles, etc..

37
(No Transcript)
38
(No Transcript)
39
Day in the Life
40
Typical Day