Module Management - Working with uspRegisterModule

This step-by-step guide shows you how to use the uspRegisterModule stored procedure to manage modules in your ExMeX Framework Core. We'll walk through practical examples from basic updates to advanced scenarios.

Features

• Enable (Activate) a module
• Disable (Inactivate) a module
• Define if a module uses a load window
• Control RowsRead functionality for complex views

Prerequisites

Before you start, ensure you have:

• Access to your ExMeX Framework Core database
• Appropriate permissions to execute stored procedures in the MetadataZoneCore schema
• Basic understanding of your module and batch structure

Step 1: Understanding Your Current Setup

First, let's explore what modules and batches you currently have in your system.

Find Your Existing Modules

-- View all available modules
SELECT TOP 10
    LoadProcessStepSK AS ModuleId,
    SourceModelCode,
    SourceEntityClassCode,
    TargetModelCode,
    TargetEntityClassCode,
    MappingRole,
    ProcessStepType
FROM MetadataZoneCore.HubLoadProcessStep
ORDER BY LoadProcessStepSK;

Find Your Available Batches

-- View all available batches
SELECT TOP 10
    LoadBatchId AS BatchId,
    BatchLabel
FROM MetadataZone.LoadBatch
ORDER BY LoadBatchId;

Check Current Module-Batch Assignments

-- View current module assignments
SELECT TOP 10
    belps.ProcessStepId AS ModuleId,
    belps.LoadBatchId AS BatchId,
    lb.BatchLabel,
    belps.ModuleIsActive,
    belps.ModuleUsesLoadWindow,
    belps.ModuleUsesRowsRead,
    belps.SourceEntity,
    belps.TargetEntity
FROM MetadataZoneCore.BatchExecuteLoadProcessStep belps
    INNER JOIN MetadataZone.LoadBatch lb ON belps.LoadBatchId = lb.LoadBatchId
ORDER BY belps.ProcessStepId;

Step 2: Your First Module Update (Using IDs)

Let's start with the simplest approach - updating a module using direct IDs.

Example: Activate a Module

-- Example values - replace with your actual IDs
DECLARE @ModuleId BIGINT = 1245  -- Your module ID
       ,@BatchId  BIGINT = 1001  -- Your batch ID
       ,@ReturnCode INT = NULL
;

-- Execute the update
EXEC @ReturnCode = MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec = 'U'      -- Update operation
    ,@ModuleId           = @ModuleId
    ,@BatchId            = @BatchId
    ,@ModuleIsActive     = 1        -- Activate the module
    ,@Debug              = 0        -- Disable debug output
;

-- Verify the change
SELECT @ReturnCode AS [Return Code],
       ModuleIsActive,
       ModuleUsesLoadWindow,
       ModuleUsesRowsRead,
       ModifiedBy,
       ModifiedAt
FROM MetadataZoneCore.BatchExecuteLoadProcessStep
WHERE ProcessStepId = @ModuleId
  AND LoadBatchId   = @BatchId;

Expected Result:

• Return code: 0 (success)
• ModuleIsActive is now 1
• ModifiedBy shows current user
• ModifiedAt shows current timestamp

Example: Configure Load Window Usage

-- Disable load window for a specific module
DECLARE @ModuleId BIGINT = 1245  -- Your module ID
       ,@BatchId  BIGINT = 1001  -- Your batch ID
       ,@ReturnCode INT = NULL
;

-- Execute the update
EXEC @ReturnCode = MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec = 'U'      -- Update operation
    ,@ModuleId           = @ModuleId
    ,@BatchId            = @BatchId
    ,@ModuleUsesLoadWindow  = 0     -- Disable load window
;

-- Verify the change
SELECT @ReturnCode AS [Return Code],
       ModuleIsActive,
       ModuleUsesLoadWindow,
       ModuleUsesRowsRead,
       ModifiedBy,
       ModifiedAt
FROM MetadataZoneCore.BatchExecuteLoadProcessStep
WHERE ProcessStepId = @ModuleId
  AND LoadBatchId   = @BatchId;

Example: Disable RowsRead for Complex Views

When working with complex views that contain intricate business logic, determining the exact row count can be time-consuming or impossible. In such cases, you can disable the RowsRead functionality:

-- Disable RowsRead for a module with complex view logic
DECLARE @ModuleId BIGINT = 1245  -- Your module ID
       ,@BatchId  BIGINT = 1001  -- Your batch ID
       ,@ReturnCode INT = NULL
;

