This article describes the process of configuring new Data Vault objects with earlier versions of ODE. Read this page instead if you use the latest version.

Sometimes developers don’t want to read much theory, they just too keen to start doing things and learn from the process. Here is a sample list of queries allowing to build your first ensemble with ODE in a few minutes. I still would recommend to read carefully step by step manual.

In this example we’ll create a Person hub and satellite with ODE. Let’s assume you already downloaded and installed ODE on your computer. As a source we’ll be using AdventureWorks table [Person].[Person].

Create a vault database ODE_Vault. Create schemas hub, sat and lnk.

Create a timevault database called ODE_Stage. Create schema “stage”. Create a view to select data from the source:

 USE [ODE_Stage]

CREATE VIEW [stage].[Person_Person] 
SELECT [BusinessEntityID] AS [PersonID]
FROM [AdventureWorks].[Person].[Person]

Create a release number:

 USE [ODE_Config]

EXECUTE dv_release.dv_release_master_insert
 @release_number       = 20150228
 ,@release_description = 'Add a Person ensemble'
 ,@reference_number    = 'US10'
 ,@reference_source    = 'Jira'

Configure a source system:

EXECUTE [dbo].[dv_source_system_insert]
 @source_system_name = 'AdventureWorks'
,@timevault_name     = 'ODE_Stage'
,@is_retired         = 0
,@release_number     = 20150228

Configure a source table. Note that source table name matches the timevault view name:

EXECUTE [dv_config].[dv_populate_source_table_columns]
 @vault_source_system   = 'AdventureWorks'
,@vault_source_schema   = 'stage'
,@vault_source_table    = 'Person_Person'
,@vault_source_table_load_type = 'Full'
,@vault_release_number  = 20150228

Configure a hub:

EXECUTE [dbo].[dv_hub_insert]
 @hub_name         = 'Person'
,@hub_abbreviation = 'h001'
,@hub_schema       = 'hub'
,@hub_database     = 'ODE_Vault'
,@is_retired       = 0
,@release_number   = 20150228

Copy the hub key number. Configure a hub column:

EXECUTE [dbo].[dv_hub_key_insert]
 @hub_key               = 1
,@hub_key_column_name   = 'PersonID'
,@hub_key_column_type   = 'int'
,@hub_key_ordinal_position = 1
,@release_number        = 20150228

Associate the hub key column to source column. To do this we need a hub column key from the last step and a PersonID column key (could be retrieved from dv_column table):

EXECUTE [dbo].[dv_hub_column_insert]
 @hub_key_column_key = 1
,@column_key         = 1
,@release_number     = 20150228

Configure a satellite:

EXECUTE [dbo].[dv_satellite_insert]
@hub_key       = 1
,@link_key     = 0
,@link_hub_satellite_flag = 'H'
,@satellite_name          = 'Person'
,@satellite_abbreviation  = 's001'
,@satellite_schema        = 'sat'
,@satellite_database      = 'ODE_Vault'
,@duplicate_removal_threshold = 0
,@is_columnstore          = 1
,@is_retired              = 1
,@release_number          = 20150228

Hook up source columns to the satellite. We need column keys from [dv_column] table and satellite key. Run the following script for each column:

EXECUTE [dbo].[dv_satellite_column_insert]
 @satellite_key  = 1
,@column_key     = 1
,@release_number = 20150228

Create vault tables:

EXECUTE [dbo].[dv_create_hub_table]
@vault_database  = 'ODE_Vault'
,@vault_hub_name = 'Person'

EXECUTE [dbo].[dv_create_sat_table]
@vault_database  = 'ODE_Vault'
,@vault_sat_name = 'Person'

Now load the data to vault tables:

EXECUTE [dbo].[dv_load_source_table]
@vault_source_system_name   = 'AdventureWorks'
,@vault_source_table_schema = 'stage'
,@vault_source_table_name   = 'Person_Person'
,@vault_source_load_type    = 'full'

That’s it, our data vault has first ensemble built and populated with the data.