1 / 40

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

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

jackie
Download Presentation

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

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Source Systems POS INVENTORY Data Warehouse ? FINANCIALS BILLING Meaningful & Easy Access Extract & Transform Information Data

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

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

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

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

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

  17. 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 + + =

  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 MetadataRepository Databases/Files MetadataImport/Export Target Adapters Extract RuntimeMetadataServices Transform Load DesignManager Source Adapters Databases/Files/Legacy Apps

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

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

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

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

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

  29. B.I. Infrastructure

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

  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/businessintelligence/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. Day in the Life

  38. Typical Day

More Related