-- Execute the update
EXEC @ReturnCode = MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec = 'U'      -- Update operation
    ,@ModuleId           = @ModuleId
    ,@BatchId            = @BatchId
    ,@ModuleUsesRowsRead = 0        -- Disable RowsRead functionality
;

-- Verify the change
SELECT @ReturnCode AS [Return Code],
       ModuleIsActive,
       ModuleUsesLoadWindow,
       ModuleUsesRowsRead,
       ModifiedBy,
       ModifiedAt
FROM MetadataZoneCore.BatchExecuteLoadProcessStep
WHERE ProcessStepId = @ModuleId
  AND LoadBatchId   = @BatchId;

Expected Result:

• Return code: 0 (success)
• ModuleUsesRowsRead is now 0
• Framework will write NULL to RowsRead column in MetadataZone.ProcessStepVapourTrail
• This indicates that the number of read rows is unknown to the framework

When to use this:

• Complex views with intricate business logic
• Views where the framework cannot determine the underlying table
• Cases where row count determination takes excessive time

Step 3: Working with Natural Keys (More Flexible)

When you don't know the exact IDs, you can use natural keys to identify your modules.

Example: Update Customer Landing Area Module

-- Using natural keys instead of numeric IDs
DECLARE @ModuleId              BIGINT        = NULL  -- Will be resolved automatically
       ,@BatchId               BIGINT        = NULL  -- Will be resolved automatically
       ,@SourceModelCode       NVARCHAR(128) = N'CRM_SOURCE'
       ,@SourceEntityClassCode NVARCHAR(128) = N'Customer'
       ,@TargetModelCode       NVARCHAR(128) = N'Staging_Layer'
       ,@TargetEntityClassCode NVARCHAR(128) = N'Customer_LDA'
       ,@MappingRole           NVARCHAR(256) = N'CustomerLandingMapping'
       ,@ProcessStepType       VARCHAR(50)   = 'DEF'
       ,@BatchLabel            NVARCHAR(256) = N'DailyCustomerLoad'
;

EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec     = 'U'
    ,@ModuleId               = @ModuleId OUTPUT          -- Returns resolved ID
    ,@BatchId                = @BatchId OUTPUT           -- Returns resolved ID
    ,@SourceModelCode        = @SourceModelCode
    ,@SourceEntityClassCode  = @SourceEntityClassCode
    ,@TargetModelCode        = @TargetModelCode
    ,@TargetEntityClassCode  = @TargetEntityClassCode
    ,@MappingRole            = @MappingRole
    ,@ProcessStepType        = @ProcessStepType
    ,@BatchLabel             = @BatchLabel
    ,@ModuleIsActive         = 1
    ,@ModuleUsesLoadWindow   = 1
    ,@ModuleUsesRowsRead     = 1
;

-- Check what IDs were resolved
PRINT 'Resolved ModuleId: ' + CAST(@ModuleId AS VARCHAR(20))
PRINT 'Resolved BatchId: ' + CAST(@BatchId AS VARCHAR(20))

-- Verify the change
SELECT @ReturnCode AS [Return Code],
       ModuleIsActive,
       ModuleUsesLoadWindow,
       ModuleUsesRowsRead,
       ModifiedBy,
       ModifiedAt
FROM MetadataZoneCore.BatchExecuteLoadProcessStep
WHERE ProcessStepId = @ModuleId
  AND LoadBatchId   = @BatchId;

Step 4: Batch Operations

Example: Disable All Modules in a Batch

-- Find all modules in a specific batch
DECLARE @TargetBatchLabel NVARCHAR(256) = N'NightlyCustomerBatch'

-- Show modules before changes
SELECT 'BEFORE UPDATE:' AS Status,
       ProcessStepId AS ModuleId,
       SourceEntity,
       TargetEntity,
       ModuleIsActive,
       ModuleUsesLoadWindow,
       ModuleUsesRowsRead
FROM MetadataZoneCore.BatchExecuteLoadProcessStep belps
    INNER JOIN MetadataZone.LoadBatch lb ON belps.LoadBatchId = lb.LoadBatchId
WHERE lb.BatchLabel = @TargetBatchLabel

-- Disable each module (you would typically use a cursor for this)
DECLARE @ModuleId BIGINT, @BatchId BIGINT

-- Get the batch ID first
SELECT @BatchId = LoadBatchId
FROM MetadataZone.LoadBatch
WHERE BatchLabel = @TargetBatchLabel

-- Example: Disable specific module in the batch
SET @ModuleId = 1005  -- Replace with actual module ID

EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec = 'U'
    ,@ModuleId           = @ModuleId
    ,@BatchId            = @BatchId
    ,@ModuleIsActive     = 0    -- Disable
    ,@Debug              = 0
