Skip to content

DataFlow

DQX Logging and DQX

In CLOE we want to allow the greatest possible flexibility. Therefore DataQuality can be switched on or off. The same is true for logging.

Logging and error correction can be controlled down to column level. The following procedure has to be observed for a clean modeling:

  1. the corresponding "include_dqX" flag must always be set first. Otherwise all functions are deactivated.
  2. set the corresponding LogDQX flag to enable logging.

Prerequisites for error-free operation:

For Include_DQ2:

  1. perform a conversion with ConvertToDatatype on at least one column(ColumnMapping).

For LogDQ2:

  1. at least for one column(ColumnMapping) set logging with IsLoggingOnConvertError flag to log corresponding errors.

For LogDQ3

  1. on at least one column(LookupReturnColumnMappings) set logging with the IsLoggingOnLookupError flag to log corresponding errors.

Important Note: At least one column must always be selected! Otherwise errors will occur later.

Set the BK in CLOE

CLOE expects in each core column a column with the name "BK". This must contain the BK accordingly. No composite keys are possible.

The BK of the source tables can be a composite key and is composed of the BK defined in ColumnMapping in BK_Order columns.

How do I store columns that are only lookup parameters?

All columns must appear in the ColumnMapping. It is not currently intended to use columns only in the lookup. The correct procedure is to store the columns in ColumnMapping and set IsUpdate+IsInsert to False.

What options do I have with IsAddTenantToLookupParameter?

IsAddTenantToLookupParameter controls the tenant in the lookup BK. Lookup IsAddTenantToLookupParameter has three values:

Possible values
1
2
3
"CONCAT('CH', '-', A1WGRC, '-', A1WGRC)" IsAddTenantToLookupParameter: true
"CONCAT('0', '-', A1WGRC, '-', A1WGRC)" IsAddTenantToLookupParameter: false
"CONCAT(A1WGRC, '-', A1WGRC)" IsAddTenantToLookupParameter: null

Lookup - How do I get my lookup key into my DWH/Core target table?

CLOE automatically creates a mapping with the source lookup BK(defined in LookupLookupParameter) and as target FK_LOOKUPNAME_BK in addition to the mappings defined in LookupReturnColumnMapping.

Example:

Example JSON
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
"Lookup": [
            {
                "FK_Lookuptable_ID": "33519256-6B87-4897-AB20-535706173F59",
                "IsAddTenantToLookupParameter": true,
                "LookupColumnName": "BK",
                "LookupLookupParameter": [
                    {
                        "LookupParameterColumnName": "BudgetAgreementNo",
                        "OrderBy": 1
                    }
                ],
                "LookupReturnColumnMapping": [
                    {
                        "FK_Sinktable_ID": "06FE1C5C-895F-4E33-A86C-84BD9D1AE4D9",
                        "IsInsert": true,
                        "IsLoggingOnLookupError": false,
                        "IsUpdate": true,
                        "OnNull_Value": "0",
                        "ReturnColumnName": "ID",
                        "TargetColumnName": "FK_Budgetagreement_ID"
                    }
                ],
                "LookupValidFromColumnName": null,
                "LookupValidParameterColumnName": null,
                "LookupValidToColumnName": null,
                "Name": "Budgetagreement"
            }
]

Here, in addition to the FK_Budgetagreement_ID, an FK_Budgetagreement_BK would be filled with BudgetAgreementNo from the source table.

How can I build lookup chains / How can I use the return value of a lookup for another lookup?

This is currently not possible in CLOE. Postprocessing must be used for this.

The recommended way is to build a view that already does the necessary joins so that the information can be retrieved with a lookup.

How do I use a column for the BK without writing it to a target column in Core/DWH?

Simply leave the TargetColumnName empty and only populate the BK_Order.

Example:

Example JSON
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
{
                    "BK_Order": 1,
                    "Calculation": null,
                    "ConvertToDatatype": "nvarchar",
                    "FK_Sinktable_ID": "06FE1C5C-895F-4E33-A86C-84BD9D1AE4D9",
                    "IsInsert": true,
                    "IsLoadOnConvertError": false,
                    "IsLoggingOnConvertError": false,
                    "IsUpdate": true,
                    "OnConvertError_Value": null,
                    "OnNull_Value": "0",
                    "SourceColumnName": "BudgetAgreementNo",
                    "TargetColumnName": null
}

How do I determine the length or type of data types in the ConvertToDatatype?

Simply create the appropriate type in common_conversion.json e.g. nvarchar255 and create the DQ2 or the Convert Template accordingly (e.g. TRY_CONVERT(NVARCHAR(255), {{ column_name }})).