1 / 33

Introduction to Data Warehousing

Introduction to Data Warehousing. Harley Eisenberg Tactics, Incorporated. Data Warehousing Presentation Topics. What is a Data Warehouse? Value Risks and Pitfalls Terminology I want one! - What next?. Who am I?. Data Warehousing Group Manager, Tactics Past President, Eastern Canada OUG

marilyn
Download Presentation

Introduction to Data Warehousing

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. Introduction to Data Warehousing Harley Eisenberg Tactics, Incorporated

  2. Data Warehousing Presentation Topics • What is a Data Warehouse? • Value • Risks and Pitfalls • Terminology • I want one! - What next?

  3. Who am I? • Data Warehousing Group Manager, Tactics • Past President, Eastern Canada OUG • Past Member, Board of Directors, IOUG • Oracle User and Developer for 11 years

  4. What is a Data Warehouse? • Why do I need a Data Warehouse? • What are its goals? • What are its objectives?

  5. Why do I need a Data Warehouse? • Multiple source systems collecting similar or related information. • Difficulty in accessing this data. • Unable to perform ad-hoc reporting.

  6. Why do I need a DW? • Do you know who your company’s: • Top/Bottom 10 customers are? • By product line? • By region? • By gross sales? • By profitability? • By quantity of orders?

  7. Why do I need a DW? • Do you know who your company’s: • Top/Bottom 10 Salespeople are? • By year/quarter/month? • By gross sales? • By profitability? • By volume of sales?

  8. Why do I need a DW? • Do you know your company’s: • Top/Bottom 10 products? • By gross sales? • By profitability? • By number of repeat orders? • By color/size/shape? • By season/quarter/month?

  9. Why do I need a DW? • Do you know your company’s trends in any of the proceeding areas? • By percentage? • By gross amount? • By profitability?

  10. Data Warehouse Defined “A data warehouse is 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” Oracle Data Warehousing, Corey et al.

  11. The Value of the Data Warehouse The Data Warehouse Defined: • Subject Oriented • Integrated • Time Variant • Non-volatile • Strategic Analysis Tool

  12. The Value of the Data Warehouse Subject Oriented • Focused on the data to meet strategic business goals • Not an enterprise-wide store of all OLTP data

  13. The Value of the Data Warehouse Integrated • Multiple Source Systems • Common Terminology • Single Domain for a Given Data Item • Consistently Updated

  14. The Value of the Data Warehouse Time Variant • Data in the Warehouse is associated with a point in time • Essential for time-series historical analysis and forecasting

  15. The Value of the Data Warehouse Non-volatile • Once loaded, data does not change. • The answer to a given historical question will not change based on the time the question is posed to the warehouse.

  16. The Value of the Data Warehouse Strategic Analysis • Historical analysis • Forecasting analysis • Evaluation of performance to corporate strategic goals • Discovery of revenue opportunities • Cost reduction

  17. Risks and Pitfalls • Target the Wrong or an Incomplete Data Set • Data Cleansing • Design • Clear Dimensional Model • Slowly Changing Dimensions

  18. Risks and Pitfalls Target the Wrong or an Incomplete Data Set • The Field of Dreams Methodology • Lack of End User Involvement

  19. Risks and Pitfalls Data Cleansing Extraction, Transformation and Cleansing account for up to 80% of total project effort Source DM Review®

  20. Risks and Pitfalls - Design Clear Dimensional Model (Star Schema) • Facts - Measures of Business Activity • Dimensions - Business Entities that Describe Facts

  21. Risks and Pitfalls - Design A dimension is considered a Slowly Changing Dimension when its attributes remain almost constant over time, requiring relatively minor alterations to represent the evolved state.

  22. Terminology • Fact Table • Dimension Table • Hierarchy • Data Warehouse/Data Mart

  23. Fact Table • A table that is used to store business information (measures) that can be used in mathematical equations. • Quantities • Percentages • Prices

  24. Dimension Table • Table used to store qualitative data about fact records • Who • What • When • Where • Why

  25. Hierarchies • Allow for the ‘rollup’ of data to more summarized levels. • Time • day • month • quarter • year

  26. Data Warehouse vs. Data Mart • Commonly misused/interchanged terms • Everyone has a different definition

  27. Data Warehouse vs. Data Mart • Data Warehouse is larger (more data) • Data Warehouse has information about multiple subject areas. • Data Warehouse may be used to feed one (or more) data marts.

  28. Data Warehouse vs. Data Mart • Data Mart is smaller • Data Mart is restricted (usually) to a single subject area. • Data Mart may feed Data Warehouse.

  29. I Want One! • What’s the next step?

  30. I Want One! • Plan! Plan! Plan! • Resources • Experienced DW Architect • Programmers Familiar with Legacy Systems • Users Familiar with Legacy Data • Code Generator

  31. I Want One! • How long will it take? • Up to Six Months per source system

  32. I Want One! • End User Presentation Tools • Oracle Discoverer • Oracle Express (OFA, OSA) • Business Objects • Cognos (Powerplay, Impromptu) • Many, many others

  33. Summary • DW are used for • Decision support • Analysis • Not the same as your operational systems • Designed badly, they are not useful

More Related