DRAFT ETL+ Load Replace Logic
DRAFT Step by step technical description of the ETL+ Load Replace process. Work in Progress
Load Replace Panel UI
Load Replace Settings / Parameters
Variables used.
<
source field
> >= <target T-SQL
><source-table> – source table or tables named in the ETL SQL Statement panel.
The
source field
/left hand entry is a name of a field/column in the <source-table>.
<target-table> – corresponding target table. The table currently selected in the Target Objects panel.
See also ETL+ Load Replace
Process
TargetRowCount
= Obtain the number of rows in the <target-table>.
Zero means the table has zero rows or the table has yet to be created in the DW.IF
TargetRowCount
= 0 AND Target T-SQL string begins with “MAX(“ THENRun Load All
End Load Replace process now.
Create a T-SQL query from the T-SQL expression in Target T-SQL that results in a single value resolved from Target T-SQL.
(In short, evaluate the Target T-SQL parameter and resolve into a single value for use in latter steps)
Depending on the syntax of Target T-SQL the T-SQL query created is either:SELECT
<Target T-SQL>FROM
<target-table>SELECT
<Target T-SQL>
Save the results of the T-SQL SELECT statement into a variable named
SQLResults.
low-value
variable used in step #6 below.
Determine the low-value for the date type of <source-table>.source field
. For instance, the low-value of the smallint data type is = -32,768.
PSEUDO CODE
low-value
= Low_Value( Data_Type( <source-table>.source field
))
WHEREData_Type – a function that returns the name of the datatype of the source field/row named in the parm.
The datatype for all source fields is shown on the Design page.
Low_Value – a function that returns the lowest value for the datatype named in the parm.
For instance Low_Value(T-SQL_smallint) = -32,768
Calculate
compare-value
variable used in the step below.
IFSQLResults
=NULL
AND Target T-SQL NOT = “NULL”
THEN
compare-value
=low-value
! see NOTE below
ELSE
compare-value
=SQLResults
Filter and Extract Source Table into temp/staging table (pseudo query language)
Simple Version. ! pseudo query language
Extract INTO staging-table ! pseudo query language
FROM <source-table>
WHERE <source-table>.source field
>=compare-value
When ETL SQL Statement includes a
JOIN
orUNION
clause. ! pseudo query language
Extract INTO staging-table ! pseudo query language
FROM <source-table>
WHERE <source-table>.source field
>=compare-value
<equivalent to JOIN or UNION clause>
Delete all rows in
target T-SQL
that meet the filter criteriaT-SQL: DELETE FROM target-table WHERE target-table.
source-field
>=compare-value
T-SQL: DELETE FROM target-table WHERE target-table.target T-SQL >= SQLResultsBoth the <source-table> and <target-table> tables must have a field or column as named in the
source-field
parameter.
Add/Insert records from staging-table into target-table.
T-SQL: INSERT FROM staging-table INTO target-table.
<source-table> – source table named in the ETL SQL Statement panel.
<target-table> – corresponding target table. The table currently selected in the Target Objects panel.
Step 6 - Assume that
<field> >= low-value
would work consistently with all Source Object query systems/data access layers whereas<field> >= NULL
might not.
T-SQL: DELETE FROM target-table WHERE target-table.target T-SQL >= SQLResults
Related Pages
ETL+ Load Replace – update & eventual replacement for Load Replace content in ETL+ Table Load Types
Â
Â
Â
Â
Proposed Functions
LOADMAX Option
Concept: Create a new, ETL+ only function called LOADMAX
that can be used in place of the MAX
function in Target T-SQL.
Replace step # 2 above with
2. IF TargetRowCount
= 0 AND Target T-SQL string begins with “LOADMAX(“ THEN
Run Load All
End Load Replace process now.
ELSE
Change “LOADMAX(
" to "MAX(
" in Target T-SQL string.