;

-- Show modules after changes
SELECT 'AFTER UPDATE:' AS Status,
       ProcessStepId AS ModuleId,
       SourceEntity,
       TargetEntity,
       ModuleIsActive,
       ModuleUsesLoadWindow,
       ModuleUsesRowsRead
FROM MetadataZoneCore.BatchExecuteLoadProcessStep belps
    INNER JOIN MetadataZone.LoadBatch lb ON belps.LoadBatchId = lb.LoadBatchId
WHERE lb.BatchLabel = @TargetBatchLabel

Step 5: Error Handling and Troubleshooting

Handle Non-Existent Module/Batch Combination

-- This will fail intentionally to show error handling
DECLARE @ModuleId BIGINT = 99999  -- Non-existent module
       ,@BatchId  BIGINT = 88888  -- Non-existent batch
       ,@ReturnCode INT
;

EXEC @ReturnCode = MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec = 'U'
    ,@ModuleId           = @ModuleId
    ,@BatchId            = @BatchId
    ,@ModuleIsActive     = 1
;

-- Check the return code
IF @ReturnCode = 11
    PRINT 'ERROR: Module/Batch combination does not exist!'
ELSE IF @ReturnCode = 0
    PRINT 'SUCCESS: Module updated successfully!'
ELSE
    PRINT 'UNKNOWN ERROR: Return code = ' + CAST(@ReturnCode AS VARCHAR(10))

Verify Natural Key Resolution

-- Check if your natural keys can be resolved before updating
DECLARE @TestModuleId BIGINT
       ,@TestBatchId BIGINT
;

-- Try to resolve module ID
SELECT @TestModuleId = LoadProcessStepSK
FROM MetadataZoneCore.HubLoadProcessStep
WHERE SourceModelCode       = 'YOUR_SOURCE'
  AND SourceEntityClassCode = 'YOUR_ENTITY'
  AND TargetModelCode       = 'YOUR_TARGET'
  AND TargetEntityClassCode = 'YOUR_TARGET_ENTITY'
  AND MappingRole           = 'YOUR_MAPPING'
  AND ProcessStepType       = 'YOUR_TYPE'

-- Try to resolve batch ID
SELECT @TestBatchId = LoadBatchId
FROM MetadataZone.LoadBatch
WHERE BatchLabel = 'YOUR_BATCH_LABEL'

-- Check results
IF @TestModuleId IS NULL
    PRINT 'WARNING: Could not resolve Module ID with provided natural keys'
ELSE
    PRINT 'Module ID resolved to: ' + CAST(@TestModuleId AS VARCHAR(20))

IF @TestBatchId IS NULL
    PRINT 'WARNING: Could not resolve Batch ID with provided batch label'
ELSE
    PRINT 'Batch ID resolved to: ' + CAST(@TestBatchId AS VARCHAR(20))

Step 6: Understanding Load Windows

Load windows are a powerful feature for implementing incremental data loading. When enabled for a module, the framework automatically creates time-based windows for filtering data.

What happens when ModuleUsesLoadWindow = 1?

When you enable load windows for a module, the framework:

1. Writes a new load window entry to MetadataZoneCore.ModuleLoadWindow
2. Provides time-based filtering capabilities for incremental loads
3. Important: This setting applies to the module across ALL batches (module-level setting)

Example: Enable Load Windows for Customer Data

-- Enable load window for customer module (affects all batches containing this module)
DECLARE @ModuleId BIGINT = 1245
       ,@BatchId  BIGINT = 1001
;

EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec = 'U'
    ,@ModuleId           = @ModuleId
    ,@BatchId            = @BatchId
    ,@ModuleUsesLoadWindow = 1        -- Enable load window functionality
;

-- Verify the load window was created
SELECT
    mlw.ModuleId,
    mlw.LoadWindowFrom,
    mlw.LoadWindowTo,
    mlw.CreatedAt,
    belps.SourceEntity,
    belps.TargetEntity
FROM MetadataZoneCore.ModuleLoadWindow mlw
    INNER JOIN MetadataZoneCore.BatchExecuteLoadProcessStep belps
        ON mlw.ModuleId = belps.ProcessStepId
WHERE mlw.ModuleId = @ModuleId
ORDER BY mlw.CreatedAt DESC;

Current Load Window Implementation (Views)

Currently, load windows can be used in views for filtering PSA/HistoryZone data:

-- Example view using load window for incremental processing
CREATE VIEW [YourSchema].[CustomerIncrementalView] AS
SELECT
    st.CustomerId,
    st.CustomerName,
    st.LastModified,
    st.InscriptionTimestamp
