How To Create a Calculated Field

In ODE v4 we have introduced a new feature, we call it functions. Functions could be used in satellites to calculate attributes on the fly (during the load actually) from other satellite's columns, i.e. calculated columns.

At the moment most of the load logic is implemented in source stored procedures. These calculations could also be a part of stored procedure logic. However, using ODE functions makes logic repeatable and traceable.

We came to the idea of functions on the project with a data source that gave us a headache. In this source all the dates and times were stored as decimals in separate fields. We didn't want to transform these fields to proper datetime fields on the first load stage as Raw data should be stored as it is. That meant we'll have to write same convert function many times. So we have added function feature to ODE instead. That's how it works.

Create satellite as usual using the helper script. Don't create physical tables yet, add datetime function first. Register the function in config. ODE has a few built-in functions, and we will be extending the range. Also you can type in your own code using SQL notation. In this example we will be using ODE built-in function:

EXEC	[dbo].[dv_ref_function_insert]
@ref_function_name = 'FormatDatetime'
, @ref_function = '[dv_scripting].[dv_int_to_datetime] (''##1'', ''##2'')'
, @is_retired = 0
, @release_number = 2017010902

The following is an example of customer code in the reference function:

EXEC	[dbo].[dv_ref_function_insert]
@ref_function_name = 'FormatDatetime'
, @ref_function = 'CAST(##1 AS date)'
, @is_retired = 0
, @release_number = 2017010902

Add calculated field to your satellite and provide a reference to the function. Satellite key, previous satellite field ordinal position and function key could be found in the previous scripts output. Provide the existing date and time field names separated by comma to the function arguments parameter. You can use the same parameter multiple times, just refer it by the same sequence number lead by hashes.

EXEC	[dbo].[dv_satellite_column_insert]
@satellite_key = 4
,@column_name = 'CreateDatetime'
,@column_type = 'datetime'
,@column_length = 8
,@column_precision = 23
,@column_scale = 3
,@Collation_Name = NULL
,@satellite_ordinal_position = 12
,@ref_function_key = 1
,@func_arguments = 'CREATE_DTE,CREATE_TIME'
,@func_ordinal_position = 1
,@release_number = 2017010902

Don't forget to create a physical table and do a test load to check the data.