Non Historized Satellite

The "Datavault4Coalesce: Non-Historized Satellite" node creates a non-historized satellite entity, attached to a non-historized link. It is typically used, when you dont want to store all your transactional attributes within the non-historized link. E.g. some attributes need to be splitted away because of privacy reasons, or to speed up queries against the non-historized link.

User Configuration

Config Name	Config Type	Explanation
Hashkey Column	string	Select the hashkey column belonging to the attached parent non-historized link.
Source is Single Batch?	Boolean	Activate this if your source data always only hold one load_date. By activating this toggle, a QUALIFY() statement is disabled, which will then increase the loading performance. But activate only, when you can ensure your data holds only one load date!

System Columns

No Sys Columns are added inside a NH-Satellite Node.

Example

In this example the Non-Historized Satellite 'LINEITEM_NS' is being created. The source table is 'STG_LINEITEM'. This Satellite is attached to the Non-Historized Link 'LINEITEM_NL'.

Create the Stage Node:

Create multiple Hash Columns, by selecting the following columns, and per selection, do 'right-click'->'Generate Hash Column'. Rename the output accordingly.

• HK_LINEITEM_L:
  • L_ORDERKEY
  • L_PARTKEY
  • L_SUPPKEY
  • L_LINENUMBER
• HK_ORDER_H:
  • L_ORDERKEY
• HK_PART_H:
  • L_PARTKEY
• HK_SUPPLIER_H:
  • L_SUPPKEY

Additionally, rename the column L_LINEITEM to DCK_LINEITEM. DCK stands for Dependant Child Key. The Lineitem number is required to uniquely identify a row in the source object LINEITEM. That's why it is also part of the Link Hashkey calculation.

This is how your Stage Node should look like (not all columns are shown):

Create the node:

• Within your Node STG_LINEITEM, multi-select the following columns:
  • HK_LINEITEM_L
  • LDTS
  • RSRC
  • L_SHIPDATE
  • L_COMMITDATE
  • L_RECEIPTDATE
  • L_SHIPINSTRUCT
  • L_SHIPMODE
  • L_COMMENT
• Then do 'right-click' -> 'Add Node' -> 'Datavault by Scalefree: Non-Historized Satellite'
• Rename the new Node to LINEITEM_NS
• Rearrange the columns in the new Node, so that the Link Hashkey is at the top, followed by LDTS and RSRC, and then the descriptive attributes:
  
  

• In the Config section on the right there is a tab called 'Data Vault'
• Expand this and you get a column selector 'Hashkey Column'
  • Select the hashkey of this Satellites parent Link, HK_LINEITEM_L.
    
    

• Create and run the node
  
  

---

Generated SQL Code of Example

CREATE OR REPLACE TABLE
"COALESCE_WORKSHOP"."CORE"."LINEITEM_NS" (
    "HK_LINEITEM_L" STRING NOT NULL,
    "LDTS" TIMESTAMP NOT NULL
    COMMENT 'The Load Date Timestamp (LDTS) describes when this data first arrived in the Data Warehouse.',
    "RSRC" STRING NOT NULL
    COMMENT 'The Record Source (RSRC) describes the source of this data.',
    "L_SHIPDATE" DATE NOT NULL,
    "L_COMMITDATE" DATE NOT NULL,
    "L_RECEIPTDATE" DATE NOT NULL,
    "L_SHIPINSTRUCT" VARCHAR (25) NOT NULL,
    "L_SHIPMODE" VARCHAR (10) NOT NULL,
    "L_COMMENT" VARCHAR (44) NOT NULL
)
COMMENT = 'Lineitem data as defined by TPC-H'

---

INSERT INTO
"COALESCE_WORKSHOP"."CORE"."LINEITEM_NS"
WITH
incoming AS (
    SELECT DISTINCT
    "STG_LINEITEM"."HK_LINEITEM_L" AS "HK_LINEITEM_L",
    "STG_LINEITEM"."LDTS" AS "LDTS",
    "STG_LINEITEM"."RSRC" AS "RSRC",
    "STG_LINEITEM"."L_SHIPDATE" AS "L_SHIPDATE",
    "STG_LINEITEM"."L_COMMITDATE" AS "L_COMMITDATE",
    "STG_LINEITEM"."L_RECEIPTDATE" AS "L_RECEIPTDATE",
    "STG_LINEITEM"."L_SHIPINSTRUCT" AS "L_SHIPINSTRUCT",
    "STG_LINEITEM"."L_SHIPMODE" AS "L_SHIPMODE",
    "STG_LINEITEM"."L_COMMENT" AS "L_COMMENT"
    FROM
    "COALESCE_WORKSHOP"."CORE"."STG_LINEITEM" "STG_LINEITEM"
    WHERE
    "LDTS" > (
        SELECT
        COALESCE(
            MAX("LDTS"),
            TO_TIMESTAMP('0001-01-01T00:00:01', 'YYYY-MM-DDTHH24:MI:SS')
        )
        FROM
        "COALESCE_WORKSHOP"."CORE"."LINEITEM_NS"
        WHERE
        "LDTS" != TO_TIMESTAMP('8888-12-31T23:59:59', 'YYYY-MM-DDTHH24:MI:SS')
    ) QUALIFY ROW_NUMBER() OVER (
        PARTITION BY
        "HK_LINEITEM_L"
        ORDER BY
        "LDTS"
    ) = 1
),
new_records AS (
    SELECT
    "SRC".*
    FROM
    incoming "SRC"
    WHERE
    NOT EXISTS (
        SELECT
        1
        FROM
        "COALESCE_WORKSHOP"."CORE"."LINEITEM_NS" "TGT"
        WHERE
        "SRC"."HK_LINEITEM_L" = "TGT"."HK_LINEITEM_L"
    )
)
SELECT
*
FROM
new_records