FROM [SourceSchema].[Customer] st
INNER JOIN [MetadataZone].[ProcessStepDeltaExport] psde
    ON psde.SourceEntityClassCode = N'Customer'  -- Your table/view name
-- Apply load window filter
AND st.InscriptionTimestamp > psde.DeltaExportAssertionTimeFrom
AND st.InscriptionTimestamp <= psde.DeltaExportAssertionTimeBefore
WHERE st.IsActive = 1;

Planned Load Window Enhancement (Future Release)

In the upcoming release, modules will support direct variable-based filtering:

-- Future implementation: Direct module filtering with variables
FROM [SourceSchema].[Customer] st
WHERE 1=1
-- Apply load window variables (planned feature)
AND st.InscriptionTimestamp > @LoadWindowAfterTimestamp
AND st.InscriptionTimestamp <= @LoadWindowToTimestamp

Example: Configure Multiple Modules for Load Windows

-- Configure customer and product modules for incremental loading
DECLARE @CustomerModuleId BIGINT = 1245
       ,@ProductModuleId  BIGINT = 1246
       ,@BatchId          BIGINT = 1001
;

-- Enable load window for customer module
EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec = 'U'
    ,@ModuleId           = @CustomerModuleId
    ,@BatchId            = @BatchId
    ,@ModuleUsesLoadWindow = 1
;

-- Enable load window for product module
EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec = 'U'
    ,@ModuleId           = @ProductModuleId
    ,@BatchId            = @BatchId
    ,@ModuleUsesLoadWindow = 1
;

-- Check load window status for both modules
SELECT
    belps.ProcessStepId AS ModuleId,
    belps.SourceEntity,
    belps.TargetEntity,
    CASE belps.ModuleUsesLoadWindow
        WHEN 1 THEN 'Load Window Enabled'
        ELSE 'Load Window Disabled'
    END AS LoadWindowStatus,
    belps.ModifiedBy,
    belps.ModifiedAt
FROM MetadataZoneCore.BatchExecuteLoadProcessStep belps
WHERE belps.ProcessStepId IN (@CustomerModuleId, @ProductModuleId)
    AND belps.LoadBatchId = @BatchId;

Step 7: Advanced Scenarios

Conditional Updates Based on Current State

-- Only update if module is currently inactive
DECLARE @ModuleId BIGINT = 1001
       ,@BatchId  BIGINT = 2001
       ,@CurrentState BIT
;

-- Check current state
SELECT @CurrentState = ModuleIsActive
FROM MetadataZoneCore.BatchExecuteLoadProcessStep
WHERE ProcessStepId = @ModuleId
  AND LoadBatchId   = @BatchId

IF @CurrentState = 0
BEGIN
    PRINT 'Module is currently inactive, activating...'

    EXEC MetadataZoneCore.uspRegisterModule
         @RegisterModuleExec = 'U'
        ,@ModuleId           = @ModuleId
        ,@BatchId            = @BatchId
        ,@ModuleIsActive     = 1
        ,@Debug              = 1
    ;
END
ELSE
    PRINT 'Module is already active, no action needed.'

Bulk Configuration Script

-- Configure multiple modules with similar settings
DECLARE @BatchLabel NVARCHAR(256) = N'DailyCustomerLoad'

-- Module 1: Customer Data
EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec     = 'U'
    ,@ModuleId               = NULL
    ,@BatchId                = NULL
    ,@SourceModelCode        = N'CRM_SOURCE'
    ,@SourceEntityClassCode  = N'Customer'
    ,@TargetModelCode        = N'Staging_Layer'
    ,@TargetEntityClassCode  = N'Customer_LDA'
    ,@MappingRole            = N'CustomerLandingMapping'
    ,@ProcessStepType        = 'DEF'
    ,@BatchLabel             = @BatchLabel
    ,@ModuleIsActive         = 1
    ,@ModuleUsesLoadWindow   = 1
    ,@ModuleUsesRowsRead     = 1
;

-- Module 2: Product Data
EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec     = 'U'
    ,@ModuleId               = NULL
    ,@BatchId                = NULL
    ,@SourceModelCode        = N'CRM_SOURCE'
    ,@SourceEntityClassCode  = N'Product'
    ,@TargetModelCode        = N'Staging_Layer'
    ,@TargetEntityClassCode  = N'Product_LDA'
    ,@MappingRole            = N'ProductLandingMapping'
    ,@ProcessStepType        = 'DEF'
    ,@BatchLabel             = @BatchLabel
    ,@ModuleIsActive         = 1
    ,@ModuleUsesLoadWindow   = 1
    ,@ModuleUsesRowsRead     = 1
