1 / 31

Data Modeling : OLTP versus OLAP

Data Modeling : OLTP versus OLAP. Who am I? Director, National Data Warehousing Practice – Data Architecture Lead 14 DW projects of diverse scope- Departmental Data Marts to Enterprise Data Warehouse Architectures Began as Business Analyst and Project Lead

liang
Download Presentation

Data Modeling : OLTP versus OLAP

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. Data Modeling : OLTP versus OLAP

  2. Who am I? Director, National Data Warehousing Practice – Data Architecture Lead 14 DW projects of diverse scope- Departmental Data Marts to Enterprise Data Warehouse Architectures Began as Business Analyst and Project Lead Data Modeler (logical and physical), DBA and Data Architect for 20 years DB2, ADABAS, IDMS, MS SQL Server, Oracle, Sybase, Essbase Industry experience Retail, Utilities, Automotive, Direct Marketing/Fulfillment, Insurance, Financial, Retirement, Higher Education, E-commerce Introductions

  3. Who are you? What are your goals regarding this presentation? Introductions

  4. Modeling Fundamentals Types of Data Models What is Data Modeling What Data Modeling is not Where Data Models are used Required Data Modeler Skills OLTP versus OLAP Definition Application Differences Modeling Objective Differences Model Differences OLAP Data Models Wrap up Outline

  5. Multiple varieties Subject Area Data Model (SADM) Entity Relationship Model (ERM) Matrix Model (MM) Dimensional Data Model (DDM) Each serves specific purpose Common to OLTP applications: Subject Area Data Model Entity Relationship Model Common to OLAP applications: Subject Area Data Model Entity Relationship Model Matrix Model Dimensional Data Model Modeling Fundamentals: Types of Data Models

  6. Subject Area Data Model

  7. Entity Relationship Model

  8. Matrix Model - Fact / Qualifier Matrix Qualifier -------------------> Fact ----------->

  9. Dimensional Data Model

  10. Data-oriented activity! Part art, part science Highly detailed, iterative process Uses basic objects to deliver pictorial image of requirements Entities(ERD &DDM) Attributes(ERD & DDM) Relationships(ERD & DDM) Uses Metadata to supplement data requirements described by pictorial image Modeling Fundamentals: What is Data Modeling

  11. Based on use and enforcement of Data Standards Depends on knowledgeable, committed participants for its ultimate success Business subject matter experts (SME) Information technology professionals Foundation for future business data requirements More important now than ever! Modeling Fundamentals: What is Data Modeling

  12. Logical Modeling Focused on business requirements Independent of technical platform Normalized - “The Key, the whole Key and nothing but the Key, so help me Codd!” Physical Modeling Focused on technical requirements Dependent on a technical platform and DBMS De-normalized (Optimized) to enhance performance Modeling Fundamentals: What is Data Modeling • Conceptual Modeling • The “50,000 foot view” of the business requirements • The precursor to Logical Modeling

  13. A waste of time! A one time effort The ultimate IT application development cure A quick process A function solely performed and understood by and for IT professionals Modeling Fundamentals: What Data Modeling is not

  14. Modeling Fundamentals: What Data Modeling is not One last clarification… Not"Date-a Model” Sorry!

  15. Operational Systems Traditional Applications designed to run the day-to-day business of the Enterprise External Systems *** Data used within an Enterprise that is obtained from outside sources Staging Areas *** Created to aid in the collection and transformation of data that is targeted for a Data Warehouse Operational Data Store *** W. H. Inmon and Claudia Imhoff definition: “A subject-oriented, integrated, volatile, current valued data store containing only corporate detailed data”. *** - Not discussed here Modeling Fundamentals: Where Data Models are used

  16. Data Warehouse (DW) W. H. Inmon definition: “A subject-oriented, integrated, non-volatile, time-variant collection of data organized to support management needs”. Data Mart (DM) TDWI definition: “A data structure that is optimized for access. It is designed to facilitate end-user analysis of data. It typically supports a single analytic application used by a distinct set of workers.” Modeling Fundamentals: Where Data Models are used

  17. Modeling Fundamentals: Required Data Modeler Skills • All Data Modelers: • Able to communicate well at all levels of an organization • Understand both business and technical requirements • Be a team player • Able to design with consistency, yet flexibility • Use data naming and design standards effectively • Understand that Conceptual, Logical and Physical Models are part of a continuum • Know what “TNF” means • Strive to collect as much metadata as possible • Have thick skin !

  18. Modeling Fundamentals: Required Data Modeler Skills • OLAP Data Modelers: • Design at multiple levels of granularity • Recognize when the requirements call for different model types • Understand when to normalize or not • Don’t be afraid of redundancy! • Know the impact of “time” on a model • Adapt to ever changing data requirements • Be a resource to ETL teams and understand data mapping issues • Look good in all sorts of hats!

  19. OLTP “Online Transaction Processing” The traditional applications development environment Mature technology and methodology Operational “bread and butter” of the Enterprise OLAP “Online Analytical Processing” DSS, EIS, DW, DM Immature technology and methodology Informational “luxury” OLTP versus OLAP: Definition

  20. OLTP versus OLAP: Application Differences OLTP OLAP

  21. OLTPFunctionality is different fromOLAP Functionality just as ... OLTP versus OLAP: Modeling Objective Differences OLTPModeling is different fromOLAP Modeling DesignOLTP Modelfor: DesignOLAPModel for:

  22. OLTP Model: OLAP Model: OLTP versus OLAP: Model Differences

  23. Data Warehouse Model Enterprise level - subject oriented Normalized, relational design Logical and Physical Data Models likely the same Granular level of detail (but not necessarily to level of Operational Models) Time oriented Non-volatile Optimized for distribution and reporting Reconciled data** from multiple legacy source systems Architected to be a long-term corporate asset ** Barry Devlin, “Data Warehouse - from Architecture to Implementation” OLTP versus OLAP: OLAP Data Models Operational Data Store Staging Areas

  24. Data Mart Model One or more per Subject Area - never at Enterprise level De-normalized relational design -or- multi-dimensional (based on textual versus numeric data requirements) Logical and Physical Data Models likely the same Summary level data - at many levels Time-oriented Non-volatile Optimized for decision support Derived data** summarized to suit needs of single Business Unit Architected as a quick solution for business analysis needs ** Barry Devlin, “Data Warehouse - from Architecture to Implementation” OLTP versus OLAP: OLAP Data Models

  25. OLTP versus OLAP: OLAP Data Models Data Mart Model - Star Schema

  26. Dimension Tables Contain information by which a Fact can be presented Include multiple levels of the Dimension (example: Market) Values for all levels of the Dimension are known (example: Time) Related to Fact Table at lowest level of Dimension (example: Market) Exist in a one-to-many relationship with the Fact Table Fact Table Designed to answer questions for one business measure Contains only single-valued Facts Represents derived values Must be applicable to all attached Dimension Tables One per Star Schema OLTP versus OLAP: OLAP Data Models Data Mart Model -Star Schema Special version of a Relational Data Model Well-suited for textual analysis

  27. OLTP versus OLAP: OLAP Data Models • Data Mart Model - Snowflake Schema • Another version of a Relational Data Model • Use carefully

  28. OLTP versus OLAP: OLAP Data Models Data Mart Model -Multi-Dimensional Cube • Proprietary designs (Essbase, Cognos, etc.) • Well-suited for financial analysis • Components: • Dimensions • Members • Measures • Great for “Slice and Dice”, “Drill down or Drill up” • Can mimic a Star Schema • Difficult to visually represent a cube larger than 3 Dimensions !

  29. ??? ??? Were your questions answered? Were your objectives met? Questions

  30. Review Key Points • Model Types • SADM, ERM, MM, DDM • OLTP versus OLAP • Reviewed Data Modeling Tasks & Input Requirements • Conceptual, Logical, Physical • Systems where used • Operational, External, Staging, ODS, DW, DM • A good OLTP modeler is not automatically a good OLAP modeler! • Modeling differences based on fact that OLTP and OLAP systems are very different • OLAP Models • Data Mart Models OLAP Modeling vs OLTP Modeling

  31. Conclusion “Many Thanks” for the opportunity to speak here today !

More Related