slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Developing and Deploying Data Warehouse and Business Intelligence Solutions Kerr-McGee Information Management Group PowerPoint Presentation
Download Presentation
Developing and Deploying Data Warehouse and Business Intelligence Solutions Kerr-McGee Information Management Group

Loading in 2 Seconds...

play fullscreen
1 / 40

Developing and Deploying Data Warehouse and Business Intelligence Solutions Kerr-McGee Information Management Group - PowerPoint PPT Presentation


  • 60 Views
  • Uploaded on

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. Who is Kerr-McGee?.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Developing and Deploying Data Warehouse and Business Intelligence Solutions Kerr-McGee Information Management Group' - jackie


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

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

who is kerr mcgee
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/
agenda
Agenda
  • Introduction to DW/BI Concepts
  • Extract, Transform & Load (ETL)
  • Business Intelligence / Reporting
  • A Day in the Life
information management strategy
Information Management Strategy

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

what is a data warehouse
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

the idea behind data warehousing
The Idea Behind Data Warehousing

Source Systems

POS

INVENTORY

Data Warehouse

?

FINANCIALS

BILLING

Meaningful

&

Easy

Access

Extract

&

Transform

Information

Data

framework architecture
Framework Architecture

Metadata Management

Enterprise

Reporting

Data

Extraction

Data

Cleansing

Data

Integration

Data

Improvement

External Data

Datamarts

OLAP

Query

Data

Mining

Data

Transformation

Operational

Data Store

Data

Warehouse

Source

Systems

Information

Delivery

Project Management & Quality Assurance

Operations & Systems Management

business intelligence
Business Intelligence

Business Activity

Business Intelligence

Integrated

Meaningful

Consistent

Validated

Easy to Use

Leveragable

Timely

Sales

Product Performance

Sales Performance

Customer Behavior

Marketing Performance

Inventory Monitoring

Financial Performance

Marketing

Service Customers

Manage Inventory

Maintain Accounts

Data

Information

slide12

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

Data

Warehouses

Maximo

Chemhouse

Existing

Reporting

Systems

TOLAS

O&G

O&G

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

slide13

Corp.

HR Data

Warehouse

K-M To-Be

Oracle

Financials

Phased Out

Passport?

Peoplesoft

Production

Operations

Budget &

Forecasting

Application

Chemical

“Centralized”

Data Warehouse(s)

Passport

Domestic

Oracle

Financials

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

O&G

Novistar

Energy

  • 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’

Tobin

O&G

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

data warehouse roles
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
slide16

Project Management & Quality Assurance

Operations & Systems Management

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

etl challenges
ETL Challenges
  • 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

Data

Volume

Source

Inclusion

ExtractTiming

Warehouse

Complexity

+

+

=

etl the heavy lifting
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
etl tools only half the story
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
etl tools core components
ETL Tools – Core Components

MetadataRepository

Databases/Files

MetadataImport/Export

Target Adapters

Extract

RuntimeMetadataServices

Transform

Load

DesignManager

Source Adapters

Databases/Files/Legacy Apps

etl the options
ETL - The Options

$5,000-$10,000

$5,000-$10,000

$250,000 and UP

  • Software Products
    • “Homegrown” Solutions
      • SQL*LOADER, 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
etl the reality
ETL - The Reality

Flat File

Mainframe DBMS

Oracle

RDBMS

RDBMS

ERP

“New” Sources

Loader

Utility

Interface

Apps

COBOL

Code

O/S

Scripts

SQL

Scripts

PERL

Scripts

3GL

Code

Data

Repository

informatica powermart
Informatica Powermart

Repository Manger

Designer

Workflow Manager

Workflow Monitor

what is business intelligence
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.

business intelligence tools
Business Intelligence Tools

Project Management & Quality Assurance

Operations & Systems Management

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

Operational

Data Store

Data

Warehouse

Information

Analysis

Source

Systems

Project Management & Quality Assurance

categorize information needs
Categorize Information Needs

Senior Management

Highly

Summarized

Executive

Financial analysts, product managers, etc..

Management

Business Analysts

Market Researchers

Moderately

Summarized

Salespersons,

line managers,

administrative staff, etc..

Integrated Operational Data

Business Users

information delivery mechanisms
Information Delivery Mechanisms

Wireless

Mobile

Web or

C/S

Delivery Mechanism Considerations

Integrated with Operations?

Detailed Reporting only?

Real-time or based on a Periodic Business Cycle (Financials)

Tethered or ‘disconnected’?

C/S

Web

Wireless

Data

Visualization

Web or

C/S

(in millions)

2002

1998

1999

2000

2001

Net Revenues

$x,xxx

$x,xxx

$x,xxx

$x,xxx

$x,xxx

Net income

xxx

xxx

xxx

xxx

x,xxx

Earnings per share

x.xx

x.xx

x.xx

x.xx

x.xx

xx%

xx%

xx%

xx%

xx%

Return on net revenues

$xxx

$xxx

$xxx

$xxx

$xxx

Cash & s/t investments

Total Assets

$xxx

$xxx

$xxx

$xxx

$xxx

Shareholder Equity

xxx

xxx

xxx

xxx

x,xxx

Operational Trends

Operational Trends

Operational Trends

Web or

C/S

Predefined

Summaries

Directed Analysis

Specialized

Algorithms

Standardized

Operational

Reporting

Ad-hoc

Queries

all things cognos
All things Cognos
  • Cognos is a vendor. The suite of applications we’ve 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.

terminology
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.

cognos upfront
Cognos - Upfront
  • Upfront - Portal Management
cognos powerplay
Cognos - PowerPlay
  • PowerPlay – web reports/slicing and dicing/data analysis, based on cubes.

More Information on Cognos website: http://www.cognos.com/products/businessintelligence/analysis/

cognos impromptu
Cognos - Impromptu
  • Impromptu – printable reports (in PDF) that may/or may not be produced with prompts for filtered information.
cognos visualizer
Cognos - Visualizer
  • Visualizer – interactive graphic depictions of data warehouse/cube information
developing visualizations
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..