;

PRINT 'Bulk configuration completed!'

Parameter Behavior Overview

Understanding how each parameter works is crucial for effective module management:

ModuleIsActive (Batch-Dependent)

• Scope: Affects the specific module in the specified batch only
• Function: Controls whether the module executes in that particular batch
• Values:
  • 1 = Module is active and will execute
  • 0 = Module is disabled and will not execute

ModuleUsesLoadWindow (Module-Level)

• Scope: Affects the module across ALL batches (module-independent setting)
• Function: Enables/disables load window generation for incremental data processing
• Values:
  • 1 = Load window functionality enabled, creates entries in MetadataZoneCore.ModuleLoadWindow
  • 0 = Load window functionality disabled
• Important: When updated, ALL entries for this module across different batches are updated

ModuleUsesRowsRead (Batch-Dependent)

• Scope: Affects the specific module in the specified batch only
• Function: Controls whether the framework attempts to count rows read during processing
• Values:
  • 1 = Framework will attempt to determine row count from statistics
  • 0 = Framework will write NULL to RowsRead column in MetadataZone.ProcessStepVapourTrail
• Use Cases:
  • Disable for complex views where row counting is time-consuming
  • Disable when framework cannot determine underlying table structure
  • Disable for views with complex business logic

Step 9: Monitoring and Maintenance

Create a Module Status Report

-- Comprehensive module status report
DECLARE @BatchLabel NVARCHAR(256) = N'DailyCustomerLoad'
DECLARE @BatchId BIGINT

SELECT  @BatchId = LoadBatchId FROM MetadataZone.LoadBatch
    WHERE BatchLabel = @BatchLabel
;

SELECT
    lb.BatchLabel,
    belps.ProcessStepId AS ModuleId,
    belps.SourceEntity,
    belps.TargetEntity,
    CASE belps.ModuleIsActive
        WHEN 1 THEN 'Active'
        WHEN 0 THEN 'Inactive'
        ELSE 'Unknown'
    END AS Status,
    CASE belps.ModuleUsesLoadWindow
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
        ELSE 'Unknown'
    END AS UsesLoadWindow,
    CASE belps.ModuleUsesRowsRead
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
        ELSE 'Unknown'
    END AS UsesRowsRead,
    belps.ModifiedBy,
    belps.ModifiedAt
FROM MetadataZoneCore.BatchExecuteLoadProcessStep belps
INNER JOIN MetadataZone.BatchHierarchy bh
    ON belps.LoadBatchId = bh.LoadBatchId
INNER JOIN MetadataZone.LoadBatch lb
    ON belps.LoadBatchId = lb.LoadBatchId
WHERE (bh.LoadBatchIdMaster = @BatchId OR bh.LoadBatchId = @BatchId)
ORDER BY lb.BatchLabel, belps.SourceEntity;

Find Recently Modified Modules

-- Show modules modified in the last 24 hours
SELECT
    ProcessStepId AS ModuleId,
    SourceEntity,
    TargetEntity,
    ModuleIsActive,
    ModuleUsesLoadWindow,
    ModuleUsesRowsRead,
    ModifiedBy,
    ModifiedAt,
    DATEDIFF(HOUR, ModifiedAt, GETUTCDATE()) AS HoursAgo
FROM MetadataZoneCore.BatchExecuteLoadProcessStep
WHERE ModifiedAt >= DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY ModifiedAt DESC;

Common Pitfalls and Solutions

❌ Problem: "Module/Batch combination does not exist"

Solution: Verify your IDs or natural keys exist in the system first.

❌ Problem: Natural key resolution returns NULL

Solution: Check for exact string matches, including case sensitivity and whitespace.

❌ Problem: Permission denied

Solution: Ensure you have EXECUTE permissions on the MetadataZoneCore schema.

❌ Problem: Transaction rollback

Solution: Check the error details in the debug output and fix underlying data issues.

Best Practices

1. Always verify changes with the SELECT statement after each update
2. Test with non-production data first
3. Use transactions when updating multiple modules
4. Document your natural key combinations for future reference
5. Regular monitoring of module status and recent changes
6. Backup before bulk operations

Next Steps

Now that you understand how to use uspRegisterModule, you can:

• Integrate it into your deployment scripts
• Create automated module management procedures
• Build monitoring dashboards using the status queries
• Develop module lifecycle management processes

Remember: This procedure currently only supports Update operations. Add and Delete operations are planned for future releases.