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

A Hub is essentially a Global Business Key.

In ODE Configuration, it consists of 2 Parts – a Table definition and a Key Definition.

The Table defines the Name and placement of the Hub.

The Key is separate to allow the designer flexibility to decide how the key will be held.

It is quite common to define all Hub Keys as Varchars, with multi part keys concatenated.

Alternatively, the Key can be defined as Multi Part, with correct Typing.

For Loading purposes, the Key(s) is hooked to the Source Column or Columns which feed the Hub.

There may be a number of these hooks – one for each Satellite attached to the Hub as well as one for each Link which references the Hub.

 

ODE Config Logical Model - Hub Focus

 

To define the Hub:

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

Parameters:

  • @hub_name – Your Chosen Hub Name. Normally it will be a Business Name e.g. “Customer”.
  • @hub_abbreviation – 4 character artefact of ODE. You can define this manually or use “SELECT [dbo].[fn_get_next_abbreviation] ()” to generate one.
  • @hub_schema – schema where the Hub will be placed e.g. “Hub”.
  • @hub_database – Database where the Hub will be placed e.g. “ODE_Vault”.
  • @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 Hub to be part of a specific release, provide the Release Number. If not, Zero.

The Procedure will return details of the Hub, together with it’s Surrogate Key, which will be useful for building further Configurations.

To add the Key to your Hub:

 

Note that, for a multi part key, run  dv_hub_key_insert for each column.

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

Parameters:

  • @hub_key – the Key of the Hub which you created above.
  • @hub_key_column_name – The name of the Key or Key Part if it is Multi Part.
  • @hub_key_column_type – the data type for the key, e.g. varchar.
  • @hub_key_column_length – key length e.g. 10
  • @hub_key_column_precision – for data types such as numeric
  • @hub_key_column_scale – for data type which have a scale such as datetime2
  • @hub_key_Collation_Name – for character columns with a special collation
  • @hub_key_ordinal_position – the position of this key within a Multi Part Key. Otherwise 1.
  • @release_number – If you want the new Key  to be part of a specific release, provide the Release Number. If not, Zero.

The column type details can be a bit confusing at times.


select types.name
,columns.max_length
,columns.precision
,columns.scale
,columns.collation_name
from sys.columns
inner join sys.types on types.user_type_id = columns.user_type_id
where columns.name = 'MyKeyName'

will give you the necessary information

 

Example

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

Returns a Hub_Key of 134

then:

EXECUTE [dbo].[dv_hub_key_insert]
 @hub_key      = 134
,@hub_key_column_name   = 'CustomerNumber'
,@hub_key_column_type   = 'varchar'
,@hub_key_column_length  = 15
,@hub_key_column_precision = null
,@hub_key_column_scale  = null
,@hub_key_Collation_Name  = null
,@hub_key_ordinal_position = 1
,@release_number    = 20150001

Next: Configure a New Link