Satellite Change Data Capture Functions - OptimalBI/optimal-data-engine-mssql GitHub Wiki

In the Data Vault satellite is a container for business concept's attributes and it has a SCD type 2 structure for storing the data. This means that history of record changes is stored in the satellite alongside with its current state. One have to use a set of conditions when querying the satellite to get the point in time dataset or a historical view. For example, we always add condition of dv_row_is_current = 1 and dv_is_tombstone = 0 to select the current state of the dataset and exclude those record which were deleted in source.

Table functions of the particular structure can help with the querying the Data Vault. There are two types of satellite functions available in the ODE Data Vault:

• get_Satellite_all - this type of function provides the full history of record changes within the specified timeframe. This function takes two parameters, the tmeframe start and timeframe end, both are datetimeoffset type. With the satellite's dataset, function returns a field dv_cdc_action that provides a CDC action flag, "D" if record has been deleted and "U" if record has been updated within the function's timeframe.
• get_Satellite_pit - this function provides a dataset as at point in time. The function takes one parameter, a point in time of the datetimeoffset type. If no point in time is provided, the function returns the current dataset.

Both functions return all the satellite's columns and the business key (or multiple key columns) from the hub the satellite belongs to.

Use the stored procedure from the Admin database to generate a table function for a single satellite. This procedure return the function creation/alteration code. Use an SSIS package to build or rebuild functions for all the active satellites from the Config.

Note that these functions could only be generated for ensemble satellites. Link satellites have different functionality.

Find out how to use these functions in business satellites data loads.