Skip to content

How to Dataflow

Use Case 1 - How to setup a DataFlow?

Prerequisites - DataFlow

  • Created src/stage table in the stage layer of your CLOE Model
  • Created sink/core table in the core layer of your CLOE Model
  • DDL deployed to database

Setting up the DataFlow

  • Provide a name for the pipe
  • Select the versioning-template to be used e.g. "MERGE (Field2Field UPDATE) \w versioning"
  • Under FK_Sinktable_ID provide the sink table on the core level
  • Under FK_Sourcetable_ID provide the sourcetable from which the data is taken and set "IsActive" to 1 (or switch on)

Use Case 2 -How to setup Column Mapping?

In the section "Column Mapping" provide column information that should be taken from the source table and written to the sink table. Data type conversions and calculations can be provided.

Regular Column Mapping

  • As the sink table provide the core table
  • If necessary provide a datatype that the data should be converted to. Make sure, that the selected data type is present in the "Data Type Template"
  • Provide either: The "SourceColumnName" or the "Calculation" as SQL
  • Activate NULL value treatment and define a default replacement value or use the global one
  • Define whether to use error handling and which kind
  • Activate the switches for "IsUpdate" and "IsInsert"

Special Case - How to construct the BK

  • To construct the BK you need to provide source column(s) in the column mapping. You do NOT need to provide a "SinkColumnName" as BK
  • If you are working with just one column as the basis for the BK, set "OrderBy" to '1' and leave the field "SinkColumnName" empty
  • If you are working with multiple columns to construct the BK, you can select the order in which they appear in the BK via the "OrderBy" field. If in addition you want to provide the column information on its own, just set the field "SinkColumnName" to the desired column name.

Use Case 3 - How to set up columns to be versioned with Slowly Changing Dimensions

Prerequisites - SCD

  • Created SCD table with "Level" set to 'ver' in your CLOE Model

Setting up SCD Columns

  • As the sink table provide the versioning table
  • Proceed as described under "How to create a standard column mapping?"

Use Case 4 - How to create Lookups?

In the section "Lookups" provide information on columns that need to be populated from other (core) tables in the database. One Lookup is a link to one other table.

  • Provide a name for the lookup (just for overview)
  • In "LookupColumnName" provide the name in the lookup table that is used to create the join
  • In "FK_Lookuptable_ID" provide the lookup table
  • Provide the column(s) used for the join from the source table as "LookupParameterColumnName". For a single column join, set "OrderBy" to '1', other wise use "OrderBy" to define the order in which the columns a concatenated for the join
  • Define Return Column Mapping: Provide the column name from the lookup table as "LookupParameterColumnName", the "SinkColumnName", the core table as sink table (Lookups cannot be versioned - so do not provide the versioning table here), a rule what to do on NULL values and lastly information for "IsInsert" and "IsUpdate" as well as error handling