uspGetLoadWindow

Purpose

The stored procedure uspGetLoadWindow is designed to retrieve a load window. This allows for the evaluation and management of data logistics processes in integration layers or presentation data layers, ensuring that specific data loads are using the correct data delta.

Motivation

The need for a defined load window arises in data logistics to ensure that data integration processes are controlled, time-bound, and consistent. The load window helps manage data flow effectively by preventing data overlaps, ensuring synchronization between various modules.

Applicability

The uspGetLoadWindow pattern is applicable in the following scenarios:

• In data logistics processes, particularly in integration and presentation layers.
• In any scenario requiring a defined time range for data retrieval or processing.
• When it is necessary to synchronize data loads across different modules, or processes.

Structure

DECLARE @LoadWindowAfterTimestamp   DATETIME2(7)
       ,@LoadWindowToTimestamp      DATETIME2(7);

EXEC MetadataZoneCore.uspGetLoadWindow
    ,@AuditTrailId             = <your audit trail id>
    ,@ModuleId                 = <your module id>
    ,@BatchId                  = <your batch id>
    ,@LoadWindowColumnCode     = <optional>
    ,@LoadWindowAfterTimestamp = @LoadWindowAfterTimestamp OUTPUT
    ,@LoadWindowToTimestamp    = @LoadWindowToTimestamp OUTPUT
    ,@Debug                    = <optional>
;
SELECT @LoadWindowAfterTimestamp, @LoadWindowToTimestamp;

Input Parameters:

• Audit Trail Id: A unique identifier to track the auditing of the data process.
• Module Id: Identifies the specific module within the ExMeX framework.
• Batch Id: Optional; used when the process involves batched data loads.
• Load Window Column Code: Optional; specifies the column to which the load window applies.
• Debug Indicator (0/1): Controls whether debugging output is enabled, 0 or 1.

The Load Window Column Code variable is globally set within the ExMeX core framework but can be set for individual moduls as needed.

Output:

• Load Window After Timestamp: The start of the load window (open period).
• Load Window To Timestamp: The end of the load window (closed period).

The load window represents an period ]After Timestamp, To Timestamp] that determines the data delta which can be processed. This ensures that the processed data is within the intended load window, preventing the use of stale or premature data.

Return Types

Returns integer.

Return Values

Return value	Meaning
0	Successful execution
>0	Any SQL Server error

Considerations and Consequences

• Performance Implications: The usage of a load window can improve query performance by limiting the scope of data being retrieved or processed. However, incorrectly configured load windows may result in incomplete data loads or missed updates.
• Scalability: For large data systems, the use of load windows allows for more granular control, making the system scalable by limiting the data processed in each operation.

Implementation Guidelines

1. Using variables (recommended):

DECLARE @LoadWindowAfterTimestamp   DATETIME2(7)
       ,@LoadWindowToTimestamp      DATETIME2(7);

EXEC MetadataZoneCore.uspGetLoadWindow
    ,@AuditTrailId             = <your audit trail id>
    ,@ModuleId                 = <your module id>
    ,@BatchId                  = <your batch id>
    ,@LoadWindowAfterTimestamp = @LoadWindowAfterTimestamp OUTPUT
    ,@LoadWindowToTimestamp    = @LoadWindowToTimestamp OUTPUT
    ;
[...]
[...]
[...]
AND st.InscriptionTimestamp >  @LoadWindowAfterTimestamp
AND st.InscriptionTimestamp <= @LoadWindowToTimestamp

2. Using join with existing system:

[...]
INNER JOIN [TEDAMOH-ExMeX-Release-Feature].MetadataZone.ProcessStepDeltaExport psde
ON psde.SourceEntityClassCode = N'<your view/table>'
-- Apply load window
AND st.InscriptionTimestamp >  psde.DeltaExportAssertionTimeFrom
AND st.InscriptionTimestamp <= psde.DeltaExportAssertionTimeBefore

This pattern outlines a clear method of retrieving and applying a load window, ensuring that data loads occur within controlled periods.

Example of creating a load window

DECLARE @LoadWindowAfterTimestamp   DATETIME2(7)
       ,@LoadWindowToTimestamp      DATETIME2(7);

EXEC MetadataZoneCore.uspGetLoadWindow
     @AuditTrailId             = 58667
    ,@ModuleId                 = 1910
    ,@BatchId                  = 1049
    ,@LoadWindowAfterTimestamp = @LoadWindowAfterTimestamp OUTPUT
    ,@LoadWindowToTimestamp    = @LoadWindowToTimestamp OUTPUT;

SELECT @LoadWindowAfterTimestamp, @LoadWindowToTimestamp;