At a past client, in order to meet timelines to fulfill urgent, unmet reporting needs, we found it necessary to build a virtualized Operational Data Store (ODS) as the first phase of a new agile DW/BI project using the Data Vault 2.0 approach. This allowed us to deliver new objects, quickly and incrementally to the report developer so we could quickly show the business users their data. In order to limit the need for refactoring in later stages of the data warehouse development, we chose to build this virtualization layer on top of a Type 2 persistent staging layer. All of this was done using Oracle SQL Developer Data Modeler (SDDM) against (gasp!) a MS SQL Server Database. In this talk I will show you the architecture for this approach, the rationale, and then some of the tricks I used in SDDM to build all the stage tables and views very quickly. In the end you will see actual SQL code for the virtual ODS that you can leverage for your own projects.


- learn a data warehouse architecture that can be implemented incrementally and in layers

- learn what a Virtual ODS is and how it can help you be Agile

- how to quickly build pattern based stage tables in SDDM

- how to build complex, pattern based views in SDDM

- tip on how to use analytic functions to create virtual columns

- see examples of how to embed change data capture into a view-based ELT loading architecture

Additional Resources

About the Speaker(s)

Kent Graziano is a Senior Technical Evangelist for Snowflake Computing and an award winning author, speaker, and trainer, in the areas of data modeling, data architecture, and data warehousing. He is an Oracle ACE Director, a certified Data Vault Master and Data Vault 2.0 Practitioner (CDVP2), expert data modeler and solution architect with more than 30 years of experience, including two decades doing data warehousing and business intelligence (in multiple industries). He is an internationally recognized expert in Oracle SQL Developer Data Modeler. Mr. Graziano has developed and led many successful software and data warehouse implementation teams, including several agile DW/BI teams. He has written numerous articles, authored three Kindle book (available on Amazon.com), co-authored four books (including the 1st Edition of The Data Model Resource Book), and has givens many presentations, nationally and internationally. He was a co-author on the first book on Data Vault, and the technical editor for Super Charge Your Data Warehouse (the main technical book for DV 1.0). In 2014, he was voted one of the best presenters at OUGF14 in Helsinki, Finland.