Virtual Data Warehouse Software

Thank you for your interest in the SQL generation (virtualisation) software: the Virtual Enterprise Data Warehouse and the associated metadata management environment TEAM.

It’s free and useful to quickly prototype your Data Warehouse model (output), validate your metadata and generally get insight in the Data Vault patterns. I use this for testing out various new patterns and ideas, as well as regression testing for pattern changes in various technologies and platforms..

The key concepts are related to the idea that, if you can generate all your ETL and you can ‘replay’ your entire Data Warehouse (when you refactor for instance – using Persistent Staging Areas), you can basically virtualise your entire Data Warehouse (I coined the term ‘NoETL’ for this :-)).

Your Data Warehouse model essentially becomes a ‘schema-on-read’ on your raw data! This is further detailed in many papers, most importantly here and here.

This page contains updates and general version information. To make life easier I have added the following pages to this site:

In general, all files (installation and support files) can be downloaded here (setup.exe). Hope everything works and is of some use. At this time the most recent working version is v1.5.5.0. Please note that in the latest version you also need to have the TEAM software installed to work with VEDW.

Feel free to reach out if you want to discuss or if you would like to contribute to the Github!

Virtual EDW – V1.5.5.0 (March 2019)

Another big update for VEDW! At a high level the following improvements have been applied:

  • Enabled for TEAM repistory v.1.5
  • Major code refactoring to simplify generation logic and use of TEAM interfaces.

Almost done (beta testing) – Virtual EDW – V1.5.0.0 (November 2018)

This is another major release with yet more bug fixes based on (yet more) real-world implementations. This is the first release that moves away from SQL Server, allowing access to JSON schemas to capture metadata.

Core changes are:

  • Implemented file watcher mechanism to automatically update code when metadata versions are changed (in TEAM)
  • Fully built-in regression test
  • Updated PIT / Dimension generation using calculation for last consistency date/time in near real-time environments

Virtual EDW – V1.4.0.1 (June 2018)

This is a major release with various bug fixes based on real-world implementations. It also completes the separation from the open-source TEAM application which is used to manage the required metadata. Splitting TEAM and VEDW will further enable focused efforts on simplifying the metadata management processes and keep the applications simple and extensible.

Core changes are:

  • Removed configuration / settings screen except the generation-only settings. This greatly simplifies the VEDW application, as these settings are now fully managed in TEAM.
  • Fixed a bug related to order of business key metadata which manifests itself in certain edge-cases where Link business key order is required to be deterministic.
  • Support for binary / character hash calculations
  • Fixed a bug where some of the paths were not stored in the configuration files. New approach applied now allows for customisation of the location of the configuration files, which also allows greater integration with TEAM.

Known improvements:

  • A file watcher was implemented, but not activated yet. The base code has been added with the idea to refresh metadata once TEAM updates are pushed through.

Virtual EDW – V1.3.2.0 (January 2018)

Small bug fix.

  • Simplified logic around determination of Business Key parts
  • Removed bug related to multiple sources mapping to the same Link, which resulted in key duplication
  • Do not generate flag in metadata management

Virtual EDW – V1.3.0.1 (September 2017)

Small bug fix.

  • Event handling around PSA keys and indexes not being identified. This now displays a proper error message.

Virtual EDW – V1.3.0 (August 2017)

Bug fix & new functionality release. The underlying metadata model has been revamped significantly and is much better now (at least I think so).

  • Added support for Same-As / Hierarchical Links
  • Fully updated the samples, the repository and the APIs. Please re-create your repository from the tool because much has changed
  • Proper logging and error handling in the validation and activation of metadata, error counts, error logging and generally more meaningful responses if things don’t work out
  • Filtering on the data tables to make it easier to work within a subject area
  • Generation of Directed Graph models (formally release, was in earlier)
  • Attribute level mapping in Directed Graph models
  • Referential Integrity checks now also work against delta’s (Staging Area), not just across the full DWH
  • Various cosmetic changes when resizing etc.
  • Better handling of model metadata, checking if it exists before doing things etc.
  • Support for Natural Business Key as an alternative for Hash Keys
  • Support for Linked Servers

Virtual EDW – V1.2.3

Bug fix release:

  • Fixed issues with Driving Key not generating properly (wrong key)
  • Fixed issue with reverse engineering of model metadata (detection of multi-active and driving key attributes)
  • Improved handling of clearing metadata, you can now independently remove model and mapping metadata
  • Fixed issue with attribute selection in full virtual mode
  • Improved attribute selection for PIT tables
  • Added support for snapshot date for PITs (if you don’t want the full history but a true point-in-time selection)
  • Added zero records for Hubs and Satellites
  • Fixed issue where business key itself could not be queried in PIT tables

Virtual EDW – V1.2.2

User-Interface release:

  • Improved multi-threading handling, no more COM issues!
  • Copy/paste now possible in grids (Excel-like functionality)
  • Better handling of grid resizing (including when resizing or maximising form)
  • Enabled manual changing of column width
  • Issue with Event Date/Time not properly handled in metadata activation

Virtual EDW – V1.2.1

Bug fix release:

  • Removed hard-coding for certain attributes
  • Applied by-pass versioning to manage model & automation metadata screens
  • Repository creation and truncation function (Metadata menu -> Repository)
  • Improved handling of changes in grids to manage information better (better handling of updates, deletes and inserts)
  • Initial validations added
  • Support for multi-key Driving Keys
  • Support for multiple Satellites off Hubs, Links
  • PIT table tested for some edge cases

Virtual EDW – V1.2

Various bug fixing and event handling, and an installation wizard!

Virtual EDW – V1.1.5 

Information on the thinking is captured in the things I am trying to achieve (work in progress). Please note that this download only contains the software install, the repository scripts are now available in the installation guide.

Virtual EDW – V1.02

Again thanks to everyone to keep pointing out these small bugs while applying this in new scenarios, some small tweaks were made:

    • Removed incorrect mapping for LINK type views / insert statements. These were using the wrong table.
    • Removed duplicate LINK mapping table from the metadata model (Virtualisation_Metadata_v1.2.ERwin).
  • Updated metadata processing procedure to use the correct link mapping table (SQL_MD_Management_SP.sql)

Download the latest version (v1.02) here.

The support files were also updated (updated model and metadata stored procedure), this is now version 1.1.

Virtual EDW – V1.01

Some minor issues were found (thanks for everyone downloading & using the app!);

    • In Link-Satellites, the Multi-Active attributes did not appear in the overall SELECT statement. The testcase in question is the table LSAT_CUSTOMER_COSTING. The partitioning logic was fine, the attribute just wasn’t selected
  • Degenerate attributes were not not picked up for Link tables. Again a testcase was created for this (LNK_MEMBERSHIP) but it slipped past the radar anyway! This is now fixed – in the testcase you can see the ‘sales channel’ attribute is now correctly added as an additional (degenerate) key

Also, the support files have been zipped independently and can be downloaded here. No changes were made compared to prior versions though.

Use at your own risk! However I tested this out in various locations and it all looks to be OK. In fact it’s the reason why things took a bit more time as I fixed a few minor issues – thanks everyone involved!

To get started you should create a database (or use an existing one) in SQL Server 2012 or 2014 and generate the tables from ERwin metadata model that has been added to the zip. The (free) ERwin community edition is sufficient for this. Once the database is there you’ll need to add the metadata generation Stored Procedure to populate this model. The SQL for this is also available in the zip file.