ODE attaches all Processing Rules to “Source Tables”.

Each Source Table can have a number of Source Table Versions, only one of which may be “Current”.

The rest are retained for history and tracking purposes.

As data is loaded into the Vault, the objects – Hubs, Links and Satellites are annotated with the key of the Source Table Version, from which the Data was loaded.

In this way, it is possible to audit what process was used to load each row in each of the ODE objects.

ODE can currently accommodate 3 types of Source Tables (and therefore “Rules”):

  1. Bespoke Procedures (this is the only method used by NZTA at this time);
  2. Externally Staged Data and
  3. Left Right Comparisons.

Externally Staged Data has no Rule detail and does not need to be versioned.

To version a Bespoke Procedure, ODE needs to:

  1. Set the Current Source Table Version to not be Current;
  2. Create a new Source Table Version, which is Current and which refers to a new Procedure.

 

To version a Left Right Comparison:

  1. Set the Current Source Table Version to not be Current;
  2. Create a new, Current Source Table Version;
  3. Copy all Matching Parameters from the Prior Source Table Version to the Current Source Table Version;
  4. Make required changes to the now Current Parameters.

 

To assist with this process, there is an Admin Stored Procedure called “ODE_version_source_rule”.

This is a “Helper” procedure, and not a part of ODE base code.

As such it may be altered to suit requirements.

Currently, “ODE_version_source_rule” will deal with Bespoke Procedures only.

More Types to follow.

When changing a Bespoke Procedure, it is recommended to make a new version of the Stored Procedure, with a new name, in order to maintain a record of the code as it was at different times during the life of the Load.

“ODE_version_source_rule” allows the developer to supply the name of the new Stored Procedure.

The advised process is however to NOT provide a Stored Procedure name.

In this case, “ODE_version_source_rule” will take the prior Stored Procedure Name and manufacture a new name by suffixing it with “_nnnn” where nnnn is a Zero Padded string created from the new “Source Version”

It is then up to the Developer to create the correctly named Stored Procedure.

For Example, over time a Procedure name may be:

usp_Load_Customer_0001

usp_Load_Customer_0002

usp_Load_Customer_0003.

The following sample script Versions a Bespoke Procedure Rule:


USE [ODE_Admin]

GO

EXECUTE [dbo].[ODE_version_source_rule]

@SourceUniqueName       = 'Load_Customer_Master'

,@SourceType             = 'BespokeProc'

,@SprintDate             = 20170226

,@ReleaseReference       = 'CU-1234'

,@ReleaseSource          = 'Jira'

The helper Stored Procedure – ODE_version_source_rule, can be downloaded from Github.

You may also want to check articles about why do we record the source of each row in a satellite, why do we capture all column detail in configuration, and what are recommended ODE Data Vault naming standards.