Steps described in this article are only applicable to earlier versions of ODE. Use the latest version for better ODE experience.

In ODE, A Data Source can be either a Table, provided by an outside process, or  a Stored Procedure, which contains a set of Rules.

There are 3 parts of a Source Definition –

  • A Source System,
  • A Source “Table” and
  • and a list of Source Column Definitions.

The Source System is documentary, used to group data sources logically.

For Raw Data, this is usually a meaningful Name of the Data Source.

For Rules, it may be “sysgen”, using standard data Vault terminology, or it can be used to group rules in a useful fashion.

Source Table is the name of a stage table, which will be loaded.

The Table may be a table of Raw Data, coming from an outside source, or it may be the output of a Stored Procedure, containing Business or Transformation Rules.

ODE Config Logical Model - Source Focus

If the correct Source System already exists in ODE, you will need the Key later.

Otherwise, create a new one using:

{Configuration Database}. [dbo].[dv_source_system_insert]

Parameters:

  • @source_system_name – a Descriptive Name for the Source e.g. “SAPB1”
  • @timevault_name – The name of the Database where the Data for this Source is staged.
  • @is_retired – Indicates whether the Source System is in use – 0 means in use, 1 means retired. This attribute is documentary only.
  • @release_number – If you want the new System to be part of a specific release, provide the Release Number. If not, Zero.

The Procedure will return a “source_system_key”, which will be needed below.

Next, Define the Source:

{Configuration Database}. [dbo].[dv_source_table_insert]

Parameters:

  • @system_key – the source_system_key from the above process
  • @source_table_schema – the schema where the source table will be staged e.g. “stg”.
  • @source_table_name – the stage table name e.g. “Stage_Customer”.
  • @source_table_load_type –  “Full” or “Delta”. The is the Default processing mode. Delta will not End date Keys in the Satellites, which have no matching key in the Stage.
  • @source_procedure_schema – If the Load is supported by some logic, this is where the Stored Procedure holding that logic exists.
  • @source_procedure_name – The Stored Procedure Name e.g. usp_Customer.
  • @is_retired – Indicates whether the Source System is in use – 0 means in use, 1 means retired. This attribute is documentary only.
  • @release_number – If you want the new Source Table to be part of a specific release, provide the Release Number. If not, Zero.

The Procedure will return a “source_table_key”, which will be needed below.

Next, the columns, which will be supplied by the Source Table need to be define.

Call the following Procedure for each Column:

{Configuration Database}.[dbo].[dv_column_insert]

  • @table_key – the source_table_key from the above process.
  • @column_name – the name of the column.
  • @column_type – the data type for the column, e.g. varchar. This is a technical column and reflects the “name” column in sys.types.
  • @column_length – column length in bytes (not characters). This is a technical column and reflects the “max_length” column in sys.columns.
  • @column_precision –  for data types such as numeric. This is a technical column and reflects the “precision” column in sys.columns.
  • @column_scale – for data type which have a scale such as datetime2. This is a technical column and reflects the “scale” column in “sys.columns”.
  • @Collation_Name – for character columns with a special collation. This is a technical column and reflects the “collation_name” column in “sys.columns”.
  • @bk_ordinal_position – not used set to 1.
  • @source_ordinal_position – used to record the column’s ordinal position at source.
  • @satellite_ordinal_position – The Ordinal Position the column will have in its satellite
  • @is_source_date – normally 0. In that case, “source_date_time” in the Satellites will be populated with the server time. If 1, the column will be used to populate “source_date_time”.
  • @discard_flag – to mark a column which is not used.
  • @is_retired – Indicates whether the Source System is in use – 0 means in use, 1 means retired. This attribute is documentary only.
  • @release_number – If you want the new Column to be part of a specific release, provide the Release Number. If not, Zero.

Example:

EXECUTE [dbo].[dv_source_system_insert]
 @source_system_name = 'SAPB1'
,@timevault_name = 'ODV_Stage'
,@is_retired = 0
,@release_number = 2015001
EXECUTE [dbo].[dv_source_table_insert]
 @system_key = source_system_key from above
,@source_table_schema = 'stg'
,@source_table_name = 'SAPB1_Customer'
,@source_table_load_type = 'Full'
,@source_procedure_schema = NULL
,@source_procedure_name = NULL
,@is_retired = 0
,@release_number = 2015001
EXECUTE [dbo].[dv_column_insert]
 @table_key = source_table_key from above
,@column_name = 'CustomerNumber'
,@column_type = 'varchar'
,@column_length = '25'
,@column_precision = null
,@column_scale = null
,@Collation_Name = null
,@bk_ordinal_position = 1
,@source_ordinal_position = 1
,@satellite_ordinal_position = 1
,@is_source_date = 0
,@discard_flag = 0
,@is_retired = 0
,@release_number = 2015001
EXECUTE [dbo].[dv_column_insert]
 @table_key = source_table_key from above
,@column_name = 'CustomerName'
,@column_type = 'varchar'
,@column_length = '100'
,@column_precision = null
,@column_scale = null
,@Collation_Name = null
,@bk_ordinal_position = 1
,@source_ordinal_position = 2
,@satellite_ordinal_position = 2
,@is_source_date = 0
,@discard_flag = 0
,@is_retired = 0
,@release_number = 2015001
There is a helper procedure, which can load up all Columns, for a Source Table, using the Databases’ meta data to define them.
This is a useful labour saver.
To use this method, first create a model Stage Table.
Then call:

{Configuration Database}. [dv_config].[dv_populate_source_table_columns]

Parameters:

@vault_source_system – source_system name for the source_table e.g. “SAPB1”
@vault_source_schema – schema where the stage table has been created (and will continue to exist) e.g. “src”
@vault_source_table – name of the source_table e.g. “Customer”
@vault_source_table_load_type  –  “Full” or “Delta”. The is the Default processing mode. Delta will not End date Keys in the Satellites, which have no matching key in the Stage.
@vault_source_procedure_schema – If there is a Procedure to build the Stage Table, the schema where is exists e.g. “stg”.
@vault_source_procedure_name – Name of the Procedure which supports the Table.
@vault_rerun_column_insert – normally 0. If set to 1, all existing columns related to the Source Table will be deleted and recreated.
@vault_release_number – If you want the new Column to be part of a specific release, provide the Release Number. If not, Zero.

Example:


EXECUTE [dv_config].[dv_populate_source_table_columns]
 @vault_source_system = 'SAPB1'
,@vault_source_schema = 'stg'
,@vault_source_table = 'Customer'
,@vault_source_table_load_type = 'Full'
,@vault_source_procedure_schema = 'stg'
,@vault_source_procedure_name = 'usp_SAPB1_Customer'
,@vault_rerun_column_insert = 0
,@vault_release_number = 2015001

Hook the Data Source up to the Vault Structures