Data Vault RMOUG Training Days 2006 Colorado Convention

Data Vault RMOUG Training Days 2006 Colorado Convention Center Denver, Colorado February 15 -16

Data Vault; What’s The Combination? Jeff Meyer Enterprise Data Integration – Oracle DBA Department of Technology Services Denver Public Schools

Data Vault n Who are we? q q q n Enterprise Data Warehouse Projects q q n DBAs Managers Analysts Currently in process Planned Data Marts

Data Vault Brief History and Revisit Some Definitions n Three Basic Building Blocks of the Data Vault n Advanced Features n Questions n

Data Vault Brief History and Revisit Some Definitions n Three Basic Building Blocks of the Data Vault n Advanced Features n Questions n

Data Vault – Brief History and Revisit Some Definitions 1970 – Dr. E. F. Codd of IBM n 1979 – First Working Relational Database by Relational Software Incorporated q Oracle v 2 n 1991 – William H. Inmon published ‘Building the Data Warehouse’ n

Data Vault – Brief History and Revisit Some Definitions n Legacy System – q n ‘… any system that has been put into production. ’ (para-phrased W. H. Inmon) Operational Data Store – q ‘… a subject-oriented, integrated, volatile, current or near current collection of operational data. ’ W. H. Inmon

Data Vault – Brief History and Revisit Some Definitions n Data Warehouse – q n ‘… a subject-oriented, integrated, time-variant, non-volatile collection of data designed for support of business decisions’ W. H. Inmon Data Vault – q ‘… a detail-oriented, historical tracking and uniquely linked set of normalized tables that support one or more functional areas of business. ’ Dan Linstedt

Data Vault – Brief History and Revisit Some Definitions n Data Mart – q n ‘… a subset of a data warehouse, for use by a single department or function. ’ www. e-formation. co. nz/glossary. asp Corporate Information Factory – q ‘… the framework that exists that surrounds the data warehouse; typically contains an ODS, a data warehouse, data marts, DSS applications, exploration warehouses, and so forth. ’ W. H. Inmon

Data Vault – Brief History and Revisit Some Definitions * Source: Bill Inmon and Claudia Imhoff

Data Vault – Why? n Why q We finally have a Data Model that will work for small, medium, or large business n q do we need it? Anyone building a Data Warehouse can use these techniques. We’ve got issues in constructing the data warehouse from 3 rd normal form, or star schema form. n There are inherent road blocks to each method that we must solve technically through our Data Model.

Data Vault Brief History and Revisit Some Definitions n Three Basic Building Blocks of the Data Vault n Advanced Features n Questions n

Data Vault – Three Basic Building Blocks n Hub – stand alone table; list of unique business keys; used for business identification n Satellite – descriptive data; historical data; used for descriptive information for the HUB or LINK n Link – associative table; list of unique relationships between keys; used for relationships between HUBs and LINKs

Data Vault – Three Basic Building Blocks Name ELA Preview Hub Employees Dates Name EEOC Hub Students EEOC Addrs Enrollments Assign Hub Schools Shots

Data Vault – Three Basic Building Blocks HUB

Data Vault – Three Basic Building Blocks SATELLITE

Data Vault – Three Basic Building Blocks Employees HUB and some of its Satellites Name ELA Hub Employees Dates EEOC

Data Vault – Three Basic Building Blocks LINK

Data Vault – Three Basic Building Blocks Hub and Satellites Name Addr ELA Hub Employees Dates Hub and Satellites EEOC Assign Sat Link and Satellites Geo Cd Hub Schools Bldg Floor

Data Vault Brief History and Revisit Some Definitions n Three Basic Building Blocks of the Data Vault n Advanced Features n Questions n

Data Vault – Advanced Features n Point-In-Time – q n Bridge – q n A structure which sustains integrity of joins across time to all the SATELLITES that are connected to the HUB or LINK. A single row table that contains the latest Load Date Time Stamp (DTS). Similar to Point-In-Time except it spans a subject-area or a schema. User Grouping Link – q The information provides the user with a customized view from a reporting standpoint and does not affect the underlying information.

Data Vault – Advanced Features Point-In-Time (PIT)

Data Vault – Advanced Features Bridge n n A single row table that contains the latest Load DTS with multiple columns. A Bridge is not a helper table. Similar to a PIT Table except it spans or applies to a subject-area or schema. A PIT Table is HUB (LINK) and SATELLITE specific.

Data Vault – Advanced Features User Grouping Link

Data Vault – How is DPS using DV

Data Vault – Why is DPS using DV Storage considerations. n Vertical partitioning of data (rate of change). n All the FACTS all the TIME. n Scalability and Extensibility. n

Data Vault – What was not covered. n n n How to apply Data Vault Modeling. Best practices. Lessons Learned. Dan Linstedt’s use of DECODE in determining changed data capture. Who’s data is it? SLAs? The new regulations / compliance that will affect all of us.

Data Vault – Questions?

Data Vault - References n DATA VAULT OVERVIEW: THE NEXT EVOLUTION IN DATA MODELING Dan Linstedt - Core Integration Partners, Inc. http: //www. tdan. com/i 021 hy 01. htm n DATA VAULT™ OVERVIEW THE NEXT EVOLUTION IN DATA MODELING SERIES 2 Dan Linstedt - Core Integration Partners, Inc. http: //www. tdan. com/i 023 hy 02. htm n DATA VAULT - SERIES 3 END-DATES AND BASIC JOINS Dan Linstedt - Core Integration Partners http: //www. tdan. com/i 024 hy 02. htm n DATA VAULT - SERIES 4 LINK TABLES Dan Linstedt - Core Integration Partners http: //www. tdan. com/i 027 ht 04. htm n DATA VAULTTM OVERVIEW THE NEXT EVOLUTION IN DATA MODELING SERIES 5 – LOADING TABLES Dan Linstedt - Core Integration Partners http: //www. tdan. com/i 027 ht 04. htm n Data Vault Modeling – Class Materials and Notes; copyright 2002 -2003 Dan Linstedt – Core Integration Partners http: //www. coreintegration. com n n Home of the Data Vault; www. danlinsedt. com Audit the Data – or Else. Un-audited Data Access Puts Business at High Risk; Bloor, Robin and Baroudi, Carol; Lumigent, Inc. ; copyright 2004

Data Vault – Contact Information JEFFREY MEYER jeffrey_meyer@dpsk 12. org

Data Vault RMOUG Training Days 2006 Colorado Convention Center Denver, Colorado February 15 -16