Optimal Data Engine (ODE) for MSSQL
Clone or download
Latest commit 4f758cf Mar 2, 2018
Failed to load latest commit information.
BIMLScripts Updated the version. Change to BIML file to handle one source table f… Mar 2, 2018
ODE_release/Tables Updated the version. Change to BIML file to handle one source table f… Mar 2, 2018
ReleaseScript Updated the version. Change to BIML file to handle one source table f… Mar 2, 2018
Security Updated split string function. Removed the limitation on the string l… Feb 22, 2018
Service Broker Fixed contract to use dbo as it used to be in previous version Jul 21, 2017
dbo Updated split string function. Removed the limitation on the string l… Feb 22, 2018
dv_config/Stored Procedures Bug fixes in couple of procedures Jul 27, 2017
dv_integrity/Stored Procedures Same fix for the link metrics Oct 12, 2017
dv_log A change to proper user name in default constraint for dv_execution, … Aug 15, 2017
dv_release Quick bug fix in release config change Aug 14, 2017
dv_scheduler Fixed bug - when load has failed, wait for running tasks to finish un… Feb 20, 2018
dv_scripting Pushed Brent's fix to MS SQL cdc problem when stage table is empty an… Aug 30, 2017
log4 MAGDVC-15 HashDiffs First Commit Jan 19, 2016
.gitignore added *.jfm to gitignore Oct 11, 2016
LICENSE Adding our product and company name to GPL boilerplate Sep 29, 2015
README.md Updated the version. Change to BIML file to handle one source table f… Mar 2, 2018
Script.PostDeployment1.sql Added release script fix for big releases Aug 9, 2017
Script.PreDeployment1.sql Resolved an issue with SBLogin if it already exists. Increased user l… Jul 22, 2017
ode_to_mssql.sln Removed Feature_Scripts Folder Aug 19, 2016
ode_to_mssql.sqlproj Updated the version. Change to BIML file to handle one source table f… Mar 2, 2018


Optimal Data Engine (ODE) Build 005.002.002

Copyright 2015 OptimalBI - Licensed under the GNU GENERAL PUBLIC LICENSE Version 3.0 (GPL-3.0)

The Approach:

Good architecture is modular, separating concerns across its components. This helps ensure each component does one job well, which in turn helps the components work together, delivering a valuable experience to users. Modular architecture is also easier to maintain. A useful data warehouse does not attempt to take data directly from source systems to end-users in one hit, instead it uses modular components we call data layers. While these modular components have many specific names (e.g. “staging”, “foundation”, “presentation”) what ties them together is their layered nature: each one does a single job well, before passing data on to another layer. Many of the ideological battles of the past (e.g. Inmon vs Kimball) were founded on an assumption that one methodology must rule them all. As soon as we think about a data warehouse in terms of layers, we are free to choose the optimal methodology for the job each layer is doing.

What Optimal Data Engine (ODE) does:

Using the Data Vault methodology, ODE breaks each complex part of your data processing into layers to make it easier and faster to manage and implement. It's a true agile methodology for building a Data Warehouse. ODE breaks the complexity into layers - to find out more visit http://www.ode.ninja/data-layers/

Requirements (All users):

To Install ODE, you need to have:

  • Access to a running Microsoft SQL Server (for example, Express Edition)
  • SQL Server Management Studio installed on a computer which you can access
  • Administrative rights to be able to create databases

If you wish to develop ODE further, we recommend:

Additional Code

The related project "optimal-data-engine-mssql-scripts" in the OptimalBI repository contains a set of scripts, which can be downloaded and used to assist in making use of ODE. https://github.com/OptimalBI/optimal-data-engine-mssql-scripts


Currently, ODE has two Branches available:

  • master and
  • develop

Master contains code which is known to be running in a production environment.

Develop contains the latest, locally tested version of the codebase.

Download and build instructions:

If you wish to develop the code base further, we recommend that you use the Visual Studio solution which is provided.

If you simply wish to build an ODE instance and use it, the following instructions will direct you:


  • Download a copy of the zip and extract to a temporary folder

Scripted Install

  • Open SQL Server Management studio and load ode_to_mssql_Create.sql from the extracted zip file. You will find it in the ReleaseScript folder.
  • Within SQL Server Management Studio > Click Query Menu > SQLCMD Mode
  • Within the script optionally change the DatabaseName and DefaultFilePrefix in the code to the preferred Configuration database name; default is ode_to_mssql. ODE_Config is recommended.
  • Click Execute from the toolbar. This should run successfully with a result of 'Update complete' on the Message panel
  • The Results panel of Management Studio query execution window should show 13 rows, which are the contents of the log4.JournalControl table.

Manual Install

  • This method expects that the user has a good understanding of Visual Studio Data Tools.
  • Download the Optimal Data Engine code from GitHub, into a directory, e.g. "C:\Git\OptimalDataEngine"
  • Using Visual Studio, open the ode_to_mssql.sln solution.
  • Publish the Database to your server - preferred name for the database is ODE_Config.

How to start using ODE for Data Vault modeling

Current functionality:

Details of the current functionality can be found here http://www.ode.ninja/category/features/


  • Untested on SQL Server editions prior to 2014. Release script is compiled for SQL Server 2016 edition.

Feedback, suggestions, bugs, contributions:

Please submit these to GitHub issue tracking or join us in developing by forking the project and then making a pull request!

Find out more:

Visit http://ode.ninja/ - this is where we keep our guides and share our knowledge. To find out more about OptimalBI and the work we do visit http://www.optimalbi.com or check out our blogs at http://optimalbi.com/blog/tag/data-vault/ for all the latest on our Data Vault journey. If you want to get in touch, you can email us at hey@optimalbi.com

Change log:

Build 005.002.002 on 20180302
	* Bug fixes
Build 005.002.001 on 20171028
	* Added connectivity to the Oracle as a data source via BIML scripts
Build 005.001.001 on 20170911
	* Added BIML scripts to generate SSIS package that loads data from another server
	* Implemented CDC (change data capture) load type
	* Removed limitation on release script length
Build 004.001.001 on 20170301
	* Added satellite columns
	* Added link columns and reorganised the hub-link relationship to enable Same-as links
	* Reorganised source table functionality
	* Added satellite functions feature for the calculated fields
	* Added table reconciliation functionality (left-right object match)
Build 002.002.001 on 20160819
    * Added the ability to pass a parameter from the scheduler, to a Staging Procedure, telling the Procedure what type of run to perform (Full or DELTA).
Build 002.001.001 on 20160805
    * Added the "is_retired" switch on dv_hub, dv_link, dv_sat, dv_source_table, dv_source_system and dv_column tables. This column is currently documentary only.
Build 001.001.001 on 20151020
	* Automated Install Script.
	* Added new schema (dv_config) which holds helper scripts to populate some configuration tables.
Build 001.001.001 on 20150928
	* Initial Build.