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.

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 staging database called ODE_Stage. Create schema “stage”. Create a stored procedure to select data from the source:

CREATE PROCEDURE [stage].[usp_Person_Person] 
AS
BEGIN

IF EXISTS
	(SELECT * FROM sys.objects WHERE object_id = object_id(N'[Stage].[Person_Person]') AND type IN (N'U'))
DROP TABLE [Stage].[Person_Person];

SELECT [BusinessEntityID]
,[PersonType]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[ModifiedDate]
INTO [Stage].[Person_Person]
FROM [AdventureWorks].[Person].[Person]
END

Create a release number:

EXECUTE [dv_release].[dv_release_master_insert] 
   @release_number = 20170123
  ,@release_description = 'Declare new stage area'
  ,@reference_number = 'US10'
  ,@reference_source = 'Jira'
GO

Register a stage database in ODE config:

EXECUTE [dbo].[dv_stage_database_insert] 
   @stage_database_name = 'ODE_Stage'
  ,@is_retired = 0
  ,@release_number = 20170123
GO

Register a stage schema in ODE config, use the stage database key assigned to stage database on the previous step:

EXECUTE [dbo].[dv_stage_schema_insert] 
   @stage_database_key = 1 -- copy the key created on the previous step
  ,@stage_schema_name = 'Stage'
  ,@is_retired = 0
  ,@release_number = 20170123
GO

Configure a schedule. Schedule is not a mandatory thing, it’s a feature. However, our helper script always schedule any new source table, so let’s create one:

EXECUTE [dv_scheduler].[dv_schedule_insert] 
   @schedule_name = 'Full_load'
  ,@schedule_description = 'General purpose Data Vault schedule'
  ,@schedule_frequency = 'Manual'
  ,@release_number = 20170123
GO

Run the helper script with the following parameters:

DECLARE
 @SatelliteOnly char(1)         = 'N'
,@SprintDate CHAR(8)            = '20170123'
,@ReleaseReference VARCHAR(50)  = 'N/A'
,@ReleaseSource VARCHAR(50)     = 'N/A'
,@SourceSystemName VARCHAR(128) = NULL
,@StageDatabase VARCHAR(128)    = 'ODE_Stage'
,@StageSchema VARCHAR(128)      = 'Stage'
,@StageTable VARCHAR(128)       = 'Person_Person'
,@StageSourceType VARCHAR(50)   = 'BespokeProc'
,@StageLoadType VARCHAR(50)     = 'Full'
,@StagePassLoadTypeToProc BIT   = 0
,@HubName VARCHAR(128)          = 'Person'
,@SatelliteName VARCHAR(128)    = 'Person'
,@VaultName VARCHAR(128)        = 'ODE_Vault'
,@ScheduleName VARCHAR(128)     = 'Full_load'
,@HubKeyNames table (column_name VARCHAR(128)
                          ,ordinal_position INT IDENTITY (1,1)) 
insert @HubKeyNames values('BusinessEntityID')

Also check if your installation of SQL Server supports columnstore indexes and compression and adjust these parameters in the script if required:

DECLARE
 @sat_is_columnstore  BIT = 1
,@sat_is_compressed   BIT = 0
,@hub_is_compressed   BIT = 1

Run this script to create physical tables in the Data Vault.

Start the scheduled job execution manually to populate ensemble with the data:

EXECUTE [dv_scheduler].[dv_process_schedule] 
   @schedule_list = 'Full_load'
GO

Give it a minute to run. That’s it, your data vault has first ensemble built and populated with the data.

What’s next? Use helper scripts to keep creating more ensembles and links. Explore ODE.ninja to find more about ODE features.