Routine supporting multiple load types. More...

Detailed Description

Generic loader for multiple load types (UPDATE, SCD2, BITEMPORAL).

Handles all elements including metadata validation, PK checking, closeouts, locking, logging, etc.

The staging table must be prepared with a unique business key. For bitemporal this means a snapshot at both technical AND business time.

ASSUMPTIONS:

Base table has relevant datetime vars: 2xTechnical, 2xBusiness, 1xProcessed
Staging table omits Technical or Processed datetimes (has Business only)
Base table has no column names containing the string "___TMP___"
Base &tech_from variable is not nullable. This should always be the case anyway whenbuilding permanent bitemporal datasets.. But the point is that this field is used to identify new records after the initial left join from staging to base table.

NOTES:

All queries against BiTemporal tables need two filter conditions, as such:
```
where &bus_from LE [tstamp] LT &bus_to
  AND &tx_from LE [tstamp] LT &tx_to
```
One cannot use BETWEEN One cannot use &xx_from LE [tstamp] LE &xx_from (equivalent to above). Background: http://stackoverflow.com/questions/20005950/best-practice-for-scd-date-pairs-closing-opening-timestamps

Areas for optimisation

loading temporal history (currently experimental)

Supporting tables

Supporting tables must exist in the library specified in the dclib param.

MPE_DATALOADS

This table is updated every time a successful load occurs, and includes information such as:

library
dataset
message (supplied in the ETLSOURCE param)
new rows
deleted rows
changed rows
timestamp
the user making the load
the version of (this) macro used to make the load

Parameters

[in]	APPEND_DSN=	(APPENDTABLE) Name of STAGING table
[in]	CONFIG_TABLE=	(&dclib..MPE_CONFIG) The table containing library engine specific config. The following scopes are supported: DCBL_REDSH
[in]	LOADTYPE=	(BITEMPORAL) Supported types: TXTEMPORAL - loads a buskey with version times BUSTEMPORAL - loads buskey with bus + ver times UPDATE - updates a buskey with NO history
[in]	PROCESSED=	(0) This column obtains a current timestamp for changed records when loading the target table. Default is 0 (not set). If the target table contains a variable called PROCESSED_DTTM, and processed=0, then this column will be used for applying the current timestamp.
	RK_MAXKEYTABLE=	(mpe_maxkeyvalues) The maxkeytable to use (must exist in DCLIB)
[in]	PK=	Business key, space separated. Should NOT include temporal fields.
[in]	RK_UNDERLYING=	If supplied will generate an RK based on these (space separated) business key fields. In this case only ONE PK field should be supplied, which is assumed to be the RK. The RK field, plus underlying fields, should all exist on the base table. The underlying fields should exist on the staging table (the RK / PK field will be overwritten). The staging table should also be unique on its PK.
[in]	dclib=	(&dc_libref) The library containing DC configuration tables
[out]	outds_del=	(work.outds_del) Output table containing deleted records
[out]	outds_add=	(work.outds_add) Output table containing appended records
[out]	outds_mod=	(work.outds_mod) Output table containing changed records
[out]	outds_audit=	(0) Load detailed changes to an audit table. Uses the mp_storediffs.sas macro. Provide the base table here, to load.

Global Variables

The following global macro variables are used. These should be replaced by macro parameters in future releases.

dc_dttmtfmt

SAS Macros

Version: 9.3

Author: 4GL Apps Ltd.

Copyright: 4GL Apps Ltd. This code may only be used within Data Controller and may not be re-distributed or re-sold without the express permission of 4GL Apps Ltd.

Warning: multitemporal loads (bitemporal for multiple points in business time) are in experimental stage

Definition in file bitemporal_dataloader.sas.